Loading MaxMind Geolite Data with pgloader

The MaxMind provides a free dataset for geolocation, which is quite popular. Using pgloader you can download the lastest version of it, extract the CSV files from the archive and load their content into your database directly.

The Command

To load data with pgloader you need to define in a command the operations in some details. Here's our example for loading the Geolite data:

You can see the full list of options in the pgloader reference manual, with a complete description of the options you see here.

Note that while the Geolite data is using a pair of integers (start, end) to represent ipv4 data, we use the very poweful ip4r PostgreSQL Extension instead.

The transformation from a pair of integers into an IP is done dynamically by the pgloader process.

Also, the location is given as a pair of float columns for the longitude and the latitude where PostgreSQL offers the point datatype, so the pgloader command here will actually transform the data on the fly to use the appropriate data type and its input representation.

Loading the data

Here's how to start loading the data. Note that the ouput here has been edited so as to facilitate its browsing online.

The timing of course includes the transformation of the 1.9 million pairs of integer into a single ipv4 range each. The finally step consists of creating the GiST specialized index as given in the main command:

CREATE INDEX blocks_ip4r_idx ON geolite.blocks USING gist(iprange);

That index will then be used to speed up queries wanting to find which recorded geolocation contains a specific IP address: