Qv7kwW3a

1CREATE PROCEDURE geo_zip_dist (IN center_zip int, IN dist int)
2
3BEGIN
4declare center_lon float; declare center_lat float;
5declare lon1 float; declare lon2 float;
6declare lat1 float; declare lat2 float;
7
8-- Lookup the original lon and lat for the zip
9select lon, lat into center_lon, center_lat from zips
10 where zip=center_zip;
11
12-- calc lat and lon for the rectangle
13set lon1 = center_lon-dist/ABS(COS(radians(center_lat))*69);
14set lon2 = center_lon+dist/ABS(COS(radians(center_lat))*69);
15set lat1 = center_lat-(dist/69);
16set lat2 = center_lat+(dist/69);
17
18-- create temporary table
19-- DROP TABLE local_zips;
20CREATE TEMPORARY TABLE IF NOT EXISTS local_zips  (
21     zip int not null,
22     distance double,
23     primary key (zip)
24);
25TRUNCATE local_zips;
26
27-- run the query and insert into temp table
28INSERT INTO local_zips
29 SELECT dest.zip,
30 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) * pi()/180 / 2), 2) +
31 COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *
32 POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) )) AS distance
33  FROM zips orig, zips dest
34   WHERE orig.zip=center_zip AND
35         dest.lon between lon1 and lon2 AND
36         dest.lat between lat1 and lat2 
37    having distance < dist
38     ORDER BY distance;