calculate distance b/w zip codes

[mgm_03]

Has anyone tried to implement a zip code search where a user selects a radius of say 10miles and companies with zip codes in that geographic circle are inlcuded in the query result?

 

I have the script that converts latitude/longitude to miles and I have the text file of 55,000 (+or-) zip codes in the U.S.

 

when inserting the text file into phpMyAdmin, the query says 55000 rows inserted but when I try to browse the zip code table, there are only 51 rows available. Interestingly, I get one row per state and it is always the last zip code in that state....it seems PMA disregards all rows except the last for each state.

 

anyway, if you\'ve tried this tactic and were successful...lemme know!!!

Thx.

[McGruff]

Can you post the script you are using to insert in to the database?

[mgm_03]

actually, since I am using PMA, there is no script...just using the \"insert data from text file\" option and selecting the options as appropriate on PMA.

 

...anyway, I got it to work....I imported the text file into Excel and deleted the last 2 columns of the text file. This data has nothing to do with the zip code calculations and deleting it out reduces the file size. Also, I deleted the first column which simply had a 2digit code for each state 01 AL , 02 AR, 03 AZ, etc. I inserted a blank space in that first column and did a \"fill-down\" to all rows in that colum. This is needed because that first field becomes the auto-incrementing primary key. Saved it as a Macintosh CSV.

 

so now, i can start scripting distances and watch my CPU usage skyrocket from all the trigometric calculations *ugh*

[Barand]

Save yourself a lot of math and calculate latitude 10 miles north and 10miles south. Then Longitude 10 miles east and 10 miles west.

 

Select places whose zip\'s lat and long fall between those values. OK its a rectangle and not a circle, but does that really matter?

 

I do a similar thing on 4 tables joined together, one table has e 200,000 recs and another table having over 3 million records. It takes about 2 seconds.

[mgm_03]

so if there are 69.1 miles between lat lines, then 10 miles = .1447 degrees.

 

if my zipcode is lat1, lon1 then, I find zip codes whose lat/lon falls in the range:

 

lat1-.1447 to lat1+.1447, lon1-.1447 to lon+.1447

 

yes?

 

i realize this is an approximation that doesn\'t consider the curvature of the earth....but i couldn\'t get that formula to work and now, i don\'t care.

 

decent primer worth reading

http://distance-calculation.com/Distance-C...Calculation.asp

[Barand]

Sounds good.

 

So if you have table company with

co_name //etc.

zipcode

 

and zipcode table with

zipcode

lat

long

 

You code becomes

 

[php:1:c888c2269b]<?php

$minlat = lat1 - 0.1447;

$maxlat = lat1 + 0.1447;

$minlong = long1 - x; // whatever x is for 10 miles of long.

$maxlong = long1 + x;

 

$sql = \"SELECT c.co_name FROM company c INNER JOIN zipcode z

ON c.zipcode = z.zipcode

WHERE z.lat BETWEEN $minlat AND $maxlat

AND z.long BETWEEN $minlong AND $maxlong \";

?>[/php:1:c888c2269b]

 

hth