Search code examples
mysqlgeospatial

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

  • Note that in MySql the order of coordinates are:

    1. POINT(lng, lat) - no SRID

    2. ST_GeomFromText('POINT(lng lat)', 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