zoek in de regio, postcode zoeken in de regio --> GeoDatas.NET

Integratie van data

Momenteel bieden wij u code- voorbeelden voor ASP,PHP en Cold Fusion aan

Aub. denkt u eraan,dat al onze voorbeelden de afstanden in kilometers berekenen. Wilt u de afstanden in mijlen berekenen, dan moet u de afstand-parameter omzetten: 1km=0.621371 mijl en de aardradius van 6378.388 km in 3963 mijl.

A.u.b denkt u ook aan de decimaalpunt. De voorbeelden gebruiken als decimaaldeelteken, maar het kan afhankelijk zijn van u programmeeromgeving, dat u ',' als decimaalteken moet gebruiken.

naar het voorbeeld van de ASP code

naar het voorbeeld van de PHP code

naar het voorbeeld van de Cold Fusion Code

voorbeeld van een 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

voorbeeld van een 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 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;
}
?>

voorbeeld van een ColdFuison 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#

postcode regio-zoeken