David, When you say "someone here found a database", do you mean someone posted a data export ? Can you provide a link ?
Dennis Byrne >-----Original Message----- >From: Dave [mailto:[EMAIL PROTECTED] >Sent: Monday, March 13, 2006 01:42 AM >To: 'MyFaces Discussion' >Subject: Re: OT: within 10 miles > >Hi david, > > Thanks! That is very helpful. > How about the performance for the SQL to search a big table(possibly > millions of businesses)? It has to go through all the records to calculate > distance. Is there a way to index somehow for better performance? > > Thanks! > >David Schlotfeldt <[EMAIL PROTECTED]> wrote: > I have a database of zip codes, city, states, longitude, and latitude. > Someone here found the database on the Internet. For a website we did we > needed to list all locations of a franchise within 100 miles of a given zip > code. It took a little while to find how to calculate the distance using > longitude and latitude. I ended up finding a JS version which I rewrote into > SQL. > >Another person here told me, after I did this, that he once wrote a about >this. If you search the web you should be able to find it. In his version I >think he creates a database procedure for the formula. Here is my code... >=================================== > // Creates a query that will select all franchises that are within 100 > miles. Orders by distance. > // Conversion of km to miles -> 0.621 > // Radius of the earth 6371 > // Algorithm based off of http://en.wikipedia.org/wiki/Haversine_formula > // this page then referenced a JS implementation > // http://www.movable-type.co.uk/scripts/LatLong.html > // (NOTE: Maybe change to a stored procedure.) > query2 = "SELECT x.*" + > " FROM (" + > " SELECT ROUND((6371 * ( 2 * ATAN2(SQRT(z.firstPart), > SQRT(1-z.firstPart)) )) * 0.621) AS distanceMiles, z.* FROM" + > " (" + > " SELECT" + > " SIN((lat2 - lat1)/2) * SIN((lat2 - lat1)/2) +" + > " (COS(lat1) * COS(lat2) * SIN((long2 - long1)/2) * > SIN((long2 - long1)/2))" + > " as firstPart," + > " y.*" + > " FROM (" + > " SELECT" + > " f.*," + > " a.latitude * PI()/180 AS lat1, a.longitude * PI()/180 AS long1," + > " b.latitude * PI()/180 AS lat2, b.longitude * PI()/180 AS long2" + > " FROM zipcodes as a, franchise AS f LEFT JOIN zipcodes AS b ON > f.franchise_zip = b.zip " + > " WHERE a.zip = ***ZIP CODE GOES HERE***"+ > " ) AS y" + > " ) AS z" + > " ) AS x" + > " WHERE x.distanceMiles <= 100" + > " ORDER BY x.distanceMiles" > ; >=================================== > >Kind of hard to follow but with this and the URLs in the comments you should >be able to figure it. > >David > >Dave wrote: I searched web, but did not find useful info. > > Goal: Given a zip code or an address, search the database to find all > businesses that are within 10 miles. > > To be concrete, two things: > 1. How to calculate distance between two addresses(or two zip codes)? > 2. How to list all the zip codes that are within 10 miles from a given zip > code? > > Is there such a service out there? But going through a service would be too > slow. > > Thank you very much for any hints. > > >--------------------------------- > Relax. Yahoo! Mail virus scanning helps detect nasty viruses! > > >--------------------------------- >Brings words and photos together (easily) with > PhotoMail - it's free and works with Yahoo! Mail.
