Latest posts for the topic "Alternative names for Countries ..."

Alternative names for Countries ...

tjewell — GMT

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.

Re:Alternative names for Countries ...

marc — GMT

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

Re:Alternative names for Countries ...

gio840av — GMT

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

Re:Alternative names for Countries ...

marc — GMT

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

Re:Alternative names for Countries ...

gio840av — GMT

Great! Thank you for your quick reply. I am more and more impressed by geonames!

Re:Alternative names for Countries ...

pau.moreno — GMT

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!

Re:Alternative names for Countries ...

marc — GMT

you find the geonameid for the admin regions over their admincode. Marc