Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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";
share|improve this question

migrated from stackoverflow.com Aug 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
    
And what about this answer –  Wouter Huysentruit Aug 14 '13 at 6:49
1  
en.wikipedia.org/wiki/Join_(SQL) –  Wouter Huysentruit Aug 14 '13 at 6:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.