Hi,
Assuming you are using PHP, you can use the following snippet to generate an SQL version of the Haversine formula, which will lookup the Geoname row based on give Latitude and Longitude values. The code also restricts the search to within a given distance (in Miles), returning the closest Geoname row within that distance.
Code:
$lat = 51.500152; //latitude value to lookup
$long = -0.126236; //longitude value to lookup
$dist = 10; //distance from lat/long to search (miles)
//first, work out a smaller area to confine the search to.
//Calculate a rectangle.
//1° of latitude ~= 69 miles
//1° of longitude ~= cos(latitude)*69
$rectLong1 = $long - $dist/abs(cos(deg2rad($lat))*69);
$rectLong2 = $long + $dist/abs(cos(deg2rad($lat))*69);
$rectLat1 = $lat-($dist/69);
$rectLat2 = $lat+($dist/69);
$sql = "SELECT *,
3956 * 2 * ASIN(SQRT( POWER(SIN(($lat - latitude) * pi()/180 / 2), 2) +COS($lat * pi()/180) * COS(latitude * pi()/180) * POWER(SIN(($long - longitude) * pi()/180 / 2), 2) ))
AS distance
FROM geoname
WHERE longitude BETWEEN $rectLong1 AND $rectLong2
AND latitude BETWEEN $rectLat1 AND $rectLat2
ORDER BY distance
LIMIT 1";
The above will generate the following SQL, for example:
Code:
SELECT * , 3956 *2 * ASIN( SQRT( POWER( SIN( ( 51.500152 - latitude ) * PI( ) /180 /2 ) , 2 ) + COS( 51.500152 * PI( ) /180 ) * COS( latitude * PI( ) /180 ) * POWER( SIN( (
- 0.126236 - longitude
) * PI( ) /180 /2 ) , 2 ) ) ) AS distance
FROM geoname
WHERE longitude BETWEEN - 0.359046621727 AND 0.106574621727
AND latitude BETWEEN 51.3552244638 AND 51.6450795362
ORDER BY distance
LIMIT 1
which will return the row for geonameid 6944334 - "Houses of Parliament" in the UK.
I hope that helps,
Cheers,
Hodge