SQL-Script für hierarchies

[Peter Wendorff]

Hallo.
Diesmal richte ich mich mal besonders an die SQL-Freaks, da ich jetzt in
meinen Bemühungen, ein SQL-Script zu schreiben, das die
geodb_hierarchies aus den anderen Daten erzeugt, nicht weiterkomme.
Ich habe keine Ahnung, wo mein Fehler liegen könnte.

Mein Vorgehen, soweit es bisher funktioniert (ich weiß, besonders weit
ist das noch nicht):

In die leere geodb_hierarchies füge ich für jede vorhandene loc_id einen
Datensatz ein.
Alle zu betrachtenen Datensätze müssen eine Ebeneninformation tragen
(also suche ich nach text_type 400200000) und werden entsprechend mit
ihrer Ebene und ihrer loc_id eingetragen.
Da ich zu Gültigkeitsdaten keine Angabe machen kann, valid_until und
date_type_until aber Pflichtfelder sind, werden diese auf "unbekannt in
der Zukunft" gesetzt:

INSERT INTO geodb_hierarchies
            (loc_id, `level` , id_lvl1, valid_until, date_type_until)
            SELECT loc_id, text_val, 104, "3000-01-01", 300500000
            FROM geodb_textdata
            WHERE text_type = 400200000; /* Ebene */


Damit sind alle Datensätze vorhanden, die in geodb_hierarchies
auftauchen werden, ab jetzt werden diese gefüllt.
Den Anfang mache ich über die folgenden Abfragen:

UPDATE geodb_hierarchies SET id_lvl1 = loc_id WHERE `level`= 1;
UPDATE geodb_hierarchies SET id_lvl2 = loc_id WHERE `level`= 2;
UPDATE geodb_hierarchies SET id_lvl3 = loc_id WHERE `level`= 3;
UPDATE geodb_hierarchies SET id_lvl4 = loc_id WHERE `level`= 4;
UPDATE geodb_hierarchies SET id_lvl5 = loc_id WHERE `level`= 5;
UPDATE geodb_hierarchies SET id_lvl6 = loc_id WHERE `level`= 6;
UPDATE geodb_hierarchies SET id_lvl7 = loc_id WHERE `level`= 7;
UPDATE geodb_hierarchies SET id_lvl8 = loc_id WHERE `level`= 8;
UPDATE geodb_hierarchies SET id_lvl9 = loc_id WHERE `level`= 9;

Hiermit wird jeweils die ID in dem level gesetzt, in der der Eintrag
selbst zu finden ist - denn die ist ja klar.

...und ab hier kriege ich Probleme...
Meine Idee für das weitere Vorgehen:
Nach und nach fülle ich jetzt Ebene e = 8, 7, 6, ..... über einzelne
Queries auf.
Dazu suche ich nach Teil-Von-Datensätzen mit der entsprechenden loc_id
in Ebene n und trage den text_val, der ja der loc_id des
parent-Datensatzes entsprechen müsste, in das Feld für Ebene n-1 ein.
nur funktioniert das irgendwie noch nicht.
Meine Abfrage zum Füllen von Level 8 bisher:

UPDATE geodb_hierarchies AS h,
       geodb_textdata AS parents,
       geodb_textdata AS lvl
SET h.id_lvl8 = lvl.loc_id
WHERE h.id_lvl9 IS NOT NULL
  AND lvl.text_type = 400200000
  AND lvl.text_val = 8
  AND parents.text_type = 400100000
  AND parents.loc_id = h.id_lvl9
  AND parents.text_val = lvl.loc_id;

Im einzelnen:
- betrachtet werden müssen die Tabellen hierarchies und textdata, wobei
letztere in zwei Rollen auftaucht.
- in hierarchies wird id_lvl8 gesetzt - das ist ja, was die Abfrage tun
soll.
- Da Level 8 nur für Datensätze gefüllt werden muss, deren
Level-9-Eintrag angegeben ist, wird auf h.id_lvl9 IS NOT NULL
eingeschränkt. (Datensätze, die selbst in Level 8 liegen, sind bereits
gefüllt, weitere Datensätze bleiben auf NULL)
- die beiden textdata-Instanzen werden jeweils auf den benötigten Typ
eingeschränkt:
- bei lvl interessiert mich lediglich, ob der parent-Datensatz auch
wirklich in level 8 liegt, und damit eine Einschränkung auf den Typ
"Ebene" (400200000) sowie den text_val = 8
- bei parents interessieren mich nur Datensätze vom Typ "Teil von",
deshalb wird auf parents.text_type = 400100000 eingegrenzt.
- Was bleibt, sind die Join-Bedingungen:
- parents.loc_id = h.id_lvl9: die Teil-Von-Datensätze enthalten als
loc_id die ID des TEIL-Datensatzes, also muss diese der ID in id_lvl9
der hierarchies entsprechen, denn dazu suchen wir ja den Parent-Eintrag.
- Mit lvl wurde überprüft, ob der übergeordnete Eintrag auch in Ebene 8
liegt, deshalb muss parents.text_val (eben der Parent) der loc_id eines
Level-8-Eintrages vom Typ Ebene (das hatten wir bereits eingegrenzt)
entsprechen.
Diese Einschränkung ist eben genau für die fehlenden Zwischenebenen
notwendig. Da sonst Einträge auf falschen Ebenen auftauchen würden.

Syntaktisch ist die Query korrekt, jedenfalls gibt mir weder phpmyadmin
noch die mysql-Kommandozeile eine Fehlermeldung zurück; allerdings wird
kein einziger Datensatz aktualisiert, obwohl 11 Datensätze mit h.id_lvl9
ungleich NULL existieren.

Falls jemand irgendeine Idee hat, woran das liegen könnte, bin ich für
jede Hilfe dankbar.

Gruß und gute Nacht

Peter Wendorff
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Stephan Schuster]

Hallo Peter,

Da ich keinen Fehler in deinen Überlegungen finden konnte, habe ich mal
einen aktuellen Stand eingespielt um deine Vorgehensweise durchzuspielen.
Dabei habe ich DE.sql, CH.sql und LI.sql, extra.sql importiert und auch
die von Martin zur Verfügung gestellte geodb_hierarchies (für DE als
geodb_hierarchies_orig) zum Vergleich.

>
> [...]
> UPDATE geodb_hierarchies SET id_lvl9 = loc_id WHERE `level`= 9;

Hier ergibt sich ein erster Unterschied: in der geodb_hierarchies_orig
gibt es für diesen level keinen Eintrag, bei mir aber nach dem UPDATE 11

> Hiermit wird jeweils die ID in dem level gesetzt, in der der Eintrag
> selbst zu finden ist - denn die ist ja klar.
>
> ...und ab hier kriege ich Probleme...
> Meine Idee für das weitere Vorgehen:
> Nach und nach fülle ich jetzt Ebene e = 8, 7, 6, ..... über einzelne
> Queries auf.
> Dazu suche ich nach Teil-Von-Datensätzen mit der entsprechenden loc_id
> in Ebene n und trage den text_val, der ja der loc_id des
> parent-Datensatzes entsprechen müsste, in das Feld für Ebene n-1 ein.
> nur funktioniert das irgendwie noch nicht.
> Meine Abfrage zum Füllen von Level 8 bisher:
>
> UPDATE geodb_hierarchies AS h,
>        geodb_textdata AS parents,
>        geodb_textdata AS lvl
> SET h.id_lvl8 = lvl.loc_id
> WHERE h.id_lvl9 IS NOT NULL
>   AND lvl.text_type = 400200000
>   AND lvl.text_val = 8
>   AND parents.text_type = 400100000
>   AND parents.loc_id = h.id_lvl9
>   AND parents.text_val = lvl.loc_id;
> [...]

Das liefert bei mir immerhin einen Eintrag, für Vergleiche aber etwas
wenig. Also habe ich Dein SQL-Statement für Ebene 5 (Landkreis) umgeschrieben
(und die JOINs als LEFT JOIN mit ON-Klausel umgeformt, da ich das besser
nachvollziehbar finde):

UPDATE geodb_hierarchies AS h
LEFT JOIN geodb_textdata AS parents ON parents.loc_id = h.id_lvl5
LEFT JOIN geodb_textdata AS lvl ON parents.text_val = lvl.loc_id
SET h.id_lvl4 = lvl.loc_id
WHERE h.id_lvl5 IS NOT NULL
  AND lvl.text_type = 400200000
  AND lvl.text_val = 4
  AND parents.text_type = 400100000;
 
Dies liefert bei mir 355 geänderte Datensätze, insgesamt existieren aber 439
Einträge in der geodb_hierarchies mit Level 5.

SELECT h.loc_id, name.text_val AS name, typ.text_val AS typ
FROM geodb_hierarchies AS h
LEFT JOIN geodb_textdata AS name ON h.loc_id = name.loc_id
LEFT JOIN geodb_textdata typ ON h.loc_id = typ.loc_id
WHERE h.level =5
AND ISNULL( h.id_lvl4 )
AND name.text_type =500100000
AND typ.text_type =400300000;

liefert dann 86 Datensätze ohne Eintrag für level 4, ich füge die hier
einfach mal ein:

loc_id;name;typ
466;Flensburg;Stadt
469;Kiel;Landeshauptstadt
472;Hansestadt Lübeck;Hansestadt
474;Neumünster;Stadt
258;Dithmarschen;Kreis
463;Herzogtum Lauenburg;Kreis
477;Nordfriesland;Kreis
480;Ostholstein;Kreis
485;Pinneberg;Kreis
489;Plön;Kreis
493;Rendsburg-Eckernförde;Kreis
251;Schleswig-Flensburg;Kreis
254;Segeberg;Kreis
257;Steinburg;Kreis
261;Stormarn;Kreis
526;Freie und Hansestadt Hamburg;Freie und Hansestadt
288;Hansestadt Bremen;Stadt
293;Bremerhaven;Stadt
283;Stadtverband Saarbrücken;Stadt und Stadtverband Saarbrücken;Kreis
264;Merzig-Wadern;Kreis
268;Neunkirchen (Saar);Kreis
277;Saarlouis;Kreis
272;Saarpfalz-Kreis;Kreis
272;Saar-Pfalz-Kreis;Kreis
272;Saarpfalz-Kreis;Kreis
280;Sankt Wendel;Kreis
319;Berlin;Stadt
310;Brandenburg an der Havel;Stadt
315;Cottbus;Stadt
320;Frankfurt (Oder);Stadt
523;Potsdam;Stadt
289;Barnim;Kreis
294;Dahme-Spreewald;Kreis
299;Elbe-Elster;Kreis
304;Havelland;Kreis
528;Märkisch-Oderland;Kreis
531;Oberhavel;Kreis
535;Oberspreewald-Lausitz;Kreis
540;Oder-Spree;Kreis
545;Ostprignitz-Ruppin;Kreis
549;Potsdam-Mittelmark;Kreis
554;Prignitz;Kreis
559;Spree-Neiße(Lausitz);Kreis
562;Teltow-Fläming;Kreis
309;Uckermark;Kreis
291;Hansestadt Greifswald;Kreisfreie Stadt
296;Neubrandenburg;Stadt
302;Hansestadt Rostock;Kreisfreie Stadt
307;Schwerin;Stadt
313;Hansestadt Stralsund;Kreisfreie Stadt
317;Hansestadt Wismar;Kreisfreie Stadt
538;Bad Doberan;Kreis
543;Demmin;Kreis
286;Güstrow;Kreis
322;Ludwigslust;Kreis
325;Mecklenburg-Strelitz;Kreis
329;Müritz (Müritz);Kreis
533;Nordvorpommern;Kreis
537;Nordwestmecklenburg;Kreis
542;Ostvorpommern;Kreis
548;Parchim;Kreis
552;Rügen auf Rügen;Kreis
557;Uecker-Randow;Kreis
560;Erfurt;Stadt
563;Gera;Stadt
567;Jena;Stadt
571;Suhl;Stadt
575;Weimar;Stadt
337;Eisenach;Stadt
333;Eichsfeld;Kreis
582;Nordhausen;Kreis
362;Wartburgkreis;Kreis
358;Unstrut-Hainich-Kreis;Kreis
578;Kyffhäuserkreis;Kreis
346;Schmalkalden-Meiningen;Kreis
342;Gotha;Kreis
354;Sömmerda;Kreis
550;Hildburghausen;Kreis
555;Ilm-Kreis;Kreis
365;Weimarer Land;Kreis
350;Sonneberg;Kreis
341;Saalfeld-Rudolstadt;Kreis
332;Saale-Holzland-Kreis;Kreis
336;Saale-Orla-Kreis;Kreis
546;Greiz;Kreis
327;Altenburger Land;Kreis

Nehme ich die loc_id 472 (Lübeck) und prüfe die parent.loc_id (119) dann
ist dieses direkt Schleswig-Holstein auf level 3 und die Verknüpfung
über lvl.text_val = 4 passt nicht auf diese loc_id. Für Sonneberg (350)
ist dies dann Thüringen, ebenfalls level 3. In meiner geodb_hierarchies_orig
finde ich übrigens gar keinen Eintrag für 472 oder 350...

Was das genau bedeutet, ist mir noch nicht klar und es ist jetzt doch
schon spät. Wir sollten da dranbleiben ;-)

Schönen Gruß
Stephan


--
Stephan Schuster <stephan.s@...>

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Martin Trautmann]

Stephan S wrote:

> Hallo Peter,
>
> Da ich keinen Fehler in deinen Überlegungen finden konnte, habe ich mal
> einen aktuellen Stand eingespielt um deine Vorgehensweise durchzuspielen.
> Dabei habe ich DE.sql, CH.sql und LI.sql, extra.sql importiert und auch
> die von Martin zur Verfügung gestellte geodb_hierarchies (für DE als
> geodb_hierarchies_orig) zum Vergleich.
>
>> [...]
>> UPDATE geodb_hierarchies SET id_lvl9 = loc_id WHERE `level`= 9;
>
> Hier ergibt sich ein erster Unterschied: in der geodb_hierarchies_orig
> gibt es für diesen level keinen Eintrag, bei mir aber nach dem UPDATE 11

Das kann an einem Fehler meinerseits liegen, weil ich die PLZ mit type
100800000 verpasst hatte und daher level 8 auf 100800000 und level 9 auf
100900000 habe.

>
>> Hiermit wird jeweils die ID in dem level gesetzt, in der der Eintrag
>> selbst zu finden ist - denn die ist ja klar.
>>
>> ...und ab hier kriege ich Probleme...
>> Meine Idee für das weitere Vorgehen:
>> Nach und nach fülle ich jetzt Ebene e = 8, 7, 6, ..... über einzelne
>> Queries auf.

Eigentlich musst du in umgekehrter Richtung arbeiten - zumindest muss
ich das mit meinem eigenen Ansatz.

> liefert dann 86 Datensätze ohne Eintrag für level 4, ich füge die hier
> einfach mal ein:
>
> loc_id;name;typ
> 466;Flensburg;Stadt
> 469;Kiel;Landeshauptstadt
> 472;Hansestadt Lübeck;Hansestadt
> Nehme ich die loc_id 472 (Lübeck) und prüfe die parent.loc_id (119) dann
> ist dieses direkt Schleswig-Holstein auf level 3 und die Verknüpfung
> über lvl.text_val = 4 passt nicht auf diese loc_id.

Hier mal alle hierarchies mit Lübeck

INSERT INTO geodb_hierarchies VALUES
  (20437,6,104,105,119,null,472,20437,null,null,null); /* DE */
  (106047,7,104,105,119,null,472,20437,106047,null,null); /* DE */
  (106048,8,104,105,119,null,472,20437,106047,106048,null); /* DE */
  (106049,8,104,105,119,null,472,20437,106047,106049,null); /* DE */
  (106046,8,104,105,119,null,472,20437,null,106046,null); /* DE */
  (106050,8,104,105,119,null,472,20437,null,106050,null); /* DE */
  (106051,8,104,105,119,null,472,20437,null,106051,null); /* DE */
  (106052,8,104,105,119,null,472,20437,null,106052,null); /* DE */
  (106053,8,104,105,119,null,472,20437,null,106053,null); /* DE */
  (106054,8,104,105,119,null,472,20437,null,106054,null); /* DE */
  (106055,8,104,105,119,null,472,20437,null,106055,null); /* DE */
  (106056,8,104,105,119,null,472,20437,null,106056,null); /* DE */
  (106057,8,104,105,119,null,472,20437,null,106057,null); /* DE */
  (106058,8,104,105,119,null,472,20437,null,106058,null); /* DE */
  (106059,8,104,105,119,null,472,20437,null,106059,null); /* DE */
  (106060,8,104,105,119,null,472,20437,null,106060,null); /* DE */
  (106061,8,104,105,119,null,472,20437,null,106061,null); /* DE */
  (106062,8,104,105,119,null,472,20437,null,106062,null); /* DE */
  (106063,8,104,105,119,null,472,20437,null,106063,null); /* DE */
  (106064,8,104,105,119,null,472,20437,null,106064,null); /* DE */

level 4 muss hier leer sein, weil es in Schleswig-Holstein keine
Regierungsbezirke gibt.
#104: Europa
#105: Deutschland
#119: Schleswig-Holstein
-: Regierungsbezirk
#472: Lübeck (Hansestadt, Kreisebene)
#20437: Lübeck (Gemeindeebene)
#106047: Klein Grönau
#106048: Gothmund
...

> Was das genau bedeutet, ist mir noch nicht klar und es ist jetzt doch
> schon spät. Wir sollten da dranbleiben ;-)

Ja, unbedingt...

Übrigens würde ich vorschlagen, s.o., das begin/end-Datum aus den
hierarchies zu entfernen. Da diese Daten abgeleitet sind und da sich das
Datum eher aus den 400-Typen ergibt, brauchen wir es hier nicht mehr.

Schönen Gruß
Martin
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Martin Trautmann schrieb:

> Stephan S wrote:
>  
>> Hallo Peter,
>>
>> Da ich keinen Fehler in deinen Überlegungen finden konnte, habe ich mal
>> einen aktuellen Stand eingespielt um deine Vorgehensweise durchzuspielen.
>> Dabei habe ich DE.sql, CH.sql und LI.sql, extra.sql importiert und auch
>> die von Martin zur Verfügung gestellte geodb_hierarchies (für DE als
>> geodb_hierarchies_orig) zum Vergleich.
>>
>>    
>>> [...]
>>> UPDATE geodb_hierarchies SET id_lvl9 = loc_id WHERE `level`= 9;
>>>      
>> Hier ergibt sich ein erster Unterschied: in der geodb_hierarchies_orig
>> gibt es für diesen level keinen Eintrag, bei mir aber nach dem UPDATE 11
>>    
>
> Das kann an einem Fehler meinerseits liegen, weil ich die PLZ mit type
> 100800000 verpasst hatte und daher level 8 auf 100800000 und level 9 auf
> 100900000 habe.
>
>  
>>> Hiermit wird jeweils die ID in dem level gesetzt, in der der Eintrag
>>> selbst zu finden ist - denn die ist ja klar.
>>>
>>> ...und ab hier kriege ich Probleme...
>>> Meine Idee für das weitere Vorgehen:
>>> Nach und nach fülle ich jetzt Ebene e = 8, 7, 6, ..... über einzelne
>>> Queries auf.
>>>      
>
> Eigentlich musst du in umgekehrter Richtung arbeiten - zumindest muss
> ich das mit meinem eigenen Ansatz.
>  

Bei deinem Ansatz gehst Du die Teil-Von-Beziehungen runter und musst
dabei die Einträge hinterher zigfach kopieren.
Mein Ansatz bringt aber in der ersten Abfrage jeden Datensatz in die
Tabelle, und von da an wird aufgefüllt, was fehlt.
Im Gegenteil würde dein Ansatz loc_ids, die keine Vorfahren bis Level 1
haben, aussen vor lassen.

>
>  
>> liefert dann 86 Datensätze ohne Eintrag für level 4, ich füge die hier
>> einfach mal ein:
>>
>> loc_id;name;typ
>> 466;Flensburg;Stadt
>> 469;Kiel;Landeshauptstadt
>> 472;Hansestadt Lübeck;Hansestadt
>> Nehme ich die loc_id 472 (Lübeck) und prüfe die parent.loc_id (119) dann
>> ist dieses direkt Schleswig-Holstein auf level 3 und die Verknüpfung
>> über lvl.text_val = 4 passt nicht auf diese loc_id.
>>    
>
>
> Hier mal alle hierarchies mit Lübeck
>
> INSERT INTO geodb_hierarchies VALUES
>   (20437,6,104,105,119,null,472,20437,null,null,null); /* DE */
>   (106047,7,104,105,119,null,472,20437,106047,null,null); /* DE */
>   (106048,8,104,105,119,null,472,20437,106047,106048,null); /* DE */
>   (106049,8,104,105,119,null,472,20437,106047,106049,null); /* DE */
>   (106046,8,104,105,119,null,472,20437,null,106046,null); /* DE */
>   (106050,8,104,105,119,null,472,20437,null,106050,null); /* DE */
>   (106051,8,104,105,119,null,472,20437,null,106051,null); /* DE */
>   (106052,8,104,105,119,null,472,20437,null,106052,null); /* DE */
>   (106053,8,104,105,119,null,472,20437,null,106053,null); /* DE */
>   (106054,8,104,105,119,null,472,20437,null,106054,null); /* DE */
>   (106055,8,104,105,119,null,472,20437,null,106055,null); /* DE */
>   (106056,8,104,105,119,null,472,20437,null,106056,null); /* DE */
>   (106057,8,104,105,119,null,472,20437,null,106057,null); /* DE */
>   (106058,8,104,105,119,null,472,20437,null,106058,null); /* DE */
>   (106059,8,104,105,119,null,472,20437,null,106059,null); /* DE */
>   (106060,8,104,105,119,null,472,20437,null,106060,null); /* DE */
>   (106061,8,104,105,119,null,472,20437,null,106061,null); /* DE */
>   (106062,8,104,105,119,null,472,20437,null,106062,null); /* DE */
>   (106063,8,104,105,119,null,472,20437,null,106063,null); /* DE */
>   (106064,8,104,105,119,null,472,20437,null,106064,null); /* DE */
>
> level 4 muss hier leer sein, weil es in Schleswig-Holstein keine
> Regierungsbezirke gibt.
> #104: Europa
> #105: Deutschland
> #119: Schleswig-Holstein
> -: Regierungsbezirk
> #472: Lübeck (Hansestadt, Kreisebene)
> #20437: Lübeck (Gemeindeebene)
> #106047: Klein Grönau
> #106048: Gothmund
> ...
>
>  
>> Was das genau bedeutet, ist mir noch nicht klar und es ist jetzt doch
>> schon spät. Wir sollten da dranbleiben ;-)
>>    
>
>  

Die leer gebliebenen Ebenen (wie hier die Ebene 4) sind noch nicht dabei
bis dahin, das ist mir klar, aber das kommt noch.

Gruß
Peter
> Ja, unbedingt...
>
> Übrigens würde ich vorschlagen, s.o., das begin/end-Datum aus den
> hierarchies zu entfernen. Da diese Daten abgeleitet sind und da sich das
> Datum eher aus den 400-Typen ergibt, brauchen wir es hier nicht mehr.
>  
gerne; aber für das Script sind die egal, insofern lasse ich die erstmal
drin, bis die aus deinen offiziellen Dumps auch raus sind.
> Schönen Gruß
> Martin
>  

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Hallo Liste.
Nochmal zum Thema zurück (nachdem ich letzte Woche meinen Rechner neu
aufsetzen musste):
Datenstand: aktuell (ca 20 Uhr heute Abend), eingespielt sind die Dateien

Ich habe nach dem Einspielen des folgenden (ersten) Befehls meines
Scripts einen Unterschied zwischen den Dump-Hierarchies und meinen
erzeugten:

INSERT INTO geodb_hierarchies
            (loc_id, `level` , id_lvl1, valid_until, date_type_until)
            SELECT loc_id, text_val, 104, "3000-01-01", 300500000
            FROM geodb_textdata
            WHERE text_type = 400200000;

Die hierarchies aus dem Dump enthalten bei mir 60.640 Einträge, die
erzeugte Tabelle enthält dagegen 55.080 Einträge - rund 5.000 Einträge
weniger.
Die Überprüfung, welche Datensätze jeweils fehlen, ergibt:
In der heruntergeladenen Tabelle fehlen die 5 Datensätze mit den IDs
85722, 144599, 144600, 144602 und 144603,
in der erzeugten Tabelle fehlen die 19 Datensätze mit den IDs 80061,
80062, 80063, 80067, 80074, 80091, 80092,  80393, 80583, 80585, 80587,
80589, 80590, 80591, 80592, 80594, 80623 und 128355.

Betrachten wir die Datensätze im Einzelnen,
beginnend mit denen, die in der heruntergeladenen Tabelle fehlen,
als Referenz nehme ich diesmal der Einfachheit halber die Oberfläche
unter fa-technik.adfc.de:
85722 - Altes Lager, ist Teil von -1 - also einer nicht-existenten loc_id
144599 - Mildensee, Teil von ist leer
144600 - Mosgikau, Teil von ist ebenfalls leer, für die anderen drei
Einträge gilt dies analog.
Zu diesen Daten sind schlichtweg also keine Informationen zu höheren
Hierarchie-Ebenen vorhanden, weshalb sie beim Top-Down-Ansatz von
Martins SQL-Erzeugung nicht vorkommen können. Dennoch gehören diese
Datensätze in gewisser Weise in die hierarchies-Tabelle, inwiefern sie
in bestimmten Anwendungen verzichtbar sind, darüber lässt sich streiten.
Auf jeden Fall lässt sich dies als Hinweis auf Fehler in der Datenbank
festhalten.

Weiter geht es mit den Datensätzen, die mein Script nicht erzeugt,
obwohl sie im Dump vorhanden sind. Bei diesen fällt auf, dass sie alle
als gelöscht markiert sind.
Dies deutet darauf hin, dass diese Daten eben schon im Stamm-Dump nicht
vorhanden sind, im hierarchies-Dump allerdings doch noch.

Wenn mir das soweit jemand verifizieren könnte, wär das ganz gut.

Dies bedeutet insbesondere übrigens, dass der hierarchies-Dump entweder
nicht gleich aktuell ist wie der Daten-Dump oder aber, dass der
hierarchie-Dump nicht korrekt erzeugt wird.

Das weitere Vorgehen folgt dann später - ich möchte vor allem erstmal
sicher gehen, dass dies soweit programm-technisch korrekt ist, und eben
auf den vermuteten Dump-Inkonsistenzen beruht.

Gruß
Peter Wendorff
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Martin Trautmann]

Peter Wendorff wrote:

> Weiter geht es mit den Datensätzen, die mein Script nicht erzeugt,
> obwohl sie im Dump vorhanden sind. Bei diesen fällt auf, dass sie alle
> als gelöscht markiert sind.

Ah, danke - kann sein, dass ich bei der Erzeugung der Hierarchies nicht
auf Löschmarkierungen achtete.

> Dies bedeutet insbesondere übrigens, dass der hierarchies-Dump entweder
> nicht gleich aktuell ist wie der Daten-Dump oder aber, dass der
> hierarchie-Dump nicht korrekt erzeugt wird.

Ich habe kurz nachgesehen - ja, ich betrachte nur id, level und of

> Das weitere Vorgehen folgt dann später - ich möchte vor allem erstmal
> sicher gehen, dass dies soweit programm-technisch korrekt ist, und eben
> auf den vermuteten Dump-Inkonsistenzen beruht.

Das sieht sehr gut aus - ich könnte nun den Fehler natürlich
korrigieren, halte das bei deinem Stand aber schon fast für überflüssig.

Schönen Gruß
Martin
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Martin Trautmann schrieb:
> Das sieht sehr gut aus - ich könnte nun den Fehler natürlich
> korrigieren, halte das bei deinem Stand aber schon fast für überflüssig.
Auf Dauer bitte trotzdem korrigieren ;)
Ich teste weiter...
Gruß
Peter
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Martin Trautmann schrieb:
> Peter Wendorff wrote:
>
>  
>> Weiter geht es mit den Datensätzen, die mein Script nicht erzeugt,
>> obwohl sie im Dump vorhanden sind. Bei diesen fällt auf, dass sie alle
>> als gelöscht markiert sind.
>>    
Die Gelöscht-Markierungen kann ich an den SQL-Daten aber nirgendwo
erkennen, oder?
Sonst würde ich die in meiner lokalen Version vorher löschen, damit ich
da einfacher testen kann.

Gruß
Peter
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Martin Trautmann]

Peter Wendorff wrote:

> Die Gelöscht-Markierungen kann ich an den SQL-Daten aber nirgendwo
> erkennen, oder?

nein - im normalen dump sind sie schlichtweg nicht vorhanden,
in extra-Daten evtl. schon und der hierarchies-hack hat die Markierung
schlichtweg ignoriert.

> Sonst würde ich die in meiner lokalen Version vorher löschen, damit ich
> da einfacher testen kann.

Kannst du nicht einfach die .tab-Dateien mit (e)grep oder einer
Tabellenkalkulation durchgehen und damit die IDs der markierten Einträge
raussuchen?

Schönen Gruß
Martin
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Weiter im Text, nachdem der erste Teil offensichtlich gelöst ist.

Stand der Dinge also:
- zu jeder loc_id existiert ein Datensatz in meiner erzeugten
hierarchies-Tabelle.
- lvl1 ist jeweils auf -1 gesetzt (später hier NULL, aber im Moment
bleibe ich noch bei der alten Tabellendefinition)
- das Feld level ist gesetzt entsprechend dem entsprechenden Wert aus
text_data
- das zu level passende Feld id_lvl? (wobei ? eben durch den Wert von
level im Bereich von [1..9] zu ersetzen ist) ist entsprechend korrekt
gesetzt.

Mein nächster Schritt ist, die Parents zu finden, die auf level 8 liegen
und entsprechend einen Nachfahren haben, der in der locations auf level
9 sitzt.
Also:

/*1*/   UPDATE geodb_hierarchies,
/*2*/          geodb_textdata AS part1,
/*3*/          geodb_textdata AS part2
/*4*/   SET id_lvl8 = part1.text_val
/*5*/   WHERE part1.text_type = 400100000
/*6*/     AND part2.text_type = 400200000
/*7*/     AND part2.text_val = 8
/*8*/     AND part2.loc_id = part1.text_val
/*9*/     AND part1.text_val = geodb_hierarchies.id_lvl9
/*10*/    AND geodb_hierarchies.level = 9;

(Die Zeilennummern stehen absichtlich in einem Kommentar, damit man das
ganze so in ein SQL-Tool übernehmen kann)

Zeilen 1 bis 3:
Das Update schreibt zwar nur in geodb_hierarchies, benötigt dafür aber
auch Daten aus textdata - und das in zwei verschiedenen Rollen, deshalb
part1 und part2.
Zeile 4:
Geschrieben wird das Feld für Level 8, wohin, folgt gleich.
Zeilen 5 bis 10:
Die Bedingungen, was genau ich denn gerne hätte - im Einzelnen:
Zeile 5: part1.text_type: Die part1-Instanz von geodb_textdata wird
gefiltert auf alle "Teil-Von"-Beziehungen. Also stehen nun in
part1.loc_id alle "teile" von den part1.text_val -Werten.
Zeile 6: bei part2 werden nur Ebenen betrachtet, weil...
Zeile 7: ...insgesamt nur Ganze (vgl. part1) auf Ebene 8 gesucht werden.
(zurück zu Zeile 4: Genau das wird da dann auch geschrieben)
Zeile 8: part2.loc_id = part1.text_val - die Ebene 8 muss zu eben diesem
Ganzen gehören.
Zeile 9: part2.loc_id = geodb_hierarchies.id_lvl9 - natürlich muss das
TEIL von dem Ganzen dann auch in der hierarchies-Tabelle entsprechend in
Ebene 9 stehen, denn nur dann passt das Ganze ja dazu
Zeile 10: ...und damit wir das nicht zu oft umsonst machen, betrachten
wir nur Einträge, bei denen das level 9 ist, denn alle anderen haben ja
gar keinen Eintrag in id_lvl9 (man könnte auch nach
geodb_hierarchies.id_lvl9 NOT NULL filtern, das ist aber für die
Weiterverwendung später nicht sinnvoll).

Wo ist jetzt mein Fehler, dass dabei kein Datensatz aktualisiert wird?
Umgebaut zur SELECT-Abfrage dauert das ganze unheimlich lange, gibt aber
auch noch eine leere Ergebnismenge zurück.

Gruß und gute Nacht für heute.
Peter Wendorff
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Peter Wendorff schrieb:

> Weiter im Text, nachdem der erste Teil offensichtlich gelöst ist.
>
> Stand der Dinge also:
> - zu jeder loc_id existiert ein Datensatz in meiner erzeugten
> hierarchies-Tabelle.
> - lvl1 ist jeweils auf -1 gesetzt (später hier NULL, aber im Moment
> bleibe ich noch bei der alten Tabellendefinition)
> - das Feld level ist gesetzt entsprechend dem entsprechenden Wert aus
> text_data
> - das zu level passende Feld id_lvl? (wobei ? eben durch den Wert von
> level im Bereich von [1..9] zu ersetzen ist) ist entsprechend korrekt
> gesetzt.
>
> Mein nächster Schritt ist, die Parents zu finden, die auf level 8 liegen
> und entsprechend einen Nachfahren haben, der in der locations auf level
> 9 sitzt.
> Also:
> [...]
>  

Hab das nochmal neu gebastelt, und komme auf die folgende Funktion, die
mir immerhin einen Datensatz (loc_id 80627) korrekt setzt (und keinen
falsch - auch ein Vorteil):

UPDATE geodb_hierarchies,
    geodb_textdata AS partOf,
    geodb_textdata AS isLevel
SET id_lvl8 = isLevel.loc_id
WHERE 1
AND partOf.loc_id = geodb_hierarchies.loc_id
AND geodb_hierarchies.level = 9
AND partOf.text_type = "400100000"
AND isLevel.loc_id = partOf.text_val
AND isLevel.text_type = "400200000"
AND isLevel.text_val = "8";


Dabei sind alle anderen Datensätze, die in meiner geodb vorhanden sind,
auf fa-technik.adfc.de als gelöscht markiert, in meiner Datenbank aber
eben noch vorhanden (s. Mail von gestern: mein Dump ist von gestern
Abend, 20:00 Uhr) -> Martin???.

Ein Problem war offensichtlich das der Typen, also: die Ganzzahlen
gehören in geodb_intdata - das sollte bald geändert werden (Ebene, Teil
von, Einwohnerzahl, ungef. EWZ, Genaue EWZ, Höhenangabe (?), max. Höhe,
min Höhe, durchschn. Höhe, Höhe an Ref.Pkt, Höhe an angeg. Koord.)

Meine Vorlesung geht jetzt los, ich bin also erstmal beschäftigt.

Gruß
Peter

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Stephan Schuster]

Hallo Peter,

> > [...]
> >  
> Hab das nochmal neu gebastelt, und komme auf die folgende Funktion, die
> mir immerhin einen Datensatz (loc_id 80627) korrekt setzt (und keinen
> falsch - auch ein Vorteil):
>
> UPDATE geodb_hierarchies,
>     geodb_textdata AS partOf,
>     geodb_textdata AS isLevel
> SET id_lvl8 = isLevel.loc_id
> WHERE 1
> AND partOf.loc_id = geodb_hierarchies.loc_id
> AND geodb_hierarchies.level = 9
> AND partOf.text_type = "400100000"
> AND isLevel.loc_id = partOf.text_val
> AND isLevel.text_type = "400200000"
> AND isLevel.text_val = "8";

Ich habe zwölf Datensatze mit einem Level von 9. Wenn ich die
übergeordneten loc_id's abfrage:

SELECT id_lvl9, text_val
FROM geodb_hierarchies h
LEFT JOIN geodb_textdata AS part_of ON h.loc_id = part_of.loc_id
WHERE `id_lvl9` > 0
AND part_of.text_type = 400100000

erhalte ich 11 Datensätze mit übergeordneter id 80592 und einen mit
81036. Letzterer ist der Ortsteil Maichingen, die anderen haben als
übergeordneten Datensatz die loc_id 80592. Diese ist in meinem Dump (ca.
2 Wochen alt) nicht vorhanden, was bei Deiner Abfrage logischerweise
dann dazu führt, dass diese nur einen Datensatz (loc_id aktualisiert
80627) aktualisiert.

Unter http://fa-technik.adfc.de/code/opengeodb.pl?locid=80592;c=DE
erhalte ich als Ergebnis Mönchsdeggingen. Da level 9 wohl noch sehr
experimentell (Straßen?) ist, wäre es vielleicht sinnvoller bei level 8
zu beginnen (?).

Schönen Gruß
Stephan

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Peter Wendorff]

Hallo Stephan

Stephan Schuster schrieb:
> Unter http://fa-technik.adfc.de/code/opengeodb.pl?locid=80592;c=DE
> erhalte ich als Ergebnis Mönchsdeggingen.
Wenn Du genau hinguckst, findest Du auch die eigentliche Ursache (die
ich schon in der Mail von heute morgen geschrieben habe). Es liegt wohl
daran, dass die Daten auf fa-technik.adfc.de aus den text-Dateien
stammen: Die betroffenen Datensätze (u.A. dein Beispiel loc_id 80592)
sind als gelöscht markiert - und werden als solche in den
Basisdaten-Dump nicht aufgenommen.
Das Script zur Erzeugung der Hierarchies, das bisher den Dump erzeugt,
betrachtet aber diesen Marker nicht, weshalb im Dump der
geodb_hierarchies die Datensätze eben doch noch drin sind.
Mein Problem soweit ist also an dieser Stelle gelöst - die weiteren
Schritte werden jetzt der nächste Schritt sein, bis die Tabelle
vollständig gefüllt ist.
>  Da level 9 wohl noch sehr
> experimentell (Straßen?) ist, wäre es vielleicht sinnvoller bei level 8
> zu beginnen (?).
>
> Schönen Gruß
> Stephan
>  
Ganz klar eben nein - und dafür kann ich Dir gleich zwei Gründe nennen:
1) Konzeptionell sollen die Lvl9-Datensätze irgendwann kommen, und ich
möchte ja eben gerade ein Script, was später NICHT für neue DATEN
angepasst werden muss.
2) Mein angenommener Fehler war eben gar keiner - das SQL-Statement
funktioniert und erzeugt im Übrigen genauso korrekte Daten wie die, die
im Moment als Dump zur Verfügung stehen, nur sind die Daten aus dem Dump
eben durch eine fehlende Prüfung nicht konsistent mit den Basisdaten

Gruß
Peter

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Martin Trautmann]

Peter Wendorff wrote:

>>   Da level 9 wohl noch sehr
>> experimentell (Straßen?) ist, wäre es vielleicht sinnvoller bei level 8
>> zu beginnen (?).

> Ganz klar eben nein - und dafür kann ich Dir gleich zwei Gründe nennen:
> 1) Konzeptionell sollen die Lvl9-Datensätze irgendwann kommen, und ich
> möchte ja eben gerade ein Script, was später NICHT für neue DATEN
> angepasst werden muss.

Dennoch würde ich auch empfehlen, sie aus den hierarchies ganz
rauszunehmen: Wenn unsere Quelle dafür die OpenStreetMap sein wird,
müssten wir die opengeodb-Daten unter eine einschränkende Lizenz stellen.

Schönen Gruß
Martin
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Stephan Schuster]

Hallo Peter,

> >  Da level 9 wohl noch sehr
> > experimentell (Straßen?) ist, wäre es vielleicht sinnvoller bei level 8
> > zu beginnen (?).

> Ganz klar eben nein - und dafür kann ich Dir gleich zwei Gründe nennen:
> 1) Konzeptionell sollen die Lvl9-Datensätze irgendwann kommen, und ich
> möchte ja eben gerade ein Script, was später NICHT für neue DATEN
> angepasst werden muss.

Ich meinte damit nicht, den Level 9 nicht mehr zu berücksichtigen,
sondern eben für die Entwicklung deines Skriptes eine Ebene höher zu
beginnen und anhand dessen die Ergebnisse zu überprüfen. Experimentelle
Daten zu verwenden um die Plausibilität deiner Ergebnisse zu
verifizieren ist aus meiner Sicht doppelt schwierig. Dass Level9 in der
Endversion weiter vorkommen muss ist klar.

> 2) Mein angenommener Fehler war eben gar keiner - das SQL-Statement
> funktioniert und erzeugt im Übrigen genauso korrekte Daten wie die, die
> im Moment als Dump zur Verfügung stehen, nur sind die Daten aus dem Dump
> eben durch eine fehlende Prüfung nicht konsistent mit den Basisdaten

Eben, deshalb würde ich eine Ebene höher anfangen ;-)

Gruß
Stephan

--
Stephan Schuster <stephan.s@...>

--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)