Beware this content is over 6 years old and considered stale. It may no longer be accurate and/or reflect the understanding of the author but remains here for reference only. Please keep this in mind as you use this content.

I’ve been doing a little geocoding experiments with some of the re-released TfL data that you can get your hands on.

With PHP and MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
function get_locations($coords, $max = 5, $distance = 500, $km = false) {
global $mysqli;
$sql = "SELECT *,(((acos(sin((".$coords['lat']."*pi()/180)) * sin((latitude*pi()/180))+cos((".$coords['lat']."*pi()/180)) * cos((latitude*pi()/180)) * cos(((".$coords['lng']."- longitude)*pi()/180))))*180/pi())*60*1.1515".($km ? '*1.609344' : '').") AS distance FROM locations ORDER BY distance ASC LIMIT ".$max;
$result = $mysqli->query($sql);
$array = array();
while($object = $result->fetch_object()) $array[] = $object;
return $array;
}

With PHP and Microsoft SQL Server (MSSQL), the solution is almost the same:

1
2
3
4
5
6
7
8
9
10
function get_nearby_properties() {
$query = "SELECT TOP $this->MapProperties *,(((acos(sin(($this->MapLatitude*pi()/180)) * sin((WGS84Latitude*pi()/180))+cos(($this->MapLatitude*pi()/180)) * cos((WGS84Latitude*pi()/180)) * cos((($this->MapLongitude - WGS84Longitude)*pi()/180))))*180/pi())*60*1.1515".($this->MapDistanceKM ? '*1.609344' : '').") AS Distance FROM $this->DB_TABLE WHERE WGS84Latitude IS NOT NULL AND WGS84Longitude IS NOT NULL AND WGS84Latitude != 0 ORDER BY Distance";
$result = $this->db_query($query);
if($this->db_num_rows($result)) $properties_array = $this->get_property_array($result, $use_placeholder = false);
return (isset($properties_array) && is_array($properties_array) ? $properties_array : false);
}