使用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 | [GEOMETRY-25B] | Tenacy 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.

因此，对于您的问题中所述的应用程序，必须使用大圆距离，它是使用Haversine或其他合适的公式计算得出的.

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(
                   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)

如果要搜索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屋！