Calculating Distance in MySQL using Spatial Point Type

I have recently been playing around with MySQL Spatial functionality. Although not complete by any means it is a good way to start thinking of storing your location based data. One of the most useful things to do is find the distance between two points. There is a place-holder in MySQL 5 called DISTANCE, however this isn’t yet implemented.

In the past we would have used something like the following to find the distance between two lat/lng points:

SQRT(POW(69.1 * (fld_lat - ( $lat )), 2) + POW(69.1 * (($lon) - fld_lon) * COS(fld_lat / 57.3 ), 2 )) AS distance

However we can now use Spatial datatypes. First thing to do is create a table and add some dummy data to it (note the lat lng values don’t have a , separating them):

INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London Eye", GeomFromText( 'POINT(51.5033 -0.1197)' ));
INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London School of Economics", GeomFromText( 'POINT(51.514 -0.1167)' ));
INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London Bridge", GeomFromText( 'POINT(51.5081 -0.0878)' ));
INSERT INTO spatialTable (placeName, geoPoint) VALUES( "Tower of London", GeomFromText( 'POINT(51.5081 -0.0761)' ));

Once we have the data in the DB we can extract the lat and lng values using the following:

SELECT X(geoPoint), Y(geoPoint) FROM spatialTable;

Now we can use the following piece of SQL to get the distance between two points. In this example geoPoint is a POINT entered into the DB using the INSERT above. GeomFromText(‘POINT(52.5177, -0.0968)’ is a Lat Lng value I want to calculate a distance from.

SELECT
placeName,
(GLength(
LineStringFromWKB(
  LineString(
    geoPoint,
    GeomFromText('POINT(51.5177 -0.0968)')
  )
 )
))
AS distance
FROM spatialTable
ORDER BY distance ASC