SQL查询，通过给定坐标选择最近的地方

2022-01-24 00:00:00 coordinates sql range php mysql

我有 $latitude = 29.6815400 和 $longitude = 64.3647100，现在在 MySQL 中，我想取离这些坐标最近的 15 个地方，我打算做这个查询:

I have $latitude = 29.6815400 and $longitude = 64.3647100, now in MySQL I would like to take the 15 nearest places to these coordinates and I'm planning to do this query:

SELECT *
FROM places
WHERE latitude  BETWEEN($latitude  - 1, $latitude  + 1)
AND   longitude BETWEEN($longitude - 1, $logintude + 1)
LIMIT 15;

您认为这是正确的还是您提出了其他建议?

Do you think it's correct or do you suggest something else?

BEETWEEN 怎么做，因为我想在附近的地方搜索最多 50Km 的范围?

How to do the BEETWEEN, since I want to search trough a maximum of 50Km range the near places?

我忘了说我也可以在运行查询之前使用 PHP 做任何事情.

I forgot to say that I can also use PHP for do anything before to run the query.

注意:我不能使用存储过程.

推荐答案

这是计算两点之间距离的PHP公式:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') 
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))+
               (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance); $distance = rad2deg($distance); 
   $distance = $distance * 60 * 1.1515;

   switch($unit) 
   { 
     case 'Mi': break;
     case 'Km' : $distance = $distance * 1.609344; 
   } 
   return (round($distance,2)); 
}

然后添加查询以获取距离小于或等于上述记录的所有记录:

then add a query to get all the records with distance less or equal to the one above:

$qry = "SELECT * 
        FROM (SELECT *, (((acos(sin((".$latitude."*pi()/180)) *
        sin((`geo_latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
        cos((`geo_latitude`*pi()/180)) * cos(((".$longitude."-
        `geo_longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) 
        as distance
        FROM `ci_geo`)myTable 
        WHERE distance <= ".$distance." 
        LIMIT 15";

您可以查看此处了解类似的计算.

and you can take a look here for similar computations.

您可以阅读更多这里

更新:

您必须记住，要计算 longitude2 和 longitude2，您需要知道:

you have to take in mind that to calculate longitude2 and longitude2 you need to know that:

纬度的每一度相距大约 69 英里(111 公里).范围从 68.703 英里(110.567 公里)不等(由于地球略呈椭圆形)在赤道到两极的 69.407(111.699 公里).这很方便，因为每分钟(1/60 度)大约是一英里.

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

经度的度数在赤道处最宽，为 69.172 英里 (111.321)，在两极逐渐缩小到零.在北纬 40° 或南纬 40° 经度之间的距离为 53 英里(85 公里).

A degree of longitude is widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. At 40° north or south the distance between a degree of longitude is 53 miles (85 km).

所以按50km计算$longitude2 $latitude2然后大概:

so to calculate $longitude2 $latitude2 according to 50km then approximately:

$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km

相关文章