地理位置距离城市表的SQL [英] Geolocation distance SQL from a cities table
问题描述
所以我有这个功能根据纬度，经度和半径参数计算最近的城市。
So I have this function to calculate nearest cities based on latitude, longitude and radius parameters.
DELIMITER $$
DROP PROCEDURE IF EXISTS `world_db`.`geolocate_close_cities`$$
CREATE PROCEDURE `geolocate_close_cities`(IN p_latitude DECIMAL(8,2), p_longitude DECIMAL(8,2), IN p_radius INTEGER(5))
BEGIN
SELECT id, country_id, longitude, latitude, city,
truncate((degrees(acos( sin(radians(latitude))
* sin(radians(p_latitude))
+ cos(radians(latitude))
* cos(radians(p_latitude))
* cos(radians(p_longitude - longitude) ) ) )
* 69.09*1.6),1) as distance
FROM cities
HAVING distance < p_radius
ORDER BY distance desc;
END$$
DELIMITER ;
以下是我的城市表格结构：
Here's the structure of my cities table:
> +------------+-------------+------+-----+---------+----------------+ |
> Field | Type | Null | Key | Default | Extra |
> +------------+-------------+------+-----+---------+----------------+ |
> id | int(11) | NO | PRI | NULL | auto_increment | |
> country_id | smallint(6) | NO | | NULL | | |
> region_id | smallint(6) | NO | | NULL | | |
> city | varchar(45) | NO | | NULL | | |
> latitude | float | NO | | NULL | | |
> longitude | float | NO | | NULL | | |
> timezone | varchar(10) | NO | | NULL | | |
> dma_id | smallint(6) | YES | | NULL | | |
> code | varchar(4) | YES | | NULL | |
> +------------+-------------+------+-----+---------+----------------+
我想做什么（伪码）是这样的：
What i'd lke to do (pseudcode) is something like:
SELECT * FROM cities WHERE DISTANCE(SELECT id FROM cities WHERE id={cityId}, {km))
b $ b
它会返回我最近的城市。
and it'll return me the closest cities.
有什么想法我能做到吗？
Any ideas of how I can do this?
现在，我只是调用函数，然后迭代通过ids到一个数组，然后在城市表中执行一个WHEREIN，显然是不是很有效率。
At the moment, I just call the function, and then iterate through the ids into an array and then perform a WHEREIN in the city table which obviously isn't very efficient.
任何帮助是非常赞赏。非常感谢。
Any help is MUCH appreciated. Thanks.
推荐答案
如果您可以限制城市与本地位置之间的最大距离，请利用一分钟的纬度（北 - 南）是一海里。
If you can limit the maximum distance between your cities and your local position, take advantage of the fact that one minute of latitude (north - south) is one nautical mile.
在您的纬度表上放置索引。
Put an index on your latitude table.
使自己成为一个哈佛像（lat1，lat2，long1，long2 ，单位）从你的问题中显示的半正矢公式存储函数。请参阅下面
Make yourself a haversine(lat1, lat2, long1, long2, unit) stored function from the haversine formula shown in your question. See below
然后按照mylatitude，mylongitude和mykm这样做。
Then do this, given mylatitude, mylongitude, and mykm.
SELECT *
from cities a
where :mylatitude >= a.latitude - :mykm/111.12
and :mylatitude <= a.latitude + :mykm/111.12
and haversine(:mylatitude,a.latitude,:mylongitude,a.longitude, 'KM') <= :mykm
order by haversine(:mylatitude,a.latitude,:mylongitude,a.longitude, 'KM')
这将使用纬度边界框粗略排除太远的城市从你的点。您的DBMS将对您的纬度索引使用索引范围扫描，以快速挑选出您的城市表中值得考虑的行。然后它会运行你的haversine函数，一个具有所有正弦和余弦数学，只有在这些行。
This will use a latitude bounding box to crudely rule out cities that are too far away from your point. Your DBMS will use an index range scan on your latitude index to quickly pick out the rows in your cities table that are worth considering. Then it will run your haversine function, the one with all the sine and cosine maths, only on those rows.
我建议纬度，因为地面距离经度随纬度而变化。
I suggest latitude because the on-the-ground distance of longitude varies with latitude.
请注意，这是粗略的。对于商店寻找者来说是好的，但如果你是土木工程师，不要使用它 - 地球有一个椭圆形，这假设它是圆形的。
Note this is crude. It's fine for a store-finder, but don't use it if you're a civil engineer -- the earth has an elliptical shape and the this assumes it's circular.
请参阅这里的可行距离函数。
See here for a workable distance function.
为什么这个MySQL存储函数给出不同于在查询中进行计算的结果？
这篇关于地理位置距离城市表的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
