GeoNames Forum

Hi everybody,

First of all: thanks for a great and well done work.

I have a little doubt. I downloaded your dump files in order import them to a database.

I've been trying to figure out how could I do some "standard" actions regarding geographical information, just like:
-"Where am I placed?"
-"Who are my children?"
-etc.

I got stuck when trying to get the query that gives my the "whole path of my parents".

While I achieved to get all childrens of a geoname id with something like this:

Code:

SELECT     gd1.Name, gd.Name AS Expr1, gh.childId 
 FROM         hierarchy gh INNER JOIN
                       GeoNames gd ON gd.GeoNameID = gh.childId INNER JOIN
                       GeoNames gd1 ON gd1.GeoNameID = gh.parentId
 WHERE     (gh.parentId = 3336901)

(That would return all provinces in Catalonia, Spain)

I also achieved to get the inmediate parent to a given geoname id:

Code:

SELECT     gd1.Name, gd.Name AS Expr1, gh.parentId
 FROM         hierarchy gh INNER JOIN
                       GeoNames gd ON gd.GeoNameID = gh.childId INNER JOIN
                       GeoNames gd1 ON gd1.GeoNameID = gh.parentId
 WHERE     (gh.childId = 3128759)

(That would return Catalonia, which is the inmediate parent of the Province of Barcelona)

My question is: is it possible to get all parents from Barcelona to Europe with a single query? (just as the first query I pasted but reverse).

Just in case my english is not understable... I would like to get something like Barcelona >Province of Barcelona > Catalonia > Spain > Europe. All in a single query not recursively querying.

Any help would be kindly appreciated.

Once again thanks for all the work!

If you have imported geonodes into a db like you did, this is a MUST read, and a good start for digging deeper:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/