www.postcode-distance.com

Data integration

Currently we offer code-examples for asp, php and coldfusion.

Please keep in mind that all of our examples calculate distances in kilometres. If you need to calculate distances in miles, you need to transform the distance-parameter: 1km = 0.621371 miles and the earth-radius from 6378.388km to 3963 miles.

Also be aware of the decimal-point. The samples use the '.' as a decimal-delimiter, but depending of your programming-environment you might have to use the ',' as the decimal-delimiter.

Go to the ASP code-example...

Go to the PHP code-example...

Go to the ColdFusion code-example...

Example of a ASP code:

'Name of database-table: 'geodb'

postalcode = Request.Form("postalcode")
distance = Request.Form("distance")

Set conn =  Server.CreateObject("ADODB.Connection")
conn.Open Application("connStr")
Set rs = Server.CreateObject("ADODB.Recordset")

sql = "SELECT * FROM geodb WHERE postalcode='" & postalcode & "';"
Set rs = conn.Execute(sql)

lng = Replace(rs.Fields("longitude"),",",".")
lat = Replace(rs.Fields("latitude"),",",".")

calc =  "SQRT(POWER((" & lat & "-latitude)*110.7,2)+POWER((" & lng & "-longitude)*75.6,2))"

sql = "SELECT postalcode, " & calc & " AS distance FROM geodb WHERE " & calc & " <= " & distance & " ORDER BY " & calc & " ASC"

Set rs = Conn.Execute(sql)

counter = 1
While Not rs.EOF
    If counter=1 Then 
	    condition = condition & " postalcode IN ('" & rs.Fields("postalcode") & "'"
    Else
        If Instr(condition,"'" & rs.Fields("postalcode") & "'")=0 Then
	        condition = condition & ",'" & rs.Fields("postalcode") & "'"
	    End If
    End If
    counter=2
    rs.MoveNext
Wend
condition = "WHERE " & condition & ")"
'condition contains the SQL-WHERE statement
Response.Write(condition)
rs.Close

Example of a PHP code:

// To create MySQL geotable:

CREATE TABLE IF NOT EXISTS `geodb` (
  `countrycode` varchar(5) NOT NULL,
  `postalcode` varchar(8) NOT NULL,
  `place` varchar(50) NOT NULL,
  `region1` varchar(50) NOT NULL,
  `region1code` varchar(50) NOT NULL,
  `region2` varchar(50) NOT NULL,
  `region2code` varchar(50) NOT NULL,
  `region3` varchar(50) NOT NULL,
  `latitude` decimal(23,20) NOT NULL,
  `longitude` decimal(23,20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 


// Form
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<input type="text" name="postalcode" value="<?php echo $_GET['postalcode']; ?>" maxlength="5"/><br />
<input type="text" name="distance" value="<?php echo $_GET['distance']; ?>" maxlength="5"/><br />
<input type="submit" name="Search" value="Search" />
</form>

// PHP-Code

<?php
if(isset($_GET['Search']))
{
    $zipcode = $_GET['postalcode'];
    $distance = $_GET['distance'];

    $conn = mysql_connect('127.0.0.1', 'root', '') or die('db connect error: ' . mysql_error());
    mysql_select_db('igonow', $conn) or die('could not select database');

    $sqlstring = "SELECT * FROM geodb WHERE postalcode = '".$zipcode."'";
    $result = mysql_query($sqlstring);

    $row = mysql_fetch_assoc($result);

    $lng = $row["longitude"] / 180 * M_PI;
    $lat = $row["latitude"] / 180 * M_PI;

    mysql_free_result($result);

    $sqlstring2 = "SELECT DISTINCT geodb.postalcode,geodb.place,(6367.41*SQRT(2*(1-cos(RADIANS(geodb.latitude))*cos(".$lat.")*(sin(RADIANS(geodb.longitude))*sin(".$lng.")+cos(RADIANS(geodb.longitude))*cos(".$lng."))-sin(RADIANS(geodb.latitude))* sin(".$lat.")))) AS Distance FROM geodb AS geodb WHERE (6367.41*SQRT(2*(1-cos(RADIANS(geodb.latitude))*cos(".$lat.")*(sin(RADIANS(geodb.longitude))*sin(".$lng.")+cos(RADIANS(geodb.longitude))*cos(".$lng."))-sin(RADIANS(geodb.latitude))*sin(".$lat."))) <= '".$distance."') ORDER BY Distance";

    $result = mysql_query($sqlstring2) or die('query failed: ' . mysql_error());

    $str = "<table width=\"300\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
    $str .= "<tr>";
    $str .= "<th>Postcode</th>";
    $str .= "<th>Town</th>";
    $str .= "<th>Distance</th>";
    $str .= "</tr>";

    while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        $str .= "<tr><td>".$row["postalcode"]."</td><td>".$row["place"]."</td><td>".round($row['Distance'])."km</td></tr>";
    }

    $str .= "</table>";

    mysql_free_result($result);
    mysql_close($conn);
    echo $str;
}
?>

Example of a ColdFusion code:

<!--- this source need to receice the parameters form_zip & form_r from preview form --->

<cfquery datasource="database_name" name="seek_own_zip">
	select lat, lng from zips  where trim(zip) = '#trim(form_zip)#'
</cfquery>

 <!--- Store own longitude & latitude --->
<cfoutput query="seek_own_zip">
    <cfset o_lng=#lng#>
    <cfset o_lat=#lat#>
</cfoutput>

<!--- seek for all zips with distance "form_r" from "form_zip" / 
change earth radius (e_rad) 6378.137 (km) to 3963.0 if you use miles --->
<cfoutput>
    <cfset rad=180/pi()>
    <cfset e_rad=6378.137>          
    <cfquery datasource="database_name" name="getradius">
      SELECT           zip, acos(sin(#o_lat#/#rad#)*sin(lat/#rad#)+cos(#o_lat#/#rad#) 
                          *cos(lat/#rad#)*cos((lng/#rad#)-(#o_lng#/#rad#)))*#e_rad# as distance FROM zips
      WHERE            acos(sin(#o_lat#/#rad#)*sin(lat/#rad#)+cos(#o_lat#/#rad#)
                          *cos(lat/#rad#)*cos((lng/#rad#)-(#o_lng#/#rad#)))*#e_rad# < #form_r#
      ORDER BY distance
    </cfquery>
</cfoutput>

<cfoutput query="getradius">
	Distance from #form_zip# to #zip# = #distance# km/miles<br>
</cfoutput>

<cfoutput>
<br><br>query of all zips in radius of #form_r# km/miles from zip #form_zip#

Please visit our new website: http://www.postcode-distance.com

Postcode distance search