...

View Full Version : How do I filter a SQL result based on distance when I have the latitude and longitude



xecure
01-06-2012, 05:17 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?

sunfighter
01-06-2012, 06:32 PM
See this answer http://www.codingforums.com/showthread.php?t=248046



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum