Hallo,
hier die versprochenden Ergebnisse der Analyse. Zuvor noch Anmerkungen als
Antwort auf einige Fragen:
Ich beziehe mich auf folgende Version:
opengeodb-0.2.4d-UTF8-postgres.zip (Download am 18.07.2006)
opengeodb-0.2.4d-UTF8-postgres.sql (aus der Zip-Datei, Datum 14.03.2006)
Alle Hinweise sind als Anregung zu verstehen, wie man die Qualität der Daten
verbessern könnte.
Bezüglich der Regeln für die Datenkonsistenz würde ich zunächst drei Fälle
unterscheiden:
Typ 1: Die Bedingung muss eingehalten werden. Ein Verstoß ist ein Fehler.
Typ 2: Das Einhalten der Bedingung ist angestrebt, aber z. B. mangels Daten
nicht erreicht. Ausnahmen müssen definiert sein. Ein Verstoß außerhalb der
definierten Ausnahmen ist ein Fehler.
Typ 3: Die Bedingung ist eingehalten, aber nicht als Regel formuliert. Das ist
eigentlich keine Regel. Die Formulierung einer entsprechenden Regel vom Typ 1
oder 2 oder die Klarstellung, dass es sich um einen Zufall handelt, könnte
sinnvoll sein.
Alle im folgenden genannten Regeln sind Vorschläge:
Typ-1-Regel: In geodb_hierarchies.id_lvln (n = 1, ..., 9) dürfen nur Verweise
auf geodb_locations.loc_id stehen.
Diese Bedingung ist eingehalten:
SELECT id_lvl1 FROM geodb_hierarchies
UNION
SELECT id_lvl2 FROM geodb_hierarchies
UNION
SELECT id_lvl3 FROM geodb_hierarchies
UNION
SELECT id_lvl4 FROM geodb_hierarchies
UNION
SELECT id_lvl5 FROM geodb_hierarchies
UNION
SELECT id_lvl6 FROM geodb_hierarchies
UNION
SELECT id_lvl7 FROM geodb_hierarchies
UNION
SELECT id_lvl8 FROM geodb_hierarchies
UNION
SELECT id_lvl9 FROM geodb_hierarchies
EXCEPT
SELECT loc_id FROM geodb_locations;
-- 1 Zeile mit NULL, OK
Typ-1-Regel: In geodb_textdata darf es keine Duplikate geben.
SELECT loc_id, text_val, text_type, text_locale, is_native_lang,
is_default_name,
valid_since, date_type_since, valid_until, date_type_until, count(*) AS
anzahl
FROM geodb_textdata
GROUP BY loc_id, text_val, text_type, text_locale, is_native_lang,
is_default_name,
valid_since, date_type_since, valid_until, date_type_until
HAVING count(*) > 1;
-- liefert PLZ 06308 bei loc_id 35877
Das wäre ein Verstoß gegen eine Typ-1-Regel.
Korrekturvorschlag: Datensätze löschen und einen neuen einfügen.
Es ist nicht möglich nur einen Datensatz zu löschen, da es keinen Schlüssel
gibt.
DELETE FROM geodb_textdata
WHERE loc_id = 35877 AND text_type = 500300000 AND text_val = '06308';
INSERT INTO geodb_textdata (loc_id, text_val, text_type, text_locale,
is_native_lang, is_default_name,
valid_since, date_type_since, valid_until, date_type_until) VALUES
(35877, '06308', 500300000, NULL, NULL, NULL, '2005-01-01', 300100000,
'3000-01-01', 300500000);
Typ-1-Regel: In geodb_hierarchies darf es keine Duplikate geben.
SELECT loc_id, level, id_lvl1, id_lvl2, id_lvl3, id_lvl4, id_lvl5, id_lvl6,
id_lvl7, id_lvl8, id_lvl9,
valid_since, date_type_since, valid_until, date_type_until, count(*) AS
anzahl
FROM geodb_hierarchies
GROUP BY loc_id, level, id_lvl1, id_lvl2, id_lvl3, id_lvl4, id_lvl5, id_lvl6,
id_lvl7, id_lvl8, id_lvl9,
valid_since, date_type_since, valid_until, date_type_until
HAVING count(*) > 1;
-- liefert einen Eintrag auf Level 7
Korrekturvorschlag: Datensätze löschen und einen neuen einfügen.
Es ist nicht möglich nur einen Datensatz zu löschen, da es keinen Schlüssel
gibt.
DELETE FROM geodb_hierarchies
WHERE (loc_id, level, id_lvl1, id_lvl2, id_lvl3, id_lvl4, id_lvl5, id_lvl6,
id_lvl7,
valid_since, date_type_since, valid_until, date_type_until) =
(17540, 7, 104, 105, 122, 0, 312, 35877, 17540, '2005-01-01' :: date,
300100000, '3000-01-01' :: date, 300500000)
AND id_lvl8 IS NULL AND id_lvl9 IS NULL;
INSERT INTO geodb_hierarchies (loc_id, level, id_lvl1, id_lvl2, id_lvl3,
id_lvl4, id_lvl5, id_lvl6, id_lvl7, id_lvl8, id_lvl9,
valid_since, date_type_since, valid_until, date_type_until)
VALUES (17540, 7, 104, 105, 122, 0, 312, 35877, 17540, NULL, NULL,
'2005-01-01', 300100000, '3000-01-01', 300500000);
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.
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;
-- 7 Ortsobjekte, die gleichzeitig auf Level 6 und Level 7 stehen
-- Anzeige der Hierarchiedaten dieser 7 Objekte
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000
WHERE h.loc_id IN (17178, 20325, 23495, 23956, 24413, 24716, 26649)
ORDER BY h.loc_id;
Korrekturvorschlag: die Gültigkeitsdaten der betroffenen Objekte korrigieren.
Typ-1-Regel: Eine Gemeinde darf nicht mehr als einen AGS haben.
Anmerkung: Es ist zu klären, ob eine Gemeinde zu verschiedenen Zeitpunkten
verschiedene AGS haben darf. Die folgende Abfrage berücksichtigt nur die
aktuellen Daten.
-- Gemeinden mit mehr als einem gültigen Gemeindeschlüssel
SELECT * FROM geodb_textdata AS td1
INNER JOIN -- filtern von Duplikaten im Subselect
(
SELECT td2.loc_id, count(*) AS anzahl FROM geodb_textdata AS td2
WHERE td2.text_type = 500600000 -- AGS
AND td2.valid_until >= current_date
GROUP BY td2.loc_id
HAVING count(*) > 1 -- mehr als ein Datensatz (AGS)
) AS t1 ON t1.loc_id = td1.loc_id
INNER JOIN geodb_textdata AS td3 ON td3.loc_id = td1.loc_id -- AGS anzeigen
AND td3.text_type = 500600000 -- AGS
AND td3.valid_until >= current_date
WHERE td1.text_type = 500100000 -- Name
ORDER BY td1.loc_id;
-- Anzeige aller Textdaten der betroffenen Objekte
SELECT * FROM geodb_hierarchies AS h
INNER JOIN geodb_textdata AS td ON td.loc_id = h.loc_id
WHERE h.loc_id IN (23577, 23578)
ORDER BY h.loc_id, td.text_type;
-- Korrektur (der AGS bezieht sich einfach nur auf das falsche Objekt, oder?)
UPDATE geodb_textdata
SET loc_id = 23578
WHERE loc_id = 23577 AND text_val = '13051086';
Aufpassen: Es könnten weitere Korrekturen erforderlich sein. Das habe ich nicht
geprüft.
Typ-1-Regel: Die AGS der Gemeinden eines Kreises in Deutschland müssen alle mit
denselben 5 Ziffern beginnen. (Ziffern 1-2 = Bundesland, Ziffer 3 =
Regierungsbezirk oder 0, Ziffern 4-5 = Kreis).
Diese Bedingung ist verletzt, wie zufällig das folgende Statement gezeigt hat:
-- Stichprobe für Vollständigkeit der Gemeinden (Landkreis Uckermark im Land
Brandenburg)
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.id_lvl2 = 105 -- alles in Deutschland
AND h.level = 6 AND lc.loc_type = 100700000 -- Gemeinden
AND h.valid_until >= current_date
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id
AND td1.text_type = 500100000 -- Name
AND td1.is_default_name
AND td1.valid_until >= current_date
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = lc.loc_id
AND td2.text_type = 500600000 -- AGS
AND td2.valid_until >= current_date
WHERE h.id_lvl5 = 309
AND td2.loc_id IS NOT NULL
ORDER BY td1.text_val;
-- Herzsprung ist falsch
-- Die Gemeinde gibt's nicht im Landkreis und AGS 12068192 passt nicht (AGS
muss mit 12073 beginnen)
-- Es gibt aber Herzsprung in der Gemeinde Angermünde mit AGS 12073008.
-- loc_id und Textdaten für alle Orte mit Namen Herzsprung anzeigen
SELECT * FROM geodb_textdata WHERE
loc_id IN
(
SELECT loc_id FROM geodb_textdata
WHERE text_val LIKE 'Herzsprung%'
)
ORDER BY loc_id, text_type;
-- 18274 und 18275
-- Hierarchie und Textdaten anzeigen für die beiden Herzsprung
SELECT * FROM geodb_hierarchies AS h
INNER JOIN geodb_textdata AS td ON td.loc_id = h.loc_id
WHERE h.loc_id IN (18274, 18275)
ORDER BY h.loc_id, td.text_type;
Korrekturvorschlag: Herzsprung muss auf Level 7 verschoben und der AGS gelöscht
werden, nur mit welchem Datum?
Die folgende Abfrage testet direkt die Bedingung:
-- Kreise mit Gemeinden, die einen falschen AGS haben
SELECT kreis_id, kreisname, count(*) AS anzahl FROM
(
SELECT DISTINCT kreis_id, kreisname, kreis_ags FROM
(
SELECT DISTINCT td.loc_id AS kreis_id, td.text_val AS
kreisname, td2.loc_id AS gemeinde_id, td2.text_val :: int / 1000 AS kreis_ags,
td3.text_val AS gemeindename
FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.id_lvl5 = td.loc_id AND
h.valid_until >= current_date AND td.valid_until >= current_date
INNER JOIN geodb_textdata AS td2 ON td2.loc_id = h.id_lvl6 AND
td2.text_type = 500600000 AND td2.valid_until >= current_date
INNER JOIN geodb_textdata AS td3 ON td3.loc_id = h.id_lvl6 AND
td3.text_type = 500100000 AND td3.valid_until >= current_date
--WHERE td.loc_id IN (309, 545, 329, 538)
WHERE h.level = 6
AND td.text_type = 500100000
) AS t1
) AS t2
GROUP BY kreis_id, kreisname
HAVING count(*) > 1;
-- bei Landkreis Uckermark hat Herzsprung einen falschen AGS, oder Herzsprung
gehört nicht zum Landkreis Uckermark
-- bei Landkreis Müritz ist ein zweites Satow aus einem anderen Kreis
hineingeraten
-- nach Korrektur 0 Zeilen
-- die betroffenen Landkreise (in der WHERE-Klausel die loc_id aufzählen)
SELECT DISTINCT td.loc_id AS kreis_id, td.text_val AS kreisname, td2.loc_id AS
gemeinde_id, td2.text_val :: int / 1000 AS kreis_ags,
td3.text_val AS gemeindename
FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.id_lvl5 = td.loc_id AND h.valid_until >=
current_date AND td.valid_until >= current_date
INNER JOIN geodb_textdata AS td2 ON td2.loc_id = h.id_lvl6 AND td2.text_type =
500600000 AND td2.valid_until >= current_date
INNER JOIN geodb_textdata AS td3 ON td3.loc_id = h.id_lvl6 AND td3.text_type =
500100000 AND td3.valid_until >= current_date
WHERE td.loc_id IN (309, 545, 329, 538)
AND h.level = 6
AND td.text_type = 500100000
-- nach Korrektur nicht mehr relevant
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?
-- testen, ob es PLZ-Bereiche gibt, bei denen die PLZ oder der Name fehlt
SELECT * FROM geodb_locations AS lc
LEFT JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id AND td1.text_type =
500100000
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = lc.loc_id AND td2.text_type =
500100004
WHERE lc.loc_type = 100800000
AND (td1.loc_id IS NULL OR td2.loc_id IS NULL)
ORDER BY td1.text_val, td1.loc_id;
-- 0 Datensätze
Zur Übersicht alle PLZ-Bereiche abfragen.
Die Einhaltung der letzten Regel wird vorausgesetzt, was die Verwendung von
INNER JOIN statt LEFT JOIN gestattet:
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id AND td1.text_type =
500100000
INNER JOIN geodb_textdata AS td2 ON td2.loc_id = lc.loc_id AND td2.text_type =
500100004
WHERE lc.loc_type = 100800000
ORDER BY td1.text_val, td1.loc_id;
-- 8270 PLZ
-- alles auf Level 6, 7 oder 8 (Gemeinde, Ort, Ortsteil)
-- alles unbegrenzt gültig
-- nur für DE
Typ-1-Regel: Jeder PLZ-Bereich hat genau eine PLZ.
-- Test, ob ein PLZ-Bereich mehrere PLZ hat
SELECT loc_id, count(*) AS anzahl FROM
(
SELECT DISTINCT td1.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
) AS t1
GROUP BY t1.loc_id;
-- OK
Typ-1-Regel: Jede PLZ hat genau einen PLZ-Bereich.
SELECT text_val, count(*) AS anzahl FROM
(
SELECT DISTINCT td1.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
) AS t1
GROUP BY t1.text_val;
-- OK
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:
-- unbenutzte PLZ-Bereiche mit benutzten Nummern
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 -- PLZ-Bereich
LEFT JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
INNER JOIN geodb_textdata AS td2 ON td2.text_val = td1.text_val AND
td2.text_type = 500300000 -- PLZ des Ortes
WHERE h.loc_id IS NULL; -- kein Eintrag in geodb_hierarchies fuer den
PLZ-Bereich
-- 0 Datensätze
Typ-2-Regel: Eine PLZ muss einen PLZ-Bereich haben. Ausnahmen: außerhalb
Deutschlands dürfen die PLZ-Bereiche fehlen (oder sogar: nicht existieren - was
nützen unvollständige PLZ-Bereichsdaten).
-- PLZ ohne PLZ-Bereich mit Staat
SELECT DISTINCT h.id_lvl2 AS staat, td2.text_val AS plz FROM geodb_textdata AS
td2
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td2.loc_id
AND td2.text_type = 500300000
AND 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 staat, plz;
-- 4825 Datensätze (nach den Korrekturen 4826)
-- PLZ ohne PLZ-Bereich mit Staat und Ort
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 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;
-- 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?
Typ-1-Regel: Eine PLZ darf bei einem Ort höchstens einmal gespeichert sein.
-- PLZ höchstens einmal bei einem Ort gespeichert
SELECT loc_id, plz FROM
(
SELECT loc_id, text_val AS plz FROM geodb_textdata AS td
WHERE valid_until >= current_date
AND text_type = 500300000 -- PLZ
) AS t1
GROUP BY loc_id, plz
HAVING count(*) > 1;
-- 0 Zeilen (1 Zeile vor der Entfernung der Duplikate oben)
-- alle Textdaten für die betroffenen Orte
SELECT * FROM geodb_textdata AS td1
INNER JOIN
(
SELECT loc_id, plz FROM
(
SELECT loc_id, text_val AS plz FROM geodb_textdata AS td
WHERE valid_until >= current_date
AND text_type = 500300000 -- PLZ
) AS t1
GROUP BY loc_id, plz
HAVING count(*) > 1
) AS t2 ON t2.loc_id = td1.loc_id
ORDER BY td1.loc_id, td1.text_type;
Typ-1-Regel: Auf jedes Objekt, dass keine PLZ ist, muss einmal in
geodb_hierarchies verwiesen werden.
SELECT loc_id FROM geodb_locations
EXCEPT
(
SELECT id_lvl1 FROM geodb_hierarchies
UNION
SELECT id_lvl2 FROM geodb_hierarchies
UNION
SELECT id_lvl3 FROM geodb_hierarchies
UNION
SELECT id_lvl4 FROM geodb_hierarchies
UNION
SELECT id_lvl5 FROM geodb_hierarchies
UNION
SELECT id_lvl6 FROM geodb_hierarchies
UNION
SELECT id_lvl7 FROM geodb_hierarchies
UNION
SELECT id_lvl8 FROM geodb_hierarchies
UNION
SELECT id_lvl9 FROM geodb_hierarchies
)
EXCEPT
SELECT loc_id FROM geodb_locations
WHERE loc_type = 100800000;
-- 0 Zeilen, OK
-- bei Weglassen des letzten EXCEPT kommen die 8270 Postleitzahlbereiche
Typ-1-Regel: bei einem Ort in geodb_hierarchies auf Level n muss loc_id mit
id_lvln übereinstimmen.
-- alle Orte (also alle Objekte, die keine PLZ-Bereiche sind) auf Level n,
-- bei denen loc_id nicht mit id_lvln übereinstimmt
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_textdata AS td ON td.loc_id = lc.loc_id
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
WHERE lc.loc_type <> 100800000 AND -- kein PLZ-Bereich
(
(level = 1 AND h.loc_id <> h.id_lvl1) OR
(level = 2 AND h.loc_id <> h.id_lvl2) OR
(level = 3 AND h.loc_id <> h.id_lvl3) OR
(level = 4 AND h.loc_id <> h.id_lvl4) OR
(level = 5 AND h.loc_id <> h.id_lvl5) OR
(level = 6 AND h.loc_id <> h.id_lvl6) OR
(level = 7 AND h.loc_id <> h.id_lvl7) OR
(level = 8 AND h.loc_id <> h.id_lvl8) OR
(level = 9 AND h.loc_id <> h.id_lvl9)
);
-- 4 Zeilen, betreffend loc_id 579, Kreisfreie Stadt Hannover
-- immer mit Verweis auf loc_id 32382, Landkreis Region Hannover
Typ-1-Regel: Zu jedem Zeitpunkt muss die Hierachie für einen Ort eindeutig
sein. Das ist nicht durch die Regel für die Duplikate oben abgedeckt. Es wäre
zum Beispiel denkbar, dass bei einem Ort A ein Verweis auf Gemeinde B und ein
Verweis auf Kreis C steht, bei Gemeinde A aber gleichzeitig ein Verweis auf
Kreis D. Die Hierarchie für Gemeinde B wäre nicht eindeutig.
Die folgenden Abfragen berücksichtigen nur die aktuellen Daten.
-- eindeutige "Vorgängerlisten" (nur für das aktuelle Datum)
-- Level 8, Ortsteile
SELECT * FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td.loc_id
WHERE h.loc_id IN
(
SELECT id_lvl8 FROM
(
SELECT DISTINCT h.id_lvl1, h.id_lvl2, h.id_lvl3, h.id_lvl4,
h.id_lvl5, h.id_lvl6, h.id_lvl7, h.id_lvl8
FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- kein PLZ-Bereich
WHERE level = 8
AND valid_until >= current_date -- aktueller Datensatz
) AS t1
GROUP BY id_lvl8
HAVING count(*) > 1 -- mehr als eine "Vorgänger"-Liste
)
ORDER BY h.loc_id, td.text_type;
-- Level 7, Orte
-- in den mit *** markierten Zeilen Level berücksichtigen
SELECT * FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td.loc_id
WHERE h.loc_id IN
(
SELECT id_lvl7 FROM -- ***
(
SELECT DISTINCT h.id_lvl1, h.id_lvl2, h.id_lvl3, h.id_lvl4,
h.id_lvl5, h.id_lvl6, h.id_lvl7 -- ***
FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- kein PLZ-Bereich
WHERE level = 7 -- ***
AND valid_until >= current_date -- aktueller Datensatz
) AS t1
GROUP BY id_lvl7 -- ***
HAVING count(*) > 1 -- mehr als eine "Vorgänger"-Liste
)
ORDER BY h.loc_id, h.valid_until, td.text_type;
-- Level 6, Gemeinden
-- in den mit *** markierten Zeilen Level berücksichtigen
SELECT * FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td.loc_id
AND td.text_type IN (500100000, 500300000, 500600000) -- nur Name, PLZ und AGS
anzeigen
WHERE h.loc_id IN
(
SELECT id_lvl6 FROM -- ***
(
SELECT DISTINCT h.id_lvl1, h.id_lvl2, h.id_lvl3, h.id_lvl4,
h.id_lvl5, h.id_lvl6 -- ***
FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- kein PLZ-Bereich
WHERE level = 6 -- ***
AND valid_until >= current_date -- aktueller Datensatz
) AS t1
GROUP BY id_lvl6 -- ***
HAVING count(*) > 1 -- mehr als eine "Vorgänger"-Liste
)
ORDER BY id_lvl2, h.loc_id, h.valid_until, td.text_type;
-- Level 5 Kreise
-- in den mit *** markierten Zeilen Level berücksichtigen
SELECT * FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td.loc_id
AND td.text_type IN (500100000, 500300000, 500600000) -- nur Name, PLZ und AGS
anzeigen
WHERE h.loc_id IN
(
SELECT id_lvl5 FROM -- ***
(
SELECT DISTINCT h.id_lvl1, h.id_lvl2, h.id_lvl3, h.id_lvl4,
h.id_lvl5 -- ***
FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- kein PLZ-Bereich
WHERE level = 5 -- ***
AND valid_until >= current_date -- aktueller Datensatz
) AS t1
GROUP BY id_lvl5 -- ***
HAVING count(*) > 1 -- mehr als eine "Vorgänger"-Liste
)
ORDER BY id_lvl2, h.loc_id, h.valid_until, td.text_type;
-- Level 4, Regierungsbezirke
-- in den mit *** markierten Zeilen Level berücksichtigen
SELECT * FROM geodb_textdata AS td
INNER JOIN geodb_hierarchies AS h ON h.loc_id = td.loc_id
AND td.text_type IN (500100000, 500300000) -- nur Name, PLZ anzeigen
WHERE h.loc_id IN
(
SELECT id_lvl4 FROM -- ***
(
SELECT DISTINCT h.id_lvl1, h.id_lvl2, h.id_lvl3, h.id_lvl4 --
***
FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND lc.loc_type <> 100800000 -- kein PLZ-Bereich
WHERE level = 4 -- ***
AND valid_until >= current_date -- aktueller Datensatz
) AS t1
GROUP BY id_lvl4 -- ***
HAVING count(*) > 1 -- mehr als eine "Vorgänger"-Liste
)
ORDER BY id_lvl2, h.loc_id, h.valid_until, td.text_type;
-- 15 Zeilen mit loc_id 0
-- Ausnahmeregel: wenn bei id_lvl4 0 steht, darf es unterschiedliche
Vorgängerlisten geben
-- Allgemeiner: wenn ein übergeordneter Level nicht verwendet wird.
Typ-2-Regel: Jeder Ort (Level 6 bis 8) hat eine PLZ. Was sind die Ausnahmen?
Oder besser Typ-1-Regel?
-- Orte ohne PLZ
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.valid_until >= current_date
AND h.level >= 6
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = h.loc_id
AND td1.text_type = 500100000 AND is_default_name -- Name
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = h.loc_id
AND td2.text_type = 500300000 -- PLZ
WHERE td2.loc_id IS NULL
ORDER BY h.id_lvl2, h.id_lvl3, h.id_lvl5, h.loc_id, h.valid_until;
-- 25 Zeilen
Typ-2-Regel: Jeder Ort (in Deutschland?) auf Level 6 hat einen AGS. Was sind
die Ausnahmen?
-- Orte auf Level 6, die keinen AGS haben (Gemeinden in Deutschland)
-- Gemeinden in Deutschland ohne Schlüssel
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.valid_until >= current_date
AND h.level = 6
AND h.id_lvl2 = 105 -- Deutschland, sonst sind sowieso keine AGS vorhanden
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = lc.loc_id
AND td1.text_type = 500100000 AND is_default_name -- Name
AND td1.valid_until >= current_date
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = lc.loc_id
AND td2.text_type = 500600000 -- AGS
AND td2.valid_until >= current_date -- ergibt 1 Zeile mehr
WHERE td2.loc_id IS NULL
ORDER BY h.id_lvl2, h.id_lvl3, h.id_lvl5, h.loc_id, h.valid_until;
Wenn das "normale" Orte sind, wie jemand geschrieben hat, was sind dann Orte
auf Level 7?
-- Orte auf Level 7, die einen Verweis auf einen "normalen" Ort auf Level 6
haben
-- Orte mit Verweis auf Gemeinden (Level 6) ohne AGS
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.valid_until >= current_date
AND h.level = 7
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = h.loc_id
AND td1.text_type = 500100000 AND is_default_name -- Name
AND td1.valid_until >= current_date
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = h.id_lvl6 -- auf diese
Bedingung achten
AND td2.text_type = 500600000 -- AGS
AND td2.valid_until >= current_date -- ergibt 3 Zeilen mehr
WHERE td2.loc_id IS NULL
ORDER BY h.id_lvl2, h.id_lvl3, h.id_lvl5, h.loc_id, h.valid_until;
-- 2738 Zeilen
-- Ortsteile (Level 8) in Belgien mit Verweisen auf Gemeinden ohne
Gemeindeschlüssel
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
--AND h.valid_until >= current_date
AND h.level = 8
AND h.id_lvl2 = 633 -- Belgien
--AND h.id_lvl7 < 2 -- unbekannter Ort?
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = h.loc_id
AND td1.text_type = 500100000 AND is_default_name -- Name
AND td1.valid_until >= current_date;
-- 29 Zeilen, davon 13 mit id_lvl7 = -1
-- Ortsteile in Deutschland mit Verweisen auf Gemeinden ohne Gemeindeschlüssel
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.valid_until >= current_date -- erhebliche Auswirkung
AND h.level = 8
AND h.id_lvl2 = 105 -- Deutschland
--AND h.id_lvl7 < 2 -- unbekannter Ort?
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = h.loc_id
AND td1.text_type = 500100000 AND is_default_name -- Name
AND td1.valid_until >= current_date
LEFT JOIN geodb_textdata AS td2 ON td2.loc_id = h.id_lvl6 -- auf diese
Bedingung achten
AND td2.text_type = 500600000 -- AGS
AND td2.valid_until >= current_date
WHERE td2.loc_id IS NULL;
-- 0 Zeilen, OK
Typ-1-Regel: Wird bei einem Ort eine PLZ eingetragen, so muss die PLZ auch bei
der übergeordneten Gemeinde eingetragen sein.
-- prüfen, ob für jeden Ort mit PLZ dieselbe PLZ bei der Gemeinde vorhanden ist
SELECT * FROM geodb_locations AS lc
INNER JOIN geodb_hierarchies AS h ON h.loc_id = lc.loc_id
AND h.valid_until >= current_date -- mit Wirkung
AND h.level IN (7, 8)
AND lc.loc_type <> 100800000
INNER JOIN geodb_textdata AS td1 ON td1.loc_id = h.loc_id
AND td1.text_type = 500100000 AND td1.is_default_name -- Name
AND td1.valid_until >= current_date
INNER JOIN geodb_textdata AS td2 ON td2.loc_id = h.loc_id
AND td2.text_type = 500300000 -- PLZ
AND td2.valid_until >= current_date -- mit Wirkung
INNER JOIN geodb_textdata AS td3 ON td3.loc_id = h.id_lvl6
AND td3.text_type = 500100000 AND td3.is_default_name -- Name
INNER JOIN geodb_textdata AS td4 ON td4.loc_id = h.id_lvl6
AND td4.text_type = 500600000 -- AGS
AND td4.valid_until >= current_date -- mit Wirkung
LEFT JOIN geodb_textdata AS td5 ON td5.loc_id = h.id_lvl6
AND td5.text_type = 500300000 -- PLZ
AND td5.valid_until >= current_date -- ohne Wirkung
AND td5.text_val = td2.text_val -- dieselbe PLZ vorhanden?
WHERE td5.loc_id IS NULL -- dieselbe PLZ bei Gemeinde nicht vorhanden
ORDER BY h.id_lvl2, h.id_lvl3, h.id_lvl5, h.loc_id, h.valid_until;
-- 92 Zeilen (nach Korrekturen 94)
Frage: Kommt es tatsächlich vor, dass eine PLZ in verschiedenen Kreisen benutzt
wird?
-- dieselbe PLZ in verschiedenen Kreisen?
SELECT t1.id_lvl2, t1.plz, count(*) AS anzahl FROM
(
SELECT DISTINCT h.id_lvl2, h.id_lvl5, td.text_val AS plz FROM
geodb_hierarchies AS h
INNER JOIN geodb_textdata AS td ON td.loc_id = h.loc_id
AND td.text_type = 500300000 -- PLZ
) AS t1
GROUP BY t1.id_lvl2, t1.plz
HAVING count(*) > 1
ORDER BY t1.id_lvl2, t1.plz;
-- 229 Zeilen
-- Ist das korrekt?
Gruß Ingmar
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb-r1mDYR0DdAyzQB+pC5nmwQ@xxxxxxxxxxxxxxxx
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)
|
