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
2Millioni record table takes about a minute.

Thanks.

premgrps (AT) gmail (DOT) com wrote:
Given a range in miles, you should be able to calculate the least and
greatest possible values of latitude and longitude, if one were to move
in a straight line south, north, west and east from the origin zipcode.

These calculations can be done in your application code, prior to
running the SQL query at all.

Then use those min & max values as a kind of "bounding box" that
contains the circle of the given range.

Use this to reduce the set of 40000 zip codes to just the subset that
have a reasonable chance of being within the radius.

Additionally calculate a sort of "inner bounding box" of latitude and
longitude that are completely inside the circle.

If the zip code is within this smaller box, it's guaranteed to be within
the circle. Again, calculate the lat & long of this box in your Perl
app, prior to using it in the SQL query.

The logic is then:

- If a given zipcode Z is inside the inner bounding box, then it's
certainly within the specified range.
- Else, if the zipcode is within the outer bounding box, then test it
with the more costly distance formula.
- Else, it's certain not within range.

SELECT *
FROM zipcodes z
WHERE
(z.longitude BETWEEN $inner_west_bound AND $inner_east_bound
AND z.latitude BETWEEN $inner_south_bound AND $inner_north_bound)
OR
(z.longitude BETWEEN $outer_west_bound AND $outer_east_bound
AND z.latitude BETWEEN $outer_south_bound AND $outer_north_bound
AND 3963.0 * ACOS(SIN(z.latitude/57.2958) *
SIN($origin_latitude_in_radians) + COS(z.latitude/57.2958) *
COS($origin_latitude_in_radians) * COS($origin_longitude_in_radians
-z.longitude/57.2958)) < $radius)

[NB: the above query is not tested.]

See http://www.meridianworlddata.com/Dis...alculation.asp for an
explanation of the Great Circle distance formula for calculating
distance between two points on a globe. Because the globe is not flat,
you need to use this formula instead of standard Pythagorean distance
calculation.

You should transform the same Great Circle distance formula to find the
least & greatest latitude and longitude values. I'll leave this
geometry exercise for you to do.

If the improvements I described above don't help enough, read the MySQL
chapter on optimization. You can probably improve things further by the
usual MySQL performance tuning methods, such as adding indexes and
tuning the MySQL cache settings.

http://dev.mysql.com/doc/refman/5.0/...imization.html

Regards,
Bill K.

You might wan to refer to the sample codes at GeoDataSource.com

http://www.geodatasource.com/developers.htm