基于纬度经度的用于执行半径搜索的SQL查询 [英] SQL Query for Performing Radius Search based on Latitude Longitude

问题描述

我们有一个餐厅表，每行都有经济数据。

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

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

  ***参数*** 
 
经度：-74.008680 
纬度：40.711676 
半径：1英里
 
 *** Query *** 
 
 SELECT * 
 FROM restaurant 
 WHERE（
 POW（（69.1 *（经度-74.008680）* cos（40.711676 / 57.3））， 2）+ POW（（69.1 *（Latitude-40.711676）），2）
）< （1 * 1）; 
  

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

此表包含nyc的餐厅数据 - 所以真实分布是而不是根据结果集。

问题：是否有任何错误使用此查询？

DB：MySQL经度：DECIMAL（10,6），Latitude：DECIMAL（10,6）

解决方案

是否有任何错误这个
查询？

在我看来，WHERE子句会因为数学而变得缓慢在WHERE子句中使用函数将会阻止数据库使用索引加速查询 - 所以，实际上，您将检查数据库中的每个餐厅，并且每次都在每一行执行大圆圈数学运算你做一个查询。

我个人计算一个正方形的TopLeft和BottomRight坐标（只需要用pythag oras），其边数等于您正在查找的范围，然后对该Lat / Long平方中较小的记录子集执行更复杂的WHERE子句测试。

<在Lat&的索引上，在数据库中查询的长度

 
 WHERE MyLat> = @MinLat AND MyLat< = @MaxLat 
 AND 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?

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

解决方案

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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！