使用 MySQL 查找距地址一定距离内的城市

Question

everyone：

我完全被这个问题困住了。 我尝试在连接时将条件添加为 WHERE 语句、SELECT 语句中的 IF 函数和 ON 语句。 这些都没有产生我想要的结果。

我需要什么：特定位置0.5度纬度和0.5度经度内的城市列表。

我得到的：两个表 - 一个表包含特定位置的列表，另一个表包含美国人口普查所述的城市列表。

表 1 包括：

Location Address
City
State
Location Latitude
Location Longitude

示例位置数据：

Street              City        State  Latitude     Longitude
774 Emerson Street  Palo Alto   CA     37.44239044  -122.15956879

表2是美国人口普查数据，包括：

City
State
City Latitude
City Longitude

示例城市数据：

City            State Latitude     Longitude
Palo Alto       CA    37.44188309   -122.14302063
Mountain View   CA    37.38605118   -122.08385468
Sunnyvale       CA    37.36883      -122.0363496

例如，如果我在加利福尼亚州帕洛阿尔托有一个位置，我想要看起来像这样的东西：

Location        City          State
Palo Alto CA    Palo Alto     CA
Palo Alto CA    Mountain View CA
Palo Alto CA    Sunnvyale     CA

我试过了：

SELECT d.`City`
      ,d.`State`
      ,l.`City`
      ,l.`StateAbbreviation`
FROM `LocationDirectoryRevised` AS d
     LEFT JOIN
     `LatLong` AS l
     ON d.`City` = l.`City` AND d.`State` = l.`StateAbbreviation`
WHERE l.`Latitude` BETWEEN (d.`Latitude` + 0.5) AND (d.`Latitude` - 0.5)
      AND
      l.`Longitude` BETWEEN (d.`Longitude` + 0.5) AND (d.`Longitude` - 0.5);

我试过了：

SELECT d.`City`
      ,d.`State`
      ,l.`City`
      ,l.`StateAbbreviation`
FROM `LocationDirectoryRevised` AS d
     LEFT JOIN
     `LatLong` AS l
     ON l.`Latitude` BETWEEN (d.`Latitude` + 0.5) AND (d.`Latitude` - 0.5) &&     
        l.`Longitude` BETWEEN (d.`Longitude` + 0.5) AND (d.`Longitude` - 0.5)

我试过了：

SELECT d.`City`
      ,d.`State`
      ,IF((l.`Latitude` < (d.`Latitude` + 0.5)) && (l.`Latitude` > (d.`Latitude` - 0.5)) &&  (l.`Longitude` < (d.`Longitude` + 0.5)) && (l.`Longitude` > (d.`Longitude` - 0.5)), l.`City`,NULL)
      ,l.`StateAbbreviation`
FROM `LocationDirectoryRevised` AS d
     LEFT JOIN
     `LatLong` AS l
     ON d.`City` = l.`City` AND d.`State` = l.`StateAbbreviation`

但这些并没有产生我想要的结果。 我错过了什么？

谢谢。

Answer 1

试试这个：

WHERE d.`City` = 'Seattle' AND d.`State` = 'WA'
AND
l.`Latitude` < (d.`Latitude` + 0.5) 
AND 
l.`Latitude > (d.`Latitude` - 0.5)
AND
l.`Longitude` < (d.`Longitude` + 0.5) 
AND 
`l.Longitude` > (d.`Longitude` - 0.5);

或者，这也可以工作：

WHERE d.`City` = 'Seattle' AND d.`State` = 'WA'
AND
l.`Latitude` BETWEEN (d.`Latitude` + 0.5) AND (d.`Latitude` - 0.5)
AND
l.`Longitude` BETWEEN (d.`Longitude` + 0.5) AND (d.`Longitude` - 0.5);

Answer 2

经过大量工作，我能够使用子查询和对参数的轻微调整来找出主要部分：

SELECT `City`
      ,`StateAbbreviation`
      ,`Latitude`
      ,`Longitude`
FROM `LatLong`
WHERE `Latitude` BETWEEN (SELECT `Latitude` - 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = 1)
                      AND (SELECT `Latitude` + 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = 1)
      AND
      `Longitude` BETWEEN (SELECT `Longitude` - 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = 1)
                      AND (SELECT `Longitude` + 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = 1);

现在，我必须弄清楚如何将此操作作为所有 660 个位置的存储过程。 如果需要，这将是一个不同的线程。

谢谢。

Answer 3

你可以使用这个：

drop procedure if exists getNearLocations;
create procedure getNearLocations();
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
declare e INT default 0;
SELECT COUNT(*) FROM LocationDirectoryRevised INTO n;
SET i=0;
WHILE i<n DO 
select ID from LocationDirectoryRevised into e;
  SELECT `City`
      ,`StateAbbreviation`
      ,`Latitude`
      ,`Longitude`
FROM `LatLong`
WHERE `Latitude` BETWEEN (SELECT `Latitude` - 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = e)
                      AND (SELECT `Latitude` + 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = e)
      AND
      `Longitude` BETWEEN (SELECT `Longitude` - 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = e)
                      AND (SELECT `Longitude` + 0.1
                          FROM `LocationDirectoryRevised`
                          WHERE `ID` = e);
  SET i = i + 1;
END WHILE;
End

这将遍历 660 个位置并生成您正在寻找的数据。

但是，由于子查询的数量，这可能是一个昂贵(缓慢(的查询，但对于 660 个位置来说，它应该足够了。