3

I would use the API Geonames, but my application won't have access to the internet. So it has to run stand-alone. I see there are many files from Geonames.org to download, but I don't see any software to aid in being able to do Reverse Geocoding. I want to provide the lat/lon and have it return the country code. I'm thinking of downloading the data and put it into a MySQL database.

What algorithm is used for Reverse Geocoding so I can make use of it with the geonames.org downloads. My project is being written in PHP. Thanks!

Edward
  • 9,430
  • 19
  • 48
  • 71
  • geonames downloads has mainly in `.txt` files, these can be imported to mysql database – WatsMyName Sep 26 '12 at 10:45
  • 1
    Yes, my question was about how to use these databases to do Reverse Geocoding. What is the algorithm or PHP software to do this? Thanks! – Edward Sep 26 '12 at 10:49

3 Answers3

3

Geonames provides data for most countries. This data can be imported into a MySQL database. This can be searched for using lat lng.

The problem is that there is probably too much data in the files for your requirements. You can eliminate all the fields except lat,lng&country code for all the countries required and then combine them in 1 record.

MySQL query

SELECT country code FROM XX WHERE lat BETWEEN 55 AND 55.5 AND lng BETWEEN -2
AND -1.5

This will pull in data over a "Box" of 111kM. You can change the range to suit.

From the table below you can see that the 111kM "box" is only valid at the equator(0°). for other latitudes the lng range would need to be increased.

    lat                 lng
0°  110.574 km  111.320 km
15° 110.649 km  107.551 km
30° 110.852 km  96.486 km
45° 111.132 km  78.847 km
60° 111.412 km  55.800 km
75° 111.618 km  28.902 km
90° 111.694 km  0.000 km
david strachan
  • 7,174
  • 2
  • 23
  • 33
  • Thanks for your posting. I am confused how this is going to work for just any lat/lon provided. Here are my tests: mysql> select country from geoname where latitude=55 and longitude=-2; +---------+ | country | +---------+ | GB | +---------+ 1 row in set (8.10 sec) mysql> select country from geoname where latitude=40.7172740 and longitude=-74.0089860; Empty set (8.27 sec) mysql> select country from geoname where latitude=40 and longitude=-74; Empty set (8.10 sec) – Edward Sep 26 '12 at 11:46
  • This shows that the database found no records in database meeting this criteria. 6 decimal places gives an accuracy of aproximately .111 meters so I would limit this see http://en.wikipedia.org/wiki/Decimal_degrees – david strachan Sep 26 '12 at 12:27
  • You will notice, I also trimmed that down to a lat of 40 and a lon of -74 and it still didn't find it. Seems like another sort of look-up table is needed that has the range of lat/lon for each country for this to work? But I don't know where that sort of data exists unless it is available in another geonames dataset I overlooked. – Edward Sep 26 '12 at 12:37
  • -74 should be 360-74 = 286. Also the parameters on the query should be exactly the same as on you database – david strachan Sep 26 '12 at 15:41
  • Thanks for the reply. So it sounds like there needs to be a look-up table of ranges established for each country? If so, seems like someone has already done that and it would be available someplace? – Edward Sep 28 '12 at 11:11
  • Did you try the update on answer for query? I am not sure i understand look up table. The allCountries.zip file contains all the country codes, post codes and lat/lng. – david strachan Sep 28 '12 at 11:24
  • Yes, I did try it. But how do you know what between range offset should be used for each lat/lon to find the country code? You said, "you can change the range to suit". But how do you know what the between range could be? This is why I said perhaps a look-up table of between ranges for the offsets would be needed. – Edward Sep 28 '12 at 11:58
  • See addition to Answer. Is this what you looking for. What desktop application are you using? – david strachan Sep 28 '12 at 12:32
2

geonames downloads has mainly in .txt files, these can be imported via SQLYog to the mysql database

Table Structure

CREATE DATABASE geonames;
USE geonames;

CREATE TABLE geoname (
geonameid int PRIMARY KEY,
name varchar(200),
asciiname varchar(200),
alternatenames varchar(4000),
latitude decimal(10,7),
longitude decimal(10,7),
fclass char(1),
fcode varchar(10),
country varchar(2),
cc2 varchar(60),
admin1 varchar(20),
admin2 varchar(80),
admin3 varchar(20),
admin4 varchar(20),
population int,
elevation int,
gtopo30 int,
timezone varchar(40),
moddate date
) CHARACTER SET utf8;

MYsql query to import data to this table

LOAD DATA INFILE '/path/to/your/file/geoname.txt' INTO TABLE `geoname`;

After all is set you can query this table against lat/long to get country name

Here is the link you can refer to

http://sgowtham.net/blog/2009/07/29/importing-geonames-org-data-into-mysql/

http://forum.geonames.org/gforum/posts/list/732.page

Hope this helps!

WatsMyName
  • 4,240
  • 5
  • 42
  • 73
  • Thanks, this was helpful in getting the database loaded. However, the queries (see my comment to 'david strachan' below. – Edward Sep 26 '12 at 11:44
  • I must be missing something. The query didn't return a country of US for a New York location I put in with lat/lon. How is this database going to find the right country unless there is some sort of way of knowing what the range of lat/lon is for each country? Thanks! – Edward Sep 26 '12 at 11:49
1

The algorithm you are looking for is more a data structure then a complicated formula. Mysql supports R-tree and spatial index queries. The concept is a hierarchical tree with the lat long pairs as a bounding box the root of the tree has the entire world in the bounding box. A Kd-tree can be also good. In rare case you want to look for a quadkey or a space filling curve. It's a fractal and reduces the dimension to a number. The formula is H(x,y) = H(x) + H(y). The fractal also preseve some proximity information which I don't know how it's solved with a R-tree.

Micromega
  • 12,486
  • 7
  • 35
  • 72