worldCities sample SQL queries

Our worldCities database includes tables in both normalized and in a single, gazetteer format flat-file. The worldcities__flatfile table is in fact a subset of all the other tables. Although it is not normalized and is thus in theory less flexible than the other, normalized tables, in practice it makes our worldCities database much more easier to use in the vast majority of the cases and you’ll be able to use it fully in just a few minutes.

Unless you have very specific needs and would like to use the preorder tree traversal hierarchy, it is recommended that you use worldcities__flatfile as your main table. Since it contains unique IDs (feature_id) for each piece of data, you can of course easily link it to the others tables to fetch more detailed information.

Below are a few sample MySQL queries to work with our worldCities database, first with the single, flat-file table, followed by more advanced queries to work with hierarchical data, including with the preorder tree traversal.

Sample data

First of all, if you haven’t done so already, we invite you to download the worldCities sample data for free. It contains data for about 480,000 places and is structured in the exact same way as the full worldCities database. It also contain the corresponding records from the optional worldCities Extensions.

Single, flat-file table queries

Using the single, worlcities__flatfile table is pretty easy and straightforward, as you can see in the following sample queries, which show how to fetch countries, 1st-level divisions, 2nd-level divisions and individual populated places, respectively.

country_id, adm1_id, etc… are the same as the feature_id values in the other tables, so you can use those values to fetch more data.

For instance, you can link ppl_id value from the flat-file to feature_id in the optional GPS Coordinates Extension:

In this one, we’ll link adm1_id value from the flat-file to feature_id in the worldcities_features_names table, to get the full name data:

And in this last example, let’s get the available translations in the optional Language Pack:

As you see, you’ll be able to get any piece of data from the worldCities database and its extensions with very simple queries, using the worldcities__flatfile table.

Normalized tables and preorder tree traversal queries

While working with the worldcities__flatfile data (see examples above) is relatively straightforward and ideal for most uses, especially since it easy to link it to the other tables, working with the preorder tree traversal hierarchy offers some more flexibility. For instance, you can fetch complete (and complex) data hierarchy in a single query. However, as you’ll see in the sample queries below, they quickly become quiet complex. Thus, they are recommended only for advanced SQL users and, even so, only when you really need to take full advantage of the preorder tree traversal.

Continents and macro-regions

Continents and regions have the feature_type value RGN. The values in field_description are used to distinguish them:

  • WORLD: world
  • CONTINENT: continent
  • REGION: region
  • SUB-REGION: sub-region

Since they cover many countries, those records do not have official languages and all names are thus marked as translations (name_type=’T’).
To get a simple list of only the continents, we use:

To fetch the whole list of continents and regions with hierarchy data, we would use a query such as this one:

Only the English names are included in the main worldCities database for those records, so filtering the data by language is not necessary. However, the Language Pack worldCities Extension contains all continents and macro-regions names in a dozen languages, so it is good practice to use iso_639_3 by default.

Countries and territories

Those records may have different feature_type values depending on the political status of each country or territory:

  • PCL: political entity
  • PCLD: dependent political entity
  • PCLF: freely associated state
  • PCLI: independent political entity
  • PCLIX: section of independent political entity
  • PCLS: semi-independent political entity

To get a simple list of all countries and territories in English, we would use the following query:

Notice the use of COALESCE to fetch the shortname value when it is available. This would display, for instance, Belgium (short name) instead of Kingdom of Belgium (official, full-length name).

Administrative divisions

There are up to 4 levels of administrative divisions for some countries, with feature_type values of ADM1, ADM2, ADM3 and ADM4.

To fetch a list of administrative divisions with hierarchy data in their official language for a specific country, let’s say Spain (feature_id=214), we can use the following query:

Populated places

The cities, towns, villages,… may have one of the following feature_type values:

  • PPL: populated place
  • PPLC: capital of a political entity
  • PPLX: section of populated place

To get the list of place within a certain administrative division or even country, just use the lft and rgt values from the preorder tree traversal. For instance, to fetch all of the populated places in Spain’s Córdoba province (feature_id=394860), we can use the following:

Alternatively, if looking for places in a lower level administrative division, you can simply use the parent_id value.

Again using the preorder tree traversal, we could also easily find the whole path down to a specific town. Here, for Strasbourg (feature_id=1361272), we’ll fetch the names in English if they are available, in their native language (French) otherwise, also giving preference to short names:

Contact us

Feel free to contact us if you have any questions about the use of our worldCities database and we will be pleased to help you.