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

Revision 114, 5.4 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 
1-----------------------------------------
2-----------------------------------------
3--  LandesflÀche [Umriss, gefÃŒllt]
4--  alles in einer Tabelle
5--  (keine Attributierungen)
6--  t gesamt: 30s
7-----------------------------------------
8-----------------------------------------
9--
10-- Erstellen eines Polygons der LandesflÀche RLP
11DROP TABLE IF EXISTS map_landesflaeche;
12--
13SELECT ST_BuildArea(ST_ExteriorRing(ST_Union( wkb_geometry ))) AS wkb_geometry
14INTO map_landesflaeche
15FROM ax_kommunalesgebiet;
16--
17CREATE INDEX map_landesflaeche_gidx ON map_landesflaeche USING GIST ( wkb_geometry );
18--
19DROP SEQUENCE IF EXISTS map_landesflaeche_gid_seq;
20CREATE SEQUENCE map_landesflaeche_gid_seq;
21ALTER TABLE map_landesflaeche ADD COLUMN gid INTEGER;
22UPDATE map_landesflaeche SET gid = nextval('map_landesflaeche_gid_seq');
23ALTER TABLE map_landesflaeche ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_gid_seq');
24--
25--
26/*
27-- GRID - MUSS NICHT JEDES MAL ERSTELLT WERDEN!
28-- DROP-Statement wird auch nochmal in Funktion selbst aufgerufen
29-- DROP TABLE IF EXISTS map_rlp_kachel_2km, map_rlp_kachel_5km, map_rlp_kachel_10km, map_rlp_kachel_25km, map_rlp_kachel_50km;
30
31SELECT grid( 'map_rlp_kachel_2km', wkb_geometry, 2000)
32FROM temp0;
33--
34SELECT grid( 'map_rlp_kachel_5km', wkb_geometry, 5000)
35FROM temp0;
36--
37SELECT grid( 'map_rlp_kachel_10km', wkb_geometry, 10000)
38FROM temp0;
39--
40SELECT grid( 'map_rlp_kachel_25km', wkb_geometry, 25000)
41FROM temp0;
42--
43SELECT grid( 'map_rlp_kachel_50km', wkb_geometry, 50000)
44FROM temp0;
45*/
46--
47--
48-------------------------------------------
49-- Clipping des GRID auf Landesgrenzen
50-- ergibt gekachelte FlÀche Rheinland-Pfalz
51-- = Grundlage weiterer rÀumlicher Verschneidungen
52-- + performante Darstellung der LandesflÀche selbst
53--
54-- 2km Kachelung [g0] 2km
55DROP TABLE IF EXISTS map_landesflaeche_g0;
56--
57SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry
58INTO map_landesflaeche_g0
59FROM  map_landesflaeche t
60LEFT JOIN map_rlp_kachel_2km g
61ON ST_Intersects( t.wkb_geometry, g.wkb_geometry );
62--
63CREATE INDEX map_landesflaeche_g0_gidx ON map_landesflaeche_g0 USING GIST ( wkb_geometry );
64--
65DROP SEQUENCE IF EXISTS map_landesflaeche_g0_gid_seq;
66CREATE SEQUENCE map_landesflaeche_g0_gid_seq;
67ALTER TABLE map_landesflaeche_g0 ADD COLUMN gid INTEGER;
68UPDATE map_landesflaeche_g0 SET gid = nextval('map_landesflaeche_g0_gid_seq');
69ALTER TABLE map_landesflaeche_g0 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g0_gid_seq');
70-- 423s fÃŒr RLP
71--
72--
73-- 5km Kachelung [g1] 5km
74DROP TABLE IF EXISTS map_landesflaeche_g1;
75--
76SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry
77INTO map_landesflaeche_g1
78FROM  map_landesflaeche t
79LEFT JOIN map_rlp_kachel_5km g
80ON ST_Intersects( t.wkb_geometry, g.wkb_geometry );
81--
82CREATE INDEX map_landesflaeche_g1_gidx ON map_landesflaeche_g1 USING GIST ( wkb_geometry );
83--
84DROP SEQUENCE IF EXISTS map_landesflaeche_g1_gid_seq;
85CREATE SEQUENCE map_landesflaeche_g1_gid_seq;
86ALTER TABLE map_landesflaeche_g1 ADD COLUMN gid INTEGER;
87UPDATE map_landesflaeche_g1 SET gid = nextval('map_landesflaeche_g1_gid_seq');
88ALTER TABLE map_landesflaeche_g1 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g1_gid_seq');
89-- 67s fÃŒr RLP
90--
91--
92-- 10km Kachelung [g2] 10km
93DROP TABLE IF EXISTS map_landesflaeche_g2;
94--
95SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry
96INTO map_landesflaeche_g2
97FROM  map_landesflaeche t
98LEFT JOIN map_rlp_kachel_10km g
99ON ST_Intersects( t.wkb_geometry, g.wkb_geometry );
100--
101CREATE INDEX map_landesflaeche_g2_gidx ON map_landesflaeche_g2 USING GIST ( wkb_geometry );
102--
103DROP SEQUENCE IF EXISTS map_landesflaeche_g2_gid_seq;
104CREATE SEQUENCE map_landesflaeche_g2_gid_seq;
105ALTER TABLE map_landesflaeche_g2 ADD COLUMN gid INTEGER;
106UPDATE map_landesflaeche_g2 SET gid = nextval('map_landesflaeche_g2_gid_seq');
107ALTER TABLE map_landesflaeche_g2 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g2_gid_seq');
108-- 17s fÃŒr RLP
109--
110--
111-- 25km Kachelung [g3] 25km
112DROP TABLE IF EXISTS map_landesflaeche_g3;
113--
114SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry
115INTO map_landesflaeche_g3
116FROM  map_landesflaeche t
117LEFT JOIN map_rlp_kachel_25km g
118ON ST_Intersects( t.wkb_geometry, g.wkb_geometry );
119--
120CREATE INDEX map_landesflaeche_g3_gidx ON map_landesflaeche_g3 USING GIST ( wkb_geometry );
121--
122DROP SEQUENCE IF EXISTS map_landesflaeche_g3_gid_seq;
123CREATE SEQUENCE map_landesflaeche_g3_gid_seq;
124ALTER TABLE map_landesflaeche_g3 ADD COLUMN gid INTEGER;
125UPDATE map_landesflaeche_g3 SET gid = nextval('map_landesflaeche_g3_gid_seq');
126ALTER TABLE map_landesflaeche_g3 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g3_gid_seq');
127-- 3s fÃŒr RLP
128--
129--
130-- 50km Kachelung [g4] 50km
131DROP TABLE IF EXISTS map_landesflaeche_g4;
132--
133SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry
134INTO map_landesflaeche_g4
135FROM  map_landesflaeche t
136LEFT JOIN map_rlp_kachel_50km g
137ON ST_Intersects( t.wkb_geometry, g.wkb_geometry );
138--
139CREATE INDEX map_landesflaeche_g4_gidx ON map_landesflaeche_g4 USING GIST ( wkb_geometry );
140--
141DROP SEQUENCE IF EXISTS map_landesflaeche_g4_gid_seq;
142CREATE SEQUENCE map_landesflaeche_g4_gid_seq;
143ALTER TABLE map_landesflaeche_g4 ADD COLUMN gid INTEGER;
144UPDATE map_landesflaeche_g4 SET gid = nextval('map_landesflaeche_g4_gid_seq');
145ALTER TABLE map_landesflaeche_g4 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g4_gid_seq');
146-- 1s fÃŒr RLP
147--
148
Note: See TracBrowser for help on using the repository browser.