...

xecure
01-06-2012, 04:15 AM
I have a database full of user. This is roughly how the database looks:

| 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:

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?

sunfighter
01-06-2012, 05:29 PM
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.

Old Pedant
01-06-2012, 07:42 PM
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.

Old Pedant
01-06-2012, 07:44 PM
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/clearviewdesign/newbie/demos.html

It's the first demo there, at the top of the page. Notice the zip file that you can download.