SQL function to count distance between two points

db, development, mysql Add comments

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.

4 Responses to “SQL function to count distance between two points”

  1. COTOHA Says:

    надо ещё параметр добавить, чтоб могла в милях считать и будет ок.

  2. Skakunov Alexander Says:

    Думал об этом. Пришёл к выводу, что будет правильнее потом использовать конвертор. Километры выступают базой – а как уже надо приложению отобразить, оно само разберётся.

  3. Mike McCutcheon Says:

    Hello there. Thanks for the above SQL function to count distance between two points. I am about to try to understand how to include it in a PHP script (I’m not a professional programmer) but first it would be good to know what the comments in Russian are please :-) . Thanks again. Mike

  4. Philipp Says:

    Mike,
    1. Use mysqli php extension to work with stored routines.
    2. Use phpmyadmin tool to create stored routine on your server.

Leave a Reply


× 6 = 12

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