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

Revision 114, 3.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_baeche_g0;
2--
3SELECT (ST_Dump(ST_Linemerge(ST_Collect(ST_SimplifyPreserveTopology( wkb_geometry, 2.5 ))))).geom AS wkb_geometry,
4NULL::text AS widmung
5INTO map_baeche_g0
6FROM ax_gewaesserachse;
7--
8DROP SEQUENCE IF EXISTS map_baeche_g0_gid_seq;
9CREATE SEQUENCE map_baeche_g0_gid_seq;
10ALTER TABLE map_baeche_g0 ADD COLUMN gid INTEGER;
11UPDATE map_baeche_g0 SET gid = nextval('map_baeche_g0_gid_seq');
12ALTER TABLE map_baeche_g0 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g0_gid_seq');
13CREATE INDEX map_baeche_g0_gidx ON map_baeche_g0 USING GIST ( wkb_geometry );
14--
15--
16DROP TABLE IF EXISTS map_baeche_g1;
17--
18SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 5 ))).geom AS wkb_geometry, widmung
19INTO map_baeche_g1
20FROM map_baeche_g0;
21--
22DROP SEQUENCE IF EXISTS map_baeche_g1_gid_seq;
23CREATE SEQUENCE map_baeche_g1_gid_seq;
24ALTER TABLE map_baeche_g1 ADD COLUMN gid INTEGER;
25UPDATE map_baeche_g1 SET gid = nextval('map_baeche_g1_gid_seq');
26ALTER TABLE map_baeche_g1 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g1_gid_seq');
27CREATE INDEX map_baeche_g1_gidx ON map_baeche_g1 USING GIST ( wkb_geometry );
28--
29--
30DROP TABLE IF EXISTS map_baeche_g2;
31--
32SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 15 ))).geom AS wkb_geometry, widmung
33INTO map_baeche_g2
34FROM map_baeche_g1;
35--
36DROP SEQUENCE IF EXISTS map_baeche_g2_gid_seq;
37CREATE SEQUENCE map_baeche_g2_gid_seq;
38ALTER TABLE map_baeche_g2 ADD COLUMN gid INTEGER;
39UPDATE map_baeche_g2 SET gid = nextval('map_baeche_g2_gid_seq');
40ALTER TABLE map_baeche_g2 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g2_gid_seq');
41CREATE INDEX map_baeche_g2_gidx ON map_baeche_g2 USING GIST ( wkb_geometry );
42--
43--
44DROP TABLE IF EXISTS map_baeche_g3;
45--
46SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 35 ))).geom AS wkb_geometry, widmung
47INTO map_baeche_g3
48FROM map_baeche_g2;
49--
50DROP SEQUENCE IF EXISTS map_baeche_g3_gid_seq;
51CREATE SEQUENCE map_baeche_g3_gid_seq;
52ALTER TABLE map_baeche_g3 ADD COLUMN gid INTEGER;
53UPDATE map_baeche_g3 SET gid = nextval('map_baeche_g3_gid_seq');
54ALTER TABLE map_baeche_g3 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g3_gid_seq');
55CREATE INDEX map_baeche_g3_gidx ON map_baeche_g3 USING GIST ( wkb_geometry );
56--
57--
58DROP TABLE IF EXISTS map_baeche_g4;
59--
60SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 65 ))).geom AS wkb_geometry, widmung
61INTO map_baeche_g4
62FROM map_baeche_g3;
63--
64DROP SEQUENCE IF EXISTS map_baeche_g4_gid_seq;
65CREATE SEQUENCE map_baeche_g4_gid_seq;
66ALTER TABLE map_baeche_g4 ADD COLUMN gid INTEGER;
67UPDATE map_baeche_g4 SET gid = nextval('map_baeche_g4_gid_seq');
68ALTER TABLE map_baeche_g4 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g4_gid_seq');
69CREATE INDEX map_baeche_g4_gidx ON map_baeche_g4 USING GIST ( wkb_geometry );
70--
71--
72DROP TABLE IF EXISTS map_baeche_g5;
73--
74SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 125 ))).geom AS wkb_geometry, widmung
75INTO map_baeche_g5
76FROM map_baeche_g4;
77--
78DROP SEQUENCE IF EXISTS map_baeche_g5_gid_seq;
79CREATE SEQUENCE map_baeche_g5_gid_seq;
80ALTER TABLE map_baeche_g5 ADD COLUMN gid INTEGER;
81UPDATE map_baeche_g5 SET gid = nextval('map_baeche_g5_gid_seq');
82ALTER TABLE map_baeche_g5 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g5_gid_seq');
83CREATE INDEX map_baeche_g5_gidx ON map_baeche_g5 USING GIST ( wkb_geometry );
84--
85--
86DROP TABLE IF EXISTS map_baeche_g6;
87--
88SELECT (ST_Dump(ST_SimplifyPreserveTopology( wkb_geometry, 250 ))).geom AS wkb_geometry, widmung
89INTO map_baeche_g6
90FROM map_baeche_g5;
91--
92DROP SEQUENCE IF EXISTS map_baeche_g6_gid_seq;
93CREATE SEQUENCE map_baeche_g6_gid_seq;
94ALTER TABLE map_baeche_g6 ADD COLUMN gid INTEGER;
95UPDATE map_baeche_g6 SET gid = nextval('map_baeche_g6_gid_seq');
96ALTER TABLE map_baeche_g6 ALTER COLUMN gid SET DEFAULT nextval('map_baeche_g6_gid_seq');
97CREATE INDEX map_baeche_g6_gidx ON map_baeche_g6 USING GIST ( wkb_geometry );
Note: See TracBrowser for help on using the repository browser.