Geodb_hierarchies

Die Tabelle geodb_hierarchies dient der Einordnung der Locations in ein Hierarchie-Konzept. Der Aufbau der Hierarchie (z.B. Staat -> Bundesland -> Regierungsbezirk -> Landkreis -> Ortschaft -> Ortsteil) muss vorher bekannt sein. Da diese Strukturen jedoch je nach Staat und sogar Bundesland variieren können, ist hier kein einheitliches Schema möglich. Dies, und die Tatsache dass diese Information implizit durch den Datentyp 400100000 (Teil von) für relevante Locations zur Verfügung steht, haben dazu geführt, dass die Tabelle aktuell nicht im Dump enthalten bzw. befüllt ist.

Bei Bedarf sollte diese Hierarchie über die "Teil von"-Beziehung selbst erzeugt werden. Alternativ stehen unter http://fa-technik.adfc.de/code/opengeodb/ entsprechende Daten zum Import zur Verfügung (*hier.sql)

Im Feld "level" steht die für die ''loc_id'' geltende Hierarchie-Ebene, die dem Eintrag in der geodb_textdata mit dem text_type 400200000 (=Ebene) entspricht. Die über- bzw untergeordneten Level werden in den weiteren Feldern (id_lv1 bis id_lv9) eingetragen. Dies ermöglicht die Ermittlung der gesamten Hierarchie-Kette, ohne rekursive Abfragen.

==Fields==

<table {{prettytable}}> <tr> <th><b>PK</b></th> <th><b>Name</b></th> <th><b>Data type</b></th> <th><b>Size</b></th> <th><b>Precision</b></th> <th><b>Values</b></th> <th><b>Default</b></th> <th><b>Auto Increment</b></th> <th><b>Binary</b></th> <th><b>Not null</b></th> <th><b>Unsigned</b></th> <th><b>Zero Fill</b></th> <th><b>Unique</b></th> </tr>

<tr> <td align="center"></td> <td>loc_id</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center">Image:Yes.gif</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>level</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center">Image:Yes.gif</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl1</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center">Image:Yes.gif</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl2</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl3</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl4</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl5</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl6</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl7</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl8</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>id_lvl9</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>valid_since</td> <td>DATE</td> <td>0</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>date_type_since</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>valid_until</td> <td>DATE</td> <td>0</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center">Image:Yes.gif</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

<tr> <td align="center"></td> <td>date_type_until</td> <td>INTEGER</td> <td>11</td> <td>0</td> <td></td> <td></td> <td align="center"></td> <td align="center"></td> <td align="center">Image:Yes.gif</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> </tr>

</table>

==Indices==

<table {{prettytable}}> <tr> <th><b>Name</b></th> <th><b>Fields</b></th> <th><b>Unique</b></th> <th><b>Collation</b></th> <th><b>Full Text</b></th> </tr>

<tr> <td>hierarchy_loc_id_idx</td> <td>loc_id</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_level_idx</td> <td>level</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl1_idx</td> <td>id_lvl1</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl2_idx</td> <td>id_lvl2</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl3_idx</td> <td>id_lvl3</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl4_idx</td> <td>id_lvl4</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl5_idx</td> <td>id_lvl5</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl6_idx</td> <td>id_lvl6</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl7_idx</td> <td>id_lvl7</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl8_idx</td> <td>id_lvl8</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_lvl9_idx</td> <td>id_lvl9</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_since_idx</td> <td>valid_since</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

<tr> <td>hierarchy_until_idx</td> <td>valid_until</td> <td align="center"></td> <td>Ascending</td> <td align="center"></td> </tr>

</table>

==Foreign Keys== There are no foreign keys for table geodb_hierarchies

==Triggers== There are no triggers for table geodb_hierarchies

==Options==

<table {{prettytable}}>

<tr> <th><b>TransactSafe</b></th> <th><b>TableType</b></th> <th><b>Row Format</b></th> <th><b>Check Sum</b></th> <th><b>Delay Key Write</b></th> <th><b>Pack Keys</b></th> <th><b>Temporary</b></th> <th><b>Min Rows</b></th> <th><b>Max Rows</b></th> <th><b>Union</b></th> </tr>

<tr> <td align="center">Image:Yes.gif</td> <td>InnoDB</td> <td>Ascending</td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td align="center"></td> <td>0</td> <td>0</td> <td></td> </tr>

</table>

==Definition==

<source lang="mysql"> CREATE TABLE geodb_hierarchies ( loc_id int(11) NOT NULL, level int(11) NOT NULL, id_lvl1 int(11) NOT NULL, id_lvl2 int(11) default NULL, id_lvl3 int(11) default NULL, id_lvl4 int(11) default NULL, id_lvl5 int(11) default NULL, id_lvl6 int(11) default NULL, id_lvl7 int(11) default NULL, id_lvl8 int(11) default NULL, id_lvl9 int(11) default NULL, valid_since date default NULL, date_type_since int(11) default NULL, valid_until date NOT NULL, date_type_until int(11) NOT NULL, KEY hierarchy_loc_id_idx (loc_id), KEY hierarchy_level_idx (level), KEY hierarchy_lvl1_idx (id_lvl1), KEY hierarchy_lvl2_idx (id_lvl2), KEY hierarchy_lvl3_idx (id_lvl3), KEY hierarchy_lvl4_idx (id_lvl4), KEY hierarchy_lvl5_idx (id_lvl5), KEY hierarchy_lvl6_idx (id_lvl6), KEY hierarchy_lvl7_idx (id_lvl7), KEY hierarchy_lvl8_idx (id_lvl8), KEY hierarchy_lvl9_idx (id_lvl9), KEY hierarchy_since_idx (valid_since), KEY hierarchy_until_idx (valid_until) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

</source>

==Beispieldatensätze==

Für Bonn ergibt sich zum Beispiel aus opengeodb_textdata die loc_id 14755.

{|border="1" cellspacing="0" cellpadding="5" style="border-collapse:collapse;" !Feld !Wert !Text-Bedeutung !Typ-ID

!Typ-Bedeutung (nach geodb_type_names
loc_id
14755
Bonn
500100000
Name
-
level
6

| |400200000

Ebene
lv1
104
Europa
100100000
Erdteil
-
lv2
105
Deutschland
100200000
Staat/Land
-
lv3
117
Nordrhein-Westfalen
100300000
Bundesland (D), Kanton (CH)
-
lv4
182
Köln
100400000
Regierungsbezirk
-
lv5
413
Kreisfreie Stadt Bonn
100500000
Landkreis
-
lv6
14755
Bonn
100700000
Ortschaft
-
lv7, lv8, lv9
NULL

| | |}

Kategorie:OpenGeoDB