SQL查询，按给定坐标选择最近的地方 [英] SQL query, select nearest places by a given coordinates

问题描述

我有$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怎么做，因为我想在附近的地方搜索最大距离为50公里的波谷?

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 km).这很方便，因为每分钟(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度之间，经度之间的距离为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).

因此要根据50公里计算$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

这篇关于SQL查询，按给定坐标选择最近的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！