問題描述
我們有一個 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?
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查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！