MySQL 基于纬度经度进行半径搜索的SQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1727137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query for Performing Radius Search based on Latitude Longitude
提问by Puneet
We have a restauranttable that has lat-long data for each row.
我们有一个restaurant表，每行都有经纬度数据。
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.
我们需要编写一个查询来执行搜索以查找提供的半径内的所有餐馆，例如 1 英里、5 英里等。
We have the following queryfor this purpose:
query为此，我们有以下内容：
***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.
该表有大约 23k 行。结果集的大小有时很奇怪，例如对于 5.4 英里的搜索，它返回 880 行，而对于 5.5 英里，它返回 21k 行。
This table contains restaurant data for nyc - so the real distribution is not as per the result set.
此表包含 nyc 的餐厅数据 - 因此实际分布与结果集不同。
Question: IS THERE ANYTHING WRONG With this query?
问题：这个查询有什么问题吗？
DB: MySQL, Longitude: DECIMAL(10,6), Latitude: DECIMAL(10,6)
DB：MySQL，经度：DECIMAL(10,6)，纬度：DECIMAL(10,6)
回答by Kristen
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.
在我看来，WHERE 子句会因为涉及数学而变慢，并且在 WHERE 子句中使用函数会阻止数据库使用索引来加速查询 - 因此，实际上，您将检查数据库，并在每次进行查询时对每一行执行大圆数学运算。
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.
就我个人而言，我会计算一个正方形的 TopLeft 和 BottomRight 坐标（只需要使用毕达哥拉斯粗略计算），边等于您要查找的范围，然后对较小的子集执行更复杂的 WHERE 子句测试该纬度/经度方格内的记录。
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)
（请注意，我对 MySQL 一无所知，只了解 MS SQL）
回答by Quassnoi
You may want to create a SPATIALindex on your table to make the searches faster.
您可能希望SPATIAL在表上创建索引以加快搜索速度。
To do this, add a POINTcolumn to your table:
为此，请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
You should store coordsas UTMcoordinates within a single zone.
您应该存储coords为UTM单个区域内的坐标。
回答by Dinci Garrone
If your data is in SQL server database, you can use this:
如果您的数据在 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
回答by cdonner
Use a function, e.g. the one I posted here.
使用一个函数，例如我在这里发布的那个。
Then, query your restaurants, e.g. to get everything within a 5-mile radius
然后，查询您的餐厅，例如获取 5 英里半径内的所有内容
select * from restaurants
where dbo.udf_Haversine(latitude, longitude, @lat, @long) < 5
This performs fine with ZIP code data.
这对于邮政编码数据表现良好。
