使用MySQL Spatial字段在Geofence(圆)中查找记录 [英] Find records within Geofence (circle) using MySQL Spatial fields

问题描述

我当前正在使用以下格式的MySQL POINT字段类型存储位置的latitude和longitude值:

I am currently storing the latitude and longitude values for a location using the MySQL POINT field type in the format:

POINT(51.507351 -0.127758)

我以前从未使用过这种字段类型，因此对查询以及如何实际，有效地使用存储的数据没有任何经验.

I have never before used this kind of field type, and therefore do not have any experience with the queries, and how to actually, efficiently, use the data stored.

我找到了许多链接，这些链接演示了在指定半径内搜索物品的各种方法.但是，其中大多数都使用独立的latitude和longitude字段，而不是使用MySQL空间字段.

I have found many links that demonstrate various methods to search for items within a specified radius. However, most of these are using independent latitude and longitude fields instead of using the MySQL spatial fields.

请参阅以下内容:

• 找到两个纬度之间最快距离的方法/长点
• http://www.plumislandmedia.net/mysql/haversine- mysql-nearest-loc/
• 使用MySQL空间扩展来选择圆内的点

• Fastest Way to Find Distance Between Two Lat/Long Points
• http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
• Use MySQL spatial extensions to select points inside circle

我正在尝试搜索给定半径(以米为单位)内的所有记录.根据我的表的结构，什么是最好的和最有效的搜索记录并返回指定半径(圆形而不是矩形)内的项目的方法?

I am trying to search for any records within a given radius (in metres). Based on the structure of my table, what is the best, and most efficient, method of searching my records and returning any items within the specified radius (circle not rectangle)?

这是我到目前为止所拥有的:

This is what I have so far:

SELECT
    *,
    (
        6373 * acos (
            cos ( radians( PASSED_IN_LATITUDE ) )
            * cos( radians( X(location) ) )
            * cos( radians( Y(location) ) - radians( PASSED_IN_LONGITUDE ) )
            + sin ( radians( PASSED_IN_LATITUDE ) )
            * sin( radians( X(location) ) 
        )
    ) AS distance
FROM locations
HAVING distance < PASSED_IN_RADIUS

我从另一个答案中获取了上面的代码，但是鉴于此答案是2年前发布的，我认为它已经过时了，因此这可能不再是最有效的方法...

I took the above code from another answer but given this answer was posted 2 years ago, I have assumed it it out of date and therefore this may not be the most efficient method anymore...

推荐答案

假设您在位置上有一个空间键，则可以执行以下操作:

Assuming you have a spatial key on location, you can do something like this:

select * from locations where 
contains(geomfromtext('polygon($bounding_rect_coords)'),location) 
and earth_distance(location,point($lat,$lon)) < $radius

边界矩形坐标应使用以下公式计算:

The bounding rectangle coordinates should be computed using the following formulas:

$deg_to_rad = $PI/180.0
$rad_to_deg = 1.0/$deg_to_rad
$delta_y = $rad_to_deg *($radius / ($earth_radius * cos($lat*$deg_to_rad))) // the length of the parallel = EARTH_R * cos(lat)
$delta_x = $rad_to_deg * ($radius/$earth_radius)
$x1 = $lat - $delta_x
$x2 = $lat + $delta_x
$y1 = $lon - $delta_y
$y2 = $lon + $delta_y

然后使用

geomfromtext('polygon(($x1 $y1,$x2 $y1,$x2 $y2, $x1 $y2, $x1 $y1))')

这最好在应用程序中完成，以减轻数据库服务器的负担.

This is best done in the application to offload the database server.

此矩形实际上是一个球形矩形，因此在其计算中使用了PI常数.这个想法很简单.对于给定的平行度，将搜索半径转换为经度.那就是我们需要从目标向东和向西移动多少度才能覆盖到我们的候选点.然后针对纬度计算相同的值-与经度不同，因为所有子午线的长度都相同，所以这与坐标无关.那就是我们需要向北和向南走几度.

This rectangle is actually a spherical rectangle, thus the use of the PI constant in its computation. The idea is simple. For the given parallel, convert the search radius into degrees of longitude. That is how many degrees east and west we need to go from the target to cover our candidate points. Then compute the same for degrees of latitude - unlike longitude, this will not be coordinate-dependent as all meridians have the same length. That is how many degrees we need to go north and south.

以上计算假设搜索半径小于平行线的长度，在美国大部分地区，合理的搜索半径就是这种情况，但例如在阿拉斯加的某些地区可能不适用.因此，最好检查一下(如果delta_y> 90)并相应地进行裁剪.您还应该检查一下自己是在北极还是南极，事情完全在那里中断了.但是希望您的数据没有太多的极地记录.

The above computation assumes that the search radius is smaller than the length of the parallel, which will be the case in most of the United States for a reasonable search radius, but might not hold in some parts of Alaska, for example. So it would be a good idea to check for that (if delta_y > 90) and clip it accordingly. You should also check to see if you are right on the North or South pole, things break there completely. But hopefully your data does not have too many polar records.

对于earth_distance()，您有几种选择:

• 使用我的UDF( http://github.com/spachev/mysql_udf_bundle )(最快，但是您需要能够在服务器上安装UDF)
• 编写一个MySQL存储功能.您可以从 http://gist.github.com/aramonc/6259563 开始，并根据需要进行调整(需要具有创建功能的能力).
• 只需将上面的距离计算直接粘贴到查询中即可(丑陋，但不需要特殊的设置或特权)

• Use my UDF (http://github.com/spachev/mysql_udf_bundle) (fastest, but you need to have the ability to install UDFs on your server)
• Write a MySQL store function. You can start with http://gist.github.com/aramonc/6259563 and adjust if needed (need ability to create functions).
• Just paste your distance computation above directly into the query (ugly, but requires no special setup or privileges)

即使已经使用了两年，您的计算本身还是可以的-就我所知，在过去两年中，就测量地球上两点之间的距离而言，没有发现任何革命性的东西.

Your computation itself is OK even though it is two years old - nothing revolutionary has been discovered in the last two years as far as measuring the distance between two points on the earth is concerned as far as I know.

您的原始方法同样有效，除了效率低下. contains子句的添加使我们可以将搜索减少到(希望)相对较小的集合，该集合可以保证很快地位于搜索半径之内.然后，我们选择每个候选者并筛选出未在earth_distance()上晋级的候选者.

Your original method would work as well except it will be inefficient. The addition of contains clause allows us to reduce our search to a (hopefully) relatively small set that is guaranteed to be within the search radius very quickly. Then we take each candidate and filter out the ones that did not make the cut on earth_distance().

我必须添加一个标准的免责声明，即我将可能未经过清理的变量插入到SQL中.在编写实际的生产代码时，请确保验证产生的SQL查询是否存在SQL注入攻击.

I must add a standard disclaimer that I inserted variables into SQL that potentially might not have been sanitized. Make sure to validate the resulting SQL queries for SQL injection attacks when writing the actual production code.

这篇关于使用MySQL Spatial字段在Geofence(圆)中查找记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！