# Need to search users by latitude and longitude

I'm still learning SQL and this is a bit more advanced than my capabilities, so I could use some help. I think I need a sub-query or something.

In the database I have a table called `user` with a column called `city` that contains the ID of their city

I have another table called `cities` that contains 3 columns: ID, city_name, and latlong. The column `latlong` contains a `POINT()` with the latitude and longitude coordinates.

I just need to select users who are within 100 miles of a particular city. My SQL is below and I know it's completely wrong (it doesn't work). My fear even with accomplishing what I want is that it would be horribly inefficient...

For discussion, let's say that the city with an ID of 20 is Chicago. This won't happen, but for the sake of good form let's say there are hundreds of thousands of users in here. Each time you browse the users within a certain distance from Chicago, will it have to calculate this distance equation for every single user? That seems like a big burden every time a simple user search is performed. Would it make more sense to first get the distances from Chicago for every city in the `cities` table, then select the users whose city matches the cities whose distance is within 100 miles of Chicago? Just thinking about how to do that makes my brain hurt.

So from what I can conjure up, it sounds like this is what needs to happen for the most efficient searching:

1. Select cities within 100 miles of Chicago (how to save this list in SQL? Or should I do it in PHP?)
2. Select users whose city matches one of the cities from #1

Does that sound correct? Should I handle #1 in PHP or can I do it all within SQL?

If you could help me out with this I'd really appreciate it. I don't think it's something that's incredibly difficult, I've just never done anything this complex with SQL.

Here's my current SQL. The distance equation works, but the rest you should just ignore ;)

``````SELECT user.id,
(
(
(acos(
sin((13.72767 * pi() / 180))
* sin((X(cities.latlong) * pi() / 180))
+ cos((13.72767 * pi() / 180))
* cos((X(cities.latlong) * pi() / 180))
* cos(((100.52415 - Y(cities.latlong)) * pi() / 180))
)) * 180 / pi()
) * 60 * 1.1515
) as distance
FROM `cities`, `user`
WHERE user.city = 20
HAVING distance <= 100 ORDER BY distance";
``````
-

## migrated from stackoverflow.comAug 15 '13 at 6:09

This question came from our site for professional and enthusiast programmers.

possible duplicate of Return Nearest Place available to Users location using Latitude-Longitude? – Wouter Huysentruit Aug 14 '13 at 6:36
The answer to that question does what I already can do. It just selects locations within a certain distance within the same table. – Gavin Aug 14 '13 at 6:44
To calculate a distance, you need 2 points, in your formula I can see only one point (cities.latlong), what are those hardcoded values? – Wouter Huysentruit Aug 14 '13 at 6:47