Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I filter a SQL result based on distance when I have the latitude and longitud

    I have a database full of user. This is roughly how the database looks:

    Code:
    | username | sex | birthdate  | zip_code |
    |----------+-----+------------+----------|
    | coolUser |  M  | 02-14-1987 |  90210   |
    | blueUser |  F  | 06-16-1982 |  10011   |
    |     .    |  .  |     .      |    .     |
    |     .    |  .  |     .      |    .     |
    |     .    |  .  |     .      |    .     |
    A user will be able to search the database for another user based on their sex, age and distance. I have another database with a list of zipcodes in the U.S. along with with their latitude and longitude. I have written distance function where it calculates the distance of 2 zipcodes based on their latitude and longitude values. I understand I can sort the users by sex and age by something like this:

    Code:
    SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986
    Which brings me to my actual question: Is there any possible way to calculate the distance of the 2 users via an SQL query? Or do I have to sort the user based on age and sex first and then get that list and then filter it by distance? If I go with the second method how do I properly display the first 10 results, and have a pagination feature?



    How do I filter a SQL result based on distance when I have the latitude and longitude?

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,220
    Thanks
    23
    Thanked 606 Times in 605 Posts
    As far as i know, mysql does not have a long/lat dist. feature. But we can narrow your search.

    I assume that your matching people as to the distance they're willing to travel in the U.S.A. I also believe a 200 mile would be the most distance.

    With that as a given the N-S distance translates into a +/- 3degrees. The E-W depends on if your in the south or in the north, but it also translates into a +/- of 3(in the south) to 4(if northerly) degrees.

    So I'd first try setting a WHERE query long is +/- 3 degrees from the given point and Lat at +/- 3.5 degrees. Then use your distance function to further eliminate.

    Run some test you might get tings a little tighter for your use.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Just for starters, you have to have another table that has all the zipcodes with their latitudes and longitudes.

    And then you need to move the formula for calculating distance based on lat/long into your SQL coding.

    Fair warning: It's a complex calculation and it's going to be *SLOW*.

    Unless you are willing to put a reasonable limit on the distance, such as the 200 miles that Sunfighrer proposed (you can probably even go up to 500), in which case you can simplify the calculations down to just using the Pythagorean Theorem.

    Sunfighter's scheme is a start on the right answer. But the big problem is that the number of miles in a degree of latitude varies widely, based on the longitude.

    My solution is to precalculate latitude and longitude *IN MILES*, instead of in degrees, and then to also precalculate the number of miles per degree of latitude at the given longitude. Put all those in your zip code table, combine it with using a limiting square as Sunfigher suggested, and now calculations are quite reasonable.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    By the by, I have a demo of all of this that works quite well, but it's written in ASP code and uses an Access database. If you think it would be useful to you to look at it, look here:
    http://www.clearviewdesign.com/clear...bie/demos.html

    It's the first demo there, at the top of the page. Notice the zip file that you can download.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •