Re: Verwaltungshierarchie mit Inkonsistenzen?

> > Typ-1-Regel: In geodb_textdata darf es keine Duplikate geben.
> 
> falsch - es darf beliebig viele Duplikate als textdata geben.

Beliebig viele in deinem Sinne schon, aber nicht beliebige.

> ok, das scheint ein echtes Duplikat zu sein, wo textdata und alle anderen
> Werte doppelt sind:

Genau das meinte ich.
  
> > Typ-1-Regel: Für jedes Objekt, das kein PLZ-Bereich ist, darf es zu jedem 
> > Zeitpunkt nur einen gültigen Eintrag in geodb_hierarchies geben.
> 
> Kann ich so noch nicht abschaetzen. Passt das bisher?

Dahinter steckt eine ganz einfache Logik. Für jeden Ort muss zu jedem Zeitpunkt 
klar sein, wo er sich in der Hierarchie befindet.

> > -- 7 Ortsobjekte, die gleichzeitig auf Level 6 und Level 7 stehen
> 
> naemlich welche?

Das ergab gerade die Abfrage, die ich hier wiederhole:

SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h1 ON h1.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- keine PLZ
AND lc.loc_id > 0 -- keine Pseudoobjekte
INNER JOIN geodb_hierarchies AS h2 ON h2.loc_id = h1.loc_id
WHERE -- Vergleich eines Datensatzes mit sich selbst ausschließen. Hier müsste 
ein Schlüssel her.
(
        (h1.level <> h2.level)
        OR (h1.id_lvl1 <> h2.id_lvl1)
        OR (h1.id_lvl2 <> h2.id_lvl2) OR (h1.id_lvl2 IS NULL AND h1.id_lvl2 IS 
NOT NULL) OR (h1.id_lvl2 IS NOT NULL AND h1.id_lvl2 IS NULL)
        OR (h1.id_lvl3 <> h2.id_lvl3) OR (h1.id_lvl3 IS NULL AND h1.id_lvl3 IS 
NOT NULL) OR (h1.id_lvl3 IS NOT NULL AND h1.id_lvl3 IS NULL)
        OR (h1.id_lvl4 <> h2.id_lvl4) OR (h1.id_lvl4 IS NULL AND h1.id_lvl4 IS 
NOT NULL) OR (h1.id_lvl4 IS NOT NULL AND h1.id_lvl4 IS NULL)
        OR (h1.id_lvl5 <> h2.id_lvl5) OR (h1.id_lvl5 IS NULL AND h1.id_lvl5 IS 
NOT NULL) OR (h1.id_lvl5 IS NOT NULL AND h1.id_lvl5 IS NULL)
        OR (h1.id_lvl6 <> h2.id_lvl6) OR (h1.id_lvl6 IS NULL AND h1.id_lvl6 IS 
NOT NULL) OR (h1.id_lvl6 IS NOT NULL AND h1.id_lvl6 IS NULL)
        OR (h1.id_lvl7 <> h2.id_lvl7) OR (h1.id_lvl7 IS NULL AND h1.id_lvl7 IS 
NOT NULL) OR (h1.id_lvl7 IS NOT NULL AND h1.id_lvl7 IS NULL)
        OR (h1.id_lvl8 <> h2.id_lvl8) OR (h1.id_lvl8 IS NULL AND h1.id_lvl8 IS 
NOT NULL) OR (h1.id_lvl8 IS NOT NULL AND h1.id_lvl8 IS NULL)
        OR (h1.id_lvl9 <> h2.id_lvl9) OR (h1.id_lvl9 IS NULL AND h1.id_lvl9 IS 
NOT NULL) OR (h1.id_lvl9 IS NOT NULL AND h1.id_lvl9 IS NULL)
        OR (h1.valid_since <> h2.valid_since) OR (h1.valid_since IS NULL AND 
h1.valid_since IS NOT NULL)
        OR (h1.valid_since IS NOT NULL AND h1.valid_since IS NULL)
        OR (h1.date_type_since <> h2.date_type_since) OR (h1.date_type_since IS 
NULL AND h1.date_type_since IS NOT NULL)
        OR (h1.date_type_since IS NOT NULL AND h1.date_type_since IS NULL)
        OR (h1.valid_until <> h2.valid_until)
        OR (h1.date_type_until <> h2.date_type_until)
)
AND -- überlappende Zeiträume (Überlegung: Standardwert statt NULL bei 
valid_since und NOT NULL setzen?)
(
        (h1.valid_since IS NULL AND (h2.valid_since IS NULL OR h2.valid_since 
<= h1.valid_until))
        OR (h1.valid_since <= h2.valid_until AND h2.valid_since <= 
h1.valid_until)
)
ORDER BY h1.loc_id, h1.valid_since;

> Schon bei historischen Daten wird's schwierig: Der Zusammenschluss zweier
> Gemeinden hat fuer die Gesamtgemeinde dann mehrere parallel gueltige
> Gemeindeschluessel.

Was bedeutet das? Unter "gültig" sein verstehe ich, dass die Daten an einem 
bestimmten Tag gültig sind. Die alten Gemeinden in deinem Beispiel haben einen 
gültigen und eindeutigen AGS bis zur Reform. Danach haben sie keinen gültigen 
AGS mehr, weil sie gar keine Gemeinden mehr sind. Statt dessen gibt es die neue 
Gemeinde, die ihrerseits ab dem Tag des Inkrafttretens der Reform einen AGS 
hat, aber nicht davor.

> > Typ-3-Regel: Jeder PLZ-Bereich (loc_type 100800000) hat genau 2 Einträge in 
> > geodb_textdata, einen mit text_type 500100000 (PLZ als Name) und einen mit 
> > text_type 500100004. Ist das Zufall oder eine Regel vom Typ 1 oder 2?
> 
> Per Definition sollte es so sein. Ich selbst halte den Repraesentanten
> fuer evtl. zufaellig und daher manchmal falsch gewaehlt - aber da dies
> anscheinend offizielle Daten als Quelle verwendet, darf es nur einen
> geben.

Das widerspricht der Erklärung von Thomas.

"Das ist mehr oder weniger Zufall. Man kann dort beliebig viele Daten
angeben. Z.B. könnte man auch alle KFZ-Kennzeichen einfügen, die in
dem Postleitzahlgebiet aktiv sind."

Vielleicht kann man das etwa so präzisieren. Ein PLZ-Bereich hat genau eine 
PLZ. Es ist zu klären, ob die PLZ eines PLZ-Bereichs vom Datum abhängig sein 
darf, d. h., ob sich die Nummer eines PLZ-Bereichs ändern darf (würde ich nicht 
zulassen). Zur Zeit hat jeder PLZ-Bereich eine alternative Ortsbezeichnung 
(text_type 500100004). Wenn es erforderlich wird, davon abzuweichen, muss man 
die Regel ändern. Weitere Informationen über die PLZ-Bereiche werden zur Zeit 
nicht in geodb_textdata gespeichert.

> > Typ-2-Regel: Jeder PLZ-Bereich wird in geodb_hierarchies verwendet. Was 
> > sind die Ausnahmen?
> > 
> > -- unbenutzte PLZ-Bereiche
> > SELECT lc.loc_id, td1.text_val FROM geodb_locations AS lc
> > INNER JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id AND 
> > td1.text_type = 500100000 -- die Nummer des PLZ-Bereichs
> > AND lc.loc_type = 100800000
> > LEFT JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
> > WHERE h.loc_id IS NULL;
> > -- 89 Datensätze
> > 
> > Die Nummern der unbenutzten PLZ-Bereiche werden auch nicht in 
> > geodb_textdata benutzt, wie das folgende Statement zeigt:
> 
> Beispiele?

Beispiele wofür? Das Statement zeigt - Fehlerfreiheit vorausgesetzt, dass in 
geodb_textdata keine PLZ (text_type 500300000) verwendet werden, für die zwar 
ein PLZ-Bereich existiert, dieser aber nicht in geodb_hierarchies verwendet 
wird.
 
> > -- mit gültigem Datum 7637 Datensätze (darunter 
> > -- mit beliebigem Datum 7644 Datensätze
> > -- Differenz sind 7 Datensätze mit 4-stelligen PLZ für Hannover, 
> > Braunschweig, Bad Harzburg
> > 
> > Es gibt also wenige Abweichungen in Deutschland. Fehler?
> 
> In dem Moment steige ich erst mal aus - da werden fuer mich zu viele
> Fragen in einer einzigen Frage zusammengepackt.

Aus dem Datenbestand geht hervor, dass es für Deutschland und nur für 
Deutschland PLZ-Bereiche gibt. Im Idealfall sind diese vollständig und man 
sollte annehmen, dass für eine PLZ, die einem Ort in Deutschland in 
geodb_textdata zugeordnet wird, ein PLZ-Bereich in geodb_locations (loc_type 
100800000) existiert. Das ist auch fast richtig. Das Statement zeigt die 
Ausnahmen. Es ist zu klären, ob es sich bei diesen Ausnahmen um Fehler oder 
begründete Ausnahmen handelt.

Ich habe das entsprechende Statement geringfügig angepasst (nur Deutschland, 
nur aktuelle Daten):

SELECT * FROM geodb_textdata AS td2
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td2.loc_id
AND td2.text_type = 500300000
AND h.id_lvl2 = 105 -- nur Deutschland betrachten
AND td2.valid_until >= current_date
INNER JOIN geodb_textdata AS td3 ON td3.loc_id = td2.loc_id
AND td3.text_type = 500100000
WHERE NOT EXISTS
(
        SELECT * FROM geodb_locations AS lc
        INNER JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id AND 
td1.text_type = 500100000 -- die Nummer des PLZ-Bereichs
        AND lc.loc_type = 100800000
        WHERE td1.text_val = td2.text_val
)
ORDER BY h.id_lvl2, td2.text_val;

Es liefert 17 Zeilen mit Dresden, Leipzig, Roitzsch u. a. Das ist etwas 
übersichtlicher.

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