使用纬度和经度找到距离MySQL最近的10个城市? [英] Find closest 10 cities with MySQL using latitude and longitude?

问题描述

我已经在网络上进行了大量搜索，以找到解决我的问题的方法，但仍然无法解决.

I have searched the Web a lot to find the solution for my problem but I still can't figure it out.

我有一个非常简单的数据库，其中包含ID，城市名称，纬度，经度和city_info.当某人进入城市页面时，a想要显示附近的10个城市.

I have a very simple database with id, city name, latitude, longitude and city_info. When someone enters a city page a would like to show the 10 nearby cities.

我该如何使用MySQL计算并通过PHP返回它?

How can I calculate this with MySQL and return it with PHP?

我在这个网站上看到了很多建议，但是这些建议都不起作用.

I have seen a lot of suggestions on this website, however none of these work somehow.

我尝试的没有成功.我没有任何结果.

What I tried without success. I do not get any results.

<?php
$slatitude = 43.2141341;
$slongitude = 64.4368684;
$miles = 200;

//connect

$query = "SELECT *, 
( 3959 * acos( cos( radians('$slatitude') ) * 
cos( radians( latitude ) ) * 
cos( radians( longitude ) - 
radians('$slongitude') ) + 
sin( radians('$slatitude') ) * 
sin( radians( latitude ) ) ) ) 
AS distance FROM cities HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 10";

$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){

while ($row = mysql_fetch_assoc($query)){
$id = $row['id'];
$cityname = $row['cityname'];
$latitude = $row['latitude'];
$longitude = $row['longitude'];

echo "$cityname<br />";

}}

?>`

推荐答案

要提高查询速度，您可以首先限制要进行计算的结果集，例如使用下面的子选择(请注意，我还使用了另一种方法来计算距离-它对我有用，ymmv). 在我的测试中，使用容差为1的where子句比不使用它的查询快100倍以上.

To improve the speed of your query you could first limit the set of results that you'll do calculations for using something like the sub-select below (note that I also used a different method for calculating the distance - it works for me, ymmv). In my tests, using the where clause with a tolerance of 1 was over 100 times faster than the query without it.

...
$tol = 1; // limits the search to lat/long within 1 from the given values
$query_args = array($lat,$long,$lat-$tol,$lat+$tol,$long-$tol,$long+$tol);
$query = "
  SELECT *,latitude, longitude, 
    SQRT( POW( 69.1 * ( latitude - %s) , 2 ) 
        + POW( 69.1 * ( %s - longitude ) 
          * COS( latitude / 57.3 ) , 2 ) ) 
    AS distance FROM zipcodes
      WHERE latitude > %d AND latitude < %d 
      AND longitude > %d AND longitude < %d 
  ORDER BY distance ASC limit 10
";
...

这篇关于使用纬度和经度找到距离MySQL最近的10个城市?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！