Geolocation Data Loading

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: