Data, Maps, Usability, and Performance

Reverse Geocoding with MySql and MaxMind

Last updated on May 26, 2016 in Development

MySQl for Reverse Geocoding

Reverse geocoding is the process of translating the geo location of a point (latitude and longitude values) into a readable address or place name. Google has the Geocoding API which includes Address Loookup and I have previously written about using Google for Reverse Geocoding. Here is an example of Google Reverse Geocoding, and here is an example of a reverse geocoding on a Google Map. This is a good tutorial explaining how to use Google’s GeoCoding API, but what about other reverse geocode services and options?

There are a few other geocoding APIs like Bing’s Find a Location by Point, MapQuest’s Reverse GeoCode Example, ArcGIS reverse geocode method, AfriGIS Reverse Geocoding API, Open Street Map reverse geocoding query, and CloudMate’s Reverse GeoCoding Method. Personally, I would go with GeoNames Web Services as it is available under Creative Commons Attribution License and you can even download their database. If you are already using MaxMind to translate IP addresses into place names, you could also leverage that database as it has city names with latitude and longitude values. But, how do you query such a database to get city names with some proximity of one geo coordinate?

After downloading MaxMind’s GeoLite City Database and importing it into MySQL, you will see a bunch of geo fields in the location table: locID, country, region, city, postalCode, latitude, longitude, dmaCode, areaCode. If you already have some geo coordinate, a sql query like this:

SELECT city FROM `location` WHERE `latitude` = 25.9388 AND `longitude` = -80.2144

would result in Miami. But, most use cases involve searching for closest city near some location and for that we need to search with some surrounding radius or distance calculation. My first take on this was to use the MySQL BETWEEN clause and add a decimal point above and below your current geo coordinate. If you think about the Miami example above, you could have made this query:

SELECT * FROM `location` WHERE (`longitude` BETWEEN -80.2 AND -80.1) AND (`latitude` BETWEEN 25.9 AND 26);

Which gives you 10 results, with some duplicates of Miami and other nearby cities like North Miami Beach, Hallandale, and Aventura. Keep in mind that the MySQL BETWEEN clause expects to get a lower value first and higher value second, so BETWEEN -80.1 and -80.2 would not give you any results. Now, these 10 results are great, but it is missing a distance calculation as I want the closes city to my one geo coordinate. After doing some research, I have found the perfect MySQL query to give you the needed reverse geocoding:

SELECT * , (
3959 * ACOS( COS( RADIANS( $LATITUDE_VALUE ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS( $LONGITUDE_VALUE ) ) + SIN( RADIANS( $LATITUDE_VALUE ) ) * SIN( RADIANS( latitude ) ) )
) AS distance
FROM location
ORDER BY distance ASC
LIMIT 0 , 10

And this is how the query for the Maimi point (25.9, -80.2) would look like:

SELECT * , (
3959 * ACOS( COS( RADIANS( 25.9 ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS( -80.2 ) ) + SIN( RADIANS( 25.9 ) ) * SIN( RADIANS( latitude ) ) )
) AS distance
FROM location
ORDER BY distance ASC
LIMIT 0 , 10

As you can see from the blurry post picture above, the results are returned and ordered by distance from the given geo coordinate. So, if you are already using MaxMind for IP to Address translation, you can now also use it for Geo Coordinate to Address translation.

External:

Batch Reverse GeoCode
Geocoding Toolbox for Python
Geolocation API for WikiPedia

Tags: , , ,

Facebook Twitter Hacker News Reddit More...