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

--[[Benutzer:Mschulz|Mschulz]] 15:52, 17. Jan 2013 (CET)
<br><br>
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%. 
<br>Für die Ersterfassung von ca. 150 Adress-Punkten ist das aber auch schon eine Erleichterung.
<br><br>
Folgende Schritte müssen ausgeführt werden (hier am Beispiel KUNST OFFEN):
<br>
===== 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:
--[[Benutzer:Mschulz|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)
<br>
'''''Die übrig gelbliebenen Datebsätze müssen nun natürlich wieder über den GLE erfasst werden.'''''