基于经纬度进行半径搜索的SQL查询

Question

我们有一个包含每一行经纬度信息的餐厅数据表。

我们需要编写一个查询，以在给定半径（例如1英里、5英里等）内查找所有餐厅。

我们有以下查询用于此目的:

***Parameters***

Longitude: -74.008680
Latitude: 40.711676
Radius: 1 mile

***Query***

SELECT *
FROM restaurant
WHERE (
POW( ( 69.1 * ( Longitude - -74.008680 ) * cos( 40.711676 / 57.3 ) ) , 2 ) + POW( ( 69.1 * ( Latitude - 40.711676 ) ) , 2 )
) < ( 1 *1 );

这个表格有大约23k行。结果集的大小有时候会很奇怪，例如对于5.4英里的搜索，它返回880行，而对于5.5英里的搜索，它返回21k行。

这个表格包含了纽约市的餐厅数据 - 所以实际分布不符合结果集。

问题：这个查询有什么问题吗？

数据库：MySQL，经度：DECIMAL(10,6)，纬度：DECIMAL(10,6)

- Puneet

---

有什么问题吗？首先，您应该使用UTM而不是Lat/Long坐标，因为它们在平面化方面具有更小的误差范围... - OMG Ponies

请查看此问题的答案（https://dev59.com/hXRB5IYBdhLWcg3wl4Sc）。相似的问题。 - Ramin

Answer 1

这个查询是否有问题？

在我看来，这个WHERE子句可能会很慢，因为它涉及到数学计算，而且在WHERE子句中使用函数会防止数据库使用索引加速查询。因此，实际上，每次查询都需要检查数据库中的每个餐厅，并对每一行执行大圆数学运算。

个人建议是计算一个正方形的左上角和右下角坐标（只需使用勾股定理粗略计算），其边长等于您要查找的范围，然后对该Lat/Long正方形内的较小记录集执行更复杂的WHERE子句测试。

如果数据库中有Lat和Long的索引，那么查询

WHERE     MyLat >= @MinLat AND MyLat <= @MaxLat
      AND MyLong >= @MinLong AND MyLong <= @MaxLong

应该非常高效。

（请注意，我没有关于MySQL的知识，只了解MS SQL）

- Kristen

Answer 2

为了使搜索更快，您可能希望在表上创建一个SPATIAL索引。

要做到这一点，需要在表中添加一个POINT列：

ALTER TABLE restaurant ADD coords POINT NOT NULL;

CREATE SPATIAL INDEX sx_restaurant_coords ON restaurant (coords);

SELECT  *
FROM    restaurant
WHERE   MBRContains(coords, LineString(Point(583734 - 1609, 4507223 - 1609), Point(583734 + 1609, 4507223 + 1609))
        AND GLength(LineString(Point(583734, 4507223), coords)) <= 1609

您应该将coords作为单个区域内的UTM坐标进行存储。

- Quassnoi

Answer 3

使用一个函数，比如我在这里发布的函数。

然后，查询餐厅信息，比如获取半径为5英里范围内的所有内容。

select * from restaurants 
  where dbo.udf_Haversine(latitude, longitude, @lat, @long) < 5

这个在处理邮政编码数据方面表现良好。

- cdonner

Answer 4

如果你的数据在 SQL Server 数据库中，你可以使用这个：

CREATE PROC up_FindZipCodesWithinRadius

    @ZipCode char(5) ,
    @GivenMileRadius int
AS
SET NOCOUNT ON

DECLARE @lat1 float, 
    @long1 float

SELECT  @lat1= latitude,
    @long1 = longitude 
FROM ZipSource
WHERE zipcode = @ZipCode

SELECT ZipCode ,DistanceInMiles
FROM
(
    SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + 
            (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) / 
            ((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * 
            Cos((longitude/57.2958) - (@Long1/57.2958)))))) as DistanceInMiles
FROM ZipSource
) a
WHERE a.DistanceInMiles <= @GivenMileRadius
--AND ZipCode <> @ZipCode
ORDER BY DistanceInMiles

GO

EXEC up_FindZipCodesWithinRadius '35085',20
GO

DROP PROC up_FindZipCodesWithinRadius

- Dinci Garrone