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

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.

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

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.


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

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.

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.

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