Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Distance in meters between two Spacial Points in MySQL query

I am trying to query a MySQL database (version 5.7.15) to retrieve all locations that are within 300 meters from some coordinates (40.7542, -73.9961 in my case):

SELECT *
FROM location 
WHERE st_distance_sphere(latlng, POINT(40.7542, -73.9961)) <= 300

From the MySQL documentation:

ST_Distance_Sphere(g1, g2 [, radius])

Returns the mimimum spherical distance between two points and/or multipoints on a sphere, in meters, or NULL if any geometry argument is NULL or empty.

Unfortunately, the query also returns points that are more than 300 meters away from POINT(40.7542, -73.9961) such as:

  • POINT(40.7501, -73.9949) (~ 470 meters in real life)
  • POINT(40.7498, -73.9937) (~ 530 meters in real life)
like image 574
grim Avatar asked Dec 02 '16 15:12

grim


People also ask

How does MySQL calculate distance?

Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM. SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance FROM your_table. Almost a decade later, this function gives THE SAME results as Google Maps distance measurement. Thank you!

How can I find the distance between two points using latitude and longitude in PHP?

php function distance($lat1, $lon1, $lat2, $lon2, $unit) { if (($lat1 == $lat2) && ($lon1 == $lon2)) { return 0; } else { $theta = $lon1 - $lon2; $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); $dist = acos($dist); $dist = rad2deg($dist); $miles = $ ...

How do you calculate Euclidean distance in SQL?

Euclidean Distance = SquareRoot(((x2-x1)^2)+((y2-y1)^2)) SquareRoot can be written as (something)^(0.5) I implemented like that. CAST(ROUND(LONG_W ,4) as numeric(36,4)) is for taking value upto 4 decimal point.


2 Answers

Note that in MySql the order of coordinates are:
1. POINT(lng, lat) - no SRID
2. ST_GeomFromText('POINT(lat lng)', 4326) - with SRID

select st_distance_sphere(POINT(-73.9949,40.7501), POINT( -73.9961,40.7542)) 

will return 466.9696023582369, as expected, and 466.9696023582369 > 300 of course

like image 134
Mituha Sergey Avatar answered Oct 17 '22 16:10

Mituha Sergey


Just to make it clear for future people (like myself):

Mituha Sergey has answered the question in comments on the OP. The problem was that OP was using POINT(lat, lng) when in fact MySQL expects POINT(lng, lat).

Not sure about the time OP posted the question, but as of today the official documentation makes it a bit clearer:

The geometry arguments should consist of points that specify (longitude, latitude) coordinate values:

  • Longitude and latitude are the first and second coordinates of the point, respectively.
  • Both coordinates are in degrees.
  • Longitude values must be in the range (-180, 180]. Positive values are east of the prime meridian.
  • Latitude values must be in the range [-90, 90]. Positive values are north of the equator.

From: https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere

And if you're getting "invalid arguments" errors it's probably because of that. Try adding WHERE lat between -90 and 90 AND lng between -180 and 180 just to be on the safe side haha :)

like image 24
Rafael Lins Avatar answered Oct 17 '22 17:10

Rafael Lins