P
premgrps
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.
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.