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:
- A database of cities with coordinates (having a population value at least for major cities is a big plus)
- 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.