Hierarchy

oxalus — GMT

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!

Re:Hierarchy

tomzeppenfeldt — GMT

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/

Latest posts for the topic "Hierarchy"

Hierarchy

Re:Hierarchy