使用 MySQL 空间扩展选择圆内的点

本文介绍了使用 MySQL 空间扩展选择圆内的点的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个名为 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 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 空间扩展选择圆内的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！