Maxmind is a cool company that offers location services for a small monthly fee. In addition, they offer a subset of the databases their services use to the public under an Open Source creative commons attribution licenses. If you download and load one of these databases, you’ll be able to do IP to location lookups. Cool stuff!

For Bridal App, the platform for the bridal industry that I’ve been working on, we want to show the visitor those bridal dresses that are actually available in their area. For highly detailed location data (provided by the W3C Geolocation Api) we need to ask the user’s permission, but we can get a rough estimate of where the visitor is based on their IP address so we can start out at least somewhere in the right area.

I’ve found Maxmind’s Geolite2 City database to offer remarkable precision, mostly pointing out the correct city and sometimes even down to the zipcode level. However, it doesn’t always work.

When we are not able to detect the user’s location based on their IP, we want to fallback to a default location based on their browser locale settings. Because Maxmind’s database is a little biased towards the US, it will often give us a location near the center of the United States, which is not convenient for us as we are based in Europe. BTW, there are people living at those fallback coordinates and they are having a hard time… Talk about the unintended consequences tech decisions can have sometimes!

Anyway, we wanted to get a list of all default coordinates for each country so we could look up the defaults based on the 2-letter country code from their browser locale. Here’s the query I ended up writing:

select distinct b.geoId, rg.countryName, rg.countryCode, b.latitude, b.longitude
from geoip_blocks_ipv4 b, geoip_regions rg
where b.geoId in (
  select r.geoId
  from geoip_regions r
  where r.locale = ‘en’
  and r.subdivision1Name = ”
  and r.subdivision2Name = ”
  and r.city = ”
  and countryCode <> ”
)
and b.countryGeoId = b.geoId
and b.representedCountryGeoId is null
and b.postalCode is null
and not b.latitude is null
and b.geoId = rg.geoId
and rg.locale = ‘en’
order by rg.countryCode asc;

It runs slowly, then yields 241 results after 7 – 8 seconds, a small snippet of which is shown below:

geoId, countryName, countryCode, latitude, longitude
3041565, Andorra, AD, 42.5, 1.5
290557, United Arab Emirates, AE, 24, 54
1149361, Afghanistan, AF, 33, 65
3576396, Antigua and Barbuda, AG, 17.05, -61.8
3573511, Anguilla, AI, 18.25, -63.1667
783754, Albania, AL, 41, 20
174982, Armenia, AM, 40, 45
3351879, Angola, AO, -12.5, 18.5
3865483, Argentina, AR, -34.6033, -58.3817
2782113, Austria, AT, 48.2, 16.3667
2077456, Australia, AU, -27, 133
587116, Azerbaijan, AZ, 40.5, 47.5
3277605, Bosnia and Herzegovina, BA, 44, 18
3374084, Barbados, BB, 13.1667, -59.5333
1210997, Bangladesh, BD, 23.7, 90.375

I’m not much of a SQL guru, so the query I came up with is slow and mostly wrong probably… As I have no idea whether the criteria I am using are the right ones. I arrived at this mostly by experimentation, guesses and trial and error. But at least it seems it’s giving us a location per country and the one for the US is actually giving us the correct result, so I’m using this until I come up with something better.

I’m posting this in the hope it may help someone out there trying to do something similar to us, as my searches came up empty and I had to invent this myself. If you think you can improve on my query, please leave a comment below!