按纬度/经度排序MySQL查询

Every user in my database has their latitude and longitude stored in two fields (lat, lon)

我的数据库中的每个用户的纬度和经度都存储在两个字段中(lat,lon)

The format of each field is:

每个字段的格式为:

lon | -1.403976 
lat | 53.428691

If a user searches for other users within, say 100 miles, I perform the following in order to calculate the appropriate lat/lon range ($lat and $lon are the current users values)

如果用户搜索其他用户,例如100英里,我执行以下操作以计算适当的纬度/经度范围($ lat和$ lon是当前用户值)

$R = 3960;  // earth's mean radius
$rad = '100';
// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));

$maxLat=number_format((float)$maxLat, 6, '.', '');
$minLat=number_format((float)$minLat, 6, '.', '');
$maxLon=number_format((float)$maxLon, 6, '.', '');
$minLon=number_format((float)$minLon, 6, '.', '');

I can then perform a query such as:

然后我可以执行查询,例如:

$query = "SELECT * FROM table WHERE lon BETWEEN '$minLon' AND '$maxLon' AND lat BETWEEN '$minLat' AND '$maxLat'";

This works fine, and I use a function to calulate and display the actual distance between users at output stage, but I'd like to be able to sort the results by decreasing or increasing distance at the query stage.

这工作正常,我使用一个函数来计算和显示输出阶段用户之间的实际距离,但我希望能够通过在查询阶段减少或增加距离来对结果进行排序。

Is there any way of doing this?

有没有办法做到这一点?

4 个解决方案

#1

Remember Pythagoras?

$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY (POW((lon-$lon),2) + POW((lat-$lat),2))";

Technically that's the square of the distance instead of the actual distance, but since you're just using it for sorting that doesn't matter.

从技术上讲,这是距离的平方而不是实际距离,但是因为你只是用它来进行无关紧要的排序。

This uses the planar distance formula, which should be good over small distances.

这使用了平面距离公式,它应该在很小的距离上很好。

HOWEVER:

If you want to be more precise or use longer distances, use this formula for great circle distances in radians:

如果您想要更精确或使用更长的距离,请使用此公式以弧度表示较大的圆距:

dist = acos[ sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lng1-lng2) ]

(To get the distance in real units instead of radians, multiply it by the radius of the Earth. That's not necessary for ordering purposes, though.)

(要以实际单位而不是弧度来获得距离,请将其乘以地球的半径。但这不是订购目的所必需的。)

Latitude and longitude is assumed by the MySQL computation engine to be in radians, so if it's stored in degrees (and it probably is), you'll have to multiply each value by pi/180, approximately 0.01745:

MySQL计算引擎假设纬度和经度为弧度,因此如果以度数(可能是)存储,则必须将每个值乘以pi / 180,大约为0.01745:

$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

or even:

$sf = 3.14159 / 180; // scaling factor
$er = 6350; // earth radius in miles, approximate
$mr = 100; // max radius
$sql = "SELECT * FROM table 
    WHERE $mr >= $er * ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

#2

Using just SELECT * FROM Table WHERE lat between $minlat and $maxlat will not be accurate enough.

在$ minlat和$ maxlat之间使用SELECT * FROM表WHERE lat将不够准确。

The correct way to query distance is using the coordinates in radians.

查询距离的正确方法是使用弧度坐标。

<?php
  $sql = "SELECT * FROM Table WHERE acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 1000";

Here is a handy reference - http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

这是一个方便的参考 - http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

For example:

<?php
  $distance = 100;
  $current_lat = 1.3963;
  $current_lon = -0.6981;
  $earths_radius = 6371;

  $sql = "SELECT * FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance";

And if you want to do the order by and show the distance:

如果您想按顺序执行并显示距离:

<?php
  $distance = 100;
  $current_lat = 1.3963;
  $current_lon = -0.6981;
  $earths_radius = 6371;

  $sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";

Edited for @Blazemonger and the avoidance of doubt :) If you want to work in degrees instead of radians:

编辑@Blazemonger并避免怀疑:)如果你想以度数而不是弧度工作:

<?php
  $current_lat_deg = 80.00209691585;
  $current_lon_deg = -39.99818366895;
  $radians_to_degs = 57.2957795;

  $distance = 100;
  $current_lat = $current_lat_deg / $radians_to_degs;
  $current_lon = $current_lon_deg / $radians_to_degs;
  $earths_radius = 6371;

  $sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";

You could easily wrap this up into a class that accepted Radians or Degrees from the information provided above.

您可以轻松地将其包含在从上面提供的信息中接受Radians或Degrees的类中。

#3

This is the formula that gave me the correct results (as opposed to the solutions above). Confirmed by using the Google Maps "Measure distance" feature (direct distance, not the transportation distance).

这是给出正确结果的公式(与上述解决方案相反)。通过使用谷歌地图“测量距离”功能(直接距离,而不是运输距离)确认。

SELECT
    *,
    ( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) ) AS `distance`
FROM `locations`
ORDER BY `distance` ASC

:latitude and :longitude are the placeholders for the PDO functions. You can replace them with the actual values if you'd like. latitude and longitude are the column names.

:latitude和:longitude是PDO函数的占位符。如果您愿意,可以用实际值替换它们。纬度和经度是列名。

3959 is the Earth radius in miles; the distance output will be in miles as well. To change it to kilometers, replace 3959 with 6371.

3959是以英里为单位的地球半径;距离输出也将以英里为单位。要将其更改为公里,请将3959替换为6371。

#4

wont give you results ordered by planar distance (not accounting for curvature of the earth) but for small radius' should work out.

不会给你平面距离排序的结果(不考虑地球的曲率),但对于小半径'应该工作。

SELECT * from table where lon between '$minLon' and '$maxLon' and lat between '$minLat' and '$maxLat' order by (abs(lon-$lon)/2) + (abs(lat-$lat)/2);

#1