Flash Website Builder- Trendy Site Builder is a Flash Site Building tool that helps users build stunning websites. Check Out Custom Custom Logo Design by LogoBee. Website Design and Free Logo Templates available.
 CodingForums.com How do I filter a SQL result based on distance when I have the latitude and longitud

Before you post, read our: Rules & Posting Guidelines

Enjoy an ad free experience by logging in. Not a member yet? Register.
 01-06-2012, 05:15 AM PM User | #1 xecure 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?
 01-06-2012, 06:29 PM PM User | #2 sunfighter Senior Coder   Join Date: Jan 2011 Location: Missouri Posts: 3,269 Thanks: 23 Thanked 468 Times in 467 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 PM User | #3 Old Pedant Supreme Master coder!     Join Date: Feb 2009 Posts: 24,938 Thanks: 75 Thanked 4,305 Times in 4,272 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.
 01-06-2012, 08:44 PM PM User | #4 Old Pedant Supreme Master coder!     Join Date: Feb 2009 Posts: 24,938 Thanks: 75 Thanked 4,305 Times in 4,272 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.

 Bookmarks

 Tags distance, mysql, php, search, sql

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home :: Client side development     JavaScript programming         DOM and JSON scripting         Ajax and Design         JavaScript frameworks         Post a JavaScript     HTML & CSS     XML     Flash & ActionScript         Adobe Flex     Graphics and Multimedia discussions     General web building         Site reviews         Building for mobile devices :: Server side development     Apache configuration     Perl/ CGI     PHP         Post a PHP snippet     MySQL         Other Databases     Ruby & Ruby On Rails     ASP     ASP.NET     Java and JSP     Other server side languages/ issues         ColdFusion         Python :: Computing & Sciences     Computer Programming     Computer/PC discussions     Geek News and Humour Web Projects and Services Marketplace     Web Projects         Small projects (quick fixes and changes)         Medium projects (new script, new features, etc)         Large Projects (new web application, complex features etc)         Unknown sized projects (request quote)         Vacant job positions         Looking for work/ for hire         Project collaboration/ partnership         Paid work offers and requests (Now CLOSED)     Career, job, and business ideas or advice     Domains, Sites, and Designs for sale         Domains for sale         Websites for sale         Design templates and graphics for sale :: Other forums     Member Offers     Forum feedback and announcements

All times are GMT +1. The time now is 02:45 PM.