Snowflake, Data Marketplace,

Home » Snowflake » Location analytics on Snowflake with OpenStreetMap, US property data, and our ready made Geography Dimension

Location analytics on Snowflake with OpenStreetMap, US property data, and our ready made Geography Dimension

by Uli Bethke

Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.

Any questions or comments for Uli? Connect with him on LinkedIn.

Published on February 19, 2021
Updated on November 20, 2024

We have made available a ready made Geography Dimension on the Snowflake Data Marketplace. The marketplace is one of the components of the Snowflake Data Cloud. It covers the first to fifth order administrative divisions and cities in all major countries. This can be used for drilling down / rolling up in any type of location analytics.

In this blog post we will integrate the Geography Dimension with administrative boundary data which we derived from OpenStreetMap (OSM). This data set is also available on the Snowflake data marketplace. The OSM data contains geo coordinates as polygons and we can perform geo-lookups for latitude / longitude pairs.

Using US property data we can look up the geo-coordinates (latitude / longitude) to locate the US County, US State and other administrative divisions of the property. We can then use the Geography Dimension to drill up and down on this data.

Let us first have a look at the data sets in detail before we show you some examples.

Geography Dimension

The geography dimension on the Snowflake Marketplace contains the data from country to city level including the first to fifth order administrative divisions in between and it covers all major countries. A detailed documentation on this is available.

The core tables in the Geography Dimension Dataset are as given below: –

COUNTRY

This is the highest level of information available in the dataset. The attributes of the country such as iso codes, fips codes, capital, area, population, continent, currency, postal code format, phone dialing code, languages, neighbouring countries are all included in this table.

ADMIN TABLES

The admin tables cover the administrative divisions within a country. We have created five main admin tables namely ADMIN_1, ADMIN_2, ADMIN_3, ADMIN_4 AND ADMIN_5 which corresponds to the first to fifth order administrative divisions in a country. In addition to this tables ADMIN_3_REL1, ADMIN4_REL1, ADMIN4_REL2, ADMIN5_REL1, ADMIN5_REL2, ADMIN5_REL3 are also available which consists the administrative divisions which skipped one or more of it’s parent admin-orders.

CITY_500

The city data is the lowest level of information available in the dataset. It covers all cities around the world with a population > 500.

OSM Data

Sonra has made available OpenStreetMap (OSM) data sets on the Snowflake DataMarketplace. The data is available in each Snowflake region.

We have derived the administrative divisions of the major countries as documented in the OSM documentation on administrative boundaries.

Here is an example of what is contained in the data set.

The GEO field contains the coordinates of the admin boundaries as polygons or multipolygons. The RANK field indicates the hierarchy of the different boundary/administrative divisions such as country borders, state borders and county borders.

You may like to read: Open Street Map Documentation (PDF)

Property data

We also have scraped some US property data for this demo to show how the in-house data of a property website or agency can be enriched with geo information for advanced location analytics.

The property data contains the following data points.

Column	Description
ID	Unique Identifier
NAME	Name of the property
STATE	The state where the property is located
COUNTY_OR_EQUIVALENT	The county or equivalent subdivision of state where the property is located
RATE	The rate of the property in dollars
AREA_IN_ACRES	The total area in acres
COORDINATES	The geo coordinates of property (lat / lon)

The data sets in action

Now let us have a look at how we can enrich our property data with admin boundary information and data from OSM.

For the demonstration, we will make use of Sonra’s OpenStreetMap United States (USA) and Geography Dimension data sets from Snowflake’s Data Marketplace.

Now let us consider the different analysis that could be done.

Get the boundary coordinates of second order administrative divisions of the US (Counties and equivalents).

This can be done by making use of the ADMIN_2 table of Geography dimension and the OSM Reverse Geocoding table BOUNDARY.

In the same way the exact boundary for any admin order in the Geography Dimension can be obtained by joining it with the BOUNDARY table choosing the right admin level(RANK).

In this example we consider the RANK 6 in the BOUNDARY table as indicates the state counties and county equivalents for US. Refer the link for more details on choosing the appropriate RANK.

SELECT
	AD2.GEO_ID,
	AD2.ADMIN2_CD,
	AD2.PARENT_ADMIN_CD,
	AD2.COUNTRY_CD,
	AD2.ASCII_NAME,
	BOUNDARY.COORDINATES COORDINATES
FROM
	(
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_2
	WHERE
    	COUNTRY_CD = 'US') AD2
LEFT JOIN (
	SELECT
    	ID,
    	NAME,
    	ST_COLLECT(GEO) COORDINATES
	FROM
    	V_OSM_USA_BOUNDARY
	WHERE
    	RANK = 6
	GROUP BY
    	ID,
    	NAME) BOUNDARY ON
	AD2.ASCII_NAME = BOUNDARY.NAME
	AND ST_COVERS(BOUNDARY.COORDINATES,
	ST_POINT(AD2.LONGITUDE,
	AD2.LATITUDE));

SELECT

AD2.GEO_ID,

AD2.ADMIN2_CD,

AD2.PARENT_ADMIN_CD,

AD2.COUNTRY_CD,

AD2.ASCII_NAME,

BOUNDARY.COORDINATES COORDINATES

FROM

(

SELECT

FROM

V_GEONM_ADMIN_2

WHERE

COUNTRY_CD = 'US') AD2

LEFT JOIN (

SELECT

ID,

NAME,

ST_COLLECT(GEO) COORDINATES

FROM

V_OSM_USA_BOUNDARY

WHERE

RANK = 6

GROUP BY

ID,

NAME) BOUNDARY ON

AD2.ASCII_NAME = BOUNDARY.NAME

AND ST_COVERS(BOUNDARY.COORDINATES,

ST_POINT(AD2.LONGITUDE,

AD2.LATITUDE));

2. List of second-order administrative divisions(counties) and number of general/departmental stores and malls.

This can be achieved by making use of ADMIN_2 from Geography dimension, BOUNDARY table from OSM Reverse Geocoding and the SHOP_GENERAL_STORE_DEPARTMENT_STORE_MALL table of OSM.

SELECT
	AD2.GEO_ID,
	AD2.ADMIN2_CD,
	AD2.PARENT_ADMIN_CD,
	AD2.COUNTRY_CD,
	AD2.ASCII_NAME,
	COUNT(SHOP.ID) CNT_SHOP
FROM
	(
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_2
	WHERE
    	COUNTRY_CD = 'US') AD2
LEFT JOIN (
	SELECT
    	ID,
    	NAME,
    	ST_COLLECT(GEO) COORDINATES
	FROM
    	V_OSM_USA_BOUNDARY
	WHERE
    	RANK = 6
	GROUP BY
    	ID,
    	NAME) BOUNDARY ON
	AD2.ASCII_NAME = BOUNDARY.NAME
	AND ST_COVERS(BOUNDARY.COORDINATES,
	ST_POINT(AD2.LONGITUDE,
	AD2.LATITUDE))
LEFT JOIN
V_OSM_USA_SHOP_GENERAL_STORE_DEPARTMENT_STORE_MALL
SHOP ON
	ST_COVERS(BOUNDARY.COORDINATES,ST_CENTROID(SHOP.COORDINATES))
GROUP BY
	AD2.GEO_ID,
	AD2.ADMIN2_CD,
	AD2.PARENT_ADMIN_CD,
	AD2.COUNTRY_CD,
	AD2.ASCII_NAME;

SELECT

AD2.GEO_ID,

AD2.ADMIN2_CD,

AD2.PARENT_ADMIN_CD,

AD2.COUNTRY_CD,

AD2.ASCII_NAME,

COUNT(SHOP.ID) CNT_SHOP

FROM

(

SELECT

FROM

V_GEONM_ADMIN_2

WHERE

COUNTRY_CD = 'US') AD2

LEFT JOIN (

SELECT

ID,

NAME,

ST_COLLECT(GEO) COORDINATES

FROM

V_OSM_USA_BOUNDARY

WHERE

RANK = 6

GROUP BY

ID,

NAME) BOUNDARY ON

AD2.ASCII_NAME = BOUNDARY.NAME

AND ST_COVERS(BOUNDARY.COORDINATES,

ST_POINT(AD2.LONGITUDE,

AD2.LATITUDE))

LEFT JOIN

V_OSM_USA_SHOP_GENERAL_STORE_DEPARTMENT_STORE_MALL

SHOP ON

ST_COVERS(BOUNDARY.COORDINATES,ST_CENTROID(SHOP.COORDINATES))

GROUP BY

AD2.GEO_ID,

AD2.ADMIN2_CD,

AD2.PARENT_ADMIN_CD,

AD2.COUNTRY_CD,

AD2.ASCII_NAME;

3. Get the county population for the top rated properties in the US.

This can be done by making use of the property data and the ADMIN_1 and ADMIN_2 table(county in the US is the second-order administrative division) of Geography dimension.

SELECT
	ID,
	PL.NAME PROP_NAME,
	COUNTY_OR_EQUIVALENT,
	RATE,
	AREA_IN_ACRES,
	ROUND(RATE / AREA_IN_ACRES,0) RATE_PER_ACRE,
	AD2.POPULATION
FROM
	PROPERTY_LANDS PL
LEFT JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_1
	WHERE
    	COUNTRY_CD = 'US') AD1 ON
	PL.STATE =
 REGEXP_SUBSTR(ADMIN1_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)
LEFT JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_2
	WHERE
    	COUNTRY_CD = 'US') AD2 ON
	UPPER(PL.COUNTY_OR_EQUIVALENT) = UPPER(AD2.ASCII_NAME)
	AND AD1.ADMIN1_CD = AD2.PARENT_ADMIN_CD
ORDER BY RATE_PER_ACRE DESC;

SELECT

ID,

PL.NAME PROP_NAME,

COUNTY_OR_EQUIVALENT,

RATE,

AREA_IN_ACRES,

ROUND(RATE / AREA_IN_ACRES,0) RATE_PER_ACRE,

AD2.POPULATION

FROM

PROPERTY_LANDS PL

LEFT JOIN (

SELECT

FROM

V_GEONM_ADMIN_1

WHERE

COUNTRY_CD = 'US') AD1 ON

PL.STATE =

REGEXP_SUBSTR(ADMIN1_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)

LEFT JOIN (

SELECT

FROM

V_GEONM_ADMIN_2

WHERE

COUNTRY_CD = 'US') AD2 ON

UPPER(PL.COUNTY_OR_EQUIVALENT) = UPPER(AD2.ASCII_NAME)

AND AD1.ADMIN1_CD = AD2.PARENT_ADMIN_CD

ORDER BY RATE_PER_ACRE DESC;

4. Number of cities with a population > 10,000 within the county where the property belongs.

This can be achieved by making use of the property data along with the ADMIN tables and CITY_500 table from Geography Dimension. We need to consider all the administrative levels from first to fifth order as the city could be listed directly under any of these divisions. We can omit the unwanted admin tables from the join if we correctly know the admin levels under which no city is listed for the country under consideration.

SELECT 
ID,NAME,STATE,COUNTY_OR_EQUIVALENT,RATE,AREA_IN_ACRES,SUM(CASE WHEN
POPULATION > 10000 THEN 1 ELSE 0 END ) CITY_CNT
FROM(
SELECT DISTINCT
	ID,PL.NAME,STATE,COUNTY_OR_EQUIVALENT,RATE,AREA_IN_ACRES,CITY.NAME
CITY_NAME,CITY.POPULATION
FROM
	PROPERTY_LANDS PL
INNER JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_1
	WHERE
    	COUNTRY_CD = 'US') AD1 ON
	PL.STATE =
REGEXP_SUBSTR(ADMIN1_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)
INNER JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_2
	WHERE
    	COUNTRY_CD = 'US') AD2 ON
	UPPER(PL.COUNTY_OR_EQUIVALENT) = UPPER(AD2.ASCII_NAME)
	AND AD1.ADMIN1_CD = AD2.PARENT_ADMIN_CD
LEFT JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_3
	WHERE
    	COUNTRY_CD = 'US') AD3 ON
 	AD2.ADMIN2_CD = AD3.PARENT_ADMIN_CD
LEFT JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_4
	WHERE
    	COUNTRY_CD = 'US') AD4 ON
 	AD3.ADMIN3_CD = AD4.PARENT_ADMIN_CD
LEFT JOIN (
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_5
	WHERE
    	COUNTRY_CD = 'US') AD5 ON
 	AD4.ADMIN4_CD = AD5.PARENT_ADMIN_CD
LEFT JOIN (
	SELECT * FROM V_GEONM_CITY_500
	WHERE
    	COUNTRY_CD = 'US' AND POPULATION > 10000) CITY ON
    	(AD1.ADMIN1_CD = CITY.ADMIN_CD OR AD2.ADMIN2_CD =
CITY.ADMIN_CD OR AD3.ADMIN3_CD = CITY.ADMIN_CD OR AD4.ADMIN4_CD =
CITY.ADMIN_CD OR AD5.ADMIN5_CD = CITY.ADMIN_CD)
)
GROUP BY ID,
NAME,
STATE,
COUNTY_OR_EQUIVALENT,
RATE,
AREA_IN_ACRES
ORDER BY ID DESC;

SELECT

ID,NAME,STATE,COUNTY_OR_EQUIVALENT,RATE,AREA_IN_ACRES,SUM(CASE WHEN

POPULATION > 10000 THEN 1 ELSE 0 END ) CITY_CNT

FROM(

SELECT DISTINCT

ID,PL.NAME,STATE,COUNTY_OR_EQUIVALENT,RATE,AREA_IN_ACRES,CITY.NAME

CITY_NAME,CITY.POPULATION

FROM

PROPERTY_LANDS PL

INNER JOIN (

SELECT

FROM

V_GEONM_ADMIN_1

WHERE

COUNTRY_CD = 'US') AD1 ON

PL.STATE =

REGEXP_SUBSTR(ADMIN1_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)

INNER JOIN (

SELECT

FROM

V_GEONM_ADMIN_2

WHERE

COUNTRY_CD = 'US') AD2 ON

UPPER(PL.COUNTY_OR_EQUIVALENT) = UPPER(AD2.ASCII_NAME)

AND AD1.ADMIN1_CD = AD2.PARENT_ADMIN_CD

LEFT JOIN (

SELECT

FROM

V_GEONM_ADMIN_3

WHERE

COUNTRY_CD = 'US') AD3 ON

AD2.ADMIN2_CD = AD3.PARENT_ADMIN_CD

LEFT JOIN (

SELECT

FROM

V_GEONM_ADMIN_4

WHERE

COUNTRY_CD = 'US') AD4 ON

AD3.ADMIN3_CD = AD4.PARENT_ADMIN_CD

LEFT JOIN (

SELECT

FROM

V_GEONM_ADMIN_5

WHERE

COUNTRY_CD = 'US') AD5 ON

AD4.ADMIN4_CD = AD5.PARENT_ADMIN_CD

LEFT JOIN (

SELECT * FROM V_GEONM_CITY_500

WHERE

COUNTRY_CD = 'US' AND POPULATION > 10000) CITY ON

(AD1.ADMIN1_CD = CITY.ADMIN_CD OR AD2.ADMIN2_CD =

CITY.ADMIN_CD OR AD3.ADMIN3_CD = CITY.ADMIN_CD OR AD4.ADMIN4_CD =

CITY.ADMIN_CD OR AD5.ADMIN5_CD = CITY.ADMIN_CD)

)

GROUP BY ID,

NAME,

STATE,

COUNTY_OR_EQUIVALENT,

RATE,

AREA_IN_ACRES

ORDER BY ID DESC;

5. List the count of educational institutions within a distance of 2000m for all the listed properties

In order to achieve this the AMENITY_EDUCATION table from OSM is used. The table AMENITY_EDUCATION has the details of educational institutions.

Likewise we can make use of the following amenity tables to get information on respective facilities within a specified distance from the property under consideration.

AMENITY_ENTERTAINMENT_ARTS_AND_CULTURE

AMENITY_FINANCIAL

AMENITY_HEALTHCARE

AMENITY_TRANSPORTATION

AMENITY_SUSTENANCE

SELECT
	PL.ID,
	PL.NAME,
	PL.STATE,
	PL.COUNTY_OR_EQUIVALENT,
	PL.RATE,
	PL.AREA_IN_ACRES,
	COUNT(*) CNT_EDUCATIONAL
FROM
	PROPERTY_LANDS PL
INNER JOIN (
	SELECT
    	*,
    	ST_X(ST_CENTROID(COORDINATES)) AS LON,
    	ST_Y(ST_CENTROID(COORDINATES)) AS LAT
	FROM
    	V_OSM_USA_AMENITY_EDUCATION ) AM_EDU ON
	ST_DISTANCE(PL.COORDINATES,
	AM_EDU.COORDINATES) < 2000
WHERE
	AM_EDU.LON BETWEEN ST_X(PL.COORDINATES) -2 AND
ST_X(PL.COORDINATES) + 2
	AND AM_EDU.LAT BETWEEN ST_Y(PL.COORDINATES) -2 AND
ST_Y(PL.COORDINATES) + 2
GROUP BY
	PL.ID,
	PL.NAME,
	PL.STATE,
	PL.COUNTY_OR_EQUIVALENT,
	PL.RATE,
	PL.AREA_IN_ACRES;

SELECT

PL.ID,

PL.NAME,

PL.STATE,

PL.COUNTY_OR_EQUIVALENT,

PL.RATE,

PL.AREA_IN_ACRES,

COUNT(*) CNT_EDUCATIONAL

FROM

PROPERTY_LANDS PL

INNER JOIN (

SELECT

ST_X(ST_CENTROID(COORDINATES)) AS LON,

ST_Y(ST_CENTROID(COORDINATES)) AS LAT

FROM

V_OSM_USA_AMENITY_EDUCATION ) AM_EDU ON

ST_DISTANCE(PL.COORDINATES,

AM_EDU.COORDINATES) < 2000

WHERE

AM_EDU.LON BETWEEN ST_X(PL.COORDINATES) -2 AND

ST_X(PL.COORDINATES) + 2

AND AM_EDU.LAT BETWEEN ST_Y(PL.COORDINATES) -2 AND

ST_Y(PL.COORDINATES) + 2

GROUP BY

PL.ID,

PL.NAME,

PL.STATE,

PL.COUNTY_OR_EQUIVALENT,

PL.RATE,

PL.AREA_IN_ACRES;

6. Average rate of land in each of the second-order administrative divisions of US(Counties) .

This is calculated using ADMIN_2 table of Geography Dimension and the property dataset.

SELECT
	AD2.GEO_ID,
	AD2.ADMIN2_CD,
	AD2.PARENT_ADMIN_CD,
	AD2.COUNTRY_CD,
	AD2.ASCII_NAME,
	AD2.POPULATION,
	PL.AVG_RATE_OF_LAND
FROM
	(
	SELECT
    	*
	FROM
    	V_GEONM_ADMIN_2
	WHERE
    	COUNTRY_CD = 'US') AD2
LEFT JOIN (
	SELECT
    	STATE,
    	COUNTY_OR_EQUIVALENT,
    	ROUND(AVG(RATE / AREA_IN_ACRES),0) AVG_RATE_OF_LAND
	FROM
    	PROPERTY_LANDS
	WHERE
    	RATE IS NOT NULL
    	AND AREA_IN_ACRES IS NOT NULL
    	AND AREA_IN_ACRES > 20
	GROUP BY
    	STATE,
    	COUNTY_OR_EQUIVALENT) PL ON
	AD2.ASCII_NAME = PL.COUNTY_OR_EQUIVALENT
	AND REGEXP_SUBSTR(AD2.ADMIN2_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)
= PL.STATE;

SELECT

AD2.GEO_ID,

AD2.ADMIN2_CD,

AD2.PARENT_ADMIN_CD,

AD2.COUNTRY_CD,

AD2.ASCII_NAME,

AD2.POPULATION,

PL.AVG_RATE_OF_LAND

FROM

(

SELECT

FROM

V_GEONM_ADMIN_2

WHERE

COUNTRY_CD = 'US') AD2

LEFT JOIN (

SELECT

STATE,

COUNTY_OR_EQUIVALENT,

ROUND(AVG(RATE / AREA_IN_ACRES),0) AVG_RATE_OF_LAND

FROM

PROPERTY_LANDS

WHERE

RATE IS NOT NULL

AND AREA_IN_ACRES IS NOT NULL

AND AREA_IN_ACRES > 20

GROUP BY

STATE,

COUNTY_OR_EQUIVALENT) PL ON

AD2.ASCII_NAME = PL.COUNTY_OR_EQUIVALENT

AND REGEXP_SUBSTR(AD2.ADMIN2_CD,'_([a-zA-Z0-9]+)',1,1,'e',1)

= PL.STATE;

Here we have covered a few of the scenarios where we can effectively make use of these datasets. A little modification to these SQLs would need to be done based on your dataset as well as your requirement. There are many more scenarios which we haven’t covered in this blog where our dataset can be made useful. Please feel free to reach out to Sonra if you need any assistance on this.

About the author:

Uli Bethke

Co-founder of Sonra

Any questions or comments for Uli? Connect with him on LinkedIn.

Follow Uli Bethke:

Back to Blog

Cookie	Duration	Description
__cfruid	session	Cloudflare sets this cookie to identify trusted web traffic.
cookielawinfo-checkbox-marketing	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Marketing".
cookielawinfo-checkbox-necessary	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-preferences	1 month	This cookie is set by the GDPR Cookie Consent plugin to check if the user has given consent to use cookies under the "Preferences" category.
cookielawinfo-checkbox-statistics	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Statistics".
cookielawinfo-checkbox-unclassified	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Unclassified".
CookieLawInfoConsent	1 month	Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie.
csrftoken	1 year	This cookie is associated with Django web development platform for python. Used to help protect the website against Cross-Site Request Forgery attacks

Cookie	Duration	Description
AnalyticsSyncHistory	1 month	Linkedin set this cookie to store information about the time a sync took place with the lms_analytics cookie.
bcookie	2 years	LinkedIn sets this cookie from LinkedIn share buttons and ad tags to recognize browser ID.
bscookie	2 years	LinkedIn sets this cookie to store performed actions on the website.
lang	session	LinkedIn sets this cookie to remember a user's language setting.
li_gc	2 years	Linkedin set this cookie for storing visitor's consent regarding using cookies for non-essential purposes.
lidc	1 day	LinkedIn sets the lidc cookie to facilitate data center selection.
mgref	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
mgrefby	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
UserMatchHistory	1 month	LinkedIn sets this cookie for LinkedIn Ads ID syncing.

Cookie	Duration	Description
G	1 year	Cookie used to facilitate the translation into the preferred language of the visitor.
SERVERID	session	This cookie is set by Slideshare's HAProxy load balancer to assign the visitor to a specific server.

Cookie	Duration	Description
_ga	2 years	The _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors.
_ga_7H38LVR4Z5	2 years	This cookie is installed by Google Analytics.
_gat_gtag_UA_44804396_1	1 minute	Set by Google to distinguish users.
_gat_UA-44804396-1	1 minute	A variation of the _gat cookie set by Google Analytics and Google Tag Manager to allow website owners to track visitor behaviour and measure site performance. The pattern element in the name contains the unique identity number of the account or website it relates to.
_gcl_au	3 months	Provided by Google Tag Manager to experiment advertisement efficiency of websites using their services.
_gid	1 day	Installed by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously.
CONSENT	2 years	YouTube sets this cookie via embedded youtube-videos and registers anonymous statistical data.
SIDCC	6 Months	The "SIDCC" cookie is used as security measure to protect users data from unauthorised access
test_cookie	15 minutes	The test_cookie is set by doubleclick.net and is used to determine if the user's browser supports cookies.
VISITOR_INFO1_LIVE	5 months 27 days	A cookie set by YouTube to measure bandwidth that determines whether the user gets the new or old player interface.
YSC	session	YSC cookie is set by Youtube and is used to track the views of embedded videos on Youtube pages.
yt-remote-connected-devices	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt-remote-device-id	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt.innertube::nextId	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.
yt.innertube::requests	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.

Cookie	Duration	Description
AN	1 month
AS	session
ebEventToTrack	1 month
eblang	1 year
SNID	2 years	This cookie is set by the Google. This cookie is used by the map which helps visitors to identify and reach the facility.
SP	session
SS	session

SQL Visualisation Guide - Query Diagrams, Lineage & ERD

SQL Visualisation Guide - Query Diagrams, Lineage & ERD

SQL Visualisation Guide - Query Diagrams, Lineage & ERD

SQL Visualisation Guide - Query Diagrams, Lineage & ERD

Location analytics on Snowflake with OpenStreetMap, US property data, and our ready made Geography Dimension

Geography Dimension

COUNTRY

ADMIN TABLES

CITY_500

OSM Data

Property data

The data sets in action

Uli Bethke

Location analytics on Snowflake with OpenStreetMap, US property data, and our ready made Geography Dimension

Geography Dimension

COUNTRY

ADMIN TABLES

CITY_500

OSM Data

Property data

The data sets in action

Related Articles

Cookies consent