www.plz-umkreis.com

Daten- Integration

Momentan bieten wir Ihnen Code-Beispiele für ASP, PHP und ColdFusion an.

Bitte beachten Sie, daß alle unsere Beispiele Abstände in Kilometern errechnen. Wenn Sie Abstände in Meilen errechnen möchten, müssen Sie den Entfernungs-Parameter umwandeln: 1km = 0.621371 Meilen und den Erdradius von 6378.388 km in 3963 Meilen.

Beachten Sie bitte ebenfalls den Dezimalpunkt. Die Beispiele verwenden '.' als Dezimaltrennzeichen, allerdings kann es abhängig von Ihrer Programmierumgebung sein, daß Sie ', ' als Dezimaltrennzeichen verwenden müssen.

Zum ASP Code-Beispiel...

Zum PHP Code-Beispiel...

Zum ColdFusion Code-Beispiel...

Beispiel eines ASP Codes:

'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

Beispiel eines PHP Codes:

// 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;
}
?>

Beispiel eines ColdFusion Codes:

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

Bitte besuchen Sie unsere neue Website: http://www.plz-umkreis.com

PLZ Umkreissuche

Bitte besuchen Sie unsere neue Website:

http://www.plz-umkreis.com