我正在尝试查询MySQL数据库(版本5.7.15)以检索距某些坐标(在我的情况下为40.7542,-73.9961)300米以内的所有位置:
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来自 MySQL文档:
ST_Distance_Sphere(g1,g2 [,radius])
返回两点之间的最小球面距离和/或 球体上的多点,以米为单位,如果有任何几何参数,则为NULL 为NULL或为空.
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.
不幸的是,查询还返回距POINT(40.7542,-73.9961)超过300米的点,例如:
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米)
- POINT(40.7498,-73.9937)(在现实生活中约为530米)
请注意,在 MySql 中,坐标顺序为: 1. POINT(lng, lat)-无SRID 2. ST_GeomFromText('POINT(lat lng)', 4326)-具有SRID
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))将按预期返回466.9696023582369,当然还会返回466.9696023582369> 300当然
will return 466.9696023582369, as expected, and 466.9696023582369 > 300 of course
更多推荐
MySQL查询中两个空间点之间的距离(以米为单位)
发布评论