GeoNames Forum

Hi guys,

I'm new to this forum, so don't blame me if my question seems to be stupid. (And yes, I've read the readme files and searched through the forum)

I've downloaded the allcountries.txt file and imported some of the data into my MySQL DB because I wanted to see how I could use its table structure for a location-aware application. But sadly I can't make head nor tail of the table structure.

Let me give you an example dataset from the allcountries Table:
Code:

 
 3039556, 'Pesada de Pal', 'Pesada de Pal', '', 42.5666667, 1.4833333, 'L', 'LCTY', 'AD', '', '00', '', '', '', 0, , 1508, 'Europe/Andorra', '1993-12-23'
 3039557, 'Pont de Pesada', 'Pont de Pesada', '', 42.5666667, 1.4833333, 'S', 'BDG', 'AD', '', '00', '', '', '', 0, , 1508, 'Europe/Andorra', '1993-12-23'
 3039558, 'Canal de Pesada', 'Canal de Pesada', '', 42.5666667, 1.4833333, 'H', 'STM', 'AD', '', '00', '', '', '', 0, , 1508, 'Europe/Andorra', '1993-12-23'
 3039559, 'Basera de Pesada', 'Basera de Pesada', '', 42.5666667, 1.4833333, 'T', 'CLF', 'AD', '', '00', '', '', '', 0, , 1508, 'Europe/Andorra', '1993-12-23'
 3039560, 'Pesada', 'Pesada', '', 42.5666667, 1.4833333, 'T', 'SLP', 'AD', '', '00', '', '', '', 0, , 1508, 'Europe/Andorra', '1993-12-23'

These entries describe all the same location in different languages but there is no column which defines the language. E.g. if I wanna deliver information about the location above in a specific language how should I query the table to get the proper title?
Compare it to the findNearbyWikipedia WebService where you have a language code tag.
I hope, I made my issue understandable and would be happy if someone could give me a solution.
I think, the problem lies on my side because obviously lots of people are using these dumps without a complaint.

Hi Zwirdsch

The allcountries table has the 'international' names of a location. If you want to know the name in a particular language you have to join it with the alternate names table (using the geonameId). There you will find the local names of a place in many languages. It is a one-to-many relationship with the allcountries table as master and the alternatenames table as detail.

Best

Marc

Hi Marc,

thanks for your help, I think I understood now

I've inserted partly the alternateName table into my database (201217 rows), I've checked then how many rows have an isoLanguage entry -> there are 96277 rows!
Meaning that less then half of entries have isoLanguage information.
Is this a good value? I'm asking because I can't put this value into any relation.

The thing is that I'm working on my thesis and one part is going to be to implement a service similar to your 'findNearbyWikipedia' webservice. It should be possible to return an information about a location in different languages like e.g. Kölner Dom in Cologne.
Therefore I'm trying to figure out if your tables could be useful for me.

Greetings,
zwirdsch