Guide to Geography Dimension on the Snowflake Marketplace

February 3, 2021

We have published a universal Geography Dimension on the marketplace of the Snowflake Data Cloud. You can easily join the data with your in house data to look up location data. You can also use it for drilling down / rolling up in any type of location analytics.

ER DIAGRAM

CORE TABLES

The core tables in the data model are listed below.

COUNTRY

This table holds the country level information.

COLUMNS

ColumnDescription
GEO_IDThe integer id of the record
COUNTRY_CDThe unique country code
ISOThe ISO code for the country
ISO3The ISO3 code for the country
ISO_NUMERICThe numeric ISO for the country
FIPSCountry FIPS code
COUNTRYCountry name
CAPITALCountry Capital
AREA_IN_SQ_KMThe total area of country in sq.km
POPULATIONTotal population of the country
CONTINENTThe continent where the country belongs
TLDTop-level domain of the country
CURRENCYCDCurrency code
CURRENCYNAMECurrency name
PHONEPhone dialing code
POSTAL_CD_FORMATPostal code format of country
POSTAL_CD_REGEXThe regular expression for the postal code of the country
LANGUAGESLanguages 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.
NEIGHBOURSThe neighbouring countries
EQUIVALENTFIPSCDEquivalent Fips code if any

ADMIN_1

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

COLUMNS

ColumnDescription
GEO_IDThe integer id of the record
ADMIN1_CDThe first order administrative division code
COUNTRY_CDCountry code where the administrative division belongs
FEATURE_CDIndicates the feature (eg: ADM1, ADM2 etc)
NAMEName of the geographical point
ASCIINAMEName of the geographical point in plain ascii characters
LATITUDELatitude in decimal degrees
LONGITUDELongitude in decimal degrees
POPULATIONPopulation
DEMDigital 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.
TIMEZONETimezone
MODIFICATION_DTDate of last modification

SAMPLE QUERIES

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

Book a demo with us.

Book a Demo

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.

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

ColumnDescription
GEO_IDThe integer id of the record
ADMIN2_CDThe second order administrative division code
PARENT_ADMIN_CDThe code of the parent administrative division(code of corresponding ADM1 division in this case)
COUNTRY_CDCountry code where the administrative division belongs
FEATURE_CDIndicates the feature (eg: ADM1, ADM2 etc)
NAMEName of the geographical point
ASCIINAMEName of the geographical point in plain ascii characters
LATITUDELatitude in decimal degrees
LONGITUDELongitude in decimal degrees
POPULATIONPopulation
DEMdigital 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.
TIMEZONETimezone
MODIFICATION_DTDate of last modification

SAMPLE QUERIES

The following query shows second order administrative divisions(ADM2) in 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.

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.

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.

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.

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

ColumnDescription
GEO_IDThe integer id of the record
COUNTRY_CDCountry code where the city belongs
ADMIN_CDThe administrative division code of the city
PARENT_ADMIN_ORDERThe maximum administrative division order under which the city comes
FEATURE_CDIndicates the feature (eg: PPLA, PPLA2 etc)
NAMEName of the city
ASCIINAMEName of the city in plain ascii characters
LATITUDELatitude in decimal degrees
LONGITUDELongitude in decimal degrees
POPULATIONPopulation of the city
DEMDigital 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.
TIMEZONETimezone
MODIFICATION_DTDate 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.

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

ColumnDescription
ALTERNATENAME_IDThe unique id for the alternate name
GEO_IDThe geo_id refers to the geo_id in admin level tables and country.
ISOLANGUAGEISO 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_NAMEAlternate 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.

FEATURE

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

COLUMNS

ColumnDescription
FEATURE_CDThe feature code
FEATURE_CLASSThe general class of the feature code
NAMEThe name of the feature code
DESCRIPTIONThe description of the feature

We are offering various GEO datasets. You can find our offering here: