NYCPHP Meetup

[Steve Manes]

Kshitij Bedi wrote:

> I have a database with zipcodes and corresponding latitudes and longitudes.
> Can anyone suggest a mysql statement that can pull me all zipcodes within 20
> or 50 or 100 miles of a given zipcode

You can do it as an imprecise rectangle, where latitude/longitude is 
plus/minus a certain number of minutes.  Then you just do a SELECT for 
all zipcodes where the longitude/latitude falls within that rectangle. 
That's what a lot of job boards do and it usually works well enough for 
estimation, especially insofar as the longitude/latitude in those 
zipcode databases are only rough centers of mass.  Otherwise, if you 
need more precision you'll need to use spherical geometry, which is very 
costly.

Developers typically use either the ragged rectangle or precalculate 
distances at fixed deltas, caching the results in a separate table.  You 
get into some hairy calulus here because at higher latitudes the 
distance between two longitudinal degrees decreases.  At the equator, 
one degree of latitude or longitude is ~70 square miles, with 
longitudinal distance decreasing to zero at the poles.

distance = 3959 * (
	sin(latitude1/57.3) * sin(latitude2/57.3) +
	cos(latitude1/57.3) * cos(latitude2/57.3) *
         cos(longitude2/57.3 - longitude1/57.3))

Or something like that.