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

Revision 114, 3.0 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_bahn_g0;
2--
3SELECT
4(ST_Dump(ST_LineMerge(ST_Collect(ST_SimplifyPreserveTopology(ax_bahnstrecke.wkb_geometry, 2.5))))).geom AS wkb_geometry, NULL::text AS widmung
5INTO map_bahn_g0
6FROM ax_bahnstrecke;
7--
8DROP SEQUENCE IF EXISTS map_bahn_g0_gid_seq;
9CREATE SEQUENCE map_bahn_g0_gid_seq;
10ALTER TABLE map_bahn_g0 ADD COLUMN gid INTEGER;
11UPDATE map_bahn_g0 SET gid = nextval('map_bahn_g0_gid_seq');
12ALTER TABLE map_bahn_g0 ALTER COLUMN gid SET DEFAULT nextval('map_bahn_g0_gid_seq');
13CREATE INDEX map_bahn_g0_gidx ON map_bahn_g0 USING GIST ( wkb_geometry );
14CREATE INDEX map_bahn_g0_widmung_idx ON map_bahn_g0 (widmung);
15--
16--
17DROP TABLE IF EXISTS map_bahn_g1;
18--
19
20SELECT ST_SimplifyPreserveTopology(wkb_geometry, 5) AS wkb_geometry, widmung
21INTO map_bahn_g1
22FROM map_bahn_g0
23WHERE ST_Length( wkb_geometry ) > 45;
24--
25DROP SEQUENCE IF EXISTS map_bahn_g1_gid_seq;
26CREATE SEQUENCE map_bahn_g1_gid_seq;
27ALTER TABLE map_bahn_g1 ADD COLUMN gid INTEGER;
28UPDATE map_bahn_g1 SET gid = nextval('map_bahn_g1_gid_seq');
29ALTER TABLE map_bahn_g1 ALTER COLUMN gid SET DEFAULT nextval('map_bahn_g1_gid_seq');
30CREATE INDEX map_bahn_g1_gidx ON map_bahn_g1 USING GIST ( wkb_geometry );
31CREATE INDEX map_bahn_g1_widmung_idx ON map_bahn_g1 (widmung);
32--
33--
34DROP TABLE IF EXISTS map_bahn_g2;
35--
36SELECT ST_SimplifyPreserveTopology(wkb_geometry, 25) AS wkb_geometry, widmung
37INTO map_bahn_g2
38FROM map_bahn_g1
39WHERE ST_Length( wkb_geometry ) > 90;
40--
41DROP SEQUENCE IF EXISTS map_bahn_g2_gid_seq;
42CREATE SEQUENCE map_bahn_g2_gid_seq;
43ALTER TABLE map_bahn_g2 ADD COLUMN gid INTEGER;
44UPDATE map_bahn_g2 SET gid = nextval('map_bahn_g2_gid_seq');
45ALTER TABLE map_bahn_g2 ALTER COLUMN gid SET DEFAULT nextval('map_bahn_g2_gid_seq');
46CREATE INDEX map_bahn_g2_gidx ON map_bahn_g2 USING GIST ( wkb_geometry );
47CREATE INDEX map_bahn_g2_widmung_idx ON map_bahn_g2 (widmung);
48--
49--
50DROP TABLE IF EXISTS map_bahn_g3;
51--
52SELECT ST_SimplifyPreserveTopology(wkb_geometry, 50) AS wkb_geometry, widmung
53INTO map_bahn_g3
54FROM map_bahn_g2
55WHERE ST_Length( wkb_geometry ) > 90;
56--
57DROP SEQUENCE IF EXISTS map_bahn_g3_gid_seq;
58CREATE SEQUENCE map_bahn_g3_gid_seq;
59ALTER TABLE map_bahn_g3 ADD COLUMN gid INTEGER;
60UPDATE map_bahn_g3 SET gid = nextval('map_bahn_g3_gid_seq');
61ALTER TABLE map_bahn_g3 ALTER COLUMN gid SET DEFAULT nextval('map_bahn_g3_gid_seq');
62CREATE INDEX map_bahn_g3_gidx ON map_bahn_g3 USING GIST ( wkb_geometry );
63CREATE INDEX map_bahn_g3_widmung_idx ON map_bahn_g3 (widmung);
64--
65--
66DROP TABLE IF EXISTS map_bahn_g4;
67--
68SELECT ST_SimplifyPreserveTopology(wkb_geometry, 150) AS wkb_geometry, widmung
69INTO map_bahn_g4
70FROM map_bahn_g3
71WHERE ST_Length( wkb_geometry ) > 90;
72--
73DROP SEQUENCE IF EXISTS map_bahn_g4_gid_seq;
74CREATE SEQUENCE map_bahn_g4_gid_seq;
75ALTER TABLE map_bahn_g4 ADD COLUMN gid INTEGER;
76UPDATE map_bahn_g4 SET gid = nextval('map_bahn_g4_gid_seq');
77ALTER TABLE map_bahn_g4 ALTER COLUMN gid SET DEFAULT nextval('map_bahn_g4_gid_seq');
78CREATE INDEX map_bahn_g4_gidx ON map_bahn_g4 USING GIST ( wkb_geometry );
79CREATE INDEX map_bahn_g4_widmung_idx ON map_bahn_g4 (widmung);
Note: See TracBrowser for help on using the repository browser.