Códigos postales distancia --> GeoDatas.NET

Integración de datos

Actualmente ofrecemos ejemplos de codificación para asp, php y coldfusion.

Por favor, tenga en cuenta que todos nuestros ejemplos calculan las distancias en kilómetros. Si necesita calcular las distancias en millas, necesita transformar el parámetro de distancia: 1 km=0,621371 millas y el radio de la tierra de 6378,388km a 3963 millas

También sea consciente de la coma decimal. Las muestras utilizan el '.' para marcar la décima pero dependiendo de su programa podría utilizar la ','.

Vaya al ejemplo de codificación ASP.

Vaya al ejemplo del codificación PHP.

Vaya al ejemplo de codificación ColdFusion.

Ejemplo de un código ASP

'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

Ejemplo de un código PHP.

// 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 geo.postalcode,geo.place,(6367.41*SQRT(2*(1-cos(RADIANS(geo.latitude))*cos(".$lat.")*(sin(RADIANS(geo.longitude))*sin(".$lng.")+cos(RADIANS(geo.longitude))*cos(".$lng."))-sin(RADIANS(geo.latitude))* sin(".$lat.")))) AS Distance FROM geozipcodes AS geo WHERE (6367.41*SQRT(2*(1-cos(RADIANS(geo.latitude))*cos(".$lat.")*(sin(RADIANS(geo.longitude))*sin(".$lng.")+cos(RADIANS(geo.longitude))*cos(".$lng."))-sin(RADIANS(geo.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;
}
?>

Ejemplo de un código ColdFusion.

<!--- 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#

Códigos postales distancias