使用 MySQL 空间扩展选择圆内的点 [英] Use MySQL spatial extensions to select points inside circle

问题描述

我有一个名为 flags 的表，其中包含一个名为 coordinates 的列，该列充满了 MySQL 的点".我需要执行一个查询，根据半径为 100m 的纬度和经度位置获取圆圈内的所有标志.

I have a table called flags that contains a column called coordinates that 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.

从使用的角度来看，这是基于用户的位置.比如手机会给出用户的经纬度位置，然后传递给这部分API.然后由 API 来围绕用户创建一个半径为 100 米的不可见圆圈，然后返回该圆圈中的标志.

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 的这一部分，我不确定如何创建，因为我不确定如何使用 SQL 创建这个不可见的圆并仅选择此半径内的点.

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.

这可能吗?是否有 MySQL 空间函数可以帮助我做到这一点?

Is this possible? Is there a MySQL spatial function that will help me do this?

我相信 Buffer() 函数可以做到这一点，但我找不到任何关于如何使用它的文档(例如 SQL 示例).理想情况下，我需要一个答案，向我展示如何使用此功能或最接近它的功能.在我将这些坐标存储为地理空间点的地方，我应该使用地理空间函数来执行我要求的最大化效率的操作.

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.

标志表:

• id
• 坐标
• 姓名

示例行:

1 |[几何 - 25B] |坚韧AB

1 | [GEOMETRY - 25B] | Tenacy AB

对于标志表，我有纬度、经度位置和东距和北距 (UTM)

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

推荐答案

MySQL 中没有支持纬度/经度距离计算的地理空间扩展函数. 从 MySQL 5.7 开始.

您要求的是地球表面的邻近圆.您在问题中提到 flags 表中的每一行都有经纬度值，还有 通用横向墨卡托 (UTM) 投影值在几个不同的UTM 区域.如果我记得我的英国军械测量局地图正确的话，UTM 可用于在这些地图上定位项目.

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 flags table, 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.

计算 UTM 中同一区域中两点之间的距离是一件简单的事情:笛卡尔距离可以解决问题.但是，当点位于不同区域时，该计算不起作用.

It's a simple matter to compute the distance between two points in the same zone in UTM: the Cartesian distance does the trick. But, when points are in different zones, that computation doesn't work.

因此，对于您问题中描述的应用程序，有必要使用Great Circle Distance，使用半正弦或其他合适的公式计算.

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.

MySQL 增加了地理空间扩展，支持将各种平面形状(点、折线、多边形等)表示为几何图元的方法.MySQL 5.6 实现了一个未公开的距离函数 st_distance(p1, p2).但是，此函数返回笛卡尔距离.所以它完全不适合用于基于纬度和经度的计算.在温带纬度地区，纬度对着表面的距离(南北)几乎是经度(东西)的两倍，因为纬度线越靠近两极越靠近.

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 unsuitable for 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.

因此，圆形邻近公式需要使用真正的纬度和经度.

So, a circular proximity formula needs to use genuine latitude and longitude.

在您的应用程序中，您可以使用如下查询找到给定 latpoint,longpoint 十法定英里内的所有 flags 点:

In your application, you can find all the flags points within ten statute miles of a given latpoint,longpoint with a query like this:

 SELECT id, coordinates, name, r,
        units * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(latpoint))
                  * COS(RADIANS(latitude))
                  * COS(RADIANS(longpoint) - RADIANS(longitude))
                  + SIN(RADIANS(latpoint))
                  * SIN(RADIANS(latitude))))) 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)

如果要搜索20公里以内的点，请更改查询的这一行

If you want to search for points within 20 km, change this line of the query

               20.0 AS r, 69.0 AS units

以这个为例

               20.0 AS r, 111.045 AS units

r 是您要搜索的半径.units 是地球表面每纬度纬度的距离单位(英里、公里、弗隆，无论你想要什么).

r is the radius in which you want to search. units are the distance units (miles, km, furlongs, whatever you want) per degree of latitude on the surface of the earth.

此查询使用边界纬度/经度和 MbrContains 来排除离起点肯定太远的点，然后使用大圆距离公式生成剩余点的距离.可以在此处找到所有这些的解释.如果您的表使用 MyISAM 访问方法并具有空间索引，MbrContains 将利用该索引让您快速搜索.

This query uses a bounding lat/long along with MbrContains to 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, MbrContains will exploit that index to get you fast searching.

最后，上面的查询选择矩形内的所有点.要将其缩小到仅圈中的点，并按接近度对它们进行排序，请按如下方式包装查询:

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 

这篇关于使用 MySQL 空间扩展选择圆内的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！