You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@myfaces.apache.org by Dave <ja...@yahoo.com> on 2006/03/11 04:13:55 UTC
OT: within 10 miles
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!
Re: OT: within 10 miles
Posted by Wayne Fay <wa...@gmail.com>.
I would take the following approach:
zip codes associated with counties
lat/long associated with zip codes
all addresses must have zip code in your db
county (1) -> zip (many)
zip (1) -> business_address (many)
hopefully zip codes don't cross county lines or this will be more
challenging ;-)
Then find the lat/long for a user-entered address, turn it into a zip
code, find the county, then use that data to limit the scope of your
search.
OR if you don't want to use counties, then somehow relate the zip
codes to distance ie 12345 is in the middle, 12346 is to the east,
12347 is west, 12348 is north, 12349 is south, so you can calculate
how many zip code distances a given zip is from another (3 zips to the
east) to limit the scope of the db query, THEN use the select ...
given below to find the matching businesses (inside 10mi etc).
Wayne
On 3/13/06, Dave <ja...@yahoo.com> wrote:
>
> 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 <tc...@plauditdesign.com> 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, th at 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"
> ;
> ===================================
> < BR>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.
>
>
Re: OT: within 10 miles
Posted by Dave <ja...@yahoo.com>.
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 <tc...@plauditdesign.com> 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.
Re: OT: within 10 miles
Posted by David Schlotfeldt <tc...@plauditdesign.com>.
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
> <http://us.rd.yahoo.com/mail_us/taglines/virusall/*http://communications.yahoo.com/features.php?page=221>
> helps detect nasty viruses!