mysql查询两个数之间的关系,MySQL查询中两个空间点之间的距离（以米为单位）...

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

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)

解决方案

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