数据库中的坐标比较 [英] Coordinates comparison in database

问题描述

我正在开发一个Spring应用程序，该应用程序必须使用GPS坐标来搜索人员. 对于数据库管理员中的每个人，我都有他的纬度和经度.客户端将一个点和最大距离传递给服务器，该服务器必须以该距离返回该点附近的所有客户端.

I am developing a Spring app that has to search for persons using GPS coordinates. For each in the DB person I have his latitude and his longitude. The client pass a point and a max distance to the server which has to return all the clients around this point in this distance.

问题在于数据库包含大约30万人，因此，如果所有人员都在关键点附近，那么让所有人员进行遍历并循环访问chck确实很慢.

The problem is that the database contains about 300k persons, so getting all the persons and iterating through this list to chck if they are near the point is really slow.

有什么想法可以加快搜索速度吗?

Any idea to speed up this search ?

推荐答案

处理邻近搜索的最佳方法是从某种边界矩形近似开始，然后从那里到人与人之间的实际大圆距离

The best way to handle proximity searches is to start with some kind of bounding-rectangle approximation, and then go from there to an actual great-circle distance between people.

只要您的纬度不太靠近极点，就可以得出两点之间距离的草率但可行的近似值(在SQLish中):

As long as your your latitudes aren't too near the poles, a sloppy but workable approximation for the distance between two points is this (in SQLish):

GREATEST(ABS(lat1-lat2),ABS(long1-long2))

如果您想更加精确，并且只知道自己在乎其中的人，可以说，彼此相距10公里，您可以使用像这样的边界矩形搜索.

If you want to be more precise and you know you only care about people who are within, let us say, 10 km each other you can use a bounding rectangle search like this.

WHERE latitude_from_table
    BETWEEN latpoint  - (10.0 / 111.045)
        AND latpoint  + (10.0 / 111.045)
  AND longitude_from_table
    BETWEEN longpoint - (10.0 / (111.045 * COS(RADIANS(latpoint))))
        AND longpoint + (10.0 / (111.045 * COS(RADIANS(latpoint))))

之所以可行，是因为一个纬度上有111.045公里.经度边界中的余弦项说明了这样一个事实，即当您靠近极点时，纬度线会更靠近在一起.这样，您就可以利用latitude_from_table和longitude_from_table列上的MySQL索引.

This works because there are 111.045 km in one degree of latitude. The cosine terms in the longitude bounds account for the fact that lines of latitude are closer together as you come near to the poles. This lets you exploit MySQL indexes on your latitude_from_table and longitude_from_table columns.

一旦有了边界框的接近度，就可以应用一个很大的圆距离公式.这是背景. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest- loc/

Once you have bounding-box proximity, you can apply a great circle distance formula. Here's background on that. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

对于您正在考虑的那种应用程序，32位IEEE-488浮点数对于您的坐标来说具有很高的精度.如果您要查看的点真的很靠近(不到一公里左右)，则要使用Vincenty公式( http://www.plumislandmedia.net/mysql/stored-function-haversine -distance-computation/).

For the kind of application you are considering, 32-bit IEEE-488 floating point is plenty of precision for your coordinates. If the points you are looking at are really close together (less than a km or so) you want to use the Vincenty formula (http://www.plumislandmedia.net/mysql/vicenty-great-circle-distance-formula/) rather than the more common so-called haversine formula (http://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/).

如果您的人数超过300K，则可能要考虑使用MySQL地理空间索引方案.它仅适用于MyISAM表，但是非常执行边界矩形搜索的速度非常快.看这里. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest- loc/

If your number of people goes much above 300K, you may want to consider using the MySQL geospatial indexing scheme. It only works with MyISAM tables, but it is very fast at doing bounding-rectangle searches. See here. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

这篇关于数据库中的坐标比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！