Latest posts for the topic "Query for alternatename"

Query for alternatename

lillolollo — GMT

Hi whit this query extract alla country from geoname table Code:

SELECT * FROM geoname where fcode = 'PCLI' and admin1=00

now I wont extract alternate name from alternatename table for es language what query should I use?

Re:Query for alternatename

marc — GMT

The clause 'admin1=00' is not required. For the alternatenames you should join the two tables (geoname and alternatename table) on the geonameid. If you only are interested in Spanish you can filter for isolanguage='es'. Best Marc

Re:Query for alternatename

lillolollo — GMT

Thanks marc but if I have this query for example Code:

SELECT *
FROM geoname, alternatename
WHERE geoname.fcode = 'PCLI'
AND geoname.geonameid = alternatename.geonameid
AND alternatename.isoLanguage = 'en'

I have Andorra Principality of Andorra not only Andorra I dont want Principality of Andorra but only Andorra

Re:Query for alternatename

marc — GMT

There are a couple of flags to help you pick the name you prefer. In your case it is the 'isShortName' flag. Best Marc

Re:Query for alternatename

lillolollo — GMT

Thanks again marc but if I use this query Code:

SELECT * FROM geoname where fcode = 'PCLI'

have 192 country (I have deteted country whit no population) If I use this Code:

SELECT *
FROM geoname, alternatename
WHERE geoname.fcode = 'PCLI'
AND geoname.geonameid = alternatename.geonameid
AND alternatename.isoLanguage = 'en' AND alternatename.isShortName=1

have 117 country if query is Code:

SELECT *
FROM geoname, alternatename
WHERE geoname.fcode = 'PCLI'
AND geoname.geonameid = alternatename.geonameid
AND alternatename.isoLanguage = '".$lang."' AND (alternatename.isShortName=1 OR alternatename.isPreferredName=1)

have 131 country not 192

Re:Query for alternatename

marc — GMT

of course. not every country has an entry for each language in the alternatenatename table, and not every country with an entry has the flags set, and others have both flags set. You can either do a preprocessing when loading the geonames data or you have to use more complex queries with outer joins etc. Best Marc

Re:Query for alternatename

lillolollo — GMT

Thanks marc can you provide an example query?

Re:Query for alternatename

marc — GMT

I would do it as a preprocessing on the application level otherwise if you want to do it on the db you can use window functions if your database supports it. Sort the window by your favorite criteria and get the best one with the first_value function. For postgres it would look like this: Code:

select distinct countrycode,first_value(name) over (partition by countrycode) as bestname from (select countrycode,case when alternatename.name is not null then alternatename.name else geoname.name end from geoname left outer join alternatename on alternatename.geonameid=geoname.geonameid and alternatename.namelocale='en' where featureclass ='A' and featurecode like 'PCL%' order by countrycode,isShortName desc nulls last,isofficialname desc nulls last)t order by countrycode;

Marc

Re:Query for alternatename

lillolollo — GMT

thanks marc very useful