source: trunk/data/konvert/postnas_0.6/atkis_rp_rendering/processing/sql/strassen_full.sql @ 114

Revision 114, 4.8 KB checked in by tobias.dick, 11 years ago (diff)

Initiale Dateien um per PostNAS importierte ATKIS Daten für ein Online Rendering aufzubereiten. Die Scripte erzeugen pro darzustellender Objektart bis zu 6 Generalisierungsstufen. Die Parameter können in den Shellscripten angepasst werden. Die Visualisierung kann z.B. über den Mapserver erfolgen. Der Bildaufbau sollte in jeder Stufe unter einer Sekunde benötigen. Die Laufzeit der Scripte beträgt auf einem single core 2.0GHz Prozessor ca 2.5 Stunden.

Line 
1DROP TABLE IF EXISTS map_strasse_g0;
2--
3SELECT
4(ST_Dump(ST_LineMerge(ST_Collect(ax_strassenachse.wkb_geometry)))).geom AS wkb_geometry,
5ax_strasse.widmung,
6ax_strasse.name_,
7CASE
8  WHEN ax_strasse.widmung = 1301
9  THEN unnest(regexp_matches(ax_strasse.bezeichnung, E'(A[0-9]+)'))
10  ELSE ax_strasse.bezeichnung
11END AS bezeichnung,
12ax_strasse.strassenschluessel
13INTO map_strasse_g0
14FROM ax_strasse
15INNER JOIN alkis_beziehungen
16ON (alkis_beziehungen.beziehung_zu = ax_strasse.gml_id)
17INNER JOIN ax_strassenachse ON (ax_strassenachse.gml_id=alkis_beziehungen.beziehung_von)
18GROUP BY name_, bezeichnung, strassenschluessel, widmung;
19--
20DROP SEQUENCE IF EXISTS map_strasse_g0_gid_seq;
21CREATE SEQUENCE map_strasse_g0_gid_seq;
22ALTER TABLE map_strasse_g0 ADD COLUMN gid INTEGER;
23UPDATE map_strasse_g0 SET gid = nextval('map_strasse_g0_gid_seq');
24ALTER TABLE map_strasse_g0 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g0_gid_seq');
25CREATE INDEX map_strasse_g0_gidx ON map_strasse_g0 USING GIST ( wkb_geometry );
26CREATE INDEX map_strasse_g0_widmung_idx ON map_strasse_g0 (widmung);
27--
28--
29DROP TABLE IF EXISTS map_strasse_g1;
30--
31SELECT ST_SimplifyPreserveTopology(wkb_geometry, 5) AS wkb_geometry, widmung, name_, bezeichnung
32INTO map_strasse_g1
33FROM map_strasse_g0;
34--
35DROP SEQUENCE IF EXISTS map_strasse_g1_gid_seq;
36CREATE SEQUENCE map_strasse_g1_gid_seq;
37ALTER TABLE map_strasse_g1 ADD COLUMN gid INTEGER;
38UPDATE map_strasse_g1 SET gid = nextval('map_strasse_g1_gid_seq');
39ALTER TABLE map_strasse_g1 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g1_gid_seq');
40CREATE INDEX map_strasse_g1_gidx ON map_strasse_g1 USING GIST ( wkb_geometry );
41CREATE INDEX map_strasse_g1_widmung_idx ON map_strasse_g1 (widmung);
42--
43--
44DROP TABLE IF EXISTS map_strasse_g2;
45--
46SELECT ST_SimplifyPreserveTopology(wkb_geometry, 15) AS wkb_geometry, widmung, name_, bezeichnung
47INTO map_strasse_g2
48FROM map_strasse_g1;
49--
50DROP SEQUENCE IF EXISTS map_strasse_g2_gid_seq;
51CREATE SEQUENCE map_strasse_g2_gid_seq;
52ALTER TABLE map_strasse_g2 ADD COLUMN gid INTEGER;
53UPDATE map_strasse_g2 SET gid = nextval('map_strasse_g2_gid_seq');
54ALTER TABLE map_strasse_g2 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g2_gid_seq');
55CREATE INDEX map_strasse_g2_gidx ON map_strasse_g2 USING GIST ( wkb_geometry );
56CREATE INDEX map_strasse_g2_widmung_idx ON map_strasse_g2 (widmung);
57--
58--
59DROP TABLE IF EXISTS map_strasse_g3;
60--
61SELECT ST_SimplifyPreserveTopology(wkb_geometry, 35) AS wkb_geometry, widmung, name_, bezeichnung
62INTO map_strasse_g3
63FROM map_strasse_g2;
64--
65DROP SEQUENCE IF EXISTS map_strasse_g3_gid_seq;
66CREATE SEQUENCE map_strasse_g3_gid_seq;
67ALTER TABLE map_strasse_g3 ADD COLUMN gid INTEGER;
68UPDATE map_strasse_g3 SET gid = nextval('map_strasse_g3_gid_seq');
69ALTER TABLE map_strasse_g3 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g3_gid_seq');
70CREATE INDEX map_strasse_g3_gidx ON map_strasse_g3 USING GIST ( wkb_geometry );
71CREATE INDEX map_strasse_g3_widmung_idx ON map_strasse_g3 (widmung);
72--
73--
74DROP TABLE IF EXISTS map_strasse_g4;
75--
76SELECT ST_SimplifyPreserveTopology(wkb_geometry, 50) AS wkb_geometry, widmung, name_, bezeichnung
77INTO map_strasse_g4
78FROM map_strasse_g3;
79--
80DROP SEQUENCE IF EXISTS map_strasse_g4_gid_seq;
81CREATE SEQUENCE map_strasse_g4_gid_seq;
82ALTER TABLE map_strasse_g4 ADD COLUMN gid INTEGER;
83UPDATE map_strasse_g4 SET gid = nextval('map_strasse_g4_gid_seq');
84ALTER TABLE map_strasse_g4 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g4_gid_seq');
85CREATE INDEX map_strasse_g4_gidx ON map_strasse_g4 USING GIST ( wkb_geometry );
86CREATE INDEX map_strasse_g4_widmung_idx ON map_strasse_g4 (widmung);
87--
88--
89DROP TABLE IF EXISTS map_strasse_g5;
90--
91SELECT ST_SimplifyPreserveTopology(wkb_geometry, 100) AS wkb_geometry, widmung, name_, bezeichnung
92INTO map_strasse_g5
93FROM map_strasse_g4;
94--
95DROP SEQUENCE IF EXISTS map_strasse_g5_gid_seq;
96CREATE SEQUENCE map_strasse_g5_gid_seq;
97ALTER TABLE map_strasse_g5 ADD COLUMN gid INTEGER;
98UPDATE map_strasse_g5 SET gid = nextval('map_strasse_g5_gid_seq');
99ALTER TABLE map_strasse_g5 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g5_gid_seq');
100CREATE INDEX map_strasse_g5_gidx ON map_strasse_g5 USING GIST ( wkb_geometry );
101CREATE INDEX map_strasse_g5_widmung_idx ON map_strasse_g5 (widmung);
102--
103--
104DROP TABLE IF EXISTS map_strasse_g6;
105--
106SELECT (ST_Dump(ST_SimplifyPreserveTopology(wkb_geometry, 200))).geom AS wkb_geometry, widmung, name_, bezeichnung
107INTO map_strasse_g6
108FROM map_strasse_g5;
109--
110DROP SEQUENCE IF EXISTS map_strasse_g6_gid_seq;
111CREATE SEQUENCE map_strasse_g6_gid_seq;
112ALTER TABLE map_strasse_g6 ADD COLUMN gid INTEGER;
113UPDATE map_strasse_g6 SET gid = nextval('map_strasse_g6_gid_seq');
114ALTER TABLE map_strasse_g6 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g6_gid_seq');
115CREATE INDEX map_strasse_g6_gidx ON map_strasse_g6 USING GIST ( wkb_geometry );
116CREATE INDEX map_strasse_g6_widmung_idx ON map_strasse_g6 (widmung);
117--
Note: See TracBrowser for help on using the repository browser.