GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Real Name of the cities  XML
Forum Index -> General
Author Message

Joined: 26/07/2017 01:22:58
Messages: 3

One year passed since I asked a column with the name of a city (also country and admin div) in the language spoken in that city, or in other words, the REAL name; a column that is even more important than the column name, that contains often the name in english.

After painfull efforts you can obtain that informaion: from city you go to the country table, from there u take the language code, then you query the alternatenames table for that city and that language code(excluding the historical and slang names) and pray that the first result is the best one and correct.
But even after all this work (to obtain the most important information) you get poor results, because it could be wrong, it could not be the first result, there can be no names with that language code (even if the name is present but the lang is missing)

But nothin happened since my first thread, not a reply...
The truth is that it's impossible now, because it's too late !!!
You made a huge library but put the books in a random order !
And now the work is too big to be made !

Well you could at least start the job and make that collumn (or a specific boolean column in alternatenames if you are stupid), slowly the community will do the job...

Joined: 08/12/2005 07:39:47
Messages: 3942

An additional column would be redundant. If you miss information in the alternate name table then please feel free to add it - you are part of the community -

If it really is too cumbersum to join the tables to get the info from the alternatenames table then we could add a convenience file with the name taken from the alternatenames table (like the admin1Codes file).

Best Regards



Joined: 26/07/2017 01:22:58
Messages: 3

LESS redundant than the 'name' column ! Why don't you put also 'name' randomly in the alternatenames column or table ?
A new file for that ? better than nothing...

Get the real name from the alternatenames column is impossible.
Get the real name from the alternatenames table is painflull and the result is incomplete and not reliable.
I imported into sqlite (sqlitebrowser) and I get the real name column with this fancy query:
SELECT cit.field2, cot.field5, ret.field3, znt.field4 FROM cit LEFT JOIN cot ON cit.field9 = cot.field1 LEFT JOIN ret ON (cit.field9||'.'||cit.field11) = ret.field1 LEFT JOIN znt ON cit.field1 = znt.field2 AND substr(cot.field16, 1,2) = znt.field3 WHERE cit.field9 =  "IT" GROUP BY cit.field1 ORDER BY cit.field2;

Considering that cit is the cities15000 table, cot is the coutriyinfo table, ret is the admin1 table(we dont need the admin1) and znt is the alternatenames table.
At least 2 steps is missing here;
1-before, remove from alternatenames the not-cities and the names that are true on cols 'isHistoric' and 'isColloquial'.
2-later, remove the real name where it's the same as name.

The result can be wrong for many reasons:
1-There's not the name.
2-There is the name but not the language.
3-This and similar queries takes the first row matching the city and the language of the city. But the first row may not be the real name but a second, local name.
The column 'isPreferredName' is what could indicate the real name but is not reliable, cos its often not set or wrong, maybe because of the generic role.
This way i get 7382 names, few, and correct ? No idea.
-fixed the query-

Joined: 26/07/2017 01:22:58
Messages: 3

For your english-centric minds (i'm not a english hater), suppose that someone in spain make a cities db, and call New York(city and state) 'Nueva York', and put 'New York' almost randomly in a list, would it be an usable database ? No! (and it would also make sense cos spanish is spoken by more pepole that english)
The name of Rome is Roma. And the same for admin1; 'Aosta Valley' ??? Oh for god's sake !

And I almost forgot the non-latin alphabets, China, japan, Russia, Africa... they really need it !

What I would do is put the real name column in place of name, and make a second col called englishname.
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team