Distance in meters between two Spacial Points in MySQL query

Question

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)

point must be set by this way `POINT(lng, lat)` `select st_distance_sphere(POINT(-73.9949,40.7501), POINT( -73.9961,40.7542)) ` give us 466.9696023582369 - as expected — Mituha Sergey, Feb 12 '17 at 01:46
@MituhaSergey but why is the query returning distances greater than 300? — grim, Feb 12 '17 at 01:50
because you're using incorrect POINT. for example `select st_distance_sphere(POINT(40.7501, -73.9949), POINT(40.7542, -73.9961))` will return 183.3146597410617 <= 300. just use correct args positions POINT(lng, lat), lng as first arg, lat - as second — Mituha Sergey, Feb 12 '17 at 01:55
Hi, I am trying to solve a similar question however my location is set in this format b'\x00\x00\x00\x00\x01\x01\x00\x00\x00\xca\x15\xde\xe5"\xc2_@\x04\xe6!S>\xbeB@'. My guess is that it is set of points creating a polygon however it is point datatype — haneulkim, Sep 19 '19 at 07:15

score 33 · Accepted Answer · edited Mar 24 '20 at 09:47

33

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

edited Mar 24 '20 at 09:47

daniel.gindi

3,457
1
30
36

answered Feb 12 '17 at 02:05

Mituha Sergey

444
6
4

score 9 · Answer 2 · edited Nov 06 '18 at 22:46

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 :)

Thanks to @MacAnthony for fixing the typos ^^ – Rafael Lins Nov 07 '18 at 10:34 — Rafael Lins, Nov 07 '18 at 10:34

Distance in meters between two Spacial Points in MySQL query

2 Answers2

Linked