GeoNames Forum

This thread is here to collect helpful/useful queries on a local geonames database. Please specify the query in the following order:

Subject: Description

Database Engine
Query

Database Engine
Query

Do not ask any questions here, this thread exists to reduce your time looking around the forum for queries. If you require support, please create a new thread.

MySQL:

Follow download instructions here: http://forum.geonames.org/gforum/posts/list/732.page

And then import using the following script.

UPDATE: 2010-08-15

Postgres 8.4 or higher

Starting with a particular GeoNames id (in this case 6429727) get its name and the name and id of each place containing it all the way up the hierarchy:

Code:

 WITH RECURSIVE geonametree AS
 
 (SELECT geoname.geonameid AS id, geoname.name AS name, hierarchy.parentid AS parent, hierarchy.childid AS child
 FROM hierarchy
 	JOIN geoname ON geoname.geonameid = hierarchy.childid 
 	WHERE geoname.geonameid = 6429727
 
 UNION ALL
 
 SELECT geoname.geonameid AS id, geoname.name AS name, hi.parentid AS parent, hi.childid AS child
 FROM hierarchy AS hi
 	JOIN geoname ON geoname.geonameid = hi.parentid 
 	INNER JOIN geonametree AS gt ON (hi.childid = gt.parent)
 )
 SELECT id, name, parent, child
 FROM geonametree;

That totally depends on what you want with the data, and what exactly you want to query. Have you taken a look at the readme.txt?

SELECT
a.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.name
FROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ON a.fclasscode = b.code
GROUP BY a.fclasscode
ORDER BY NbGeoNamesId DESC
LIMIT 0,10;