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

发布于 2024-08-11 13:42:51 字数 650 浏览 5 评论 0原文

我们有一个restaurant 表，其中每行都有经纬度数据。

我们需要编写一个查询来执行搜索，以查找所提供半径（例如 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)

原文

We have a restaurant table that has lat-long data for each row.

We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc.

We have the following query for this purpose:

***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 );

The table has about 23k rows. The size of the result set is weird at times e.g. for a 5.4 mile search, it gives back 880 rows and for 5.5 miles, it gives back 21k rows.

This table contains restaurant data for nyc - so the real distribution is not as per the result set.

Question: IS THERE ANYTHING WRONG With this query?

DB: MySQL, Longitude: DECIMAL(10,6), Latitude: DECIMAL(10,6)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟花易冷人易散 2024-08-18 13:42:51

这有什么问题吗
查询？

在我看来，由于涉及数学，WHERE 子句会很慢，并且在 WHERE 子句中使用函数将阻止数据库使用索引来加速查询 - 因此，实际上，您将检查数据库，并在每次进行查询时对每一行执行大圆数学运算。

就我个人而言，我会计算边长等于您要查找的范围的正方形的 TopLeft 和 BottomRight 坐标（只需使用毕达哥拉斯粗略地计算），然后对较小的子集执行更复杂的 WHERE 子句测试该纬度/经度方块内的记录。

带有 Lat & 索引长时间在数据库中查询

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

应该非常高效

（请注意，我对 MySQL 没有具体的了解，只对 MS SQL 有了解）

IS THERE ANYTHING WRONG With this
query?

In my opinion the WHERE clause is going to be slow because of the maths involved, and the use of functions in the WHERE clause will prevent the database using an index to speed the query - so, in effect, you will examine every restaurant in the database, and perform the great-circle maths on every row, every time you make a query.

Personally I would calculate the TopLeft and BottomRight co-ordinates of a square (which only needs to be crudly calculated using pythagoras) with sides equal to the range you are looking for, and then perform the more complicated WHERE clause test on the smaller subset of records that are within that Lat/Long square.

With an Index on Lat & Long in the database the query

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

should be very efficient

(Please note that I have no knowledge of MySQL specifically, only of MS SQL)

回复收藏 0 原文

千仐 2024-08-18 13:42:51

您可能需要在表上创建一个 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 坐标。

You may want to create a SPATIAL index on your table to make the searches faster.

To do this, add a POINT column to your table:

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

You should store coords as UTM coordinates within a single zone.

回复收藏 0 原文

没有心的人 2024-08-18 13:42:51

使用一个函数，例如我在此处发布的函数。

然后，查询您的餐馆，例如获取 5 英里半径内的所有内容。

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

这对于邮政编码数据来说效果很好。

Use a function, e.g. the one I posted here.

Then, query your restaurants, e.g. to get everything within a 5-mile radius

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

This performs fine with ZIP code data.

回复收藏 0 原文

芸娘子的小脾气 2024-08-18 13:42:51

如果你的数据在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

If your data is in SQL server database, you can use this:

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

回复收藏 0 原文

~没有更多了~