I want to be free

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.

Useful function.

DELIMITER //

DROP FUNCTION IF EXISTS DISTANCE; //

CREATE FUNCTION DISTANCE( lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE )
    RETURNS DOUBLE NO SQL DETERMINISTIC
    COMMENT 'counts distance (km) between 2 points on Earth surface'
BEGIN
    DECLARE dtor DOUBLE DEFAULT 57.295800;

    RETURN (6371 * acos(sin( lat1/dtor) * sin(lat2/dtor) +
        cos(lat1/dtor) * cos(lat2/dtor) *
        cos(lon2/dtor - lon1/dtor)));
END; //

DELIMITER ;

In next post I will explain how I used this function to implement a reverse geocoding — it’s useful when you need to find a closest city by given coordinates.

I attempted to setup Sphinx on Windows, but was not trivial — problem was that I couldn’t run the SearchD as a Windows service since I got 1067 error (“Process Terminated unexpectedly“).

It’s not obvious but helpful to know how you can get the error message the service crashes with. To do so, go to Control panel → Administration → Event Viewer. Here you can get all notices and error messages that the service produces:

If you get your service running and search tool gives you the correct results, but your PHP application gets FALSE as query result, try to see what SphinxClient’s getLastError() method returns:

$s = new SphinxClient;
$s->setServer("localhost", 3312);
...
$result = $s->query('test');
echo $s->getLastError();

In my case there was a following message:

searchd error:
  client version is higher than daemon version
  (client is v.1.22, daemon is v.1.19)

At times, some records in a recordset are more important than others — e.g. a featured product in a products list. In such a case you can make 2 requests to get the featured item first, then to load other N-1 items, and then display them.

If you want to make it by one move, you can use a feature of preferrable sorting — you can define what value and in which field goes first if such value is present.

For instance, you want to show an item with item_id=15 first:

SELECT *
FROM items
ORDER BY item_id IN (15) DESC

Which returns something like (featured item goes first):

+----------+------------+
| item_id  | name       |
+----------+------------+
| 15       | Product 15 |
| 32       | Product 32 |
| 31       | Product 31 |
| 30       | Product 30 |
| 29       | Product 29 |
| ...      | ...        |
+----------+------------+