[SOLVED] in need for a mysql postcode dump

[redarrow]

Hi there all hope you can help.

 

I am in need for a mysql uk postcode dump any idears please.

[redarrow]

what wrong with this please.

 

CREATE TABLE `postcodes` (
  `Postcode_ID` int(11) NOT NULL auto_increment,
  `Pcode` tinytext NOT NULL,
  `Grid_N` varchar(15) NOT NULL default '',
  `Grid_E` varchar(15) NOT NULL default '',
  `Latitude` varchar(15) NOT NULL default '',
  `Longitude` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`Postcode_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2823 ;

[redarrow]

This is the table in the database ok,

  Postcode_ID int(11)  No  auto_increment               

  Pcode tinytext  No                 

  Grid_N varchar(15)  No                 

  Grid_E varchar(15)  No                 

  Latitude varchar(15)  No                 

  Longitude

 

There are 2,355 rows with the following information.

 

  Postcode_ID  Pcode  Grid_N  Grid_E  Latitude  Longitude 

      1 AB10 392900 804900 57.135 -2.117

      2 AB11 394500 805300 57.138 -2.092

      3 AB12 393300 801100 57.101 -2.111

      4 AB13 385600 801900 57.108 -2.237

      5 AB14 383600 801100 57.101 -2.27

      6 AB15 390000 805300 57.138 -2.164

      7 AB16 390600 807800 57.161 -2.156

      8 AB21 387900 813200 57.21 -2.2

      9 AB22 392800 810700 57.187 -2.119

      10 AB23 394700 813500 57.212 -2.088

 

now i need to get the distince between two postcodes but stuck

 

form

<form name="zip_lookup" id="zip_lookup" method="get">
Postcode A : <input type="text" name="post_a" id="post_a" /> <br/>
Postcode B : <input type="text" name="post_b" id="post_b" /> <br/>
<input type="submit" value="Lookup Distance" />
</form>

 

need help getting the postcodes to compare distence and echo result.

 

 

[pocobueno1388]

Well, first of all you probably need to figure out the formula used to calculate the distance between zip codes...then from there you can do the math. I wish I knew the formula...but I don't. I am sure you can find it on google fairly easy.

[redarrow]

I can not find it the formula to calc distance anyone please.

[redarrow]

 

was simple in the end cheers.

SELECT
a.Town,
a.Postcode,
b.lat_dec AS lat1, b.long_dec AS long1,
c.lat_dec AS lat2, c.long_dec AS long2,
round(degrees(acos(sin(radians(b.lat_dec))
* sin(radians(c.lat_dec))
+ cos(radians(b.lat_dec))
* cos(radians(c.lat_dec))
* cos(radians(b.long_dec - c.long_dec))))*69, 2) AS distance
FROM
postcode_names a, postcode_data b, postcode_data c
WHERE
c.Postcode = ‘NR32’
AND
a.Postcode = b.Postcode
AND
round(degrees(acos(sin(radians(b.lat_dec))
* sin(radians(c.lat_dec))
+ cos(radians(b.lat_dec))
* cos(radians(c.lat_dec))
* cos(radians(b.long_dec - c.long_dec))))*69, 2) GROUP BY
a.Postcode
ORDER BY
distance

[pocobueno1388]

Thats one heck of a query 0_0