source: trunk/import/alkis_db_patch_2014-08.sql @ 323

Revision 323, 14.2 KB checked in by frank.jaeger, 7 years ago (diff)

SQL-Script für Reparatur von "Darstellung von Straßennamen in der Karte".

Line 
1
2-- Straßennamen als P und L-Variante
3-- =================================
4
5-- Bisher wurden die Namen von Straßen, Wegen und Klassifizierungen nur angezeigt, wenn sie
6-- als Punkt über die Tabelle "ap_pto" positioniert waren.
7-- Nun tauchen auch Texte auf, die über Linien positioniert sind (ap_lto).
8
9-- Dies Script passt die Datenbank an. Ein überarbeitetes Mapfile muss die neue Tabelle präsentiern.
10
11-- Es wurden mehrere Scripte angepasst.
12-- Diese treten in Kraft, sobald eine Datenbank neu angelegt und neu geladen wird.
13
14-- Dies Script patcht eine bereits bestehende Datenbank.
15-- Dies Script ist nur temporär im Einsatz und wird darum nicht langfristig gepflegt.
16
17-- Stand 2014-08-25
18
19
20-- *** aus "pp_definition.sql"
21
22-- Variante für Punkt-Geometrie
23CREATE TABLE pp_strassenname_p
24(   gid                    serial NOT NULL,
25    gml_id                 character(16),
26 -- advstandardmodell      character varying[],
27    schriftinhalt          character varying,      -- Label: anzuzeigender Text
28    hor                    character varying,
29    ver                    character varying,
30 -- signaturnummer         character varying,
31 -- darstellungsprioritaet integer,
32    art                    character varying,
33    winkel                 double precision,
34    CONSTRAINT pp_snamp_pk  PRIMARY KEY (gid)
35) WITH (OIDS=FALSE);
36
37-- :alkis_epsg = 25832
38SELECT AddGeometryColumn('pp_strassenname_p','the_geom',25832,'POINT',2);
39CREATE INDEX pp_snamp_gidx ON pp_strassenname_p USING gist(the_geom);
40
41  COMMENT ON TABLE  pp_strassenname_p                IS 'Post-Processing: Label der Straßennamen in der Karte, Punktgeometrie. Auszug aus ap_pto.';
42
43  COMMENT ON COLUMN pp_strassenname_p.gid            IS 'Editierschlüssel der Tabelle';
44--COMMENT ON COLUMN pp_strassenname_p.gml_id         IS 'Objektschlüssel des Präsentationsobjektes aus ap_pto. Zur Verbindung mit Katalog.';
45  COMMENT ON COLUMN pp_strassenname_p.gml_id         IS 'Objektschlüssel des Präsentationsobjektes aus "ax_lagebezeichnungohnehausnummer". Zur Verbindung mit Katalog beim Nachladen leerer Felder.';
46  COMMENT ON COLUMN pp_strassenname_p.schriftinhalt  IS 'Label, darzustellender Name der Straße oder Klassifikation';
47  COMMENT ON COLUMN pp_strassenname_p.hor            IS 'Horizontale Ausrichtung des Textes zur Punkt-Koordinate: linksbündig, zentrisch, ...';
48  COMMENT ON COLUMN pp_strassenname_p.ver            IS 'Vertikale   Ausrichtung des Textes zur Punkt-Koordinate: Basis, ..';
49  COMMENT ON COLUMN pp_strassenname_p.art            IS 'Klasse der Straße: Straße, Weg, .. , BezKlassifizierungStrasse';
50  COMMENT ON COLUMN pp_strassenname_p.winkel         IS 'Drehung des Textes';
51  COMMENT ON COLUMN pp_strassenname_p.the_geom       IS 'Position (Punkt) der Labels in der Karte';
52
53-- Variante für Linien-Geometrie
54CREATE TABLE pp_strassenname_l
55(   gid                    serial NOT NULL,
56    gml_id                 character(16),
57    schriftinhalt          character varying,      -- Label: anzuzeigender Text
58    hor                    character varying,
59    ver                    character varying,
60    art                    character varying,
61 -- winkel                 double precision,       -- bei Linien-Variante nicht benötigt
62    CONSTRAINT pp_snaml_pk  PRIMARY KEY (gid)
63) WITH (OIDS=FALSE);
64
65-- :alkis_epsg = 25832
66SELECT AddGeometryColumn('pp_strassenname_l','the_geom',25832,'LINESTRING',2); -- Hier liegt der Unterschied
67CREATE INDEX pp_snaml_gidx ON pp_strassenname USING gist(the_geom);
68
69  COMMENT ON TABLE  pp_strassenname_l                IS 'Post-Processing: Label der Straßennamen in der Karte, Liniengeometrie. Auszug aus ap_lto.';
70
71  COMMENT ON COLUMN pp_strassenname_l.gid            IS 'Editierschlüssel der Tabelle';
72  COMMENT ON COLUMN pp_strassenname_l.gml_id         IS 'Objektschlüssel des Präsentationsobjektes aus "ax_lagebezeichnungohnehausnummer". Zur Verbindung mit Katalog beim Nachladen leerer Felder.';
73  COMMENT ON COLUMN pp_strassenname_l.schriftinhalt  IS 'Label, darzustellender Name der Straße oder Klassifikation';
74  COMMENT ON COLUMN pp_strassenname_l.hor            IS 'Horizontale Ausrichtung des Textes: linksbündig, zentrisch, ...';
75  COMMENT ON COLUMN pp_strassenname_l.ver            IS 'Vertikale   Ausrichtung des Textes: Basis, ..';
76  COMMENT ON COLUMN pp_strassenname_l.art            IS 'Klasse der Straße: Straße, Weg, .. , BezKlassifizierungStrasse';
77  COMMENT ON COLUMN pp_strassenname_l.the_geom       IS 'Position (Punkt) der Labels in der Karte';
78
79
80--  *** aus "sichten_wms.sql"
81
82-- HIER START für Wiederholung
83
84DROP VIEW ap_pto_stra;
85
86CREATE OR REPLACE VIEW ap_pto_stra
87AS
88  SELECT p.ogc_fid,
89         l.gml_id,                               -- wird im PP zum Nachladen aus Katalog gebraucht
90         p.schriftinhalt,                        -- WMS: LABELITEM
91         p.art,                                  -- WMS: CLASSITEM
92         p.horizontaleausrichtung  AS hor,       -- Verfeinern der Text-Position ..
93         p.vertikaleausrichtung    AS ver,       --  .. durch Klassifizierung hor/ver
94         p.drehwinkel * 57.296     AS winkel,    -- * 180 / Pi
95         p.wkb_geometry
96    FROM ap_pto p
97    JOIN alkis_beziehungen v   -- Relation zur Lagebezeichnung o. HsNr.
98      ON p.gml_id = v.beziehung_von
99    JOIN ax_lagebezeichnungohnehausnummer l
100      ON v.beziehung_zu = l.gml_id
101   WHERE  p.endet IS NULL                            -- nichts historisches
102     AND  p.art   IN ('Strasse','Weg','Platz','BezKlassifizierungStrasse') -- Diese Werte als CLASSES in LAYER behandeln.
103     AND  v.beziehungsart = 'dientZurDarstellungVon' -- kann, muss aber nicht
104     AND (   'DKKM1000' = ANY (p.advstandardmodell)  -- "Lika 1000" bevorzugen
105          OR 'DLKM'     = ANY (p.advstandardmodell)   
106     -- Leopoldshöhe, Heinestraße: 'DLKM'
107           -- Ersatzweise auch "keine Angabe", aber nur wenn es keinen besseren Text zur Lage gibt
108           OR (p.advstandardmodell IS NULL
109               AND (SELECT s.ogc_fid                -- irgend ein Feld
110                      FROM ap_pto s                 -- eines anderen Textes (suchen)
111                      JOIN alkis_beziehungen vs     -- zur gleichen Lage o.HsNr
112                        ON s.gml_id = vs.beziehung_von
113                      JOIN ax_lagebezeichnungohnehausnummer ls
114                        ON vs.beziehung_zu = ls.gml_id
115                     WHERE ls.gml_id = l.gml_id
116                       AND vs.beziehungsart = 'dientZurDarstellungVon' -- kann, muss aber nicht
117                       AND NOT s.advstandardmodell IS NULL
118                     LIMIT 1  -- einer reicht als Beweis
119                   ) IS NULL
120              ) -- "Subquery IS NULL" liefert true wenn kein weiterer Text gefunden wird
121         )
122;
123
124COMMENT ON VIEW ap_pto_stra
125  IS 'Sicht für Kartendarstellung: Beschriftung aus "ap_pto" für Lagebezeichnung mit Art "Straße", "Weg", "Platz" oder Klassifizierung.
126 Vorzugsweise mit advstandardmodell="DKKM1000", ersatzweise ohne Angabe. Dient im Script pp_laden.sql zum ersten Füllen der Tabelle "pp_strassenname_p".';
127
128-- Daten aus dem View "ap_pto_stra" werden im PostProcessing gespeichert in der Tabelle "pp_strassenname_p".
129-- Der View übernimmt die Auswahl des passenden advstandardmodell und rechnet den Winkel passend um,
130-- In der Tabelle werden dann die leer gebliebenen Label aus dem Katalog noch ergänzt.
131
132
133DROP VIEW ap_lto_stra;
134
135CREATE OR REPLACE VIEW ap_lto_stra
136AS
137  SELECT p.ogc_fid,
138         l.gml_id,                               -- wird im PP zum Nachladen aus Katalog gebraucht
139         p.schriftinhalt,                        -- WMS: LABELITEM
140         p.art,                                  -- WMS: CLASSITEM
141         p.horizontaleausrichtung  AS hor,       -- Verfeinern der Text-Position ..
142         p.vertikaleausrichtung    AS ver,       --  .. durch Klassifizierung hor/ver
143         p.wkb_geometry
144    FROM ap_lto p
145    JOIN alkis_beziehungen v   -- Relation zur Lagebezeichnung o. HsNr.
146      ON p.gml_id = v.beziehung_von
147    JOIN ax_lagebezeichnungohnehausnummer l
148      ON v.beziehung_zu = l.gml_id
149   WHERE  p.endet IS NULL                            -- nichts historisches
150     AND  p.art   IN ('Strasse','Weg','Platz','BezKlassifizierungStrasse') -- Diese Werte als CLASSES in LAYER behandeln.
151     AND  v.beziehungsart = 'dientZurDarstellungVon' -- kann, muss aber nicht
152
153--   AND (   ('DKKM1000' = ANY (p.advstandardmodell)     -- "Lika 1000" bevorzugen
154--        OR ('DLKM'     = ANY (p.advstandardmodell)  )
155
156     -- ++ Muss als Array angelegt sein!!
157     AND ( NOT p.advstandardmodell  IS NULL          -- ++ Zwischenlösung bis DB mit neuem Schema (2014-08-22) angelegt und geladen wurde ++
158
159           -- Ersatzweise auch "keine Angabe", aber nur wenn es keinen besseren Text zur Lage gibt
160           OR (p.advstandardmodell IS NULL
161               AND (SELECT s.ogc_fid                -- irgend ein Feld
162                      FROM ap_lto s                 -- eines anderen Textes (suchen)
163                      JOIN alkis_beziehungen vs     -- zur gleichen Lage o.HsNr
164                        ON s.gml_id = vs.beziehung_von
165                      JOIN ax_lagebezeichnungohnehausnummer ls
166                        ON vs.beziehung_zu = ls.gml_id
167                     WHERE ls.gml_id = l.gml_id
168                       AND vs.beziehungsart = 'dientZurDarstellungVon' -- kann, muss aber nicht
169                       AND NOT s.advstandardmodell IS NULL
170                     LIMIT 1  -- einer reicht als Beweis
171                   ) IS NULL
172              ) -- "Subquery IS NULL" liefert true wenn kein weiterer Text gefunden wird
173         )
174;
175COMMENT ON VIEW ap_lto_stra
176  IS 'Sicht für Kartendarstellung: Beschriftung aus "ap_lto" für Lagebezeichnung mit Art "Straße", "Weg", "Platz" oder Klassifizierung.
177 Vorzugsweise mit advstandardmodell="DKKM1000", ersatzweise ohne Angabe. Dient im Script pp_laden.sql zum ersten Füllen der Tabelle "pp_strassenname_l".';
178
179-- 2014-08-22: Daten aus dem View "ap_lto_stra" werden im PostProcessing gespeichert in den Tabellen "pp_strassenname_l".
180-- Der View übernimmt die Auswahl des passenden advstandardmodell.
181-- In der Tabelle werden dann die leer gebliebenen Label aus dem Katalog noch ergänzt.
182
183
184-- *** aus "pp_laden.sql"
185
186-- Alles auf Anfang
187TRUNCATE pp_strassenname_p;
188
189-- Zunächst die Sonderschreibweisen (Abkürzungen) und die Standardschreibweisen,
190-- die von der Migration redundant abgelegt wurden.
191INSERT INTO pp_strassenname_p (gml_id, schriftinhalt, hor, ver, art, winkel, the_geom)
192       SELECT gml_id, schriftinhalt, hor, ver, art, winkel, wkb_geometry
193       FROM ap_pto_stra; -- Der View sucht das passende advstandardmodell
194
195-- Schriftinhalt ergänzen
196-- Das sind die Standardschreibweisen aus dem Katalog, die nicht mehr redundant in ap_pto sind.
197UPDATE pp_strassenname_p  p
198   SET schriftinhalt =     -- Hier ist der Label noch leer
199   -- Subquery "Gib mir den Straßennamen":
200   ( SELECT k.bezeichnung                         -- Straßenname ..
201       FROM ax_lagebezeichnungkatalogeintrag k    --  .. aus Katalog
202       JOIN ax_lagebezeichnungohnehausnummer l    -- verwendet als Lage o.H.
203         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
204      WHERE p.gml_id = l.gml_id                   -- die gml_id wurde aus View importiert
205    )
206 WHERE     p.schriftinhalt IS NULL
207   AND NOT p.the_geom      IS NULL;
208
209-- Die immer noch leeren Texte sind nun sinnlos.
210-- Die finden sich ggf. in der Variante "_l" mit Liniengeometrie.
211DELETE FROM pp_strassenname_p WHERE schriftinhalt IS NULL;
212
213-- Nun das Gleiche noch einmal für Linien-Geometrie
214
215-- Auf Anfang
216TRUNCATE pp_strassenname_l;
217
218-- Zunächst die Sonderschreibweisen (Abkürzungen) und die Standardschreibweisen,
219-- die von der Migration redundant abgelegt wurden.
220INSERT INTO pp_strassenname_l (gml_id, schriftinhalt, hor, ver, art, the_geom)
221       SELECT gml_id, schriftinhalt, hor, ver, art, wkb_geometry
222       FROM ap_lto_stra; -- Der View sucht das passende advstandardmodell
223
224-- Schriftinhalt ergänzen (korrigiert 2014-08-25)
225-- Das sind die Standardschreibweisen aus dem Katalog, die nicht mehr redundant in ap_pto sind.
226-- Der Satz mit der passenen gml_id (Lage o.H.) ist aus dem View bereits importiert.
227-- Jetzt noch den dazu passenen Schriftinhalt aus dem Katalog holen.
228UPDATE pp_strassenname_l  p
229   SET schriftinhalt =     -- Hier ist der Label noch leer
230   -- Subquery "Gib mir den Straßennamen":
231   ( SELECT k.bezeichnung                         -- Straßenname ..
232       FROM ax_lagebezeichnungkatalogeintrag k    --  .. aus Katalog
233       JOIN ax_lagebezeichnungohnehausnummer l    -- verwendet als Lage o.H.
234         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
235      WHERE p.gml_id = l.gml_id                   -- die gml_id wurde aus View importiert
236    )
237 WHERE     p.schriftinhalt IS NULL
238   AND NOT p.the_geom      IS NULL;
239
240-- Die immer noch leeren Texte sind sinnlos.
241DELETE FROM pp_strassenname_l WHERE schriftinhalt IS NULL;
242
243
244-- *** aus "grant.sql"
245
246-- Berechtigungen
247
248  GRANT SELECT ON TABLE  pp_strassenname_p                 TO ms6;
249  GRANT SELECT ON TABLE  pp_strassenname_l                 TO ms6;
250
251
252-- Zwischenzeitlich auch noch die alte Tabelle (ohne _P + _L-Trennung) aktualisieren
253-- Wirkt in Produktion bis zur Einführung der angepassten Mapfiles (Entwickler-Version)
254
255-- Alles auf Anfang
256TRUNCATE pp_strassenname;
257
258INSERT INTO pp_strassenname (gml_id, schriftinhalt, hor, ver, art, winkel, the_geom)
259       SELECT gml_id, schriftinhalt, hor, ver, art, winkel, wkb_geometry
260       FROM ap_pto_stra;
261
262UPDATE pp_strassenname  p
263   SET schriftinhalt =   
264   ( SELECT k.bezeichnung                         -- Straßenname ..
265       FROM ax_lagebezeichnungkatalogeintrag k    --  .. aus Katalog
266       JOIN ax_lagebezeichnungohnehausnummer l    -- verwendet als Lage o.H.
267         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
268      WHERE p.gml_id = l.gml_id                   -- die gml_id wurde aus View importiert
269    )
270 WHERE     p.schriftinhalt IS NULL
271   AND NOT p.the_geom      IS NULL;
272
273
274-- ENDE --
Note: See TracBrowser for help on using the repository browser.