使用 MySQL 空间扩展选择圆内的点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21168380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use MySQL spatial extensions to select points inside circle
提问by jskidd3
I have a table called flagsthat contains a column called coordinatesthat is full of MySQL 'points'. I need to perform a query where I get all the flags within a circle based on a latitude and longitude position with 100m radius.
我有一个名为的表flags，其中包含一个名为coordinates充满 MySQL“点”的列。我需要执行一个查询，根据半径为 100m 的纬度和经度位置获取圆圈内的所有标志。
From a usage point of view this is based around the user's position. For example, the mobile phone would give the user's latitude and longitude position and then pass it to this part of the API. It's then up to the API to create an invisible circle around the user with a radius of 100 metres and then return the flags that are in this circle.
从使用的角度来看，这是基于用户的位置。比如手机会给出用户的经纬度位置，然后传递给这部分API。然后由 API 在用户周围创建一个半径为 100 米的不可见圆圈，然后返回该圆圈中的标志。
It's this part of the API I'm not sure how to create as I'm unsure how to use SQL to create this invisible circle and select points only within this radius.
这是 API 的这一部分，我不确定如何创建，因为我不确定如何使用 SQL 创建这个不可见的圆并仅在此半径内选择点。
Is this possible? Is there a MySQL spatial functionthat will help me do this?
这可能吗？是否有一个MySQL 空间函数可以帮助我做到这一点？
I believe the Buffer()function can do this but I can't find any documentation as to how to use it (eg example SQL). Ideally I need an answer that shows me how to use this function or the closest to it. Where I'm storing these coordinates as geospatial points I should be using a geospatial function to do what I'm asking to maximize efficiency.
我相信该Buffer()函数可以做到这一点，但我找不到任何关于如何使用它的文档（例如 SQL 示例）。理想情况下，我需要一个答案，向我展示如何使用此功能或最接近它的功能。在我将这些坐标存储为地理空间点的地方，我应该使用地理空间函数来执行我要求的最大化效率的操作。
Flags table:
标志表：
- id
- coordinates
- name
- ID
- 坐标
- 姓名
Example row:
示例行：
1 | [GEOMETRY - 25B] | Tenacy AB
1 | [几何 - 25B] | 坚韧AB
For the flags table I have latitude, longitude positions and easting and northing (UTM)
对于标志表，我有纬度、经度位置和东距和北距 (UTM)
The user's location is just standard latitude/longitude but I have a library that can conver this position to UTM
用户的位置只是标准的纬度/经度，但我有一个可以将此位置转换为 UTM 的库
回答by O. Jones
There are no geospatial extension functions in MySQL supporting latitude / longitude distance computations.There is as of MySQL 5.7.
MySQL 中没有支持纬度/经度距离计算的地理空间扩展函数。从 MySQL 5.7 开始。
You're asking for proximity circles on the surface of the earth. You mention in your question that you have lat/long values for each row in your flagstable, and also universal transverse Mercator(UTM) projected values in one of several different UTM zones. If I remember my UK Ordnance Survey maps correctly, UTM is useful for locating items on those maps.
你要求地球表面的邻近圆。您在问题中提到，您的flags表格中的每一行都有经纬度值，以及几个不同UTM 区域之一中的通用横向墨卡托(UTM) 投影值。如果我记得我的英国军械测量地图正确的话，UTM 可用于在这些地图上定位项目。
It's a simple matter to compute the distance between two points in the same zonein UTM: the Cartesian distance does the trick. But, when points are in different zones, that computation doesn't work.
在 UTM 中计算同一区域中两点之间的距离是一件简单的事情：笛卡尔距离可以解决问题。但是，当点位于不同区域时，该计算不起作用。
Accordingly, for the application described in your question, it's necessary to use the Great Circle Distance, which is computed using the haversine or another suitable formula.
因此，对于您的问题中描述的应用程序，有必要使用Great Circle Distance，它是使用半正弦或其他合适的公式计算的。
MySQL, augmented with geospatial extensions, supports a way to represent various planar shapes (points, polylines, polygons, and so forth) as geometrical primitives. MySQL 5.6 implements an undocumented distance function st_distance(p1, p2). However, this function returns Cartesian distances. So it's entirely unsuitablefor latitude and longitude based computations. At temperate latitudes a degree of latitude subtends almost twice as much surface distance (north-south) as a degree of longitude(east-west), because the latitude lines grow closer together nearer the poles.
MySQL 增加了地理空间扩展，支持将各种平面形状（点、折线、多边形等）表示为几何图元的方法。MySQL 5.6 实现了一个未公开的距离函数st_distance(p1, p2)。但是，此函数返回笛卡尔距离。所以它完全不适合基于纬度和经度的计算。在温带纬度地区，纬度对着表面的距离（南北）几乎是经度（东西向）的两倍，因为纬度线越靠近两极越靠近。
So, a circular proximity formula needs to use genuine latitude and longitude.
因此，圆形邻近公式需要使用真正的纬度和经度。
In your application, you can find all the flagspoints within ten statute miles of a given latpoint,longpointwith a query like this:
在您的应用程序中，您可以使用如下查询找到flags给定地点 10 法定英里范围内的所有点latpoint,longpoint：
SELECT id, coordinates, name, r,
units * DEGREES( ACOS(
COS(RADIANS(latpoint))
* COS(RADIANS(X(coordinates)))
* COS(RADIANS(longpoint) - RADIANS(Y(coordinates)))
+ SIN(RADIANS(latpoint))
* SIN(RADIANS(X(coordinates))))) AS distance
FROM flags
JOIN (
SELECT 42.81 AS latpoint, -70.81 AS longpoint,
10.0 AS r, 69.0 AS units
) AS p ON (1=1)
WHERE MbrContains(GeomFromText (
CONCAT('LINESTRING(',
latpoint-(r/units),' ',
longpoint-(r /(units* COS(RADIANS(latpoint)))),
',',
latpoint+(r/units) ,' ',
longpoint+(r /(units * COS(RADIANS(latpoint)))),
')')), coordinates)
If you want to search for points within 20 km, change this line of the query
如果要搜索 20 公里以内的点，请更改查询的这一行
20.0 AS r, 69.0 AS units
to this, for example
对此，例如
20.0 AS r, 111.045 AS units
ris the radius in which you want to search. unitsare the distance units (miles, km, furlongs, whatever you want) per degree of latitude on the surface of the earth.
r是您要搜索的半径。 units是地球表面每纬度纬度的距离单位（英里、公里、弗隆，无论你想要什么）。
This query uses a bounding lat/long along with MbrContainsto exclude points that are definitely too far from your starting point, then uses the great circle distance formula to generate the distances for the remaining points. An explanation of all this can be found here. If your table uses the MyISAM access method and has a spatial index, MbrContainswill exploit that index to get you fast searching.
此查询使用边界纬度/经度以及MbrContains排除离起点肯定太远的点，然后使用大圆距离公式生成剩余点的距离。可以在此处找到所有这些的解释。如果您的表使用 MyISAM 访问方法并具有空间索引，MbrContains则将利用该索引让您快速搜索。
Finally, the query above selects all the points within the rectangle. To narrow that down to only the points in the circle, and order them by proximity, wrap the query up like this:
最后，上面的查询选择矩形内的所有点。要将其缩小到仅圆圈中的点，并按接近度对它们进行排序，请按如下方式包装查询：
SELECT id, coordinates, name
FROM (
/* the query above, paste it in here */
) AS d
WHERE d.distance <= d.r
ORDER BY d.distance ASC
回答by Mihado
UPDATE
更新
Use ST_Distance_Sphere() to calculate distances using a lat/long
使用 ST_Distance_Sphere() 使用纬度/经度计算距离
回答by Andrew - OpenGeoCode
This assumes the coordinates in the table are stored as a POINT() datatype in a column labeled 'point'. The function X(point) and Y(point) extract the latitude and longitude values from the point value respectively.
这假设表中的坐标以 POINT() 数据类型存储在标记为“point”的列中。函数 X(point) 和 Y(point) 分别从点值中提取纬度和经度值。
SET @lat = the latitude of the point
SET @lon = the longitude of the point
SET @rad = radius in Kilometers to search from the point
SET @table = name of your table
SELECT
X(point),Y(point),*, (
6373 * acos (
cos ( radians( @lat ) )
* cos( radians( X(point) ) )
* cos( radians( Y(point) ) - radians( @lon ) )
+ sin ( radians( @lat ) )
* sin( radians( X(point) ) )
)
) AS distance
FROM @table
HAVING distance < @rad
If you want to do it in miles, replace the constant 6373 with 3959
如果要以英里为单位，请将常量 6373 替换为 3959
For those wanting to reduce the query syntax, here's a common implementation of a user defined MySQL function for implementing a distance function based on the Haversine formulae.
对于那些想要减少查询语法的人，这里有一个用户定义的 MySQL 函数的常见实现，用于实现基于 Haversine 公式的距离函数。
CREATE FUNCTION HAVERSINE ( coord1 POINT, coord2 POINT )
RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE dist DOUBLE;
SET rlat1 = radians( X( coord1 ) );
SET rlat2 = radians( X( coord2 ) );
SET rlon1 = radians( Y( coord1 ) );
SET rlon2 = radians( Y( coord2 ) );
SET dist = ACOS( COS( rlat1 ) * COS( rlon1 ) * COS( rlat2 ) * COS( rlon2 ) + COS( rlat1 ) * SIN( rlon1 ) * COS( rlat2 ) * SIN( rlon2 ) + SIN( rlat1 ) * SIN( rlat2 ) ) * 6372.8;
RETURN dist;
END
回答by TMS
Buffers won't help you much in MySQL < 5.6, since buffer is a polygon, and polygon operations in MySQL < 5.6 are implemented as "Minimal Bounding Rectangles" (MBR), which are pretty useless.
缓冲区在 MySQL < 5.6 中对您没有多大帮助，因为缓冲区是一个多边形，而 MySQL < 5.6 中的多边形操作被实现为“最小边界矩形”（MBR），这是非常无用的。
Since MySQL 5.6, the full non-MBR st_*operations were implemented. But the best solution for you, in case of circle, is to use undocumentedfunction st_distance:
从 MySQL 5.6 开始，实现了完整的非 MBRst_*操作。但是，在圈子的情况下，对您来说最好的解决方案是使用未记录的函数st_distance：
select *
from waypoints
where st_distance(point(@center_lon, @center_lat), coordinates) <= radius;
It was hard to find, since it's undocumented :-) But it's mentioned on this blog, whose author also filled the mentioned bugreport. There are caveats though (citing the blog):
很难找到，因为它没有记录:-) 但是在这个博客上提到了它，它的作者也填写了提到的错误报告。不过有一些警告（引用博客）：
The bad news is:
1) All functions still only use the planar system coordinates. Different SRIDs are not supported.
2) Spatial indexes (RTREE) are only supported for MyISAM tables. One can use the functions for InnoDB tables, but it will not use spatial keys.
坏消息是：
1) 所有函数仍然只使用平面系统坐标。不支持不同的 SRID。
2) 空间索引 (RTREE) 仅支持 MyISAM 表。可以使用 InnoDB 表的函数，但不会使用空间键。
Point 1) means that the unit of distance will be the same as the unit of coordinates (degrees in case of WGS84). If you need distance in meters, you have to use projected coordination system (e.g. UTM or similar) that has units corresponding to meters.
点 1) 表示距离单位将与坐标单位相同（WGS84 中的度数）。如果您需要以米为单位的距离，则必须使用具有与米对应的单位的投影协调系统（例如 UTM 或类似系统）。
So, in case you don't want to go with these caveats, or in case of MySQL < 5.6, you will have to write your own custom distance function.
因此，如果您不想遵循这些警告，或者在 MySQL < 5.6 的情况下，您将不得不编写自己的自定义距离函数。
回答by dexxtr
Hope my version helps
希望我的版本有帮助
SELECT
*
FROM
`locator`
WHERE
SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`
details here http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql
详情请见http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql
回答by Michal - wereda-net
from: https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql
来自：https: //gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql
SELECT
id, (
6371 * acos (
cos ( radians(78.3232) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(65.3234) )
+ sin ( radians(78.3232) )
* sin( radians( lat ) )
)
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
(remember to replace all constants, this example is for kilometers)
（记得替换所有常量，这个例子是公里）
回答by newway
You can use:
您可以使用：
SELECT name, lat, lng
FROM vw_mytable
WHERE ST_Contains(ST_Buffer(
ST_GeomFromText('POINT(12.3456 34.5678)'), (0.00001*1000)) , mypoint) = 1
The expression: 0.00001*1000 inside statment above give to you a circle with 1000 Meters of diameter, its being applied on a view here, name column is just a label to point , mypoint is the name of my point column, lat was calculated inside view with ST_X(mytable.mypoint) and lng with ST_Y(mytable.mypoint) and they simply show me te literal values of lat and lng. It will give to you all coordinates that belongs to circle.
上面的表达式：0.00001*1000里面的语句给你一个直径为1000米的圆，它被应用在这里的一个视图上，name列只是一个指向的标签，mypoint是我的点列的名称，lat是在里面计算的使用 ST_X(mytable.mypoint) 和 lng 使用 ST_Y(mytable.mypoint) 查看，它们只是向我显示 lat 和 lng 的文字值。它将为您提供属于圆的所有坐标。
回答by AlterX
for the sake of completeness, as of MySQL 5.7.6. you can use the ST_Distance_Spherefunction which achieves the same result:
为了完整起见，从 MySQL 5.7.6 开始。您可以使用达到相同结果的ST_Distance_Sphere函数：
SET @pt1 = ST_GeomFromText('POINT(12.3456 34.5678)');
SELECT * from
(SELECT * ,(ST_Distance_Sphere(@pt1, location, 6373)) AS distance FROM mydb.Event ORDER BY distance) x WHERE x.distance <= 30;
In this case, we provide the approximate radius of the Earth in kilometers (6373) and a point (@pt1). This code will calculate the distance (in kilometers) between that point (long 12.3456, lat 34.5678) and all the points contained in the database where the distance is 30km or less.
在这种情况下，我们提供以公里为单位的地球近似半径 (6373) 和一个点 (@pt1)。此代码将计算该点（长 12.3456，纬度 34.5678）与数据库中包含的距离为 30 公里或更短的所有点之间的距离（以公里为单位）。
