来自城市表的地理位置距离 SQL

Geolocation distance SQL from a cities table(来自城市表的地理位置距离 SQL)

问题描述

所以我有这个函数可以根据纬度、经度和半径参数计算最近的城市.

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)) 

它会返回最近的城市.

关于我如何做到这一点的任何想法?

Any ideas of how I can do this?

目前，我只是调用该函数，然后将 ids 迭代到一个数组中，然后在 city 表中执行 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 将在您的纬度索引上使用索引范围扫描来快速挑选出您的城市表中值得考虑的行.然后它会运行你的 hasrsine 函数，一个包含所有正弦和余弦数学的函数，只在那些行上.

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.

(很抱歉 111.12 幻数.这是一个纬度的公里数，即 60 海里.)

(Sorry about the 111.12 magic number. That's the number of km in a degree of latitude, that is in sixty nautical miles.)

有关可行的距离函数，请参见此处.

See here for a workable distance function.

为什么这个 MySQL 存储函数给出的结果是否与在查询中进行计算不同?

这篇关于来自城市表的地理位置距离 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！