GOV/Qualitätskontrolle

GOV-Hauptseite > GOV/Projekt > Qualitätskontrolle

Projekt GOV
hier: GOV/Qualitätskontrolle
GOV-Datenbankabfrage: Einfache Suche Erweiterte Suche Infoseiten zum Projekt: GOV/Projekt GOV/Datenmodell Datenerfassung: GOV/Anleitung GOV/Quicktext GOV/Objekttypen GOV/Datenerfassungen GOV/Mehrfachkennungen Kontakt: Online-Bereich für technische Fragen Kategorien: Kategorie:GOV Kategorie:GOV-Newsletter Kategorie:GOV-Intern Kategorie:GOV-Quelle

Fehlersuche in Datenbank

Objekte ohne Namen

Vielfach werden in GOV Kindobjekte aufgelistet, die keinen Namen haben und die damit auch nicht als Objekt editierbar oder löschbar sind. Diese Objekte sollten über einen Suchmechanismus gefunden werden können und dann gelöscht werden.

SELECT o.textual_id FROM gov_item o 
   LEFT JOIN property n ON n.gov_object=o.id AND n.type=1
   WHERE o.deleted=0 AND o.item_class='o' AND n.id IS NULL;

• 2018-03-14 kein Eintrag
• 2019-09-15 12 Einträge

Objekte ohne Typ

SELECT o.textual_id FROM gov_item o 
   LEFT JOIN property t ON t.gov_object=o.id AND t.type=2
   WHERE o.deleted=0 AND o.item_class='o' AND t.id IS NULL;

• 2018-03-14 keine Einträge
• 2019-09-15 6 Einträge

Kirchen ohne Standort

SELECT o.textual_id FROM gov_item o, property t
 LEFT JOIN relation r ON r.child=t.gov_object AND r.type=2
 WHERE t.type=2 AND t.type_object=26 
   AND o.id=t.gov_object AND r.parent IS NULL;

• 2012-05-13 13.876 Einträge
• 2019-09-15 9.961 Einträge

Eingeschränkt auf eine Landeskirche oder einen Kirchenkreis (hier 172369=Oppeln):

SELECT church.textual_id 
 FROM relation_index, gov_item church, property t 
 LEFT JOIN relation r ON r.child=t.gov_object AND r.type=2 
 WHERE relation_index.parent =172369 
  AND church.id=relation_index.child 
  AND t.gov_object=relation_index.child 
  AND t.type=2 AND type_object=26 
  AND r.parent IS NULL;

Kirchspiele ohne Kirche

Relevante Objekt-Typen:

• 29 (Kirchspiel)
• 42 (Pfarrei)
• 81 (Kloster)
• 92 (Kirchengemeinde)

SELECT o.textual_id FROM gov_item o, property t
   LEFT JOIN relation r ON r.parent=t.gov_object AND r.type=3
   WHERE t.type=2 and t.type_object IN (29,92,42,81) 
     AND o.id=t.gov_object and r.child IS NULL;

• 2019-09-15 5.413 Einträge

Religiöse Objekte ohne Konfession

Relevante Objekt-Typen:

• 26 (Kirche)
• 29 (Kirchspiel)
• 42 (Pfarrei)
• 81 (Kloster)
• 92 (Kirchengemeinde)

SELECT o.textual_id FROM gov_item o, property t
    LEFT JOIN property k ON k.gov_object=t.gov_object AND k.type=7
    WHERE t.type=2 and t.type_object IN (26,29,92,42,81)
      AND o.id=t.gov_object AND k.id IS NULL;

• 2012-05-13 1.133 Einträge
• 2019-09-15 1.306 Einträge

Orte und Ortsteile mit falscher PLZ

• Länge der PLZ = 1 (z.B. nur "O" oder "W")
• kein Jahr bis bei PLZ die mit "O" oder "W" beginnt
• in DE: Länge PLZ = 4

Orte und Koordinaten im übergeordneten Objekt

• Orte in einem Bundesland/Regierungsbezirk, die keine Koordinaten haben

SELECT c.textual_id, p.textual_id 
  FROM property, type, gov_item p, gov_item c, relation_index 
  WHERE parent = p.id AND child=c.id 
   AND p.textual_id='adm_369130'    # Kennung des übergeordneten Objekts
   AND property.gov_object=c.id 
   AND property.type=2 
   AND type.id=type_object 
   AND type.has_position=1         # Typ muß eine Position erlauben
   AND c.deleted=0                 # nicht gelöscht
   AND type_object<>18             # keine Gemeinde
   AND (c.latitude is null OR c.latitude=0);   # keine Position angegeben

• Orte die identische Koordinaten in einem Bundesland/Regierungsbezirk haben

Orte ohne Koordinate

Gelöschte Objekte und solche mit einem Typ, der keine Position zulässt, dürfen nicht mitgezählt werden.

SELECT count(distinct textual_id) FROM gov_item i 
    JOIN property p ON  i.id=p.gov_object AND p.type=2 
    JOIN Type t ON t.id=type_object 
    WHERE item_class='o' AND deleted=0 and (latitude is null or latitude=0) and has_position=1 AND type_object<>18;

• 2012-05-13 38.355 Einträge
• 2019-09-15 36.506 Einträge

Gelöschte Objekte mit Kindern

Ein gelöschtes Objekt darf nie als Elternobjekt eingetragen sein.

SELECT DISTINCT textual_id
  FROM gov_item p, relation r 
  WHERE p.deleted =1 AND parent =p.id;

• 2012-05-13 keine Einträge
• 2019-09-05 keine Einträge

auch nicht im relationenindex:

SELECT DISTINCT textual_id
  FROM gov_item, relation_index
  WHERE deleted = 1 AND parent=gov_item.id;

• 2012-05-13 keine Einträge
• 2019-09-05 85 Einträge

Gelöschte Objekte als Kind

Ein gelöschtes Objekt darf nicht als Kindobjekt eingetragen sein.

SELECT DISTINCT textual_id
  FROM gov_item p, relation r 
  WHERE p.deleted =1 AND child =p.id;

• 2019-09-15 24 Einträge

auch nicht im relationenindex:

SELECT DISTINCT textual_id
  FROM gov_item, relation_index
  WHERE deleted = 1 AND child=gov_item.id;

• 2019-09-15 72 Einträge

Orte an den Rändern von Einheiten

im Norden:

 SELECT c.* FROM gov_item i JOIN relation_index ON relation_index.parent=i.id JOIN gov_item c ON relation_index.child=c.id
   WHERE c.latitude IS NOT NULL 
    AND i.textual_id='OBJEKTKENNUNG' ORDER BY c.latitude DESC LIMIT 1;

im Süden:

 SELECT c.* FROM gov_item i JOIN relation_index ON relation_index.parent=i.id JOIN gov_item c ON relation_index.child=c.id
   WHERE c.latitude IS NOT NULL 
    AND i.textual_id='OBJEKTKENNUNG' ORDER BY c.latitude ASC LIMIT 1;

im Westen:

 SELECT c.* FROM gov_item i JOIN relation_index ON relation_index.parent=i.id JOIN gov_item c ON relation_index.child=c.id
   WHERE c.latitude IS NOT NULL 
    AND i.textual_id='OBJEKTKENNUNG' ORDER BY c.longitude ASC LIMIT 1;

im Osten:

 SELECT c.* FROM gov_item i JOIN relation_index ON relation_index.parent=i.id JOIN gov_item c ON relation_index.child=c.id
   WHERE c.latitude IS NOT NULL 
    AND i.textual_id='OBJEKTKENNUNG' ORDER BY c.longitude DESC LIMIT 1;

Gemeinden ohne OpenGeoDB-Zuordnung

Da OpenGeoDB alle deutschen Gemeinden enthält, müßte auch jede aktuelle Gemeinde im GOV eine externe Kennung opengeodb:... haben.

SELECT textual_id FROM property t, relation_index, gov_item 
  LEFT JOIN property o ON o.gov_object = gov_item.id AND o.type=6 AND o.content like 'opengeodb:%'
  WHERE t.type=2 and t.type_object=18 AND o.id is null AND t.gov_object = gov_item.id 
    AND (t.time_end IS NULL OR t.time_end > 30000000)                        # aktuelle Gemeinde
    AND relation_index.parent=149273 AND relation_index.child=gov_item.id;    # in Deutschland

• 2012-05-13 3.084 Einträge
• 2019-09-15 2.471 Einträge

Gemeinden ohne übergeordnetes Objekt

Für Gemeinden lassen sich einfach Zugehörigkeiten feststellen. Diese Gemeinden haben überhaupt keine Zugehörigkeit:

SELECT textual_id FROM property, gov_item 
  LEFT JOIN relation_index p ON p.child=gov_item.id 
  WHERE gov_item.deleted =0 and gov_item.id=property.gov_object
  AND property.type=2 and property.type_object =18 AND p.id is null;

• 2012-05-13 763 Einträge
• 2019-09-15 77 Einträge

vermischte Gemeinden und Dörfer

Um Ein- und Umgemeindungen korrekt modellieren zu können, ist es notwendig, eigene Gemeindeobjekt zu haben. Leider gibt es bereits viele Einträge, bei denen Gemeinde und Dorf vermischt wurden.

SELECT textual_id FROM property p1, property p2, gov_item
 WHERE  gov_item.id=p1.gov_object AND p1.type=2 AND p2.type=2
   AND p1.type_object <> p2.type_object AND p1.gov_object = p2.gov_object
   AND p1.type_object=18 AND p2.type_object<>247;

• 2012-05-13 17.661 Einträge

Diese Abfrage ist nicht korrekt, da sie auch erlaubte Wechsel (z.B. von Gemeinde zu Stadt) findet.

Speziell die Wechsel von Gemeinde zu Ortsteil (Wohnplatz) finden:

SELECT textual_id FROM property p1, property p2, gov_item  WHERE  gov_item.id=p1.gov_object AND p1.type=2 AND p2.type=2    AND p1.type_object <> p2.type_object AND p1.gov_object = p2.gov_object    AND p1.type_object=18 AND p2.type_object=40;

Eingeschränkt auf Kreis/Land etc:

SELECT DISTINCT c.textual_id 
  FROM property p1, property p2, gov_item c, gov_item p, relation_index  
  WHERE  c.id=p1.gov_object AND p1.type=2 AND p2.type=2
   AND p1.type_object <> p2.type_object AND p1.gov_object = p2.gov_object
   AND p1.type_object=18  AND p1.gov_object=relation_index.child AND relation_index.parent=p.id 
   AND p.textual_id='adm_369097';

kirchliche Abhängigkeiten

Die komplette Zugehörigkeit sieht so aus:

So findet man Objekte bei denen eine der Abhängigkeiten fehlt:

Verbindung B-C

SELECT a.textual_id, b.textual_id, c.textual_id 
FROM gov_item a, relation ac, relation ab, property at, (gov_item c, gov_item b)
LEFT JOIN relation bc ON bc.type=1 AND bc.child=b.id AND bc.parent=c.id
WHERE  at.gov_object=a.id AND at.type=2 AND at.type_object in (124,13,26,30)
AND ac.type=3 AND ab.type=2
AND ab.child=a.id AND ab.parent=b.id 
AND ac.child=a.id AND ac.parent=c.id
AND bc.parent IS NULL;

Hier sind viele "falsche" Treffer enthalten, wenn das Pfarrdorf noch nicht der Kirchengemeinde zugeordnet ist. Reparieren kann man aber in jedem Fall etwas.
2012-05-13 5292 Einträge

Verbindung A-B

SELECT a.textual_id, b.textual_id, c.textual_id 
FROM gov_item c, relation ac, property at, relation bc, (gov_item b, gov_item a)
LEFT JOIN relation ab ON ab.type=2 AND ab.child=a.id AND ab.parent=b.id
WHERE  at.gov_object=a.id AND at.type=2 AND at.type_object in (124,13,26,30)
AND ac.type=3 AND bc.type=1
AND bc.child=b.id AND bc.parent=c.id 
AND ac.child=a.id AND ac.parent=c.id
AND ab.parent IS NULL;

Hier sind viele "falsche" Treffer enthalten, wenn die Kirche (im GOV) noch nicht im Pfarrdorf steht. Reparieren kann man aber in jedem Fall etwas.
2012-05-13 8793 Einträge

Verbindung A-C

SELECT a.textual_id, b.textual_id, c.textual_id 
FROM gov_item b, relation ab, property at, relation bc, (gov_item c, gov_item a)
LEFT JOIN relation ac ON ac.type=2 AND ac.child=a.id AND ac.parent=c.id
WHERE  at.gov_object=a.id AND at.type=2 AND at.type_object in (124,13,26,30)
AND ab.type=3 AND bc.type=1
AND bc.child=b.id AND bc.parent=c.id 
AND ab.child=a.id AND ab.parent=c.id
AND ac.parent IS NULL;

Hier sind viele "falsche" Treffer enthalten, wenn die Kirche noch nicht die Kirchengemeinde repräsentiert. Reparieren kann man aber in jedem Fall etwas.
2012-05-13 10.360 Einträge

Ersatzkennungen für existierende Objekte

Sie entstehen, wenn man das Verschmelzen von zwei Objekten zurückgängig macht.

SELECT * FROM updated_ids, gov_item WHERE updated_ids.old_id=textual_id AND deleted=0 ;

• 2012-05-13 keine Einträge
• 2019-09-15 keine Einträge

Gemeinden, die zu Gemeinden gehören

Eine Gemeinde sollte nicht Teil einer anderen Gemeinde sein. Es liegt dann vermutlich in mindestens einem der beiden Objekte eine Vermischung von Dorf und Gemeinde vor.

SELECT DISTINCT c.textual_id AS child, p.textual_id AS parent 
 FROM property p1, property p2, gov_item c, gov_item p, relation_index 
 WHERE c.id=p1.gov_object AND p.id=p2.gov_object AND p1.type=2 AND p2.type=2 
 AND p1.type_object=18 AND p2.type_object=18 AND child=c.id AND parent=p.id;

• 2012-05-13 10.754 Einträge
• 2019-09-15 11.580 Einträge

Eingeschränkt auf ein Bundesland:

SELECT DISTINCT c.textual_id AS child, p.textual_id AS parent 
  FROM property p1, property p2, gov_item c, gov_item p, relation_index r1, relation_index r2
  WHERE c.id=p1.gov_object AND p.id=p2.gov_object AND p1.type=2 AND p2.type=2 
  AND p1.type_object=18 AND p2.type_object=18 AND r1.child=c.id AND r1.parent=p.id
  AND r2.child=p.id AND r2.parent=(SELECT id FROM gov_item WHERE textual_id='adm_369010');

Kreise 1871

alle Objekte, die im GOV zum Landkreis Plön gehören:

select count(*) from dr1871, gov_item p, relation_index
 WHERE dr1871.gov_id=p.textual_id AND relation_index.parent=p.id and dr1871.id=380
 group by dr1871.gov_id;

Objekte, die im GOV zum Landkreis Plön gehören und in der bounding box des Kreises 1871 liegen:

select dr1871.gov_id, count(distinct c.textual_id) as count
 from dr1871, gov_item p, gov_item c, relation_index
 WHERE dr1871.gov_id=p.textual_id AND relation_index.parent=p.id AND relation_index.child=c.id
   AND MBRContains(geom, Point( c.latitude, c.longitude)) and dr1871.id=380
 group by dr1871.gov_id;

Objekte, die im GOV zum Landkreis Plön gehören aber nicht der bounding box des Kreises 1871 liegen:

select dr1871.gov_id, count(distinct c.textual_id) as count
 from dr1871, gov_item p, gov_item c, relation_index
 WHERE dr1871.gov_id=p.textual_id AND relation_index.parent=p.id AND relation_index.child=c.id
   AND not MBRContains(geom, Point( c.latitude, c.longitude)) and dr1871.id=380
 group by dr1871.gov_id;

Amtlicher Gemeindeschlüssel

So kann man zählen, wie vielen Gemeinden ein Amtlicher Gemeindeschlüssel zugeordnet ist.

create temporary table ags ( ags char(8));
insert into ags select distinct content FROM gov_item, property, relation_index WHERE relation_index.parent=149273 AND gov_item.id=relation_index.child AND gov_item.id = property.gov_object and property.type=16 and length(content)=8;
select substring(ags,1,2) as bl , count(*) from ags group by bl;

Diese Zahlen kann man dann mit den bekannten AGS von 1993 und 2014 vergleichen und nach Bundesländern auswerten:

diff -u AGS-in-GOV.csv AGS-1993.txt  |grep ^+ | cut -c 2-3 | uniq -c

Die Abfrage beschränkt auf heute noch existierende Gemeinden:

create temporary table ags ( ags char(8));
insert into ags select distinct content FROM gov_item, property, relation_index WHERE relation_index.parent=149273 AND gov_item.id=relation_index.child AND gov_item.id = property.gov_object  and property.type=16 AND relation_index.time_end=2147483647;
select substring(ags,1,2) as bl , count(*) from ags group by bl;

Kennzahlen

Manche Kennzahlen geben Auskunft über die Entwicklung der Qualität der im GOV enthaltenen Daten.

Anzahl Orte mit Position

  SELECT count(*) FROM gov_item WHERE item_class ='o' AND latitude >0;

Anzahl Orte ohne Position

  SELECT count( distinct textual_id) 
   FROM gov_item i , property p, Type t  
   WHERE (latitude =0 or latitude is null) 
    and item_class='o' AND gov_object=i.id 
    AND property_class='t' and type_object=t.id 
    and t.locatedIn=1 ;

• Anzahl Orte ohne Elternobjekt
• Anzahl Werte mit Quellenangaben
• Verteilung Anzahl Elternobjekte
• Verteilung Verschachtelungstiefe
• Verteilung Anzahl Kindobjekte
• Objekte mit vielen Kindobjekte

Sonstiges

• Kreise 1871
• Qualitätskontrolle mit Taxo2Map