MySQL查询中两个空间点之间的距离(以米为单位)

本文介绍了MySQL查询中两个空间点之间的距离(以米为单位)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试查询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