GeoNames Forum

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?

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

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

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

Best

Marc

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

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

Thanks marc can you provide an example query?

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

thanks marc very useful