CODEWITHSUNDEEP
mysql
user2694306
user2694306

Reputation: 4050

MySQL Query To Select Closest City

I am trying to repeat the following query for all rows. Basically I am trying to map the closest city (based on the latitude and longitude) to the places latitude and longitude. I have a table places which contains the places that need to be mapped, and a table CityTable with the places to be matched to. I have the following query which works for a single row:

SELECT p.placeID, p.State, p.City, p.County, p.name, 
       SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) 
       + POW((53 * (p.lng - z.Loungitude)), 2)) AS distance,
       p.lat,p.lng,z.Latitude,z.Loungitude,z.City 
FROM places p,CityTable z 
WHERE p.placeID = 1 
ORDER BY distance ASC 
LIMIT 1;

This works for a single location. Obviously I would need to remove the WHERE constraints to apply it to the entire table.The problem that I am encountering is that it seems to want to make a copy to compare to every other element in the table. For example, if there are 100 rows in p and 100 rows in z, then the resulting table seems to be 10,000 rows. I need the table to be of size count(*) for p. Any ideas? Also, are there any more efficient ways to do this if my table p contains over a million rows? Thanks.

Upvotes: 3

Views: 1106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can find the nearest city to a place using:

SELECT p.placeID, p.State, p.City, p.County, p.name, 
       (select z.City
        from CityTable z
        order by SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) + POW((53 * (p.lng - z.Loungitude)), 2)) 
        limit 1
       ) as City,
       p.lat, p.lng
FROM places p
ORDER BY distance ASC;

(If you want additional city information, join the city table back in on City.)

This doesn't solve the problem of having to do the Cartesian product. It does, however, frame it in a different way. If you know that a city is within five degrees longitude/latitude of any place, then you can make the subquery more efficient:

       (select z.City
        from CityTable z
        where z.lat >= p.lat + 5 and z.lat <= p.lat - 5 and
              z.long <= p.long + 5 and z.long <= p.lat - 5
        order by SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) + POW((53 * (p.lng - z.Loungitude)), 2)) 
        limit 1
       ) as City,
       p.lat, p.lng;

This query will use an index on lat. It might even use an index on lat, long.

If this isn't sufficient, then you might consider another way of reducing the search space, by looking only at neighboring states (in the US) or countries.

Finally, you may want to consider the geospatial extensions to MySQL if you are often dealing with this type of data.

Upvotes: 3

Related Questions