Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
01-06-2012, 05:15 AM #1
- Join Date
- Nov 2011
- 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:
| username | sex | birthdate | zip_code | |----------+-----+------------+----------| | coolUser | M | 02-14-1987 | 90210 | | blueUser | F | 06-16-1982 | 10011 | | . | . | . | . | | . | . | . | . | | . | . | . | . |
SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986
How do I filter a SQL result based on distance when I have the latitude and longitude?
01-06-2012, 06:29 PM #2
- Join Date
- Jan 2011
- Thanked 834 Times in 832 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.
01-06-2012, 08:42 PM #3
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.Be yourself. No one else is as qualified.
01-06-2012, 08:44 PM #4
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:
It's the first demo there, at the top of the page. Notice the zip file that you can download.Be yourself. No one else is as qualified.