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:
- Select cities within 100 miles of Chicago (how to save this list in SQL? Or should I do it in PHP?)
- 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";