Index  |  Resources  |  Buy  |  Information  |  FAQ  |  Contact  
 
  Resources and useful information
 
Search nearby locations to a given point using database geo-coordinates
 

Having the Mapanet postal codes file you can easily find locations near to a given point in a radius of X kilometers or miles using a formula.
 

Content
Theory
Formula used to search localities and its application in a SQL command
Sample with live data using our online database
Code samples in programming languages: ASP, PHP


Theory

The World Geodetic System is a standard for use in cartography , geodesy , and navigation . It comprises a standard coordinate frame for the Earth , a standard spheroidal reference surface (the datum or reference ellipsoid) for raw altitude data, and a gravitational equipotential surface (the geoid) that defines the nominal sea level.

The latest revision is WGS 84 (dating from 1984 and last revised in 2004), which will be valid up to about 2010 . Earlier schemes included WGS 72, WGS 66, and WGS 60. WGS 84 is the reference coordinate system used by the Global Positioning System .

WGS84 Parameters

Semi-major Axis: 6.378.137 m
Semi-minor Axis: 6.356.752,3142 m
Inverse flattening: 1/298,257223563
Product of Gravitational Constant (G) and the Earth Mass (M): GM = 3,986004418x1014 m3/s2
Angular Speed of Earth ω: 7,292115x10-5 rad/s


Formula

For our sample we will use the basic values of:

Radius of Earth:      6378 km or 3963 for miles or 3441 nautical miles.
Distance:               1 kilometer = 0.621371 miles

For more precision:
Radius:  6378.137 km. 3963.191 miles or 3441.596 nautical miles

Important: The coordinates in the database are stored with decimal pint. When programming, data retrieved may be shown with decimal coma or decimal point depending of character encoding used in you pages. In our samples decimal point is used and in code samples like ASP, a conversion replace any decimal comma in coordinates value is applied after data is read from database.

How to apply in SQL command
We show below two formulas and a sample of SQL command, both build the formula before it is applied to SQL command, this way an easier way to understand how it works.

Simplified formula:

Calc = "SQRT(POWER(( Lat - Latitude) * 110.7, 2) + POWER(( Lng - Longitude ) * 75.6, 2 ))

SELECT Locality, Calc AS Distance FROM Table WHERE Calc <= '5' ORDER BY Distance ASC

Formula actually used in the practical sample:
 
Calc = 6378 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS(Lat)) * Cos(RADIANS(Lng) - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS(Lat)) )

SELECT Locality, Calc AS Distancia FROM Table WHERE Distance <= '5' ORDER BY Distance

Where:
Calc is a variable to store the formula.
Lat and Lng are the coordinates of the central point.
Latitude y Longitude not colored mean the data fields of the database table.
Locality is the field that contains the city name in the table as the data we would like to get.
Table is the name of the table in the database.




Practical sample
    

Data Value
Country
Postal code  
Distance
   


Code sample in ASP
 

<%
'Tabla de base de datos: 'GeoData'

CountryA2 = Request.Form("CountryA2")
PostalCode = Request.Form("PostalCode")
Distance = Request.Form("Distance")
Radius = Request.Form("Radius")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("ConnectionString")
Set RS = Server.CreateObject("ADODB.Recordset")

    'Search the postal code provided and get lat, lng coordinates as the central pointl
     '-------------------------------------------------------------------------------------------------------------------
     SQL = "SELECT * FROM GeoData WHERE CountryA2 = '" & CountryA2 & "' AND PostalCode='" & PostalCode & "';"
     Set RS = Conn.Execute(SQL)

     Lat = Replace(RS("Latitude"),",",".")
     Lng = Replace(RS("Longitude"),",",".")
     '------------------------------------------------------------------------------------------------------------------

'Build formula and store it in 'calc' variable to use it in SQL command

calc = Radius & " * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS(" & Lat & ")) * Cos(RADIANS(" & Lng & ") "
calc = calc & " - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS(" & Lat & ")) ) "


SQL = "SELECT TOP 100 "
SQL = SQL & "CountryA2, "
SQL = SQL & "Postalcode, "
SQL = SQL & "Region1Name, "
SQL = SQL & "Region2Name, "
SQL = SQL & "Region3Name, "
SQL = SQL & "Locality, "
SQL = SQL & "SubLocality, "
SQL = SQL & "Latitude, "
SQL = SQL & "Longitude, "
SQL = SQL & calc & " AS Distance "
SQL = SQL & "FROM [GeoData] "
SQL = SQL & "WHERE (CountryA2 = '" & CountryA2 & "') "
SQL = SQL & "AND " & calc & " <= " & Distance & " "
SQL = SQL & "ORDER BY Distance ;"

Set RS = Conn.Execute(SQL)

DO While Not RS.EOF

     Response.Write RS("CountryA2") & "<br>"
     Response.Write RS("PostalCode") & "<br>"
     Response.Write RS("Region1Name") & "<br>"
     Response.Write RS("Region2Name") & "<br>"
     Response.Write RS("Region3Name") & "<br>"
     Response.Write RS("Locality") & "<br>"
     Response.Write RS("SubLocality") & "<br>"
     Response.Write FormatNumber(RS("Distance"),1) & "<br>"

RS.MoveNext
Loop
RS.Close
Set RS = nothing

Conn.Close
%>
 


Code sample in PHP
 


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

$sqlstring = "SELECT * FROM GeoData WHERE PostalCode = '".$PostalCode."'";
$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 PostalCode, Locality, (6367.41 * SQRT(2 * (1-Cos(RADIANS(Latitude)) * Cos(".$Lat.") * (Sin(RADIANS(Longitude))*Sin(".$Lng.") + Cos(RADIANS(Longitude)) * Cos(".$Lng.")) - Sin(RADIANS(Latitude)) * Sin(".$Lat.")))) AS Distance FROM GeoData WHERE (6367.41 * SQRT(2 * (1 - Cos(RADIANS(Latitude)) * Cos(".$Lat.") * (Sin(RADIANS(Longitude)) * Sin(".$Lng.") + cos(RADIANS(Longitude)) * Cos(".$Lng.")) - Sin(RADIANS(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>PostalCode</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;
}
?>