[114] | 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 | |
---|