Guide to Geography Dimension on the Snowflake Marketplace

We’ve published a universal Geography Dimension on the Snowflake Data Marketplace!

This dataset makes it easy to enhance your in-house data with detailed location information. Whether you’re performing location lookups or diving deep into location analytics, the Geography Dimension has you covered.

Use it to:

• Drill down into granular details or roll up to higher-level trends in your analytics.
• Quickly and seamlessly join location data with your existing datasets.

No need to build or maintain complex geography tables—just integrate and start unlocking insights. Check it out on the Snowflake Marketplace today!

Here is a walk through of what is available.

CORE TABLES

The core tables in the data model are listed below.

COUNTRY

This table holds the country level information.

COLUMNS

Column	Description
GEO_ID	The integer id of the record
COUNTRY_CD	The unique country code
ISO	The ISO code for the country
ISO3	The ISO3 code for the country
ISO_NUMERIC	The numeric ISO for the country
FIPS	Country FIPS code
COUNTRY	Country name
CAPITAL	Country Capital
AREA_IN_SQ_KM	The total area of country in sq.km
POPULATION	Total population of the country
CONTINENT	The continent where the country belongs
TLD	Top-level domain of the country
CURRENCYCD	Currency code
CURRENCYNAME	Currency name
PHONE	Phone dialing code
POSTAL_CD_FORMAT	Postal code format of country
POSTAL_CD_REGEX	The regular expression for the postal code of the country
LANGUAGES	Languages spoken in a country ordered by the number of speakers. The language code is a ‘locale’, where any two-letter primary-tag is an ISO-639 language abbreviation and any two-letter initial subtag is an ISO-3166 country code. Example : es-AR is the Spanish variant spoken in Argentina.
NEIGHBOURS	The neighbouring countries
EQUIVALENTFIPSCD	Equivalent Fips code if any

GEONM_POSTALCODE_ALL

This dataset provides postal code information for nearly 100 countries worldwide, linking postal codes to place names, administrative divisions (up to three levels), and estimated geographic coordinates (latitude and longitude). It’s important to note that for the United States, this dataset only contains the basic five-digit ZIP code and does not include the more precise ZIP+4 code (the nine-digit format).

COLUMNS

Column Name	Description
country code	ISO 3166-1 alpha-2 country code
postal code	The postal code
place name	Name associated with the postal code
admin name1	1st order administrative division name (e.g., state)
admin code1	1st order administrative division code
admin name2	2nd order administrative division name (e.g., county)
admin code2	2nd order administrative division code
admin name3	3rd order administrative division name (e.g., community)
admin code3	3rd order administrative division code
latitude	Latitude (WGS84 datum)
longitude	Longitude (WGS84 datum)
accuracy	Indicator of latitude/longitude accuracy (1=estimated, 4=geonameid, 6=centroid)

ADMIN_1

All first-order/primary administrative divisions within each country, such as a state in the United States.

COLUMNS

Column	Description
GEO_ID	The integer id of the record
ADMIN1_CD	The first order administrative division code
COUNTRY_CD	Country code where the administrative division belongs
FEATURE_CD	Indicates the feature (eg: ADM1, ADM2 etc)
NAME	Name of the geographical point
ASCIINAME	Name of the geographical point in plain ascii characters
LATITUDE	Latitude in decimal degrees
LONGITUDE	Longitude in decimal degrees
POPULATION	Population
DEM	Digital elevation model, srtm3 or gtopo30, average elevation of 3”x3” (ca 90mx90m) or 30”x30” (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
TIMEZONE	Timezone
MODIFICATION_DT	Date of last modification

SAMPLE QUERIES

As an example, here is the query to find all the first-order administrative divisions of France.

SELECT
    ADMIN1_CD,
    NAME,
    LATITUDE,
    LONGITUDE,
    POPULATION,
    DEM
FROM
    ADMIN_1
WHERE
    COUNTRY_CD = 'FR' ;

Book a demo with us.

The result shows the details of 13 first-order administrative divisions(ADM1) in France.
The number of the first-order administrative divisions within a country can be obtained from the following query.

SELECT
	COUNT(*)
FROM
	ADMIN_1 AD1
JOIN COUNTRY C ON
	AD1.COUNTRY_CD = C.COUNTRY_CD
WHERE
	C.COUNTRY = 'Italy';

ADMIN_2

All second-order administrative divisions within each country which is a subdivision of a first-order administrative division, such as a county in the US.

COLUMNS

Column	Description
GEO_ID	The integer id of the record
ADMIN2_CD	The second order administrative division code
PARENT_ADMIN_CD	The code of the parent administrative division(code of corresponding ADM1 division in this case)
COUNTRY_CD	Country code where the administrative division belongs
FEATURE_CD	Indicates the feature (eg: ADM1, ADM2 etc)
NAME	Name of the geographical point
ASCIINAME	Name of the geographical point in plain ascii characters
LATITUDE	Latitude in decimal degrees
LONGITUDE	Longitude in decimal degrees
POPULATION	Population
DEM	digital elevation model, srtm3 or gtopo30, average elevation of 3”x3” (ca 90mx90m) or 30”x30” (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
TIMEZONE	Timezone
MODIFICATION_DT	Date of last modification

SAMPLE QUERIES

The following query shows second order administrative divisions(ADM2) in France.

SELECT
	AD2.ADMIN2_CD,
	AD2.PARENT_ADMIN_CD,
	AD2.NAME,
	AD2.LATITUDE,
	AD2.LONGITUDE,
	AD2.POPULATION,
	AD2.DEM
FROM
	ADMIN_2 AD2
INNER JOIN COUNTRY C ON
	AD2.COUNTRY_CD = C.COUNTRY_CD
WHERE
	C.COUNTRY = 'France';

The result has the data of 96 second-order administrative levels in France.

ADMIN_3

All third-order administrative divisions within each country which is a subdivision of a second-order administrative division.

SAMPLE QUERIES

The query given below can be used to fetch the latitude,longitude and population of the third-order administrative divisions of Spain.

SELECT
	AD3.ADMIN3_CD,
	AD3.NAME,
	AD3.LATITUDE,
	AD3.LONGITUDE,
	AD3.POPULATION
FROM
	ADMIN_3 AD3
INNER JOIN COUNTRY C
    	ON AD3.COUNTRY_CD = C.COUNTRY_CD
WHERE
	C.COUNTRY = 'Spain';

ADMIN_4

All fourth-order administrative divisions within each country which is a subdivision of a third-order administrative division.

SAMPLE QUERIES

The query given below can be used to fetch the number of fourth-order divisions under each of the first-order administrative divisions of France.

SELECT
    ADM1.ADMIN1_CD,
    ADM1.NAME,
    COUNT(*)
FROM
    ADMIN_4 ADM4
INNER JOIN ADMIN_3 ADM3 ON
    ADM4.PARENT_ADMIN_CD = ADM3.ADMIN3_CD
INNER JOIN ADMIN_2 ADM2 ON
    ADM3.PARENT_ADMIN_CD = ADM2.ADMIN2_CD
INNER JOIN ADMIN_1 ADM1 ON
    ADM2.PARENT_ADMIN_CD = ADM1.ADMIN1_CD
INNER JOIN COUNTRY C ON
    ADM4.COUNTRY_CD = C.COUNTRY_CD
WHERE
    C.COUNTRY = 'France'
GROUP BY
    ADM1.ADMIN1_CD,
    ADM1.NAME;

ADMIN_5

All fifth-order administrative divisions within each country which is a subdivision of a fourth-order administrative division.

SAMPLE QUERY

The query given below can be used to fetch the hierarchical parent administrative divisions of a fifth order administrative division.

SELECT
    AD1.NAME ADMIN1_NAME,
    AD2.NAME ADMIN2_NAME,
    AD3.NAME ADMIN3_NAME,
    AD4.NAME ADMIN4_NAME,
    AD5.NAME ADMIN5_NAME
FROM
    ADMIN_5 AD5
INNER JOIN ADMIN_4 AD4 ON
    AD5.PARENT_ADMIN_CD = AD4.ADMIN4_CD
INNER JOIN ADMIN_3 AD3 ON
    AD4.PARENT_ADMIN_CD = AD3.ADMIN3_CD
INNER JOIN ADMIN_2 AD2 ON
    AD3.PARENT_ADMIN_CD = AD2.ADMIN2_CD
INNER JOIN ADMIN_1 AD1 ON
    AD2.PARENT_ADMIN_CD = AD1.ADMIN1_CD
INNER JOIN COUNTRY C ON
    AD5.COUNTRY_CD = C.COUNTRY_CD
WHERE
    C.COUNTRY = 'Italy'
    AND AD5.NAME = 'La Storta';

ADMIN_3_REL1, ADMIN4_REL1, ADMIN4_REL2, ADMIN5_REL1, ADMIN5_REL2, ADMIN5_REL3

Administrative Divisions may contain skip level hierarchies, i.e. that certain levels in the hierarchy are skipped for some or all children and directly reference grandchildren or great grandchildren.
For these scenarios we created additional tables that are suffixed with _REL<parent admin level>.
For example ADMIN_3_REL1 table has third-order administrative divisions which come directly under a first-order administrative division and ADMIN_4_REL2 has fourth-order administrative divisions which come directly under a second-order administrative division.
As an example, the third-order administrative division Oktyabr’skiy Rayon in Russia comes directly under the first order administrative division Belgorod Oblast.

SAMPLE QUERIES

The query given below fetches the third-order administrative divisions in Russia that come directly under first-order administrative divisions.

SELECT
    AD1.ADMIN1_CD,
    AD1.NAME,
    AD31.ADMIN3_CD,
    AD31.PARENT_ADMIN_CD,
    AD31.NAME
FROM
    ADMIN_3_REL1 AD31
INNER JOIN ADMIN_1 AD1 ON
    AD31.PARENT_ADMIN_CD = AD1.ADMIN1_CD
INNER JOIN COUNTRY C ON
    AD31.COUNTRY_CD = C.COUNTRY_CD
WHERE
    C.COUNTRY = 'Russia';

Country level skip hierarchies

Update April 2024

Some administrative divisions at lower admin levels are directly attached to the country level. 

We have created some additional tables for these skip level hierarchies:

ADMIN_2_RELCOUNTRY (reserved for future use)

ADMIN_3_RELCOUNTRY

ADMIN_4_RELCOUNTRY

ADMIN_5_RELCOUNTRY

Example

The records in ADMIN_3_RELCOUNTRY are directly linked to the country code as parent

The sample query to retrieve these records is as follows

CITY_500

The table has all cities with a population > 500 or seats of adm div down to PPLA4(seat of a fourth-order administrative division).

COLUMNS

Column	Description
GEO_ID	The integer id of the record
COUNTRY_CD	Country code where the city belongs
ADMIN_CD	The administrative division code of the city
PARENT_ADMIN_ORDER	The maximum administrative division order under which the city comes
FEATURE_CD	Indicates the feature (eg: PPLA, PPLA2 etc)
NAME	Name of the city
ASCIINAME	Name of the city in plain ascii characters
LATITUDE	Latitude in decimal degrees
LONGITUDE	Longitude in decimal degrees
POPULATION	Population of the city
DEM	Digital elevation model, srtm3 or gtopo30, average elevation of 3”x3” (ca 90mx90m) or 30”x30” (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
TIMEZONE	Timezone
MODIFICATION_DT	Date of last modification

SAMPLE QUERIES

The query given below can be used to find the highest populated city in all of the nineteen first-order administrative divisions of Spain.

SELECT
    ADMIN1_NAME,
    CITY_NAME,
    POPULATION,
    RANK() OVER(PARTITION BY ADMIN1_NAME
ORDER BY
    POPULATION DESC) AS POPULATION_RANK
FROM
    (
    SELECT
   	 AD1.NAME ADMIN1_NAME,
   	 C500.NAME CITY_NAME,
   	 C500.POPULATION
    FROM
   	 CITY_500 C500
    INNER JOIN ADMIN_4 AD4 ON
   	 C500.ADMIN_CD = AD4.ADMIN4_CD
    INNER JOIN ADMIN_3 AD3 ON
   	 AD4.PARENT_ADMIN_CD = AD3.ADMIN3_CD
    INNER JOIN ADMIN_2 AD2 ON
   	 AD3.PARENT_ADMIN_CD = AD2.ADMIN2_CD
    INNER JOIN ADMIN_1 AD1 ON
   	 AD2.PARENT_ADMIN_CD = AD1.ADMIN1_CD
    INNER JOIN COUNTRY C ON
   	 C500.COUNTRY_CD = C.COUNTRY_CD
    WHERE
   	 C.COUNTRY = 'Spain'
   	 AND PARENT_ADMIN_ORDER = 'ADM4'
UNION
    SELECT
   	 AD1.NAME ADMIN1_NAME,
   	 C500.NAME CITY_NAME,
   	 C500.POPULATION
    FROM
   	 CITY_500 C500
    INNER JOIN ADMIN_3 AD3 ON
   	 C500.ADMIN_CD = AD3.ADMIN3_CD
    INNER JOIN ADMIN_2 AD2 ON
   	 AD3.PARENT_ADMIN_CD = AD2.ADMIN2_CD
    INNER JOIN ADMIN_1 AD1 ON
   	 AD2.PARENT_ADMIN_CD = AD1.ADMIN1_CD
    INNER JOIN COUNTRY C ON
   	 C500.COUNTRY_CD = C.COUNTRY_CD
    WHERE
   	 C.COUNTRY = 'Spain'
   	 AND PARENT_ADMIN_ORDER = 'ADM3'
UNION
    SELECT
   	 AD1.NAME ADMIN1_NAME,
   	 C500.NAME CITY_NAME,
   	 C500.POPULATION
    FROM
   	 CITY_500 C500
    INNER JOIN ADMIN_2 AD2 ON
   	 C500.ADMIN_CD = AD2.ADMIN2_CD
    INNER JOIN ADMIN_1 AD1 ON
   	 AD2.PARENT_ADMIN_CD = AD1.ADMIN1_CD
    INNER JOIN COUNTRY C ON
   	 C500.COUNTRY_CD = C.COUNTRY_CD
    WHERE
   	 C.COUNTRY = 'Spain'
   	 AND PARENT_ADMIN_ORDER = 'ADM2' ) QUALIFY POPULATION_RANK = 1;

ALT_NM

This table has the alternate name details if any, for the geographical points. If a point has more than one alternate name more than one row will be there in the table for that particular point.

COLUMNS

Column	Description
ALTERNATENAME_ID	The unique id for the alternate name
GEO_ID	The geo_id refers to the geo_id in admin level tables and country.
ISOLANGUAGE	ISO 639 language code 2- or 3-characters; 4-characters ‘post’ for postal codes and ‘iata’,’icao’ and faac for airport codes, fr_1793 for French Revolution names, abbr for abbreviation, link to a website (mostly to wikipedia), wkdt for the wikidataid
ALTERNATE_NAME	Alternate name or name variant
ISPREFERREDNAME	‘1’, if this alternate name is an official/preferred name
ISSHORTNAME	‘1’, if this is a short name like ‘California’ for ‘State of California’
ISCOLLOQUIAL	‘1’, if this alternate name is a colloquial or slang term
ISHISTORIC	‘1’, if this alternate name is historic and was used in the past

SAMPLE QUERIES

The query given below can be used to fetch the alternate names of Lazio which is one among the 20 first-order administrative divisions of Italy.

SELECT
    AN.*
FROM
    ALT_NM AN
INNER JOIN ADMIN_1 AD1 ON
    AN.GEO_ID = AD1.GEO_ID
WHERE
    AD1.NAME = 'Lazio'
    AND AD1.COUNTRY_CD = 'IT';

FEATURE

The table contains the name and description for the feature codes.

COLUMNS

Column	Description
FEATURE_CD	The feature code
FEATURE_CLASS	The general class of the feature code
NAME	The name of the feature code
DESCRIPTION	The description of the feature