GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Mysql query to retrive alternateNames ordered by name  XML
Forum Index -> General
Author Message
libonati



Joined: 26/07/2010 22:26:07
Messages: 3
Offline


Hi,
I'm doing an INNER JOIN between geonames table and alternatenames table using the language as variable.
I'm having troubles to get the right results since sometimes PPLs' alternateName have only the english version. As an example, if I want results for PPLs in italian, sometimes I have the italian version of the name, someothers I have only the english name. Moreover I need to order by isPreferredName DESC to avoid gather results like Big Apple instead of New York.

So I have to retrive results from an INNER JOIN between geonames table and alternatenames table, taking the italian name when available or the english name when italian is not available, ordered by alternateName DESC and by isPreferredName DESC

As an example of query limited to New York (but I don't need to filter by geonameid, but retrive all places):

SELECT g.geonameid, alternateName FROM geoname_new g INNER JOIN alternatename_new a ON g.geonameid=a.geonameid WHERE (isoLanguage='it' OR isoLanguage='en') and country='us' and g.geonameid=5128581 GROUP BY geonameid ORDER BY alternateName DESC, isPreferredName DESC LIMIT 0,10

The result is Big Apple...
Any suggestion on how to sort this out?
Best,
L.
rouz



Joined: 29/11/2021 10:03:24
Messages: 2
Offline

Hi Libonati,

I think the best way is to use a subquery with ORDER BY FIELD :

SELECT gp.geonames_id, gp.name,
(SELECT ga.name
FROM geonames_alternates ga
WHERE ga.geonames_id = gp.geonames_id
ORDER BY FIELD(ga.lang_code, 'it', 'en', null) DESC LIMIT 1) AS alt_name
FROM geonames_places gp
LIMIT 10;

Best regards
 
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team