[114] | 1 | DROP TABLE IF EXISTS map_strasse_g0; |
---|
| 2 | -- |
---|
| 3 | SELECT |
---|
| 4 | (ST_Dump(ST_LineMerge(ST_Collect(ax_strassenachse.wkb_geometry)))).geom AS wkb_geometry, |
---|
| 5 | ax_strasse.widmung, |
---|
| 6 | ax_strasse.name_, |
---|
| 7 | CASE |
---|
| 8 | WHEN ax_strasse.widmung = 1301 |
---|
| 9 | THEN unnest(regexp_matches(ax_strasse.bezeichnung, E'(A[0-9]+)')) |
---|
| 10 | ELSE ax_strasse.bezeichnung |
---|
| 11 | END AS bezeichnung, |
---|
| 12 | ax_strasse.strassenschluessel |
---|
| 13 | INTO map_strasse_g0 |
---|
| 14 | FROM ax_strasse |
---|
| 15 | INNER JOIN alkis_beziehungen |
---|
| 16 | ON (alkis_beziehungen.beziehung_zu = ax_strasse.gml_id) |
---|
| 17 | INNER JOIN ax_strassenachse ON (ax_strassenachse.gml_id=alkis_beziehungen.beziehung_von) |
---|
| 18 | GROUP BY name_, bezeichnung, strassenschluessel, widmung; |
---|
| 19 | -- |
---|
| 20 | DROP SEQUENCE IF EXISTS map_strasse_g0_gid_seq; |
---|
| 21 | CREATE SEQUENCE map_strasse_g0_gid_seq; |
---|
| 22 | ALTER TABLE map_strasse_g0 ADD COLUMN gid INTEGER; |
---|
| 23 | UPDATE map_strasse_g0 SET gid = nextval('map_strasse_g0_gid_seq'); |
---|
| 24 | ALTER TABLE map_strasse_g0 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g0_gid_seq'); |
---|
| 25 | CREATE INDEX map_strasse_g0_gidx ON map_strasse_g0 USING GIST ( wkb_geometry ); |
---|
| 26 | CREATE INDEX map_strasse_g0_widmung_idx ON map_strasse_g0 (widmung); |
---|
| 27 | -- |
---|
| 28 | -- |
---|
| 29 | DROP TABLE IF EXISTS map_strasse_g1; |
---|
| 30 | -- |
---|
| 31 | SELECT ST_SimplifyPreserveTopology(wkb_geometry, 5) AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 32 | INTO map_strasse_g1 |
---|
| 33 | FROM map_strasse_g0; |
---|
| 34 | -- |
---|
| 35 | DROP SEQUENCE IF EXISTS map_strasse_g1_gid_seq; |
---|
| 36 | CREATE SEQUENCE map_strasse_g1_gid_seq; |
---|
| 37 | ALTER TABLE map_strasse_g1 ADD COLUMN gid INTEGER; |
---|
| 38 | UPDATE map_strasse_g1 SET gid = nextval('map_strasse_g1_gid_seq'); |
---|
| 39 | ALTER TABLE map_strasse_g1 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g1_gid_seq'); |
---|
| 40 | CREATE INDEX map_strasse_g1_gidx ON map_strasse_g1 USING GIST ( wkb_geometry ); |
---|
| 41 | CREATE INDEX map_strasse_g1_widmung_idx ON map_strasse_g1 (widmung); |
---|
| 42 | -- |
---|
| 43 | -- |
---|
| 44 | DROP TABLE IF EXISTS map_strasse_g2; |
---|
| 45 | -- |
---|
| 46 | SELECT ST_SimplifyPreserveTopology(wkb_geometry, 15) AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 47 | INTO map_strasse_g2 |
---|
| 48 | FROM map_strasse_g1; |
---|
| 49 | -- |
---|
| 50 | DROP SEQUENCE IF EXISTS map_strasse_g2_gid_seq; |
---|
| 51 | CREATE SEQUENCE map_strasse_g2_gid_seq; |
---|
| 52 | ALTER TABLE map_strasse_g2 ADD COLUMN gid INTEGER; |
---|
| 53 | UPDATE map_strasse_g2 SET gid = nextval('map_strasse_g2_gid_seq'); |
---|
| 54 | ALTER TABLE map_strasse_g2 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g2_gid_seq'); |
---|
| 55 | CREATE INDEX map_strasse_g2_gidx ON map_strasse_g2 USING GIST ( wkb_geometry ); |
---|
| 56 | CREATE INDEX map_strasse_g2_widmung_idx ON map_strasse_g2 (widmung); |
---|
| 57 | -- |
---|
| 58 | -- |
---|
| 59 | DROP TABLE IF EXISTS map_strasse_g3; |
---|
| 60 | -- |
---|
| 61 | SELECT ST_SimplifyPreserveTopology(wkb_geometry, 35) AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 62 | INTO map_strasse_g3 |
---|
| 63 | FROM map_strasse_g2; |
---|
| 64 | -- |
---|
| 65 | DROP SEQUENCE IF EXISTS map_strasse_g3_gid_seq; |
---|
| 66 | CREATE SEQUENCE map_strasse_g3_gid_seq; |
---|
| 67 | ALTER TABLE map_strasse_g3 ADD COLUMN gid INTEGER; |
---|
| 68 | UPDATE map_strasse_g3 SET gid = nextval('map_strasse_g3_gid_seq'); |
---|
| 69 | ALTER TABLE map_strasse_g3 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g3_gid_seq'); |
---|
| 70 | CREATE INDEX map_strasse_g3_gidx ON map_strasse_g3 USING GIST ( wkb_geometry ); |
---|
| 71 | CREATE INDEX map_strasse_g3_widmung_idx ON map_strasse_g3 (widmung); |
---|
| 72 | -- |
---|
| 73 | -- |
---|
| 74 | DROP TABLE IF EXISTS map_strasse_g4; |
---|
| 75 | -- |
---|
| 76 | SELECT ST_SimplifyPreserveTopology(wkb_geometry, 50) AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 77 | INTO map_strasse_g4 |
---|
| 78 | FROM map_strasse_g3; |
---|
| 79 | -- |
---|
| 80 | DROP SEQUENCE IF EXISTS map_strasse_g4_gid_seq; |
---|
| 81 | CREATE SEQUENCE map_strasse_g4_gid_seq; |
---|
| 82 | ALTER TABLE map_strasse_g4 ADD COLUMN gid INTEGER; |
---|
| 83 | UPDATE map_strasse_g4 SET gid = nextval('map_strasse_g4_gid_seq'); |
---|
| 84 | ALTER TABLE map_strasse_g4 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g4_gid_seq'); |
---|
| 85 | CREATE INDEX map_strasse_g4_gidx ON map_strasse_g4 USING GIST ( wkb_geometry ); |
---|
| 86 | CREATE INDEX map_strasse_g4_widmung_idx ON map_strasse_g4 (widmung); |
---|
| 87 | -- |
---|
| 88 | -- |
---|
| 89 | DROP TABLE IF EXISTS map_strasse_g5; |
---|
| 90 | -- |
---|
| 91 | SELECT ST_SimplifyPreserveTopology(wkb_geometry, 100) AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 92 | INTO map_strasse_g5 |
---|
| 93 | FROM map_strasse_g4; |
---|
| 94 | -- |
---|
| 95 | DROP SEQUENCE IF EXISTS map_strasse_g5_gid_seq; |
---|
| 96 | CREATE SEQUENCE map_strasse_g5_gid_seq; |
---|
| 97 | ALTER TABLE map_strasse_g5 ADD COLUMN gid INTEGER; |
---|
| 98 | UPDATE map_strasse_g5 SET gid = nextval('map_strasse_g5_gid_seq'); |
---|
| 99 | ALTER TABLE map_strasse_g5 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g5_gid_seq'); |
---|
| 100 | CREATE INDEX map_strasse_g5_gidx ON map_strasse_g5 USING GIST ( wkb_geometry ); |
---|
| 101 | CREATE INDEX map_strasse_g5_widmung_idx ON map_strasse_g5 (widmung); |
---|
| 102 | -- |
---|
| 103 | -- |
---|
| 104 | DROP TABLE IF EXISTS map_strasse_g6; |
---|
| 105 | -- |
---|
| 106 | SELECT (ST_Dump(ST_SimplifyPreserveTopology(wkb_geometry, 200))).geom AS wkb_geometry, widmung, name_, bezeichnung |
---|
| 107 | INTO map_strasse_g6 |
---|
| 108 | FROM map_strasse_g5; |
---|
| 109 | -- |
---|
| 110 | DROP SEQUENCE IF EXISTS map_strasse_g6_gid_seq; |
---|
| 111 | CREATE SEQUENCE map_strasse_g6_gid_seq; |
---|
| 112 | ALTER TABLE map_strasse_g6 ADD COLUMN gid INTEGER; |
---|
| 113 | UPDATE map_strasse_g6 SET gid = nextval('map_strasse_g6_gid_seq'); |
---|
| 114 | ALTER TABLE map_strasse_g6 ALTER COLUMN gid SET DEFAULT nextval('map_strasse_g6_gid_seq'); |
---|
| 115 | CREATE INDEX map_strasse_g6_gidx ON map_strasse_g6 USING GIST ( wkb_geometry ); |
---|
| 116 | CREATE INDEX map_strasse_g6_widmung_idx ON map_strasse_g6 (widmung); |
---|
| 117 | -- |
---|