SQL 查询某个位置半径内的总点

Question

我有一个包含美国所有邮政编码的数据库表，其中包括城市、州、纬度和地区。每个邮政编码的经度。我还有一个点数据库表，每个点都有一个纬度和纬度。与它们相关的经度。我希望能够使用 1 个 MySQL 查询为我提供邮政编码表中所有唯一城市/州组合的列表，以及该城市/州给定半径内的总点数。我可以使用以下查询获取唯一的城市/州列表：

select city,state,latitude,longitude
from zipcodes 
group by city,state order by state,city;

我可以使用以下查询获取特定城市 100 英里半径内纬度“$lat”和经度“$lon”的点数：

select count(*) 
from points 
where (3959 * acos(cos(radians($lat)) * cos(radians(latitude)) * cos(radians(longitude) - radians($lon)) + sin(radians($lat)) * sin(radians(latitude)))) < 100;

我有什么我无法做的是找出如何以不会破坏我的数据库的方式组合这些查询。这是我的一次悲伤的尝试：

select city,state,latitude,longitude,
    (select count(*) from points
     where status="A" AND 
          (3959 * acos(cos(radians(zipcodes.latitude)) * cos(radians(latitude)) * cos(radians(longitude) - radians(zipcodes.longitude)) + sin(radians(zipcodes.latitude)) * sin(radians(latitude)))) < 100) as 'points' 
from zipcodes 
group by city,state order by state,city;

这些表当前具有以下索引：

Zipcodes - `zip` (zip)
Zipcodes - `location` (state,city)
Points - `status_length_location` (status,length,longitude,latitude)

当我在上一个 MySQL 查询之前运行解释时，这是输出：

+----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+
| id | select_type        | table    | type | possible_keys          | key                    | key_len | ref   | rows  | Extra                           |
+----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+
|  1 | PRIMARY            | zipcodes | ALL  | NULL                   | NULL                   | NULL    | NULL  | 43187 | Using temporary; Using filesort | 
|  2 | DEPENDENT SUBQUERY | points   | ref  | status_length_location | status_length_location | 2       | const | 16473 | Using where; Using index        | 
+----+--------------------+----------+------+------------------------+------------------------+---------+-------+-------+---------------------------------+

我知道我可以循环遍历所有邮政编码并计算给定半径内的匹配点数量但积分表会一直增长，我不想在邮政编码数据库中出现过时的积分总数。我希望 MySQL 专家能够告诉我我的方法的错误。预先感谢您的帮助！

Answer 1

无论是否是 MySQL Guru，问题是，除非您找到一种过滤掉各种行的方法，否则需要计算每个点和每个城市之间的距离......

有两种通用方法可以帮助这种情况

• 使距离公式更简单
• 过滤掉给定城市 10 万半径范围内不太可能的候选者

在进入这两种改进途径之前，您应该决定这 100 英里距离所需的精度级别，还应该指出数据库覆盖了哪个地理区域（这只是美国大陆等吗？

这样做的原因是，虽然在数值上更精确，但大圆公式的计算量非常大。性能改进的另一个途径是另外存储各种“网格坐标”（或者代替） ) 纬度/经度坐标

编辑：
关于更简单（但不太精确）的公式的一些想法：
由于我们处理的是相对较小的距离（我猜测在北纬 30 度到 48 度之间），我们可以使用欧几里德距离（或者更好的是欧几里德距离的平方）而不是更复杂的球面三角函数公式.
根据预期的精度水平，甚至可以接受一个完整经度的线性距离参数，取所考虑区域的平均值（例如大约 46法定 英里） 。然后，该公式将变为

  LatDegInMi = 69.0
  LongDegInMi = 46.0
  DistSquared = ((Lat1 - Lat2) * LatDegInMi) ^2 + ((Long1 - Long2) * LongDegInMi) ^2

“关于具有网格信息的列进行过滤以限制距离计算所考虑的行数”的想法。
系统中的每个“点”，无论是城市还是另一个点（？送货地点、商店位置……等等）都被分配两个整数坐标，定义该点所在位置的平方（例如 25 英里 * 25 英里）。距参考点（给定城市）100 英里内的任何点的坐标在 x 方向上最多为 +/- 4，在 y 方向上最多为 +/- 4。然后，我们可以编写类似于以下

SELECT city, state, latitude, longitude, COUNT(*)
FROM zipcodes Z
JOIN points P 
  ON P.GridX IN (
    SELECT GridX - 4, GridX - 3, GridX - 2, GridX - 1, GridX, GridX +1, GridX + 2 GridX + 3, GridX +4
   FROM zipcode ZX WHERE Z.id = ZX.id)
  AND
   P.GridY IN (
    SELECT GridY - 4, GridY - 3, GridY - 2, GridY - 1, GridY, GridY +1, GridY + 2 GridY + 3, GridY +4
   FROM zipcode ZY WHERE Z.id = ZY.id)
WHERE P.Status = A
   AND ((Z.latitude - P.latitude) * LatDegInMi) ^2 
      + ((Z.longitude - P.longitude) * LongDegInMi) ^2 < (100^2)
GROUP BY city,state,latitude,longitude;

内容的查询。请注意，LongDegInMi 可以是硬编码的（对于美国大陆内的所有位置都相同），也可以来自邮政编码表中的相应记录。类似地，LatDegInMi 可以进行硬编码（几乎不需要使其变化，因为与其他不同，它相对恒定）。

之所以更快，是因为对于邮政编码表和点表之间的笛卡尔积中的大多数记录，我们根本不计算距离。我们根据索引值（GridX 和 GridY）消除它们。

这给我们带来了要生成哪些 SQL 索引的问题。当然，我们可能想要：
- GridX + GridY + 状态（在积分表上）
- GridY + GridX + 状态（可能）
- 邮政编码表上的城市 + 州 + 纬度 + 经度 + GridX + GridY

网格的另一种替代方法是根据给定城市的纬度和经度“绑定”我们将考虑的纬度和经度限制。即 JOIN 条件变成一个范围而不是 IN ：

JOIN points P 
  ON    P.latitude > (Z.Latitude - (100 / LatDegInMi)) 
    AND P.latitude < (Z.Latitude + (100 / LatDegInMi)) 
    AND P.longitude > (Z.longitude - (100 / LongDegInMi)) 
    AND P.longitude < (Z.longitude + (100 / LongDegInMi)) 

Answer 2

当我进行这些类型的搜索时，我的需求允许一些近似值。因此，我使用第二个查询中的公式首先计算“边界”——允许半径的极值处的四个纬度/经度值，然后采用这些边界并执行一个简单的查询以查找其中的匹配项（小于最大纬度、经度，大于最小纬度、经度）。所以我最终得到的是一个正方形内的所有东西，位于由半径定义的圆内。

Answer 3

SELECT * FROM tblLocation 
    WHERE 2 > POWER(POWER(Latitude - 40, 2) + POWER(Longitude - -90, 2), .5)

其中 2 >部分是平行线的数量，40和-90是测试点的纬度/经度

对不起，我没有使用你的表名或结构，我只是从我的一个数据库中的一个存储过程中复制了这个。

如果我想查看邮政编码中的点数，我想我会这样做：

SELECT 
    ParcelZip, COUNT(LocationID) AS LocCount 
FROM 
    tblLocation 
WHERE 
    2 > POWER(POWER(Latitude - 40, 2) + POWER(Longitude - -90, 2), .5)
GROUP BY 
    ParcelZip

获取范围内所有位置的总数将如下所示：

SELECT 
    COUNT(LocationID) AS LocCount 
FROM 
    tblLocation 
WHERE 
    2 > POWER(POWER(Latitude - 40, 2) + POWER(Longitude - -90, 2), .5)

交叉连接在这里可能效率低下，因为我们正在讨论大量记录，但这应该在单个查询中完成工作：

SELECT 
    ZipCodes.ZipCode, COUNT(PointID) AS LocCount 
FROM
    Points
CROSS JOIN 
    ZipCodes
WHERE 
    2 > POWER(POWER(Points.Latitude - ZipCodes.Latitude, 2) + POWER(Points.Longitude - ZipCodes.Longitude, 2), .5)
GROUP BY 
    ZipCodeTable.ZipCode