Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL Point type.

In order to put the Point datatype in a context where it makes sense, we’re going to download a complete geolocation data set and normalize it, thus making good use of both the normalization good practice and those other PostgreSQL data types we’ve been learning about in the previous articles of this series.

Buckle-up, this is a long article with a lot of SQL inside.

Geonames

The GeoNames geographical database covers all countries and contains over eleven million place names that are available for download free of charge.

The website offers online querying and all the data is made available to download and use. As is often the case, it comes in an ad-hoc format and requires some processing and normalization before it’s usable in a PostgreSQL database.

begin;

create schema if not exists raw;

create table raw.geonames
 (
   geonameid         bigint,
   name              text,
   asciiname         text,
   alternatenames    text,
   latitude          double precision,
   longitude         double precision,
   feature_class     text,
   feature_code      text,
   country_code      text,
   cc2               text,
   admin1_code       text,
   admin2_code       text,
   admin3_code       text,
   admin4_code       text,
   population        bigint,
   elevation         bigint,
   dem               bigint,
   timezone          text,
   modification      date
 );

create table raw.country
 (
  iso                 text,
  iso3                text,
  isocode             integer,
  fips                text,
  name                text,
  capital             text,
  area                double precision,
  population          bigint,
  continent           text,
  tld                 text,
  currency_code       text,
  currency_name       text,
  phone               text,
  postal_code_format  text,
  postal_code_regex   text,
  languages           text,
  geonameid           bigint,
  neighbours          text,
  fips_equiv          text
 );

\copy raw.country from 'countryInfoData.txt' with csv delimiter E'\t'

create table raw.feature
 (
  code        text,
  description text,
  comment     text
 );

\copy raw.feature from 'featureCodes_en.txt' with csv delimiter E'\t'

create table raw.admin1
 (
  code       text,
  name       text,
  ascii_name text,
  geonameid  bigint
 );

\copy raw.admin1 from 'admin1CodesASCII.txt' with csv delimiter E'\t'

create table raw.admin2
 (
  code       text,
  name       text,
  ascii_name text,
  geonameid  bigint
 );

\copy raw.admin2 from 'admin2Codes.txt' with csv delimiter E'\t'

commit;

Once we have loaded the raw data from the published files at http://download.geonames.org/export/dump/, we can normalize the content and begin to use the data.

You might notice that the SQL file above is missing the \copy command for the raw.geonames table. That’s because copy failed to load the file properly: some location names include single and double quotes, and those are not properly quoted… and not properly escaped. So we resorted to pgloader to load the file, with the following command:

load csv
  from /tmp/geonames/allCountries.txt
  into pgsql://[email protected]/appdev
  target table raw.geonames

  with fields terminated by '\t',
       fields optionally enclosed by '§',
       fields escaped by '%',
       truncate;

Here’s the summary obtained when loading the dataset on the laptop used to prepare this book:

             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0                     0.009s
-----------------------  ---------  ---------  ---------  --------------
           raw.geonames          0   11540466     1.5 GB       6m43.218s
-----------------------  ---------  ---------  ---------  --------------
        Files Processed          0          1                     0.026s
COPY Threads Completion          0          2                  6m43.319s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓          3     1.5 GB       6m43.345s

Normalizing our Geonames data model

To normalize the schema, we apply the rules from the definition of the normal forms. Basically, we want to avoid any dependency in between the attributes of our models. Any dependency means that we need to create a separate table where to manage a set of data that makes sense in isolation is managed.

I did a full talk on the topic of Data Modeling, Normalization and Denormalization at pgday Nordic this year in Oslo, and the slides are of course available in the Talks section here. Check-it out!

The raw.geonames table uses several reference data that GeoNames provide as separate downloads. We then need to begin with fixing the reference data used in the model.

Features

The GeoNames model tags all of its geolocation data with a feature class and a feature. The description for those codes are detailed on the GeoNames codes page and available for download in the featureCodes_en.txt file. Some of the information we need is only available in a text form and has to be reported manually.

begin;

create schema if not exists geoname;

create table geoname.class
 (
  class        char(1) not null primary key,
  description  text
 );

insert into geoname.class (class, description)
     values ('A', 'country, state, region,...'),
            ('H', 'stream, lake, ...'),
            ('L', 'parks,area, ...'),
            ('P', 'city, village,...'),
            ('R', 'road, railroad '),
            ('S', 'spot, building, farm'),
            ('T', 'mountain,hill,rock,... '),
            ('U', 'undersea'),
            ('V', 'forest,heath,...');

create table geoname.feature
 (
  class       char(1) not null references geoname.class(class),
  feature     text    not null,
  description text,
  comment     text,

  primary key(class, feature)
 );

insert into geoname.feature
     select substring(code from 1 for 1) as class,
            substring(code from 3) as feature,
            description,
            comment
       from raw.feature
      where feature.code <> 'null';

commit;

As we see in this file we have to deal with an explicit ‘null’ entry: there’s a text that is four letters long in the last line (and reads null) and that we don’t want to load.

Also, the provided file uses the notation A.ADM1 for an entry of class A and feature ADM1, which we split into proper attributes in our normalization process. The natural key for the geoname.feature table is the combination of the class and the feature.

Once all the data is loaded and normalized, we can get some nice statistics:

  select class, feature, description, count(*)
    from feature
         left join geoname using(class,feature)
group by class, feature
order by count desc
   limit 10;

This is a very simple top-10 query, per feature:

 class │ feature │   description   │  count  
═══════╪═════════╪═════════════════╪═════════
 P     │ PPL     │ populated place │ 1711458
 H     │ STM     │ stream          │  300283
 S     │ CH      │ church          │  236394
 S     │ FRM     │ farm            │  234536
 S     │ SCH     │ school          │  223402
 T     │ HLL     │ hill            │  212659
 T     │ MT      │ mountain        │  192454
 S     │ HTL     │ hotel           │  170896
 H     │ LK      │ lake            │  162922
 S     │ BLDG    │ building(s)     │  143742
(10 rows)

Countries

The raw.country table has several normalization issues. Before we list them, having a look at some data will help us:

─[ RECORD 1 ]──────┬─────────────────────────
iso                │ FR
iso3               │ FRA
isocode            │ 250
fips               │ FR
name               │ France
capital            │ Paris
area               │ 547030
population         │ 64768389
continent          │ EU
tld                │ .fr
currency_code      │ EUR
currency_name      │ Euro
phone              │ 33
postal_code_format │ #####
postal_code_regex  │ ^(\d{5})$
languages          │ fr-FR,frp,br,co,ca,eu,oc
geonameid          │ 3017382
neighbours         │ CH,DE,BE,LU,IT,AD,MC,ES
fips_equiv         │ ¤

The main normalization failures we see are:

  • Nothing guarantees the absence of duplicate rows in the table, so we need to add a primary key constraint.

    Here the isocode attribute looks like the best choice, as it’s both unique and an integer.

  • The languages and neighbours attributes both contain multiple-valued content, a comma-separated list of either languages or country codes.

  • To reach 2NF then, all non-key attributes should be dependent on the entire of the key, and the currencies and postal code formats are not dependent on the country.

A good way to check for dependencies on the key attributes is with the following type of query:

  select currency_code, currency_name, count(*)
    from raw.country
group by currency_code, currency_name
order by count desc
   limit 5;

In our dataset, we have the following result, showing 34 countries using the Euro currency:

 currency_code │ currency_name │ count 
═══════════════╪═══════════════╪═══════
 EUR           │ Euro          │    34
 USD           │ Dollar        │    16
 AUD           │ Dollar        │     8
 XOF           │ Franc         │     8
 XCD           │ Dollar        │     8
(5 rows)

In the context of this article, we’re going to pass on the currency, language, and postal code formats of countries and focus on some information only. That gives us the following normalization process:

begin;

create schema if not exists geoname;

create table geoname.continent
 (
  code    char(2) primary key,
  name    text
 );

insert into geoname.continent(code, name)
     values ('AF', 'Africa'),
            ('NA', 'North America'),
            ('OC', 'Oceania'),
            ('AN', 'Antarctica'),
            ('AS', 'Asia'),
            ('EU', 'Europe'),
            ('SA', 'South America');

create table geoname.country
 (
  isocode   integer primary key,
  iso       char(2) not null,
  iso3      char(3) not null,
  fips      text,
  name      text,
  capital   text,
  continent char(2) references geoname.continent(code),
  tld       text,
  geonameid bigint
 );

insert into geoname.country
     select isocode, iso, iso3, fips, name,
            capital, continent, tld, geonameid
       from raw.country;

create table geoname.neighbour
 (
  isocode   integer not null references geoname.country(isocode),
  neighbour integer not null references geoname.country(isocode),

  primary key(isocode, neighbour)
 );

insert into geoname.neighbour
   with n as(
     select isocode,
            regexp_split_to_table(neighbours, ',') as neighbour
       from raw.country
   )
   select n.isocode,
          country.isocode
     from n
          join geoname.country
            on country.iso = n.neighbour;

commit;

Note that we add the continent list (for completeness in the region drill down) and then introduce the geoname.neighbour part of the model. Having an association table that links every country with its neighbours on the map (a neighbour has a common border) allows us to easily query for the information:

select neighbour.iso,
       neighbour.name,
       neighbour.capital,
       neighbour.tld

  from geoname.neighbour as border
       
       join geoname.country as country
         on border.isocode = country.isocode
       
       join geoname.country as neighbour
         on border.neighbour = neighbour.isocode

 where country.iso = 'FR';

So we get the following list of neighbor countries for France:

 iso │    name     │     capital      │ tld 
═════╪═════════════╪══════════════════╪═════
 CH  │ Switzerland │ Bern             │ .ch
 DE  │ Germany     │ Berlin           │ .de
 BE  │ Belgium     │ Brussels         │ .be
 LU  │ Luxembourg  │ Luxembourg       │ .lu
 IT  │ Italy       │ Rome             │ .it
 AD  │ Andorra     │ Andorra la Vella │ .ad
 MC  │ Monaco      │ Monaco           │ .mc
 ES  │ Spain       │ Madrid           │ .es
(8 rows)

Administrative Zoning

The raw data from the GeoNames website then offers an interesting geographical breakdown in the country_code, admin1_code and admin2_code.

select geonameid, name, admin1_code, admin2_code
  from raw.geonames
 where country_code = 'FR'
 limit 5
offset 50;

To get an interesting result set, we select randomly from the data for France, where the code has to be expanded to be meaningful. With a USA based data set, we get states codes as admin1_code (e.g. IL for Illinois), and the necessity for normalized data might then be less visible.

Of course, never use offset in your application queries, as seen on No Offset. Here, we are doing interactive discovery of the data, so it is found acceptable, to some extent, to play with the offset facility.

Here’s the data set we get:

 geonameid │        name         │ admin1_code │ admin2_code 
═══════════╪═════════════════════╪═════════════╪═════════════
   2967132 │ Zintzel du Nord     │ 44          │ 67
   2967133 │ Zinswiller          │ 44          │ 67
   2967134 │ Ruisseau de Zingajo │ 94          │ 2B
   2967135 │ Zincourt            │ 44          │ 88
   2967136 │ Zimming             │ 44          │ 57
(5 rows)

The GeoNames website provides files admin1CodesASCII.txt and admin2Codes.txt for us to use to normalize our data. Those files again use admin codes spelled as AD.06 and AF.01.1125426 where the raw.geonames table uses them as separate fields. That’s a good reason to split them now.

Here’s the SQL to normalize the admin breakdowns, splitting the codes and adding necessary constraints, to ensure data quality:

begin;

create schema if not exists geoname;

create table geoname.region
 (
  isocode   integer not null references geoname.country(isocode),
  regcode   text not null,
  name      text,
  geonameid bigint,

  primary key(isocode, regcode)
 );

insert into geoname.region
   with admin as
   (
     select regexp_split_to_array(code, '[.]') as code,
            name,
            geonameid
       from raw.admin1
   )
   select country.isocode as isocode,
          code[2] as regcode,
          admin.name,
          admin.geonameid
     from admin
          join geoname.country
            on country.iso = code[1];

create table geoname.district
 (
  isocode   integer not null,
  regcode   text not null,
  discode   text not null,
  name      text,
  geonameid bigint,

  primary key(isocode, regcode, discode),
  foreign key(isocode, regcode)
   references geoname.region(isocode, regcode)
 );

insert into geoname.district
   with admin as
   (
     select regexp_split_to_array(code, '[.]') as code,
            name,
            geonameid
       from raw.admin2
   )
     select region.isocode,
            region.regcode,
            code[3],
            admin.name,
            admin.geonameid
       from admin
            
            join geoname.country
              on country.iso = code[1]
            
            join geoname.region
              on region.isocode = country.isocode
             and region.regcode = code[2];

commit;

The previous query can now be rewritten, showing region and district names rather than admin1_code and admin2_code, which we still have internally in case we need them of course.

select r.name, reg.name as region, d.name as district
  from raw.geonames r
       
       left join geoname.country
              on country.iso = r.country_code
       
       left join geoname.region reg
              on reg.isocode = country.isocode
             and reg.regcode = r.admin1_code
       
       left join geoname.district d
              on d.isocode = country.isocode
             and d.regcode = r.admin1_code
             and d.discode = r.admin2_code
 where country_code = 'FR'
 limit 5
offset 50;

The query uses left join operations because we have geo-location data without the admin1 or admin2 levels of details — more on that later. Here’s the same list of French areas, this time with proper names:

        name         │  region   │           district            
═════════════════════╪═══════════╪═══════════════════════════════
 Zintzel du Nord     │ Grand Est │ Département du Bas-Rhin
 Zinswiller          │ Grand Est │ Département du Bas-Rhin
 Ruisseau de Zingajo │ Corsica   │ Département de la Haute-Corse
 Zincourt            │ Grand Est │ Département des Vosges
 Zimming             │ Grand Est │ Département de la Moselle
(5 rows)

Geolocation Data

Now that we have loaded the reference data, we can load the main geolocation data with the following script. Note that we skip parts of the data we don’t need for this book, but that you might want to load in your application’s background data.

Before loading the raw data into a normalized version of the table, which will make heavy use of the references we normalized before, we have to study and understand how the breakdown works:

select count(*) as all,
       count(*) filter(where country_code is null) as no_country,
       count(*) filter(where admin1_code is null) as no_region,
       count(*) filter(where admin2_code is null) as no_district,
       count(*) filter(where feature_class is null) as no_class,
       count(*) filter(where feature_code is null) as no_feat
  from raw.geonames ;

We have lots of entries without reference for a country, and even more without detailed breakdown (admin1_code and admin2_code are not always part of the data). Moreover we also have points without any reference feature and class, some of them in the Artic.

   all    │ no_country │ no_region │ no_district │ no_class │ no_feat 
══════════╪════════════╪═══════════╪═════════════╪══════════╪═════════
 11540466 │       5821 │     45819 │     5528455 │     5074 │   95368
(1 row)

Given that, our normalization query must be careful to use left join operations, so as to allow for fields to be null when the foreign key reference doesn’t exist. Be careful to drill down properly to the country, then the region, and only then the district, as the data set contains points of several layers of precision as seen in the query above.

begin;

create table geoname.geoname
 (
   geonameid         bigint primary key,
   name              text,
   location          point,
   isocode           integer,
   regcode           text,
   discode           text,
   class             char(1),
   feature           text,
   population        bigint,
   elevation         bigint,
   timezone          text,

   foreign key(isocode)
    references geoname.country(isocode),
   
   foreign key(isocode, regcode)
    references geoname.region(isocode, regcode),
    
   foreign key(isocode, regcode, discode)
    references geoname.district(isocode, regcode, discode),

   foreign key(class)
    references geoname.class(class),

   foreign key(class, feature)
    references geoname.feature(class, feature)
 );

insert into geoname.geoname
  with geo as
  (
     select geonameid,
            name,
            point(longitude, latitude) as location,
            country_code,
            admin1_code,
            admin2_code,
            feature_class,
            feature_code,
            population,
            elevation,
            timezone
       from raw.geonames
   )
     select geo.geonameid,
            geo.name,
            geo.location,
            country.isocode,
            region.regcode,
            district.discode,
            feature.class,
            feature.feature,
            population,
            elevation,
            timezone
       from geo
            left join geoname.country
              on country.iso = geo.country_code

            left join geoname.region
              on region.isocode = country.isocode
             and region.regcode = geo.admin1_code

            left join geoname.district
              on district.isocode = country.isocode
             and district.regcode = geo.admin1_code
             and district.discode = geo.admin2_code

           left join geoname.feature
             on feature.class = geo.feature_class
            and feature.feature = geo.feature_code;

create index on geoname.geoname using gist(location);

commit;

Now that we have a proper data set loaded, it’s easier to make sense of the administrative breakdowns and the geo-location data.

The real use case for this data comes later: thanks to the GiST index over the geoname.location column we are now fully equipped to do a names lookup from the geo-localized information.

  select continent.name,
         count(*),
         round(100.0 * count(*) / sum(count(*)) over(), 2) as pct,
         repeat('■', (100 * count(*) / sum(count(*)) over())::int) as hist
    from geoname.geoname
         join geoname.country using(isocode)
         join geoname.continent
           on continent.code = country.continent
group by continent.name order by continent.name;

We can see that the GeoNames data is highly skewed towards Asia, North America, and then Europe. Of course, the Antartica data is not very dense.

     name      │  count  │  pct  │               hist                
═══════════════╪═════════╪═══════╪═══════════════════════════════════
 Africa        │ 1170043 │ 10.14 │ ■■■■■■■■■■
 Antarctica    │   21125 │  0.18 │ 
 Asia          │ 3772195 │ 32.70 │ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 Europe        │ 2488807 │ 21.58 │ ■■■■■■■■■■■■■■■■■■■■■■
 North America │ 3210802 │ 27.84 │ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 Oceania       │  354325 │  3.07 │ ■■■
 South America │  517347 │  4.49 │ ■■■■
(7 rows)

Geolocation GiST Indexing

The previous geoname table creation script contains the following index definition:

create index on geoname.geoname using gist(location);

Such an index is useful when searching for a specific location within our table, which contains about 11.5 million entries. PostgreSQL supports index scan based lookups in several situations, including the kNN lookup, also known as the nearest neighbor lookup.

In the arrays non-relational data type example we loaded a data set of 200,000 geo-localized tweets in the hashtag table. Here’s an extract of this table’s content:

─[ RECORD 1 ]────────────────────────────────────────────────
id       │ 720553458596757504
date     │ 2016-04-14 10:05:00+02
uname    │ Police Calls 32801
message  │ #DrugViolation at 335 N Magnolia Ave. #orlpol #opd
location │ (-81.3769794,28.5469591)
hashtags │ {#DrugViolation,#opd,#orlpol}

It’s possible to retrieve more information from the GeoNames data thanks to the following lateral left join lookup in which we implement a kNN search with order by ... <-> ... limit k clause:

  select id,
         round((hashtag.location <-> geoname.location)::numeric, 3) as dist,
         country.iso,
         region.name as region,
         district.name as district
    from hashtag
         left join lateral
         (
            select geonameid, isocode, regcode, discode, location
              from geoname.geoname
          order by location <-> hashtag.location
             limit 1
         )
         as geoname
         on true
         left join geoname.country using(isocode)
         left join geoname.region using(isocode, regcode)
         left join geoname.district using(isocode, regcode, discode)
order by id
   limit 5;

The <-> operator computes the distance in between its argument, and by using the limit 1 clause we select the nearest known entry in the geoname.geoname table for each entry in the hashtag table.

Then it’s easy to add our normalized GeoNames information from the country, region and district tables. Here’s the result we get here:

         id         │ dist  │ iso │    region    │      district       
════════════════════╪═══════╪═════╪══════════════╪═════════════════════
 720553447402160128 │ 0.004 │ US  │ Florida      │ Orange County
 720553457015324672 │ 0.004 │ US  │ Texas        │ Smith County
 720553458596757504 │ 0.001 │ US  │ Florida      │ Orange County
 720553466804989952 │ 0.001 │ US  │ Pennsylvania │ Philadelphia County
 720553475923271680 │ 0.000 │ US  │ New York     │ Nassau County
(5 rows)

To check that our GiST index is actually used, we use the explain command of PostgreSQL, with the spelling explain (costs off) followed by the whole query as above, and we get the following query plan:

\pset format wrapped
\pset columns 70

                              QUERY PLAN                              
══════════════════════════════════════════════════════════════════════
 Limit
   ->  Nested Loop Left Join
         ->  Nested Loop Left Join
               ->  Nested Loop Left Join
                     Join Filter: (geoname.isocode = country.isocode)
                     ->  Nested Loop Left Join
                           ->  Index Scan using hashtag_pkey on hasht…
…ag
                           ->  Limit
                                 ->  Index Scan using geoname_locatio…
…n_idx on geoname
                                       Order By: (location <-> hashta…
…g.location)
                     ->  Materialize
                           ->  Seq Scan on country
               ->  Index Scan using region_pkey on region
                     Index Cond: ((geoname.isocode = isocode) AND (ge…
…oname.regcode = regcode))
         ->  Index Scan using district_pkey on district
               Index Cond: ((geoname.isocode = isocode) AND (geoname.…
…regcode = regcode) AND (geoname.discode = discode))
(16 rows)

The index scan using geoname_location_idx on geoname is clear: the index has been used. On the laptop on which this book has been written, we get the result in about 13 milliseconds.

A Sampling of Countries

This dataset of more than 11 million rows is not practical to include in the book’s material for the Full Edition and Enterprise Edition, where you have a database dump or Docker image to play with. We instead take a random sample of 1% of the table’s content, and here’s how the magic is done:

begin;

create schema if not exists sample;

drop table if exists sample.geonames;

create table sample.geonames
   as select /*
              * We restrict the “export” to some columns only, so as to
              * further reduce the size of the exported file available to
              * download with the book.
              */
             geonameid,
             name,
             longitude,
             latitude,
             feature_class,
             feature_code,
             country_code,
             admin1_code,
             admin2_code,
             population,
             elevation,
             timezone
             /*
              * We only keep 1% of the 11 millions rows here.
              */
        from raw.geonames TABLESAMPLE bernoulli(1);

\copy sample.geonames to 'allCountries.sample.copy'

commit;

In this script, we use the tablesample feature of PostgreSQL to only keep a random selection of 1% of the rows in the table. The tablesample accepts several methods, and you can see the PostgreSQL documentation entitled Writing A Table Sampling Method yourself if you need to.

Here’s what the from clause documentation of the select statement has to say about the choice of bernouilli and system, included by default in PostgreSQL:

The BERNOULLI and SYSTEM sampling methods each accept a single argument which is the fraction of the table to sample, expressed as a percentage between 0 and 100. This argument can be any real-valued expression. (Other sampling methods might accept more or different arguments.) These two methods each return a randomly-chosen sample of the table that will contain approximately the specified percentage of the table’s rows. The BERNOULLI method scans the whole table and selects or ignores individual rows independently with the specified probability. The SYSTEM method does block-level sampling with each block having the specified chance of being selected; all rows in each selected block are returned. The SYSTEM method is significantly faster than the BERNOULLI method when small sampling percentages are specified, but it may return a less-random sample of the table as a result of clustering effects.

Running the script, here’s what we get:

yesql# \i geonames.sample.sql
BEGIN
CREATE SCHEMA
DROP TABLE
SELECT 115904
COPY 115904
COMMIT

Our sample.geonames table only contains 115,904 rows. Another run of the same query yielded 115,071 instead. After all the sampling is made following a random-based algorithm.

Conclusion

When dealing with geolocation data, it’s possible to put the PostgreSQL data type POINT to good use. The PostgreSQL support for GiST indexes makes it easy to then query the data, including for kNN searches and in complex queries.

While it’s possible to just use a flat table with a point column, it might be best to normalize your geolocation database schema. This helps with data quality!

This article is an extract from my book Mastering PostgreSQL in Application Development, which teaches SQL to developers so that they may replace thousands of lines of code with very simple queries. The book has a full chapter about data types in PostgreSQL, check it out!