Geolocation distance SQL from a cities table

Question

So I have this function to calculate nearest cities based on latitude, longitude and radius parameters.

DELIMITER $$
DROP PROCEDURE IF EXISTS `world_db`.`geolocate_close_cities`$$
CREATE PROCEDURE `geolocate_close_cities`(IN p_latitude DECIMAL(8,2), p_longitude DECIMAL(8,2), IN p_radius INTEGER(5))
BEGIN
        SELECT id, country_id, longitude, latitude, city,
        truncate((degrees(acos( sin(radians(latitude)) 
        * sin(radians(p_latitude)) 
        + cos(radians(latitude)) 
        * cos(radians(p_latitude)) 
        * cos(radians(p_longitude - longitude) ) ) ) 
        * 69.09*1.6),1) as distance 
        FROM cities
        HAVING distance < p_radius
        ORDER BY distance desc;
    END$$

DELIMITER ;

Here's the structure of my cities table:

> +------------+-------------+------+-----+---------+----------------+ |
> Field      | Type        | Null | Key | Default | Extra          |
> +------------+-------------+------+-----+---------+----------------+ |
> id         | int(11)     | NO   | PRI | NULL    | auto_increment | |
> country_id | smallint(6) | NO   |     | NULL    |                | |
> region_id  | smallint(6) | NO   |     | NULL    |                | |
> city       | varchar(45) | NO   |     | NULL    |                | |
> latitude   | float       | NO   |     | NULL    |                | |
> longitude  | float       | NO   |     | NULL    |                | |
> timezone   | varchar(10) | NO   |     | NULL    |                | |
> dma_id     | smallint(6) | YES  |     | NULL    |                | |
> code       | varchar(4)  | YES  |     | NULL    |                |
> +------------+-------------+------+-----+---------+----------------+

It works very well.

What i'd lke to do (pseudcode) is something like:

SELECT * FROM cities WHERE DISTANCE(SELECT id FROM cities WHERE id={cityId}, {km))

and it'll return me the closest cities.

Any ideas of how I can do this?

At the moment, I just call the function, and then iterate through the ids into an array and then perform a WHEREIN in the city table which obviously isn't very efficient.

Any help is MUCH appreciated. Thanks.

I would take a look at [this other question](https://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates). This should help. — John Kane, Sep 21 '11 at 20:17
the lat, long, and distance would be params that are passed in. It wouldnt be exact, but it should be a decent estimation. How accurate does this need to be? — John Kane, Sep 21 '11 at 20:24
Not 'time-crticial' accurate. When a user clicks on 'users in my area'. they can select a radios and it'll filter cities close to theirs. Note: currently it's fairly accurate. Of course some will be a few km out, but this isn't a big deal. Indeed, the more accurate the better! But i'd like efficiency too. — Flukey, Sep 21 '11 at 20:26
The formula needs to include items from the cities table that are within a range of latitudes. — O. Jones, Sep 21 '11 at 20:29
Ideally the lat +- distance and the long +- distance would be done before hand (I left it in here just to show my logic). I was thinking that this would not be too inefficient because it would be a strict comparison instead of a computation. — John Kane, Sep 21 '11 at 20:31
@Ollie Jones 7 Thank you, I did not fully read the question. I assumed that the author was looking for all cities within some known boundary. I edited my response — John Kane, Sep 21 '11 at 20:41

score 1 · Accepted Answer · edited May 23 '17 at 12:10

If you can limit the maximum distance between your cities and your local position, take advantage of the fact that one minute of latitude (north - south) is one nautical mile.

Put an index on your latitude table.

Make yourself a haversine(lat1, lat2, long1, long2, unit) stored function from the haversine formula shown in your question. See below

Then do this, given mylatitude, mylongitude, and mykm.

SELECT * 
  from cities a
 where :mylatitude >= a.latitude  - :mykm/111.12
   and :mylatitude <= a.latitude  + :mykm/111.12
   and haversine(:mylatitude,a.latitude,:mylongitude,a.longitude, 'KM') <= :mykm
 order by haversine(:mylatitude,a.latitude,:mylongitude,a.longitude, 'KM')

This will use a latitude bounding box to crudely rule out cities that are too far away from your point. Your DBMS will use an index range scan on your latitude index to quickly pick out the rows in your cities table that are worth considering. Then it will run your haversine function, the one with all the sine and cosine maths, only on those rows.

I suggest latitude because the on-the-ground distance of longitude varies with latitude.

Note this is crude. It's fine for a store-finder, but don't use it if you're a civil engineer -- the earth has an elliptical shape and the this assumes it's circular.

(Sorry about the 111.12 magic number. That's the number of km in a degree of latitude, that is in sixty nautical miles.)

See here for a workable distance function.

Why does this MySQL stored function give different results than to doing the calculation in the query?

Hi @Ollie Jones. Thank you for your prompt response. To be honest, i'm not too sure I fully understand how I'd go about changing my function to match what your sql does. thanks! :-) — Flukey, Sep 21 '11 at 20:36
I createde haversine formula, here's my query: `SELECT * from cities_temp a where 52.205 >= a.latitude - 100/111.12 and 52.205 <= a.latitude + 100/111.12 and haversine(52.205,a.latitude, 0.144,a.longitude, 'KM') <= 100 order by haversine(52.205,a.latitude, 0.144,a.longitude, 'KM') ` where latitude is 52.205 and longitude is 0.144 (Cambridge, UK) — Flukey, Sep 21 '11 at 21:32

Geolocation distance SQL from a cities table

1 Answers1

Linked