GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Reverse Geocoding with it loaded into MySQL database?  XML
Forum Index -> FAQ - frequently asked questions
Author Message
peppertown



Joined: 24/09/2012 09:58:12
Messages: 1
Offline

How is reverse geocoding done with allCountries.txt loaded into a MySQL database? When you search for a latitude and longitude in order to get back a country code, where is the data or formula(?) to specify the range of the lat/lon data points so it returns back the correct country code for any lat/lon searched on?
hodge



Joined: 23/11/2012 06:24:00
Messages: 1
Offline

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
 
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team