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.
FlowForward.
All Things Data Engineering
Straight to Your Inbox!
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 2 3 |
SELECT * FROM LOCATION.GEONM_202404.ADMIN_3_RELCOUNTRY a JOIN LOCATION.GEONM_202404.COUNTRY b on a.parent_admin_cd = b.country_cd; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 |
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.
Follow Uli Bethke: 
