php SQL查询，通过给定坐标选择最近的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14254641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query, select nearest places by a given coordinates
提问by itsme
I have $latitude = 29.6815400and $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:
我有$latitude = 29.6815400和$longitude = 64.3647100，现在在 MySQL 中，我想将 15 个最近的位置带到这些坐标，我打算执行以下查询：
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?
您认为这是正确的还是您有其他建议？
How to do the BEETWEEN, since I want to search trough a maximum of 50Km range the near places?
怎么办BEETWEEN，因为我想搜索最大 50 公里范围的附近地点？
I forgot to say that I can also use PHP for do anything before to run the query.
我忘了说在运行查询之前我也可以使用 PHP 做任何事情。
Note: I can't use stored procedures.
注意：我不能使用存储过程。
回答by mamdouh alramadan
here's the PHPformula for calculating the distance between two points:
这里是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 herefor similar computations.
您可以在此处查看类似的计算。
and you can read more here
你可以在这里阅读更多
Update:
更新：
you have to take in mind that to calculate longitude2and longitude2you need to know that:
您必须记住，要计算longitude2和longitude2，您需要知道：
Each degree of latitudeis 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 英里（111 公里）。范围从赤道的 68.703 英里（110.567 公里）到两极的 69.407（111.699 公里）不等（由于地球略呈椭圆形）。这很方便，因为每分钟（1/60 度）大约是一英里。
A degree of longitudeis 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).
的程度经度是在最宽的在赤道69.172英里（111.321）和在两极逐渐缩小到零。在北纬或南纬 40° 处，经度之间的距离为 53 英里（85 公里）。
so to calculate $longitude2 $latitude2according to 50km then approximately:
所以$longitude2 $latitude2按照50km计算然后大约：
$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km
回答by Valerio Bozz
You have to consider that flooding any DBMS like MySQL with heavy queries should not be the best solution.
你必须考虑到用大量查询淹没任何像 MySQL 这样的 DBMS 不应该是最好的解决方案。
Instead you can speculate a very-fast SQL query selecting all the places with coordinates inside the simple square of side $radius, instead of selecting suddently a perfect circle radius. PHPcan filter the surplus.
相反，您可以推测一个非常快速的 SQL 查询，选择在 side 的简单正方形内具有坐标的所有地方$radius，而不是突然选择一个完美的圆半径。PHP可以过滤多余的。
Let me show the concept:
让我展示一下这个概念：
$lat = 45.0.6072;
$lon = 7.65678;
$radius = 50; // Km
// Every lat|lon degree° is ~ 111Km
$angle_radius = $radius / ( 111 * cos( $lat ) );
$min_lat = $lat - $angle_radius;
$max_lat = $lat + $angle_radius;
$min_lon = $lon - $angle_radius;
$max_lon = $lon + $angle_radius;
$results = $db->getResults("... WHERE latitude BETWEEN $min_lat AND $max_lat AND longitude BETWEEN $min_lon AND $max_lon"); // your own function that return your results (please sanitize your variables)
$filtereds = [];
foreach( $results as $result ) {
if( getDistanceBetweenPointsNew( $lat, $lon, $result->latitude, $result->longitude, 'Km' ) <= $radius ) {
// This is in "perfect" circle radius. Strip it out.
$filtereds[] = $result;
}
}
// Now do something with your result set
var_dump( $filtereds );
In this way MySQL runs a very friendly query that does not require a full table scan while PHP strips out the surplus with something similar to the getDistanceBetweenPointsNew()function posted in this page, comparing the distance from the coordinates of the result set to the center of your radius.
通过这种方式，MySQL 运行一个非常友好的查询，不需要全表扫描，而 PHP 用类似于getDistanceBetweenPointsNew()本页中发布的函数的东西去除多余的部分，比较从结果集的坐标到半径中心的距离.
In order to do not waste the (big) performance gain, index your coordinates columns in the database.
为了不浪费（大）性能增益，请在数据库中索引您的坐标列。
Happy hacking!
快乐黑客！
回答by Timmytjc
I've done something similar with a selling houses app, ordering by distance from a given point, place this in your SQL select statement:
我已经用一个卖房子的应用程序做了类似的事情，按距给定点的距离排序，把它放在你的 SQL 选择语句中：
((ACOS(SIN(' . **$search_location['lat']** . ' * PI() / 180) * SIN(**map_lat** * PI() / 180) + COS(' . **$search_location['lat']** . ' * PI() / 180) * COS(**map_lat** * PI() / 180) * COS((' . **$search_location['lng']** . ' - **map_lng**) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS "distance"
Replace $search_locationwith your relevant lat/lng values and the map_lat/map_lng values are the SQL columns which contain the lat/lng values. You can then order the results by distance and either use a where or having clause to filter our properties within a 50km range.
替换$search_location为您相关的 lat/lng 值，map_lat/map_lng 值是包含 lat/lng 值的 SQL 列。然后，您可以按距离对结果进行排序，并使用 where 或 having 子句在 50 公里范围内过滤我们的属性。
I would recommend using SQL as the approach compared to PHP in the event you require additional functionality such as paging.
如果您需要其他功能（例如分页），我会建议使用 SQL 作为与 PHP 相比的方法。
回答by PalDev
A bit late but it may help someone - if you want the nearest city by location, I wouldn't go on distance because then an isolated location wouldn't retrieve anything. Try this:
有点晚了，但它可能对某人有所帮助 - 如果您想要按位置最近的城市，我不会继续远距离，因为这样一个孤立的位置将无法检索任何东西。尝试这个：
$G_how_close_to_find_cities = "1.1"; // e.g. 1.1 = 10% , 1.2=20% etc
$G_how_many_cities_to_find_by_coordinates = "10";
$query = "SELECT * from Cities WHERE
Cities__Latitude <= ('".$latitude*$G_how_close_to_find_cities."') AND Cities__Latitude >= ('".$latitude/$G_how_close_to_find_cities."')
AND Cities__Longitude <= ('".$longitude*$G_how_close_to_find_cities."') AND Cities__Longitude >= ('".$longitude/$G_how_close_to_find_cities."')
ORDER BY SQRT(POWER((Cities__Latitude - ".$latitude."),2)+POWER((Cities__Longitude - ".$longitude."),2)) LIMIT 0,".$G_how_many_cities_to_find_by_coordinates;
