Find Cities Within a Certain Distance From Address using MySQL

Question

everyone:

I am completely stuck with this problem. I have tried adding conditions as a WHERE statement, IF function in the SELECT statement, and ON statement when joining. None of these produce the result I want.

WHAT I NEED: A list of cities within 0.5 degrees latitude and 0.5 degrees longitude of a specific location.

WHAT I WAS GIVEN: Two tables - One table with a list of specific locations and another table with a list of cities as stated by US Census.

Table 1 includes:

Location Address
City
State
Location Latitude
Location Longitude

Sample Location Data:

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

Table 2, which is US Census data, includes:

City
State
City Latitude
City Longitude

Sample City Data:

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

So for example, if I have a location in Palo Alto, CA, I want something that looks like this:

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

I tried:

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

I tried:

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)

I tried:

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`

But these did not produce the results I wanted. What am I missing?

Thanks.

What was wrong with the results? Your query doesn't have any longitude limitations, which is probably important. — ceejayoz, Jan 17 '18 at 21:34
Oops. Forgot about that. I have added that line which is the same as the latitude where statement. The results show me an empty table. — Cinji18, Jan 17 '18 at 21:49
Could you give us a couple of rows worth of data so we can get an idea for what the information stored in your database actually is? — Jhecht, Jan 17 '18 at 21:59
Please create a http://sqlfiddle.com/ with your schema and this sample data. — ceejayoz, Jan 17 '18 at 22:31
That said, your query says `WHERE d.City = 'Seattle' AND d.State = 'WA'`, but your sample data says `Palo Alto, CA`. Either fix that or provide us with the actual data/query pairings. — ceejayoz, Jan 17 '18 at 22:32
I'm starting to think I need to create a procedure or use a WHILE/IF statement. Not sure this is right either. — Cinji18, Jan 18 '18 at 00:52
I tried putting the latitude/longitude limits as conditions in an IF function, but that didn't work either. — Cinji18, Jan 19 '18 at 23:22

studio1057 · Answer 1 · 2018-01-17T22:51:28.230

0

Try this:

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

Or, this also works:

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

edited Jan 17 '18 at 22:51

answered Jan 17 '18 at 22:44

studio1057

147
2
5

see next answer – studio1057 Jan 17 '18 at 22:48
So I implemented the first solution because the second one didn't seem to work for me. Even so, I only get one city per location instead of a list of cities per location. – Cinji18 Jan 18 '18 at 00:43
Try and increase the distance and see if that returns more cities. Also, check your data, see if there are sufficient lat/long combinations for it to work with the distance you need. I have tested this with our geo table and it did return data. – studio1057 Jan 24 '18 at 20:26

score 0 · Answer 2 · answered Jan 21 '18 at 19:11

After much work, I was able to figure the primary part out using subqueries and a slight adjustment to my parameters:

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

Now I have to figure out how to do this as a stored procedure for all 660 locations. That would be a different thread if needed.

Thanks.

score 0 · Answer 3 · answered Feb 08 '18 at 19:46

You could use this:

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

This will loop through the 660 locations and produce the data you are looking for.

This however, could be an expensive (slow) query because of the number of the subqueries but for 660 locations it should be sufficient.

Find Cities Within a Certain Distance From Address using MySQL

3 Answers3