Search⌘ K
Home
Courses
The Art of PostgreSQL

Geolocation Data Loading

Explore how to import and process geolocation data in PostgreSQL by using extensions like ip4r and tools such as pgloader. Learn to load, transform, and query IP address ranges alongside location data to enable geographic analysis and find nearby points of interest within a single SQL query.

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:

PostgreSQL
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:

PostgreSQL
/*
 * 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 ...