Calculate distance between two long/lat

Question

After researching a lot and not coming to a solution i have to ask. How can I calculate the distance between two Long/Lat Points in Mysql? (efficient way?)

I also found this solution, but there I dont know which @lat @long is and i am not using Points because spring-data-jpa doesnt allow a Geometry Type (Except using MysqlSpatial dialect)

In Java i am calculating like this:

private double getDistanceFromLatLonInKm(double lat1,double lon1,double lat2,double lon2) {
    int R = 6371; //Earth Radius
    double dLat = degreeToRadiant(lat2-lat1);
    double dLon = degreeToRadiant(lon2-lon1);
    double a =
            Math.sin(dLat/2) * Math.sin(dLat/2) +
                    Math.cos(degreeToRadiant(lat1)) * Math.cos(degreeToRadiant(lat2)) *
                            Math.sin(dLon/2) * Math.sin(dLon/2)
            ;
    double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
    return R * c; //Distance in km
}

And my table/Entity contains one column for longitude and another for latitude.

Another question, could there be a possibility to solve this with as said before with spring data jpa?

I tried to translate the Java code to Mysql, but the Distances are to high.

SELECT name, (6371 * (
    2 * ATAN2(
                SQRT(
                    SIN(RADIANS((48.3069400-latitude))/2) * SIN(RADIANS((48.3069400-latitude)/2)) +
                    COS(RADIANS(latitude)) * COS(RADIANS(48.3069400)) * SIN(RADIANS((48.3069400-longitude)/2)) *
                    SIN(RADIANS((48.3069400-longitude)/2))
                ),SQRT(
                    1-(SIN(RADIANS((48.3069400-latitude))/2) * SIN(RADIANS((48.3069400-latitude)/2)) +
                    COS(RADIANS(latitude)) * COS(RADIANS(48.3069400)) * SIN(RADIANS((48.3069400-longitude)/2)) *
                    SIN(RADIANS((48.3069400-longitude)/2)))
                )
            )
)) as distance FROM event

Possible duplicate of [Fastest Way to Find Distance Between Two Lat/Long Points](https://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points) — Strahinja, Sep 19 '17 at 08:41

olegsv · Accepted Answer · 2017-09-24T14:52:36.390

3

In MySQL 5.7.x the most effective way IMHO is to use geometry function:

SELECT st_distance_sphere( POINT( lat1, long1) , POINT(lat2, long2 ));

you'll get the distance in meters.

On MariaDB st_distance( ... ) will return similar result.

edited Sep 24 '17 at 14:52

answered Sep 19 '17 at 09:29

olegsv

1,422
1
14
21

Indeed, the [compatibility matrix](https://mariadb.com/kb/en/library/mysqlmariadb-spatial-support-matrix/) indicates that MariaDB does not support distance functions. – olegsv Sep 19 '17 at 10:30
@MarkusG. use ST_DISTANCE () – olegsv Sep 24 '17 at 14:51
Thank you, but i have to multiply by 100 to get the kilometers but some points are not right I guess. – Markus G. Sep 25 '17 at 06:26

score 1 · Answer 2 · answered Sep 19 '17 at 09:09

Okay I found the solution and the mistake in my code above, i forgot to insert the longitude haha

For those who are also searching for such a code, the following code calculates the distance between a given point and the coordinates in a table row. Where the number with 48.306... is the given latitude and the number with 14.28... is the given longitude

The distance is calculated in km and the number 6371 is the Earth Radius.

SELECT name, (6371 * (
    2 * ATAN2(
                SQRT(
                    SIN(RADIANS((48.3069400-latitude))/2) * SIN(RADIANS((48.3069400-latitude)/2)) +
                    COS(RADIANS(latitude)) * COS(RADIANS(48.3069400)) * SIN(RADIANS((14.2858300-longitude)/2)) *
                    SIN(RADIANS((14.2858300-longitude)/2))
                ),SQRT(
                    1-(SIN(RADIANS((48.3069400-latitude))/2) * SIN(RADIANS((48.3069400-latitude)/2)) +
                    COS(RADIANS(latitude)) * COS(RADIANS(48.3069400)) * SIN(RADIANS((14.2858300-longitude)/2)) *
                    SIN(RADIANS((14.2858300-longitude)/2)))
                )
            )
)) as distance FROM event

Calculate distance between two long/lat

2 Answers2