使用MySQL空间扩展来选择圈内的点。

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.

我有一个名为flags的表，其中包含一个名为“坐标”的列，它包含了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 function that 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.

我相信缓冲区()函数可以这样做，但是我找不到任何关于如何使用它的文档(例如SQL)。理想情况下，我需要一个答案来告诉我如何使用这个函数或者最接近它。我把这些坐标存储为地理空间点我应该用地理空间函数来做我要求的最大化效率。

Flags table:

旗帜表:

• id
• id
• coordinates
• 坐标
• name
• 的名字

Example row:

示例行:

1 | [GEOMETRY - 25B] | Tenacy AB

|[几何- 25B] | Tenacy 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的库。

8 个解决方案

#1

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

你要求的是地球表面的接近圆。您在您的问题中提到，您的标记表中的每一行都有lat/long值，并且在几个不同的UTM区域中，也有通用的横向Mercator (UTM)投影值。如果我正确地记得我的英国地形测量地图，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.

在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.

因此，对于您的问题中所描述的应用程序，有必要使用大圆距离，这是用haversine或另一个合适的公式来计算的。

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.

用地理空间扩展增强的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 flags points within ten statute miles of a given latpoint,longpoint with a query like this:

在您的应用程序中，您可以在给定的latpoint的10个法定范围内找到所有的标记点，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

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.

r是你想要搜索的半径。单位是距离单位(英里，km, furlongs，无论你想要多少)在地球表面的纬度。

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.

这个查询使用一个边界lat/long和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 

#2

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.

这假定表中的坐标作为点()datatype存储在标记为“POINT”的列中。函数X(点)和Y(点)分别从点值提取纬度和经度值。

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

如果你想用英里来做，用3959代替常数6373。

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

#3

UPDATE

更新

Use ST_Distance_Sphere() to calculate distances using a lat/long

使用ST_Distance_Sphere()使用lat/long计算距离。

http://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere

http://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html function_st-distance-sphere

#4

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 undocumented function st_distance:

自MySQL 5.6以来，实现了完全的非mbr st_*操作。但是对于您来说，最好的解决方案是使用无文档的函数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):

这是很难找到的，因为它是无证的:-)但是在这个博客上，它的作者也填写了提到的bugreport。不过也有一些警告(引用博客):

The bad news is:

坏消息是:

1) All functions still only use the planar system coordinates. Different SRIDs are not supported.

1)所有功能仍然只使用平面系统坐标。不支持不同的SRIDs。

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.

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的情况下，您将不得不编写自己的自定义距离函数。

#5

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

#6

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)

(记住要替换所有的常数，这个例子是千米)

#7

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米,它被应用在一个视图,只是一个标签名称列点,mypoint是我的观点的名字列,lat计算内部视图与ST_X(mytable.mypoint)和液化天然气ST_Y(mytable.mypoint),他们只是告诉我te lat和液化天然气的文字值。它会给你所有属于圆的坐标。

#8

for the sake of completeness, as of MySQL 5.7.6. you can use the ST_Distance_Sphere function 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,lat 34.5678)之间的距离，以及数据库中所包含的距离为30公里或以下的所有点。