Automatische Erfassung von Punktgeometrien auf Grundlage von Adressdaten (z.B. aus Excel)

--Mschulz 15:52, 17. Jan 2013 (CET)

Um Mitarbeitern aus den Fachämtern den Einstieg in kvwmap zu erleichtern, habe ich die Ersterfassung von Punktgeometrien aus Adressdaten versucht zu automatisieren. Das funktioniert allerdings nur bei exakter Schreibweise der Straßen und Gemarkungen bzw. Orte müssen Gemarkungen sein; hat bei mir eine Trefferquote um die 50-70%.
Für die Ersterfassung von ca. 150 Adress-Punkten ist das aber auch schon eine Erleichterung.

Folgende Schritte müssen ausgeführt werden (hier am Beispiel KUNST OFFEN):

VIEW erstellen

• Zunächst legt man einen View an, der sich alle Straßen und Hausnummern mit Gemeinden, Gemarkungen und der entsprechenden Punktgeometrie (Beschriftung der Hausnummer) aus den entsprechenden Tabellen holt:

--Mschulz 14:20, 27. Nov 2013 (CET) editiert (einige Fehler wurden beseitigt z.B. Beschriftungen wie Tankstelle usw.)

CREATE OR REPLACE VIEW adressen_lk AS 
SELECT DISTINCT h.gemeinde,g.gemeindename, gem.gemkgschl,substr(a.flurstkennz::text, 1, 6) AS gemarkung, 
                gem.gemkgname, h.strasse,  s.strassenname, p.label, 
                (s.strassenname::text || ' '::text) || btrim(p.label::text) AS adresse, p.the_geom
           FROM alknhaus h, alkobj_t_pkt p, alb_f_adressen a, alb_v_gemeinden g, alb_v_gemarkungen gem, alb_v_strassen s
          WHERE h.lfdnr::text = '001'::text AND h.objnr::text = p.objnr::text AND 
                 (substr(btrim(p.label::text), 1, 1) = '0'::text OR substr(btrim(p.label::text), 1, 1) = '1'::text 
               OR substr(btrim(p.label::text), 1, 1) = '2'::text OR substr(btrim(p.label::text), 1, 1) = '3'::text 
               OR substr(btrim(p.label::text), 1, 1) = '4'::text OR substr(btrim(p.label::text), 1, 1) = '5'::text 
               OR substr(btrim(p.label::text), 1, 1) = '6'::text OR substr(btrim(p.label::text), 1, 1) = '7'::text 
               OR substr(btrim(p.label::text), 1, 1) = '8'::text OR substr(btrim(p.label::text), 1, 1) = '9'::text) 
               AND a.gemeinde = h.gemeinde AND btrim(a.strasse::text) = btrim(h.strasse::text) 
               AND btrim(upper(a.hausnr::text)) = btrim(upper(h.hausnr::text)) AND g.gemeinde = h.gemeinde 
               AND gem.gemkgschl = substr(a.flurstkennz::text, 1, 6)::integer AND s.strasse::text = h.strasse::text 
               AND h.gemeinde = s.gemeinde
       ORDER BY h.gemeinde, h.strasse, p.label, substr(a.flurstkennz::text, 1, 6);

CSV erstellen

• Excel-Tabelle in CSV-Format bringen z.B. Kunst-Offen mit Bezeichnung, Adresse(Straße & Hausnummer), Ort
• plus zusätzliches Feld erstellen mit fortlaufender Nummer (ID)

DB-Tabelle

• entsprechende Datenbank-Tabelle erstellen

CREATE TABLE kunstoffen
(
  bezeichnung character varying(50),
  adresse character varying(200),
  ort character varying(50),
  id character varying(6)
)

COPY

• CSV in die Datenbanktabelle einspielen

COPY kunstoffen FROM '/PFAD/ZUR/CSV/kunstoffen.csv' DELIMITER AS ';';

Geometrietabelle

• zusätzliche (zweite) DB-Tabelle mit Geometriefeld anlegen

CREATE TABLE kunstoffen_geom
(
 bezeichnung character varying(50),
 adresse character varying(200),
 ort character varying(50),
 id character varying(6),
 the_geom geometry,
 CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
 CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 2398)
)

Punkte auf Hausnummern automatisch setzen

• Punkte automatisch setzen über View adressen_lk (siehe oben) und Adressfeld in der erstellten DB-Tabelle
• -> Ergebnis sind automatisch gesetzte Punktgeometrien auf Basis der Hausnummernbeschriftung der ALK

INSERT INTO kunstoffen_geom
SELECT a.*, b.the_geom 
FROM kunstoffen as a, adressen_lk as b
WHERE trim(a.ort)=trim(b.gemkgname) 
AND (lower(a.adresse)=lower(b.adresse) 
OR replace(lower(a.adresse), 'straße', 'str.')=lower(b.adresse))
ORDER BY ort

• bezogen auf mein Beispiel (KUNSTOFFEN):
  • die mögliche Abkürzung straße -> str. wurde mit einbezogen, zusätzliche sind natürlich denkbar
  • die Attribute Straße und Hausnummer sind in der Spalte Adresse vereint - bei getrennter Straße und Hausnummer muss adresse durch trim(strasse)||' '||trim(hausnummer) ersetzt werden

Doppelte Datensätze löschen

• Nun noch die Datensätze aus der originalen Tabelle über die ID löschen.

DELETE FROM kunstoffen
WHERE id IN (SELECT id FROM kunstoffen_geom)

Die übrig gelbliebenen Datebsätze müssen nun natürlich wieder über den GLE erfasst werden.