go up through hirachical data for specific row in the same table (row = location in opengeodb)

Question

Hi!

I need to query the opengeodb to figure out the 'Bundesland' to a given location. You can find information to the DB schema at DB Schema. Sorry there is only German documentation text, but the graphical schema on this page is in english.

To make it possible that the opengeodb is extended later the data is stored in a special way, which means that any location data has a row and a type but there are no direct relations between the locations. You can easily query the other information to a specific location_id if it is directly related to the location. But I need to go up through a hirachical chain where I don't know how many steps are neccesarry.

e.g: I have the village with the loc_id = 1 and I know that this village is part_of loc_id = 2 (loc_id = 2 is let's say the county) I know that because with the village loc_id = 1 is also stored the location which the village belongs to in a field 'part_of' which keeps another loc_id.

But now here is my problem - I want to know to which other locations this village belongs going up in the chain. In this case I need the State

e.g. Village -> County -> District -> State

Is this even possible with pure SQL or will I need to write a script?

I try to explain it with example data.

loc_id | type    | name        | part_of
----------------------------------------
 1     | village | Springfield | 2
 2     | county  | Countyname  | 3
 3     | state   | Statename   | 4

...

I need now an SQL which gives me back all the data for the village (WHERE loc_id = 1) plus walks up in the hirachy an gives me the Statename in an aditional cloumn. Something like:

 1 | Springfield | Statename

Should be the outcome.

Can this be queried? And also it is not safe that the chain has always the same amount of steps (maybe there is a district between or the village belongs to a city bevore belonging to a county). It would be neccessary to stop walking up until the type = 'state' has been reached.

Thanks for any ideas! regards marcus

Answer 1

Standard SQL supports recursive queries, but MySQL doesn't have this feature (yet).

There are several ways to store hierarchical data in MySQL even without support for recursive queries. See my presentation Models for Hierarchical Data with SQL and PHP for examples.