基于距离的查询建议

Question

好的，自从iv不得不做这么复杂的事情已经有一段时间了，我有点迷惑了。

我有一个库类，它接受一个邮政编码，并返回一个以输入的邮政编码为半径的邮政编码的关联数组，以邮政编码为键，以与原始邮政编码的距离为值。

示例: FK27DJ => 0.094146570284875

web应用程序是一个外卖餐厅查找器，我需要运行一个查询来查找每种类型的外卖与输入的邮政编码最接近的外卖。

基于takeaway_type_id字段，有4种类型的外卖。因此，如果用户输入自己的邮政编码，则会向他们显示每种类型的单个外卖，这也是该类型与输入的邮政编码最接近的外卖。

因此，我需要传入库类中的邮政编码数组，并在外卖表中搜索每种类型的最接近的外卖。

这有意义吗？

外卖表格包含以下相关字段：

id(整数)，takeaway_type_id(整数)，邮政编码

这可以在一个查询中完成吗？该网站是基于php的。

Answer 1

是。因为它看起来像英国邮政编码，所以您需要另一个将邮政编码映射到GPS坐标(经度和纬度)的表。你可以从这里拿到。http://jamiethompson.co.uk/web/2008/07/24/full-uk-postcode-database-for-free/ (其他地方可能也有，请查看http://www.freepostcodes.org.uk/)

然后你需要在SQL中做一个"select“，连接两个表，计算距离，这在SQL中是可以做到的。"SQRT(POWER(Long1 -Long1 2，2)+ POWER(Lat1 - Lat2,2))“会给出大约的距离。警告:由于需要对每一行进行计算，因此将可能的结果限制为Long+-range，Lat+-range。

要获得准确的距离，您确实需要一些trig。这是我在最近的一个应用程序中使用的一个函数，它做了类似的事情，你应该能够通过它来工作，甚至在SQL查询中构建。

function CalculateDistance($Longitude1, $Latitude1, $Longitude2, $Latitude2) {
    // Not perfect for curvature of earth on the diaganol, but it's close enough ;)
    $LatDiff = abs($Latitude1 - $Latitude2);
    $LongDiff = abs($Longitude1 - $Longitude2);
    // Convert to meters - 1 deg latitude = 111.12km
    $mLatDiff = $LatDiff * 0.11112;
    $mLongDiff = $LongDiff * 0.11112 * cos(($Latitude1 + $Latitude2) / 2);
    // Work out difference.
    $Diff = sqrt( ($mLatDiff * $mLatDiff) + ($mLongDiff * $mLongDiff) );
    return $Diff;
}

编辑:添加代码示例

未经测试的代码，但从最近的项目编辑。将需要语法检查和调整，但会给出想法。

$UserPostcode = "AB1 2FG";

// Find the Long/Lat of the user (you don;t have to do this, but it saves a lot of DB stress)
// Postcodes is the table that links users to postcodes
$sql = 'SELECT long, lat FROM postcodes WHERE postcode="' . mysql_real_escape_string($UserPostcode) .'"';
// ... Missing a few lines of check row exists, error reporting etc
$UserLat = $row['lat'];
$UserLong = $row['long'];

// Next, come up with an acceptable range so as not to over-stress the DB:
// What are the acceptable limits?
// 1 deg latitude = 111.12km
// LATITUDE: 1km = 1/111.2 degrees of latitude = 0.0089992800575953923686105111591073
// LONGITUDE: 1km = 1/(111.2 * cos(LATITUDE))

$RangeLat = 8 * 1/111.2; // For 8km = 5 miles. Work out what you want here, the larger = more DB stress
$RangeLong = 8 * 1/(111.2 * cos(deg2rad($UserLat)); // For 8km = 5 miles. Work out what you want here, the larger = more DB stress

// Now the query you want.
// You'll need to change this to get restauants by group, but this gives you the essence of what you need:

$sql = 'SELECT r.restaurant_id, r.OtherDetails, r.postcode,
                  p.lat, p.long,
                  SQRT(POWER(p.lat-'.$UserLat.',2) + POWER(p.long-'.$UserLong.',2)) AS distance 
           FROM resturants r
             LEFT JOIN postcodes p ON r.postcode=p.postcode
           WHERE p.lat >='.($UserLat - $RangeLat).' AND p.lat <='.($UserLat + $RangeLat).' AND p.long>='.($UserLong-$RangeLong).' AND p.long>='.($UserLong+$RangeLong).'
           ORDER BY SQRT(POWER(p.lat-'.$UserLat.',2) + POWER(p.long-'.$UserLong.',2)) ASC';
if (!$result = mysql_query($sql)) { ExitError(4, $sql . '<br />' . mysql_error()); }
while ($row = mysql_fetch_assoc($result)) {
        // This is where you can count the 4 in each category, and disregard if you get more that 4.
        // If you get less than 4, offer to expand the range - or stick in a loop and do it automatically.

        // Also for efficienty, I'd suggest you remove the "distance" calculation from the SQL and use the function above
        // The function above it more accurate as it users cosine. Then simply sort by distance here instead on in SQL.

        // Also note the above function is written for the northern hemisphere. Also won't work across the date line ;)
        // But that shouldn't be a worry.

}
mysql_free_result($result);

但是-可以在一个查询中完成吗:当然，如果您想要关闭SQL server

同样，没有经过测试，但会给出要做什么的要点。不过，我强烈建议您不要这么做！

$sql = 'SELECT r.restaurant_id, r.OtherDetails, r.postcode,
                  p.lat, p.long,
                  SQRT(POWER(p.lat-p2.lat,2) + POWER(p.long-p2.long,2)) AS distance 
           FROM resturants r
             LEFT JOIN postcodes p ON r.postcode=p.postcode
             LEFT JOIN postcodes p2 ON p2.postcode="' . mysql_real_escape_string($UserPostcode) .'"
           ORDER BY SQRT(POWER(p.lat-p2.lat,2) + POWER(p.long-p2.long,2)) ASC';

//如果你走这条路，你想要更高的准确性，我会让你知道如何让COS进入那里:)。您需要的mySQL函数是RADIANS()和COS()