ST_Distance_Sphere in mysql not giving accurate distance between two locations

user_4976564 0
Viewed 14812

My requirement is to calculate the distance between two locations on a given map using mysql. I found a function in mysql named ST_Distance_Sphere which returns the minimum spherical distance between two locations and/or multi locations on a sphere in meters.

When I computed the distance between two locations using ST_Distance_Sphere and the lat_lng_distance function , I found that the ST_Distance_Sphere is not giving the same distance as that of the lat_lng_distance function.

My lat_lng_distance function code is as follows 

CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
RETURNS FLOAT
DETERMINISTIC
BEGIN
    RETURN 6371 * 2 * ASIN(SQRT(
        POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),
        2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) *
        pi()/180) * POWER(SIN((lng1 - lng2) *
        pi()/180 / 2), 2) ));
END

The two locations ((38.898556,-77.037852),(38.897147,-77.043934)) passed to the ST_Distance_Sphere and lat_lng_distance function is as follows

SET @pt1 = ST_GeomFromText('POINT (38.898556 -77.037852)');
SET @pt2 = ST_GeomFromText('POINT (38.897147 -77.043934 )');

SELECT ST_Distance_Sphere(@pt1, @pt2)/1000,lat_lng_distance(38.898556,-77.037852,38.897147,-77.043934 );

The Results Obtained is as follows enter image description here

I checked the distance between the two locations on google maps and found that lat_lng_distance is close to the actual distance between the two locations. Can someone let me know why is the ST_Distance_Sphere not giving accurate distance between two locations?

mysql-workbench mysql sql
4 Answers

First of all, you could not use default SRID of 0 to do any calculations. When you use geometry from text function you have to provide 4326 (SRID that is degrees) as this is what your input format is. MYSQL might not care about it, but it should done as every serious GIS database does care and demands that input SRID was specified.

Second longitude is X and latitude is Y (not another way around)

SET @pt1 = ST_GeomFromText('POINT (-77.037852 38.898556 )', 4326);
SET @pt2 = ST_GeomFromText('POINT (-77.043934 38.897147  )',4326);

Last but not least when you are calculating distance you must transform coordinates to a local most precise SRID available for the region you are.

For example SRID 2877 is used for USA (where according your coordinates you are).

MYSQL ST_Distance_Sphere function does not care about input SRID and always return results in meters.

However it is not generally right and all other database use designated SRID units of measures applicable to it.

Bellow I am trying to do things right and transforming SRID to 2877 even MYSQL would work the same way if we left everything as 4326 (google mercator).

For 2877 PostGRES would return results in feet for the same query but MYSQL is still giving back meters. So output is devided by 1609 and we are getting the correct result of around 0.34 miles. It is a correct value as was tested using different methods 

SELECT ST_Distance_Sphere(ST_GeomFromText(ST_AsText(@pt1),2877), ST_GeomFromText(ST_AsText(@pt2),2877))/1609.344;
user_16107780

As an aside, MySQL internally implements this with an obscure and dated constant. So it really depends on your definition of accurate.

So in essence, the radius in MySQL was lifted from a lazy-copy-job from PostGIS that converted a radius in miles to meters from an obscure constant from a random 20-year old PostgreSQL module.

user_1244860

For all who are working with MYSQL 8:

For all mysql geolocation functions there must be the right SRID used, otherwise you won't get the right results.

Most commenly used is SRID 4326 (GPS Coordinates, Google Earth) AND SRID 3857 (used on Google Maps, OpenStreetMap, and most other web maps).

Example of a correct distance calculation between two points:

SELECT ST_Distance(ST_GeomFromText('POINT(51.513 -0.08)', 4326), ST_GeomFromText('POINT(37.745 -122.4383)', 4326)) / 1000 AS km;

Here is a good explanation of this topic: https://medium.com/maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443

There are some very good explanations from the mysqlserverteam:
https://mysqlserverteam.com/spatial-reference-systems-in-mysql-8-0/ https://mysqlserverteam.com/geography-in-mysql-8-0/

user_48200950
Related