Author |
Message |
09/07/2008 18:57:06
|
tjewell
Joined: 09/07/2008 18:54:24
Messages: 2
Offline
|
Hi, apologies if this is an obvious FAQ.
I've used the downloadable files to create lists of alternative names for common cities. However, I'm unsure where to start creating a list of alternatives for *country* names, eg Germany = Deutschland etc.
Which file should I be looking at?
Many thanks in advance, and thanks all of you for producing such a wonderful resource.
Tony.
|
|
|
10/07/2008 08:01:44
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4412
Offline
|
Tony
For countries it is exactly the same as for cities. join the geonames table with the alternatenames table to get the names in other languages.
Best
Marc
|
|
|
|
28/07/2008 18:19:10
|
gio840av
Joined: 28/07/2008 18:07:15
Messages: 5
Offline
|
Hi Marc,
it is still not clear to me how we go from the two letter iso-country code say IT to the different possible localization of "Italy" in the different languages.
when you say the "geonames table" you mean the cittyXXXX, right?
This geonames is truly amazing. Thanks for such a fantastic collection of data.
Giovanni
|
|
|
28/07/2008 20:24:24
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4412
Offline
|
Giovanni
With "GeoNames Table" I refer to the allCountries.zip file or one of the country files. If you are only after the name of countries you can use the countryInfo.txt file which contains geoNameIds for the countries and get the names from the alternate names table.
The cityxxx files are only cities and no countries.
Best
Marc
|
|
|
|
28/07/2008 20:29:50
|
gio840av
Joined: 28/07/2008 18:07:15
Messages: 5
Offline
|
Great! Thank you for your quick reply. I am more and more impressed by geonames!
|
|
|
08/04/2010 12:24:14
|
pau.moreno
Joined: 08/04/2010 10:11:23
Messages: 1
Offline
|
Hi
marc wrote:
If you are only after the name of countries you can use the countryInfo.txt file which contains geoNameIds for the countries and get the names from the alternate names table.
I am interested in getting the names of all the administrative divisions and the country in a specific language, so for the query "Vic, ES" I would like to return:
Vic, Provincia de Barcelona, Catalunya, EspaƱa
(City name, Admin2 name in Spanish, Admin1 name in Spanish, country name in Spanish)
The way I do it is the following:
First I perform a search for "Vic" using Sphinx, where I have indexed the rows of the geoname table which have fclass = P. I order the results by proximity to the user's location, so I get first the geonameid corresponding to the "Spanish" Vic (3106050).
I get all the data corresponding to Vic by querying Code:
SELECT * FROM geoname WHERE geonameid = 3106050
to my db.
I get the admin2 name in Spanish, by first getting the geonameid of the region:
Code:
SELECT * FROM admin2codes WHERE code = $country.$adm1code.$adm2code
and then looking for the name in Spanish:
Code:
SELECT * FROM alternateNames WHERE geonameid = $region2.geonameid AND lang = 'es' ORDER BY isPreferredName DESC
The same happens for the country name.
However, i cannot do it for the admin1 codes, because the table has only the codes and the names in English, but hasn't got their geonameids.
Aren't the geonameids of admin1regions present in the table for any particular reason? Has anyone else encountered this problem? If this is the case, has somebody found an easy workaround?
I am considering to build a script that gets all the geonameids for the regions from the geoname table and adds then in a new column of the admin1codes table.
Thanks for your help,
Pau
PS: Great work, this DB is amazing!
|
|
|
08/04/2010 20:04:48
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4412
Offline
|
you find the geonameid for the admin regions over their admincode.
Marc
|
|
|
|
|