omkretssök, omkretssök med postnummer --> GeoDatas.NET

data-integration

ögonblickligt erbjuder vi kode-exempel för ASP, PHP och ColdFusion.

Var snäll och beakta att alla våra exempel beräknar distanser i kilometer. Om du önskar beräkna distanser i mil får du vandla om distans-parametern: 1km = 0.621371 mil och jordradius från 6378.388 km i 3963 mil

Beakta likaså decimalpunkten. Exemplarna använda '.' som decimaldivis, men det kan bero på ditt programeringsområde att du får använda ', ' som decimaldivis

till ASP kode exempel...

till PHP kode exempel...

till ColdFusion exempel...

exempel för en ASP kode

'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

exempel för en PHP kode

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

exempel för en ColdFusion kode

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