i have downloaded whole database from geonames.org and imported all countries into mysql.
i wonder how i can get the continent, country, state, county when i got a city's id?
cause i want to make a cascading list menu with these information.
thanks!
+2
A:
Looking things over, all the data that you are asking for appears to exist in their dataset. The exact query you are looking for should be pretty easy to come up with assuming that you imported everything in a relatively straightforward fashion.
Basically, you'll need to join the geonames table against the admin1codes and admin2codes tables as they contain State and County information respectively. You'll also need to create a table to store continent information and join against that table also. Then, you merely have your query match a specific city and connect all your joins.
If you haven't already, I'd recommend looking through their FAQ forum:
http://forum.geonames.org/gforum/forums/show/6.page.
There are posts there detailing importing the data into just about every major database, and also some information about chaining drop down lists.
If you have a more specific question, please post here so we can help.
Morinar
2010-03-20 05:24:41
i have noticed that some of the cities are under admin4. so country -> admin1 -> admin2 -> admin3 -> admin4 -> city. so weird..there is no such table. and even if its under admin2 i can't find the 'id' of it in that table.
never_had_a_name
2010-03-20 06:27:09
it seems to be no problem to use their WEB API for this matter. just enter the id of city and it give you back all upper levels in xml format. but i cant extract this data from my local database. i think a lot of people got the same issue.
never_had_a_name
2010-03-20 06:28:15