Jump to content


Photo

calculate distance b/w zip codes


  • Please log in to reply
5 replies to this topic

#1 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 15 September 2003 - 05:43 AM

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.
Guru?...in my dreams!
Sorry Everybody

----------------------

#2 McGruff

McGruff
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 15 September 2003 - 06:45 PM

Can you post the script you are using to insert in to the database?
McGruff [br] [br]\"I\'m working so I won\'t have to try so hard\" [br]- The Strokes

#3 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 16 September 2003 - 02:33 AM

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*
Guru?...in my dreams!
Sorry Everybody

----------------------

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 16 September 2003 - 07:21 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 16 September 2003 - 11:01 PM

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-calc...Calculation.asp
Guru?...in my dreams!
Sorry Everybody

----------------------

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 17 September 2003 - 06:06 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users