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

Revision 114, 1.8 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_wege_g0;
2--
3SELECT
4(ST_Dump(ST_LineMerge(ST_Collect(ST_SimplifyPreserveTopology(ax_fahrwegachse.wkb_geometry, 2.5))))).geom AS wkb_geometry,
5NULL::text AS widmung
6INTO map_wege_g0
7FROM ax_fahrwegachse;
8--
9DROP SEQUENCE IF EXISTS map_wege_g0_gid_seq;
10CREATE SEQUENCE map_wege_g0_gid_seq;
11ALTER TABLE map_wege_g0 ADD COLUMN gid INTEGER;
12UPDATE map_wege_g0 SET gid = nextval('map_wege_g0_gid_seq');
13ALTER TABLE map_wege_g0 ALTER COLUMN gid SET DEFAULT nextval('map_wege_g0_gid_seq');
14CREATE INDEX map_wege_g0_gidx ON map_wege_g0 USING GIST ( wkb_geometry );
15CREATE INDEX map_wege_g0_widmung_idx ON map_wege_g0 (widmung);
16--
17--
18DROP TABLE IF EXISTS map_wege_g1;
19--
20
21SELECT ST_SimplifyPreserveTopology(wkb_geometry, 5) AS wkb_geometry, widmung
22INTO map_wege_g1
23FROM map_wege_g0
24WHERE ST_Length( wkb_geometry ) > 5;
25--
26DROP SEQUENCE IF EXISTS map_wege_g1_gid_seq;
27CREATE SEQUENCE map_wege_g1_gid_seq;
28ALTER TABLE map_wege_g1 ADD COLUMN gid INTEGER;
29UPDATE map_wege_g1 SET gid = nextval('map_wege_g1_gid_seq');
30ALTER TABLE map_wege_g1 ALTER COLUMN gid SET DEFAULT nextval('map_wege_g1_gid_seq');
31CREATE INDEX map_wege_g1_gidx ON map_wege_g1 USING GIST ( wkb_geometry );
32CREATE INDEX map_wege_g1_widmung_idx ON map_wege_g1 (widmung);
33--
34--
35DROP TABLE IF EXISTS map_wege_g2;
36--
37SELECT ST_SimplifyPreserveTopology(wkb_geometry, 15) AS wkb_geometry, widmung
38INTO map_wege_g2
39FROM map_wege_g1
40WHERE ST_Length( wkb_geometry ) > 15;
41--
42DROP SEQUENCE IF EXISTS map_wege_g2_gid_seq;
43CREATE SEQUENCE map_wege_g2_gid_seq;
44ALTER TABLE map_wege_g2 ADD COLUMN gid INTEGER;
45UPDATE map_wege_g2 SET gid = nextval('map_wege_g2_gid_seq');
46ALTER TABLE map_wege_g2 ALTER COLUMN gid SET DEFAULT nextval('map_wege_g2_gid_seq');
47CREATE INDEX map_wege_g2_gidx ON map_wege_g2 USING GIST ( wkb_geometry );
48CREATE INDEX map_wege_g2_widmung_idx ON map_wege_g2 (widmung);
49--
50--
Note: See TracBrowser for help on using the repository browser.