469,599 Members | 2,862 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,599 developers. It's quick & easy.

smarter zipcode search algorithm

Hi,
I have a database with two tables
a) A table of 2 million records with city, zip and associated
information (say XYZ) and
b) zipcode latitude, longitude table having >40,000 records/zip codes

PROBLEM:
I need to find the the XYZs within the the range of a certain zipcode.
This zipcode and radial range in miles is entered by the user (web
interface).

The brute force way is to calculate the distance between the user
zipcode and all the zipcodes in the database. Once the zipcode_range
subroutine gives back the zipcodes within a certain radius, I need to
find all the XYZs from the table #1.

Another approach is to find the zipcodes with a square region (min/max
of the user zipcode latitude/longitude position).

Both the approaches are consuming too much time. Especially if the
radial distance starts increasing.

My questions:
1. Is there any other smart way to do the above task.
2. I am working on a 2.4ghz/512MB RAM machine. Any suggestions how to
increase the performance. Right now each select command to the
2Million record table takes about a minute.

Thanks.

Jul 5 '06 #1
2 4972
pr******@gmail.com wrote:
Hi,
I have a database with two tables
a) A table of 2 million records with city, zip and associated
information (say XYZ) and
b) zipcode latitude, longitude table having >40,000 records/zip codes

PROBLEM:
I need to find the the XYZs within the the range of a certain zipcode.
This zipcode and radial range in miles is entered by the user (web
interface).

The brute force way is to calculate the distance between the user
zipcode and all the zipcodes in the database. Once the zipcode_range
subroutine gives back the zipcodes within a certain radius, I need to
find all the XYZs from the table #1.

Another approach is to find the zipcodes with a square region (min/max
of the user zipcode latitude/longitude position).

Both the approaches are consuming too much time. Especially if the
radial distance starts increasing.

My questions:
1. Is there any other smart way to do the above task.
2. I am working on a 2.4ghz/512MB RAM machine. Any suggestions how to
increase the performance. Right now each select command to the
2Million record table takes about a minute.

Thanks.
have you read this article over at
http://www.phparchitect.com/sample.php?mid=9

the whole thing seems very informative - particularly listing 3

Jul 6 '06 #2
za*******@gmail.com wrote:
pr******@gmail.com wrote:
>>Hi,
I have a database with two tables
a) A table of 2 million records with city, zip and associated
information (say XYZ) and
b) zipcode latitude, longitude table having >40,000 records/zip codes

PROBLEM:
I need to find the the XYZs within the the range of a certain zipcode.
This zipcode and radial range in miles is entered by the user (web
interface).

The brute force way is to calculate the distance between the user
zipcode and all the zipcodes in the database. Once the zipcode_range
subroutine gives back the zipcodes within a certain radius, I need to
find all the XYZs from the table #1.

Another approach is to find the zipcodes with a square region (min/max
of the user zipcode latitude/longitude position).

Both the approaches are consuming too much time. Especially if the
radial distance starts increasing.

My questions:
1. Is there any other smart way to do the above task.
2. I am working on a 2.4ghz/512MB RAM machine. Any suggestions how to
increase the performance. Right now each select command to the
2Million record table takes about a minute.

Thanks.


have you read this article over at
http://www.phparchitect.com/sample.php?mid=9

the whole thing seems very informative - particularly listing 3
Seems about right to me.

I've tried the cos, sin acos and that seemed a little slow so I did this:

(Perl)
my
$mile_lat=convertToMiles($L{latitude},$L{longitude },$L{latitude}-1,$L{longitude});
my
$mile_lon=convertToMiles($L{latitude},$L{longitude },$L{latitude},$L{longitude}-1);

$sql_zip=qq{ , ceiling(sqrt(pow(((latitude - $L{latitude}) *
$mile_lat),2) + pow(((longitude -$L{longitude}) * $mile_lon),2))) AS
distance };

sub convertToMiles{
($lat1, $lon1, $lat2, $lon2)=@_;
$dist = acos(sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +
cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($lon1 - $lon2)));

$dist = rad2deg($dist);
return $miles = $dist * 69;
} # end sub

The $L{latitude} stuff is from the zipcode database, I found that
indexing the zipcode database was essential!

Close enough and more than fast enough my purposes. All that is doing is
finding the approximate distance per degree ($mile_lat and $mile_lon)
and then just finding the length of the hypoteneus. That will start to
fail for large distances (multi state) because the earth is curved.

Jeff

>
Jul 10 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by William Morris | last post: by
10 posts views Thread by pembed2003 | last post: by
27 posts views Thread by Mark A. Gibbs | last post: by
28 posts views Thread by joshc | last post: by
4 posts views Thread by Dameon | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.