GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Retrieving correct translations from `alternatename`  XML
Forum Index -> FAQ - frequently asked questions
Author Message

Joined: 18/10/2016 11:04:10
Messages: 2

I'm having trouble creating a query that matches a geonameid to a single record in the `alternatename` table. Basically when I retrieve a "DE" translation for some entity, I want my SQL join to only return 1 match.

However, the fields `alternatename.isShortName` and `alternatename.isPreferredName` does not seem to be properly filled out. I get multiple matches per geonameid on my query, but i would like to have the following situation (returning the first record that matches the criteria):

if isPreferredName=1, then use it.
else if isShortName=1, then use that.
else, use whatever match we can find for that language code.

Any clue on how to build this query while still allowing it to be performant on a large dataset?

Joined: 18/10/2016 11:04:10
Messages: 2

it took 2 days for this post to get through the moderation. I've since found an answer. I'm not going to post it until this forum opens up the doors

Joined: 11/02/2017 20:41:32
Messages: 10


I am currently having the same issue.
Without nesting selects, i dont think it is possible to do in SQL alone, as the if/else match is on the row not multiple rows.
You could use "COALESCE( isPreferredName, isShortName) AS name" to get either the isPreferredName or isShortName, but again this only works on single rows.
Unfortunately nesting selects will be slow for a large data set.

I am currently getting the duplicate results/rows, then filtering them in code. At presents i think this will be the fastest way...

I am interested to know how you did this?

I think maybe the best way would be to run a routine on alternatename to build a custom table for your needs (you can have nested selects in this routine
as it will only need to run on initial data import or monthly if you subscribe to the data feed/premium data). this routine will take hours to run, but if you set the server to do this at 3am, etc. this should be fine...

PS if you want me to write the nested select, please let me know.
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team