GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Collection of geonames SQL queries  XML
Forum Index -> General
Author Message
a110y



Joined: 13/08/2010 17:38:48
Messages: 3
Offline

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.
a110y



Joined: 13/08/2010 17:38:48
Messages: 3
Offline

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
 Description Import sql script. Import using mysql> source import.sql [Disk] Download
 Filesize 230 Kbytes
 Downloaded:  67144 time(s)

jcreel256



Joined: 31/01/2013 21:38:11
Messages: 2
Offline

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;
 
[WWW]
Oliver91



Joined: 04/11/2016 09:32:16
Messages: 1
Offline

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?
[WWW]
AlexandraHudson



Joined: 08/02/2019 10:10:28
Messages: 1
Offline

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;
 
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team