Reverse geocoding in your app

db, development, mysql Add comments

Reverse geocoding is a process of getting toponym name (city name) by its coordinates.

To make this, you can use a remote service like I have already described, or implement it yourself.

To do so you need two things:

  1. A database of cities with coordinates (having a population value at least for major cities is a big plus)
  2. A method to find a closest city

#1 is left for you, let’s talk about #2.

In the previous post I introduced the DISTANCE function (SQL) to count a distance between 2 points by coordinates. Let’s use it to find the biggest city closest to a given point:

DELIMITER //
DROP FUNCTION IF EXISTS REVERSE_GEOCODE;//
CREATE FUNCTION REVERSE_GEOCODE( latitude DOUBLE, longitude DOUBLE, radius DOUBLE ) 
    RETURNS DOUBLE READS SQL DATA DETERMINISTIC
    COMMENT 'Finds a city by coordinates given. Returns City ID.'
BEGIN
    DECLARE cityID INT(10) DEFAULT 0;
    DECLARE d DOUBLE DEFAULT 0; /* useless var */
    
    SELECT id, DISTANCE(`lat`, `lng`, latitude, longitude) AS dist
    INTO cityID, d
    FROM `geo_cities`
    WHERE lat BETWEEN FLOOR(latitude - 0.5) 
                  AND CEIL(latitude  + 0.5)
      AND lng BETWEEN FLOOR(longitude- 0.5)
                  AND CEIL(longitude + 0.5)
    HAVING dist < radius
    ORDER BY IF(population > 0, population, 1) * (1/IF(dist > 0, dist, 1)) DESC
    LIMIT 1;
    
    RETURN cityID;
END; //
DELIMITER ;

Well, this function orders cities by (population and distance) function — so that the biggest closest city is returned.

WHERE clause is supposed to shorten the number of cities which are looked through; otherwise we would have to search through whole world’s cities. In my solution the search is limited by a reasonably small rectangle.

In the next post I’ll tell you how to find a biggest city depending on current Google Maps zoom, so that it won’t give you a small city near Paris if you click on France at a world-level map view.

One Response to “Reverse geocoding in your app”

  1. dllud Says:

    Hi,

    First of all thanks for publishing this function. It was quite helpful.

    Your idea of limiting the search to a small area using the WHERE clause works great at reducing the query time. Though your implementation is rather simplistic and won’t work near the poles and near the prime and 180th meridians. Besides, the area isn’t a proper square when projected on the Earth’s surface, it will be more like a trapezoid with curve edges.

    The ideal solution would be to write the inverse function of DISTANCE. Then instrument it in order to: given a point p and a distance d, return the 4 points which are d away from p but at the same latitude or longitude. You could then use these 4 points in the WHERE clause to limit the query.

    Instead of the proper solution, I implemented a work around based on your solution. Here it is:

    DELIMITER //
    CREATE FUNCTION REVERSE_GEOCODE(lati float, longi float)
    RETURNS smallint READS SQL DATA DETERMINISTIC
    COMMENT ‘Finds a city by coordinates given. Returns city guid.’
    BEGIN
    DECLARE city_guid smallint unsigned DEFAULT 0;
    DECLARE d float DEFAULT 0; /* useless var */
    DECLARE use_or tinyint unsigned DEFAULT 0;
    DECLARE lati_floor float DEFAULT lati – 5;
    DECLARE lati_ceil float DEFAULT lati + 5;
    DECLARE longi_floor float DEFAULT longi – 10;
    DECLARE longi_ceil float DEFAULT longi + 10;
    IF lati >= 70 THEN
    SET lati_floor = 70;
    SET lati_ceil = 90;
    SET longi_floor = -180;
    SET longi_ceil = 180;
    ELSEIF lati <= -70 THEN
    SET lati_floor = -90;
    SET lati_ceil = -70;
    SET longi_floor = -180;
    SET longi_ceil = 180;
    ELSE
    IF longi_floor 180 THEN
    SET longi_ceil = -360 + longi_ceil;
    SET use_or = 1;
    END IF;
    END IF;

    SELECT guid, DISTANCE(latitude, longitude, lati, longi) AS dist
    INTO city_guid, d
    FROM cities
    WHERE latitude BETWEEN lati_floor AND lati_ceil
    AND IF(use_or = 1, longitude >= longi_floor OR longitude <= longi_ceil, longitude BETWEEN longi_floor AND longi_ceil)
    ORDER BY dist ASC
    LIMIT 1;

    RETURN city_guid;
    END; //
    DELIMITER ;

    Note that I am using large search areas. I can do that because my database is small (only ~5000 cities worldwide). In a bigger database you would have to reduce the area size.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in