SQL Abs计算-使用纬度和经度-数学！ [英] SQL abs calculation - using latitudes and longitudes - MATH!

问题描述

我有一个sql查询，它基于一定范围内的坐标从数据库中提取位置.现在，我正在尝试通过一些数学运算来按顺序排列它们，但是无论我将其写在纸上多少次，我似乎都无法找出一个可行的解决方案.

I've got an sql query that pulls locations from a database based on coordinates within a certain range. Now I'm trying to order them by proximity with a bit of math - but no matter how many times im writing it out on paper I can't seem to figure out a working solution.

让我们描述一些变量:

• $ plat(地点的纬度)
• $ plong(地点的经度)
• $ slat(搜索位置的纬度)
• $ slong(搜索位置的经度)

要注意的一件大事是，我已将所有坐标转换为正数，并拥有另一个确定正数或负数的字段-但为此，我已经正确查询了附近的坐标.

One big thing to note is that I've converted all coordinates to positive numbers and have another feild that determines positive or negative - but for this assume that I've already queried the nearby coordinates properly.

为了以这种方式进行排序，我已经看到人们使用"ORDER BY abs(coord-coords)"来实现简单的操作-但我需要的是类似的内容:

In order to order it this way I've seen people use "ORDER BY abs(coord-coords)" for something simple -but what I need is something more like:

[(($ slat-$ plat)*($ slong-plong)]]-但这会引起问题，因为如果一侧的计算结果为零，则在相乘后结果也将为零，从而使结果不准确.

[($slat - $plat) * ($slong - plong)] - but this causes problems because if the resulting calulation on one side is zero - then after multiplied the result would be zero as well - making it innaccurate.

任何想法-

推荐答案

纬度/经度坐标之间的距离的解决方案称为

The solution for distance between latitude/longitude coordinates is called the haversine formula. It's complex because you need to take the curvature of the earth into account unless your distances are very short.

这里有一篇有关使用PHP和MySQL来实现定位器应用程序的文章:创建PHP，MySQL&的商店定位器Google地图

Here's an article about using PHP and MySQL to implement a locator app: Creating a Store Locator with PHP, MySQL & Google Maps

您还可以在Stack Overflow上找到有关计算坐标之间距离的许多其他问题: https ://stackoverflow.com/search?q =经度+距离

You can also find many other questions here on Stack Overflow regarding calculating distance between coordinates: https://stackoverflow.com/search?q=longitude+distance

如果您只需要计算5公里以内的距离，则地球的曲率可能并不重要.您可以使用普通的距离公式.如果您只需要使用此值来排序较接近的值，甚至可以跳过平方根计算.

If you only need to calculate distance within 5km, the curvature of the earth is probably not significant. You can use a plain distance formula. You can even skip the square-root calculation if you only need to use this value to sort which one is closer.

SELECT s.location_id,
  (s.lat-p.lat)*(s.lat-p.lat) + (s.long-p.long)*(s.long-p.long) AS distance_squared
FROM Locations s, Locations p
WHERE p.location_id = ?
ORDER BY distance_squared;

我从中选择的表(位置")具有纬度/经度坐标(我们称它们为$ plat和$ plong).

The table ('locations') I am selecting from has lat/long coordinates (lets call them $plat and $plong).

不，这些是PHP变量.您需要根据数据库表每行上的经纬度坐标计算距离.

No, those are PHP variables. You need to calculate the distance from the lat/long coordinates on each row of your database table.

问题是SQL通常一次只计算一个行中的内容.因此，您需要某种方式将两行合并为一行，以便计算可以使用两个位置的坐标.您可以通过 self-join 进行此操作.这基本上是将每一行与同一表中的另一行配对.这就是为什么我两次列出Locations并给他们两个不同的别名s和p(技术术语为相关名称).

The problem is that SQL normally only calculates things from one row at a time. So you need to have some way of combining two rows into one row, so a calculation can use the coordinates of two locations. You do this with a self-join. This is basically pairing each row with another row from the same table. And that's why I list Locations twice, and give them two different aliases, s and p (the technical term is correlation name).

如果您习惯使用PHP，则可以将这种自连接视为类似于嵌套循环:

If you're accustomed to PHP, think of this self-join as analogous to a nested loop:

foreach ($locations as &$s) {
    foreach ($locations as &$p) {
        // calculate the distance between $s and $p
    }
}

WHERE子句将p的行限制为您仅从其开始的位置(您可以将?占位符替换为一个值)，因此查询与桌子.

The WHERE clause restricts the rows of p to just the place you start from (you would substitute a single value for the ? placeholder), so it's just one row that the query pairs with all the rows of the table.

另一个提示:我跳过了使用SQRT()的原因，因为不必仅按距离排序.也就是说，如果三个位置距离我10公里，15公里和20公里，那么我可以按100、225和400进行排序，并获得与我按10、15和20进行排序相同的排序.优点是，我消除了计算平方根的步骤，这在某种程度上降低了我的查询成本.

Another tip: I skipped using SQRT() because it's not necessary just to sort by distance. That is, if three locations are 10km, 15km, and 20km from me, then I can sort by 100, 225, and 400 and get the same ordering as if I sort by 10, 15, and 20. The advantage is that I've eliminated calculating the square root which reduces the cost of my query somewhat.

这篇关于SQL Abs计算-使用纬度和经度-数学！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！