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

Revision 114, 3.9 KB checked in by tobias.dick, 10 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_fluesse_g0;
2--
3
4SELECT (ST_Dump(ST_Union(ax_fliessgewaesser.wkb_geometry))).geom AS wkb_geometry, ax_wasserlauf.name_ AS widmung
5INTO map_fluesse_g0
6FROM ax_wasserlauf
7INNER JOIN alkis_beziehungen
8        ON (alkis_beziehungen.beziehung_zu = ax_wasserlauf.gml_id)
9INNER JOIN ax_fliessgewaesser
10        ON (ax_fliessgewaesser.gml_id=alkis_beziehungen.beziehung_von)
11GROUP BY name_
12/*
13--
14UNION ALL
15--
16SELECT wkb_geometry AS wkb_geometry, NULL AS name_
17FROM ax_gewaesserachse
18*/
19;
20--
21DROP SEQUENCE IF EXISTS map_fluesse_g0_gid_seq;
22CREATE SEQUENCE map_fluesse_g0_gid_seq;
23ALTER TABLE map_fluesse_g0 ADD COLUMN gid INTEGER;
24UPDATE map_fluesse_g0 SET gid = nextval('map_fluesse_g0_gid_seq');
25ALTER TABLE map_fluesse_g0 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g0_gid_seq');
26CREATE INDEX map_fluesse_g0_gidx ON map_fluesse_g0 USING GIST ( wkb_geometry );
27--
28--
29DROP TABLE IF EXISTS map_fluesse_g1;
30--
31SELECT (ST_Dump(ST_Buffer(ST_SimplifyPreserveTopology(ST_Buffer(wkb_geometry, 4), 8),-4))).geom AS wkb_geometry, widmung
32INTO map_fluesse_g1 FROM map_fluesse_g0;
33--
34DROP SEQUENCE IF EXISTS map_fluesse_g1_gid_seq;
35CREATE SEQUENCE map_fluesse_g1_gid_seq;
36ALTER TABLE map_fluesse_g1 ADD COLUMN gid INTEGER;
37UPDATE map_fluesse_g1 SET gid = nextval('map_fluesse_g1_gid_seq');
38ALTER TABLE map_fluesse_g1 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g1_gid_seq');
39CREATE INDEX map_fluesse_g1_gidx ON map_fluesse_g1 USING GIST ( wkb_geometry );
40--
41--
42DROP TABLE IF EXISTS map_fluesse_g2;
43--
44SELECT (ST_Dump(ST_SimplifyPreserveTopology(ST_Buffer(ST_Buffer(wkb_geometry, 8), -8),30))).geom AS wkb_geometry, widmung INTO map_fluesse_g2 FROM map_fluesse_g1;
45--
46DROP SEQUENCE IF EXISTS map_fluesse_g2_gid_seq;
47CREATE SEQUENCE map_fluesse_g2_gid_seq;
48ALTER TABLE map_fluesse_g2 ADD COLUMN gid INTEGER;
49UPDATE map_fluesse_g2 SET gid = nextval('map_fluesse_g2_gid_seq');
50ALTER TABLE map_fluesse_g2 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g2_gid_seq');
51CREATE INDEX map_fluesse_g2_gidx ON map_fluesse_g2 USING GIST ( wkb_geometry );
52--
53--
54--
55DROP TABLE IF EXISTS map_fluesse_g3;
56--
57SELECT wkb_geometry, widmung INTO map_fluesse_g3 FROM map_fluesse_g2;
58--
59DROP SEQUENCE IF EXISTS map_fluesse_g3_gid_seq;
60CREATE SEQUENCE map_fluesse_g3_gid_seq;
61ALTER TABLE map_fluesse_g3 ADD COLUMN gid INTEGER;
62UPDATE map_fluesse_g3 SET gid = nextval('map_fluesse_g3_gid_seq');
63ALTER TABLE map_fluesse_g3 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g3_gid_seq');
64CREATE INDEX map_fluesse_g3_gidx ON map_fluesse_g3 USING GIST ( wkb_geometry );
65--
66--
67DROP TABLE IF EXISTS map_fluesse_g4;
68--
69SELECT wkb_geometry, widmung INTO map_fluesse_g4 FROM map_fluesse_g3;
70--
71DROP SEQUENCE IF EXISTS map_fluesse_g4_gid_seq;
72CREATE SEQUENCE map_fluesse_g4_gid_seq;
73ALTER TABLE map_fluesse_g4 ADD COLUMN gid INTEGER;
74UPDATE map_fluesse_g4 SET gid = nextval('map_fluesse_g4_gid_seq');
75ALTER TABLE map_fluesse_g4 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g4_gid_seq');
76CREATE INDEX map_fluesse_g4_gidx ON map_fluesse_g4 USING GIST ( wkb_geometry );
77--
78--
79DROP TABLE IF EXISTS map_fluesse_g5;
80--
81SELECT wkb_geometry, widmung INTO map_fluesse_g5 FROM map_fluesse_g4;
82--
83DROP SEQUENCE IF EXISTS map_fluesse_g5_gid_seq;
84CREATE SEQUENCE map_fluesse_g5_gid_seq;
85ALTER TABLE map_fluesse_g5 ADD COLUMN gid INTEGER;
86UPDATE map_fluesse_g5 SET gid = nextval('map_fluesse_g5_gid_seq');
87ALTER TABLE map_fluesse_g5 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g5_gid_seq');
88CREATE INDEX map_fluesse_g5_gidx ON map_fluesse_g5 USING GIST ( wkb_geometry );
89--
90--
91DROP TABLE IF EXISTS map_fluesse_g6;
92--
93SELECT wkb_geometry, widmung INTO map_fluesse_g6 FROM map_fluesse_g5;
94--
95DROP SEQUENCE IF EXISTS map_fluesse_g6_gid_seq;
96CREATE SEQUENCE map_fluesse_g6_gid_seq;
97ALTER TABLE map_fluesse_g6 ADD COLUMN gid INTEGER;
98UPDATE map_fluesse_g6 SET gid = nextval('map_fluesse_g6_gid_seq');
99ALTER TABLE map_fluesse_g6 ALTER COLUMN gid SET DEFAULT nextval('map_fluesse_g6_gid_seq');
100CREATE INDEX map_fluesse_g6_gidx ON map_fluesse_g6 USING GIST ( wkb_geometry );
101
102
103
104
Note: See TracBrowser for help on using the repository browser.