Determining the distance between geographic points in MySQL

When developing a modern website, it is often necessary to implement the functionality of outputting nearby geographic points. The best way to solve this problem is to shift the work of implementing the definition of points on the shoulders of MySQL. More specifically, we will need the features of MySQL spatial extensions (prior to version 5.0.16, these extensions were available only for MyISAM, later versions of MySQL support the work of spatial extensions with InnoDB, NDB, BDB and ARCHIVE).

The distance between points will be calculated according to the formula of haversinus . The formula allows you to get the distance between points with a very low error (the magnitude of the error is directly proportional to the distance between points, and does not exceed 10-20 kilometers when calculating very large distances, for example between Google headquarters in California (37.422045, -122.084347) and the opera house in Sydney, Australia (-33.856553, 151.214696)).


To correctly calculate nearby points with a distance, you will need to create the following stored functions and procedures:
')

geodist function

geodist () determines the distance between points by their coordinates.

--  6371 -     ,       ,         DELIMITER $$ DROP FUNCTION IF EXISTS geodist $$ CREATE FUNCTION geodist ( src_lat DECIMAL(9,6), src_lon DECIMAL(9,6), dst_lat DECIMAL(9,6), dst_lon DECIMAL(9,6) ) RETURNS DECIMAL(6,2) DETERMINISTIC BEGIN SET @dist := 6371 * 2 * ASIN(SQRT( POWER(SIN((src_lat - ABS(dst_lat)) * PI()/180 / 2), 2) + COS(src_lat * PI()/180) * COS(ABS(dst_lat) * PI()/180) * POWER(SIN((src_lon - dst_lon) * PI()/180 / 2), 2) )); RETURN @dist; END $$ DELIMITER ; 

geodist_pt function

geodist_pt () is a wrapper for geodist (), and works with the coordinates of points as an object of type POINT.

 DELIMITER $$ DROP FUNCTION IF EXISTS geodist_pt $$ CREATE FUNCTION geodist_pt (src POINT, dst POINT) RETURNS DECIMAL(6,2) DETERMINISTIC BEGIN RETURN geodist(X(src), Y(src), X(dst), Y(dst)); END $$ DELIMITER ; 

geobox_pt procedure

Calculates the coordinates of the upper left and lower right corners of the search area using the geobox () procedure, then converts the obtained coordinates into POINT objects.

 -- pt ->     -- dist ->      -- top_lft ->      (  POINT) -- bot_rgt ->      (  POINT) DELIMITER $$ DROP PROCEDURE IF EXISTS geobox_pt $$ CREATE PROCEDURE geobox_pt ( IN pt POINT, IN dist DECIMAL(6,2), OUT top_lft POINT, OUT bot_rgt POINT ) DETERMINISTIC BEGIN CALL geobox(X(pt), Y(pt), dist, @lat_top, @lon_lft, @lat_bot, @lon_rgt); SET top_lft := POINT(@lat_top, @lon_lft); SET bot_rgt := POINT(@lat_bot, @lon_rgt); END $$ DELIMITER ; 

geobox procedure

Calculates search field coordinates

 -- src_lat, src_lon ->     -- dist ->      -- lat_top, lon_lft ->       -- lat_bot, lon_rgt ->       DELIMITER $$ DROP PROCEDURE IF EXISTS geobox $$ CREATE PROCEDURE geobox ( IN src_lat DECIMAL(9,6), IN src_lon DECIMAL(9,6), IN dist DECIMAL(6,2), OUT lat_top DECIMAL(9,6), OUT lon_lft DECIMAL(9,6), OUT lat_bot DECIMAL(9,6), OUT lon_rgt DECIMAL(9,6) ) DETERMINISTIC BEGIN SET lat_top := src_lat + (dist / 69); SET lon_lft := src_lon - (dist / ABS(COS(RADIANS(src_lat)) * 69)); SET lat_bot := src_lat - (dist / 69); SET lon_rgt := src_lon + (dist / ABS(COS(RADIANS(src_lat)) * 69)); END $$ DELIMITER ; 

Example of use:

Suppose there is a table in which geographic data is stored. The table has the following structure:

 CREATE TABLE geo ( id INT, name VARCHAR(100), x DECIMAL(9,6), y DECIMAL (9,6) ); 

then, to get all settlements located at a distance of 200 kilometers from the starting point, you will need to do the following:

 --   src        POINT (  Point()    POINT    ) SELECT @src := Point(x,y) FROM geo WHERE name = ''; --  " "    CALL geobox_pt(@src, 200.0, @top_lft, @bot_rgt); --   SELECT g.name, geodist(X(@src), Y(@src), x, y) AS dist FROM geo g WHERE x BETWEEN X(@top_lft) AND X(@bot_rgt) AND y BETWEEN Y(@top_lft) AND Y(@bot_rgt) HAVING dist < 200.0 ORDER BY dist desc;