Country & City Dimension

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 August 18, 2023
Updated on November 20, 2024

Country

The COUNTRY table serves as a comprehensive repository, capturing essential attributes and details about countries globally. It encompasses unique identifiers, standardized codes, geographical data, and practical information.

  • COUNTRY_CD:
    • Description: A short code representing the country.
    • Sample Value: AD
    • Example: The code AD stands for Andorra.
  • ISO:
    • Description: A two-letter standardized country code.
    • Sample Value: AE
    • Example: The code AE represents the United Arab Emirates.
  • ISO3:
    • Description: A three-letter standardized country code.
    • Sample Value: AND
    • Example: AND is the three-letter code for Andorra.
  • ISO_NUMERIC:
    • Description: A numeric representation of the country code.
    • Sample Value: 020
    • Example: The number 020 corresponds to Andorra.
  • FIPS_CD:
    • Description: Federal Information Processing Standards code for the country.
    • Sample Value: AN
    • Example: AN is the FIPS code for Andorra.
  • COUNTRY_NAME:
    • Description: The full name of the country.
    • Sample Value: Afghanistan
    • Example: The country name “Afghanistan” corresponds to the codes AF, AFG, and 004.
  • CAPITAL:
    • Description: The capital city of the country.
    • Sample Value: Kabul
    • Example: Kabul is the capital city of Afghanistan.
  • AREA:
    • Description: The total land area of the country in square kilometers.
    • Sample Value: 647,500
    • Example: Afghanistan covers an area of 647,500 sq km.
  • POPULATION:
    • Description: The total population of the country.
    • Sample Value: 37,172,386
    • Example: Afghanistan has a population of approximately 37.17 million.
  • CONTINENT_CD:
    • Description: The continent code where the country is located.
    • Sample Value: AS
    • Example: Countries like Afghanistan are located in Asia.
  • CONTINENT_NAME
    • Description: The continent name where the country is located
    • Sample Value: Europe
  • TLD:
    • Description: The top-level domain associated with the country.
    • Sample Value: .af
    • Example: Websites from Afghanistan might end with .af.
  • CURRENCY_CD:
    • Description: The code for the country’s official currency.
    • Sample Value: EUR
    • Example: EUR stands for the Euro used in Andorra.
  • CURRENCY_NAME:
    • Description: The name of the country’s official currency.
    • Sample Value: Euro
    • Example: Euro is the official currency of Andorra.
  • PHONE:
    • Description: The phone code for the country.
    • Sample Value: +1-268
    • Example: +1-268 is the phone code for Antigua and Barbuda.
  • POSTAL_CD_FORMAT:
    • Description: The format used for postal codes in the country.
    • Sample Value: AD###
    • Example: In Andorra, postal codes follow a format like AD123.
  • POSTAL_CD_REGEX:
    • Description: A regular expression pattern for validating the country’s postal code.
    • Sample Value: ^(?:AD)*(\d{3})$
    • Example: This regex pattern matches the Andorran postal code format AD###.
  • LANGUAGES:
    • Description: An array of official languages spoken in the country.
    • Sample Value: [“en-AG”]
    • Example: English (en-AG) is the official language of Antigua and Barbuda.
  • NEIGHBORS:
    • Description: An array of neighboring countries.
    • Sample Value: [“SA,OM”]
    • Example: The United Arab Emirates has neighbors like Saudi Arabia (SA) and Oman (OM).
  • EQUIVALENT_FIPS_CD:
    • Description: An equivalent FIPS code for the country.
    • Sample Value: (No sample provided in the dataset)
    • Example: This might be an alternative or extended FIPS code for a region within a country.

Create 5 questions that someone would like to ask against the data and 3 sample queries to answer those questions, e.g. give me all of the countries in continent Europe

  • Which countries have more than three neighboring countries?

  • Which countries in Europe have a population less than 1 million?

  • How many countries have more than one official language?How many countries have more than one official language?

  • Which continents have the highest and lowest total population?
  • What are the predominant languages in Asia?

City

The table provides a detailed overview of cities, combining geographical and administrative data. Precise geographical coordinates, including latitude, longitude, and elevation, are stored alongside unique identifiers like ‘GEO_ID’.

  • COUNTRY_CD:
    • Description: A short code indicating the country of the city. Foreign key to the Country table
    • Sample Value: NP
    • Example: The code NP stands for Nepal.
  • CITY_NAME:
    • Description: The name of the city.
    • Sample Value: Jambari
    • Example: Jambari is the name of a city or location in the dataset.
  • ASCII_NAME:
    • Description: The ASCII representation of the location’s name.
    • Sample Value: Jambari
    • Example: This ensures the city name can be read in systems that only support ASCII characters.
  • LATITUDE:
    • Description: Latitude coordinates of the location.
    • Sample Value: 51.50853
    • Example:
  • LONGITUDE:
    • Description: Longitude coordinates of the location.
    • Sample Value: -0.12574
    • Example:.
  • POPULATION:
    • Description: Indicates the population of the city.
  • DEM:
    • Description: 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.
    • Sample Value: 88
  • TIMEZONE:
    • Description: The time zone in which the city is located.
    • Sample Value: Asia/Kathmandu
    • Example: The city follows the time zone of Kathmandu, Nepal.
  • MODIFICATION_DT:
    • Description: The date when the record was last modified.
    • Sample Value: 2019-10-17
    • Example: The details of this city were last updated or modified on October 17, 2019

Sample Queries

  • Which are the top 2 cities with the highest population in each of the countries?

  • Which country has the most cities listed in the table?

  • Top 10 countries from the provided list which has the highest average recorded population across its cities

Joining City and Country Tables:

  • Which countries have more than 100 cities listed?

  • Which country with English as one of its official languages has the most cities listed?

Uli Bethke

About the author:

Uli Bethke

Co-founder of Sonra

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.