mysql 地理位置距离_mysql – 来自cities表的地理位置距离SQL

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

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 ;

这是我的城市表的结构：

> +------------+-------------+------+-----+---------+----------------+ |

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

> +------------+-------------+------+-----+---------+----------------+

它工作得很好.

我要做的事(伪代码)是这样的：

SELECT * FROM cities WHERE DISTANCE(SELECT id FROM cities WHERE id={cityId}, {km))

它会让我回到最近的城市.

有关如何做到这一点的任何想法？

此刻,我只是调用函数,然后将id迭代到一个数组中,然后在city表中执行一个显然效率不高的WHEREIN.

任何帮助深表感谢.谢谢.