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 |
---|
11 | DROP TABLE IF EXISTS map_landesflaeche; |
---|
12 | -- |
---|
13 | SELECT ST_BuildArea(ST_ExteriorRing(ST_Union( wkb_geometry ))) AS wkb_geometry |
---|
14 | INTO map_landesflaeche |
---|
15 | FROM ax_kommunalesgebiet; |
---|
16 | -- |
---|
17 | CREATE INDEX map_landesflaeche_gidx ON map_landesflaeche USING GIST ( wkb_geometry ); |
---|
18 | -- |
---|
19 | DROP SEQUENCE IF EXISTS map_landesflaeche_gid_seq; |
---|
20 | CREATE SEQUENCE map_landesflaeche_gid_seq; |
---|
21 | ALTER TABLE map_landesflaeche ADD COLUMN gid INTEGER; |
---|
22 | UPDATE map_landesflaeche SET gid = nextval('map_landesflaeche_gid_seq'); |
---|
23 | ALTER 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 | |
---|
31 | SELECT grid( 'map_rlp_kachel_2km', wkb_geometry, 2000) |
---|
32 | FROM temp0; |
---|
33 | -- |
---|
34 | SELECT grid( 'map_rlp_kachel_5km', wkb_geometry, 5000) |
---|
35 | FROM temp0; |
---|
36 | -- |
---|
37 | SELECT grid( 'map_rlp_kachel_10km', wkb_geometry, 10000) |
---|
38 | FROM temp0; |
---|
39 | -- |
---|
40 | SELECT grid( 'map_rlp_kachel_25km', wkb_geometry, 25000) |
---|
41 | FROM temp0; |
---|
42 | -- |
---|
43 | SELECT grid( 'map_rlp_kachel_50km', wkb_geometry, 50000) |
---|
44 | FROM 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 |
---|
55 | DROP TABLE IF EXISTS map_landesflaeche_g0; |
---|
56 | -- |
---|
57 | SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry |
---|
58 | INTO map_landesflaeche_g0 |
---|
59 | FROM map_landesflaeche t |
---|
60 | LEFT JOIN map_rlp_kachel_2km g |
---|
61 | ON ST_Intersects( t.wkb_geometry, g.wkb_geometry ); |
---|
62 | -- |
---|
63 | CREATE INDEX map_landesflaeche_g0_gidx ON map_landesflaeche_g0 USING GIST ( wkb_geometry ); |
---|
64 | -- |
---|
65 | DROP SEQUENCE IF EXISTS map_landesflaeche_g0_gid_seq; |
---|
66 | CREATE SEQUENCE map_landesflaeche_g0_gid_seq; |
---|
67 | ALTER TABLE map_landesflaeche_g0 ADD COLUMN gid INTEGER; |
---|
68 | UPDATE map_landesflaeche_g0 SET gid = nextval('map_landesflaeche_g0_gid_seq'); |
---|
69 | ALTER 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 |
---|
74 | DROP TABLE IF EXISTS map_landesflaeche_g1; |
---|
75 | -- |
---|
76 | SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry |
---|
77 | INTO map_landesflaeche_g1 |
---|
78 | FROM map_landesflaeche t |
---|
79 | LEFT JOIN map_rlp_kachel_5km g |
---|
80 | ON ST_Intersects( t.wkb_geometry, g.wkb_geometry ); |
---|
81 | -- |
---|
82 | CREATE INDEX map_landesflaeche_g1_gidx ON map_landesflaeche_g1 USING GIST ( wkb_geometry ); |
---|
83 | -- |
---|
84 | DROP SEQUENCE IF EXISTS map_landesflaeche_g1_gid_seq; |
---|
85 | CREATE SEQUENCE map_landesflaeche_g1_gid_seq; |
---|
86 | ALTER TABLE map_landesflaeche_g1 ADD COLUMN gid INTEGER; |
---|
87 | UPDATE map_landesflaeche_g1 SET gid = nextval('map_landesflaeche_g1_gid_seq'); |
---|
88 | ALTER 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 |
---|
93 | DROP TABLE IF EXISTS map_landesflaeche_g2; |
---|
94 | -- |
---|
95 | SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry |
---|
96 | INTO map_landesflaeche_g2 |
---|
97 | FROM map_landesflaeche t |
---|
98 | LEFT JOIN map_rlp_kachel_10km g |
---|
99 | ON ST_Intersects( t.wkb_geometry, g.wkb_geometry ); |
---|
100 | -- |
---|
101 | CREATE INDEX map_landesflaeche_g2_gidx ON map_landesflaeche_g2 USING GIST ( wkb_geometry ); |
---|
102 | -- |
---|
103 | DROP SEQUENCE IF EXISTS map_landesflaeche_g2_gid_seq; |
---|
104 | CREATE SEQUENCE map_landesflaeche_g2_gid_seq; |
---|
105 | ALTER TABLE map_landesflaeche_g2 ADD COLUMN gid INTEGER; |
---|
106 | UPDATE map_landesflaeche_g2 SET gid = nextval('map_landesflaeche_g2_gid_seq'); |
---|
107 | ALTER 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 |
---|
112 | DROP TABLE IF EXISTS map_landesflaeche_g3; |
---|
113 | -- |
---|
114 | SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry |
---|
115 | INTO map_landesflaeche_g3 |
---|
116 | FROM map_landesflaeche t |
---|
117 | LEFT JOIN map_rlp_kachel_25km g |
---|
118 | ON ST_Intersects( t.wkb_geometry, g.wkb_geometry ); |
---|
119 | -- |
---|
120 | CREATE INDEX map_landesflaeche_g3_gidx ON map_landesflaeche_g3 USING GIST ( wkb_geometry ); |
---|
121 | -- |
---|
122 | DROP SEQUENCE IF EXISTS map_landesflaeche_g3_gid_seq; |
---|
123 | CREATE SEQUENCE map_landesflaeche_g3_gid_seq; |
---|
124 | ALTER TABLE map_landesflaeche_g3 ADD COLUMN gid INTEGER; |
---|
125 | UPDATE map_landesflaeche_g3 SET gid = nextval('map_landesflaeche_g3_gid_seq'); |
---|
126 | ALTER 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 |
---|
131 | DROP TABLE IF EXISTS map_landesflaeche_g4; |
---|
132 | -- |
---|
133 | SELECT (ST_Dump(ST_Intersection( t.wkb_geometry, g.wkb_geometry ))).geom AS wkb_geometry |
---|
134 | INTO map_landesflaeche_g4 |
---|
135 | FROM map_landesflaeche t |
---|
136 | LEFT JOIN map_rlp_kachel_50km g |
---|
137 | ON ST_Intersects( t.wkb_geometry, g.wkb_geometry ); |
---|
138 | -- |
---|
139 | CREATE INDEX map_landesflaeche_g4_gidx ON map_landesflaeche_g4 USING GIST ( wkb_geometry ); |
---|
140 | -- |
---|
141 | DROP SEQUENCE IF EXISTS map_landesflaeche_g4_gid_seq; |
---|
142 | CREATE SEQUENCE map_landesflaeche_g4_gid_seq; |
---|
143 | ALTER TABLE map_landesflaeche_g4 ADD COLUMN gid INTEGER; |
---|
144 | UPDATE map_landesflaeche_g4 SET gid = nextval('map_landesflaeche_g4_gid_seq'); |
---|
145 | ALTER TABLE map_landesflaeche_g4 ALTER COLUMN gid SET DEFAULT nextval('map_landesflaeche_g4_gid_seq'); |
---|
146 | -- 1s fÃŒr RLP |
---|
147 | -- |
---|
148 | |
---|