The Art of PostgreSQL/

...

/

Geolocation Data Loading

Geolocation Data Loading

Load the Geolocation data to learn more about prospects provided by PostgreSQL.

We'll cover the following...

We’re going to have a look at how to geolocalize an IP address and locate the nearest pub, all within a single SQL query.

Locating the nearest pub

For that, we’re going to use the ip4r extension from RhodiumToad.

The first step is to find a Geolocation database, and several providers offer that. The one we ended up choosing for the example is the MaxMind free database available at GeoLite Free Downloadable Databases.

Note: We can access the databases from this website after logging in.

After having a look at the files there, we define the table schema we want and load the archive using pgloader. So, first, the target schema is created using the following script:

Press + to interact
create extension if not exists ip4r;
create schema if not exists geolite;
create table if not exists geolite.location
(
   locid      integer primary key,
   country    text,
   region     text,
   city       text,
   postalcode text,
   location   point,
   metrocode  text,
   areacode   text
);
       
create table if not exists geolite.blocks
(
   iprange    ip4r,
   locid      integer
);
create index if not exists blocks_ip4r_idx on geolite.blocks using gist(iprange);

The data can now be imported to those target tables thanks to the following pgloader command, which is quite involved:

Press + to interact
/*
 * Loading from a ZIP archive containing CSV files.
*/
LOAD ARCHIVE
   FROM https://files-cdn.liferay.com/mirrors/geolite.maxmind.com/download/geoip/database/GeoLiteCity-latest.zip
-- FROM https://files-cdn.liferay.com/mirrors/geolite.maxmind.com/download/geoip/database/GeoLiteCity-latest.zip
   INTO postgres://postgres:postgres@localhost:5432/geonames
   BEFORE LOAD EXECUTE 'geolite.sql'
   LOAD CSV
        FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/
             WITH ENCODING iso-8859-1
             (
                locId,
                country,
                region     [ null if blanks ],
                city       [ null if blanks ],
                postalCode [ null if blanks ],
                latitude,
                longitude,
                metroCode  [ null if blanks ],
                areaCode   [ null if blanks ]
             )
        INTO postgres://postgres:postgres@localhost:5432/geonames
        TARGET TABLE geolite.location
             (
                locid,country,region,city,postalCode,
                location point using (format nil "(~a,~a)" longitude latitude),
                metroCode,areaCode
             )
        WITH skip header = 2,
             drop indexes,
             fields optionally enclosed by '"',
             fields escaped by double-quote,
             fields terminated by ','
  AND LOAD CSV
        FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/
             WITH ENCODING iso-8859-1
             (
                startIpNum, endIpNum, locId
             )
        INTO postgres://postgres:postgres@localhost:5432/geonames
        TARGET TABLE geolite.blocks
             (
                iprange ip4r using (ip-range startIpNum endIpNum),
                locId
             )
        WITH skip header = 2,
             drop indexes,
             fields optionally enclosed by '"',
             fields escaped by double-quote,
             fields terminated by ',';

The pgloader command describes the file format so ...