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

6fe3ivhb 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(344)

各位：
我完全被这个问题困住了。我尝试过在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`

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

mysql latitude-longitude coordinate-systems geographic-distance

来源：https://stackoverflow.com/questions/48310305/find-cities-within-a-certain-distance-from-address-using-mysql

3条答案

按热度按时间

doinxwow1#

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

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个位置的存储过程。如果需要的话，那会是另一条线。
谢谢。

赞(0）回复(0）举报 2021-06-21

gkn4icbw2#

你可以用这个：

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个位置来说，它应该足够了。

赞(0）回复(0）举报 2021-06-21

chhkpiq43#

试试这个：

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);

赞(0）回复(0）举报 2021-06-21

我来回答

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

3条答案

相关问题

热门标签

最新问答