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

问题描述

我们有一个 restaurant 表，其中包含每一行的经纬度数据.

我们需要编写一个查询来执行搜索以查找提供的半径内的所有餐馆，例如1 英里、5 英里等.

为此，我们有以下查询:

***参数***经度:-74.008680纬度:40.711676半径:1英里***询问***选择 *从餐厅在哪里 (POW( ( 69.1 * ( 经度 - -74.008680 ) * cos( 40.711676/57.3 ) ) , 2 ) + POW( ( 69.1 * ( 纬度 - 40.711676 ) ) , 2 )) <( 1 *1 );

该表有大约 23k 行.结果集的大小有时很奇怪，例如对于 5.4 英里的搜索，它返回 880 行，对于 5.5 英里，它返回 21k 行.

此表包含纽约市的餐厅数据 - 因此实际分布与结果集不同.

问题:这个查询有什么问题吗?

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

解决方案

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

我个人会计算一个正方形的 TopLeft 和 BottomRight 坐标(只需要使用毕达哥拉斯进行粗略计算)，其边等于您正在寻找的范围，然后在该纬度/经度正方形内的较小记录子集.

带有关于 Lat & 的索引长在数据库中的查询

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

应该很高效

(请注意，我不了解 MySQL，只了解 MS SQL)

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?

解决方案

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)

这篇关于基于经纬度进行半径搜索的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！