Latest posts for the topic "Collection of geonames SQL queries"

Collection of geonames SQL queries

a110y — GMT

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. :)

Geonames import script

a110y — GMT

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

Re:Collection of geonames SQL queries

jcreel256 — GMT

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;

Re:Collection of geonames SQL queries

Oliver91 — GMT

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?

Re:Collection of geonames SQL queries

AlexandraHudson — GMT

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;