[53] | 1 |
|
---|
| 2 | -- Vereinfachung der Geometrie mit der PostGIS-Function ST_Simplify
|
---|
| 3 |
|
---|
| 4 | -- Stand 2010-10-11
|
---|
| 5 |
|
---|
| 6 | -- Kommentar siehe Datei "alkis_geom_simplify.sql"
|
---|
| 7 | -- Siehe Diskussion auf der Mailingliste
|
---|
| 8 |
|
---|
| 9 | -- http://www.postgis.org/documentation/manual-1.3/ch06.html#id2575027
|
---|
| 10 |
|
---|
| 11 | -- ST_Simplify(geometry, tolerance)
|
---|
| 12 |
|
---|
| 13 | -- Returns a "simplified" version of the given geometry using the Douglas-Peuker algorithm.
|
---|
| 14 | -- Will actually do something only with (multi)lines and (multi)polygons but you can safely call it with any kind of geometry.
|
---|
| 15 | -- Since simplification occurs on a object-by-object basis you can also feed a GeometryCollection to this function.
|
---|
| 16 | -- Note that returned geometry might loose its simplicity (see IsSimple)
|
---|
| 17 |
|
---|
| 18 |
|
---|
| 19 | -- geeignete Tabellen für die Optimierung:
|
---|
| 20 |
|
---|
| 21 | CREATE VIEW simplify_optimierbare_tabellen
|
---|
| 22 | AS
|
---|
| 23 | SELECT f_table_name, type
|
---|
| 24 | FROM geometry_columns
|
---|
| 25 | WHERE not f_geometry_column = 'dummy'
|
---|
| 26 | AND type in ('LINESTRING', 'POLYGON', 'MULTIPOLYGON'); -- ,'GEOMETRY' ?
|
---|
| 27 |
|
---|
| 28 |
|
---|
| 29 | -- liefert u.a.:
|
---|
| 30 | --
|
---|
| 31 | --"ax_flurstueck" "MULTIPOLYGON"
|
---|
| 32 | --"ax_wald" "POLYGON"
|
---|
| 33 | --"ax_wohnbauflaeche" "POLYGON"
|
---|
| 34 |
|
---|
| 35 |
|
---|
| 36 | -- einfacher Test der Function
|
---|
| 37 | CREATE VIEW flst_simple
|
---|
| 38 | AS
|
---|
| 39 | SELECT gml_id,
|
---|
| 40 | flurstueckskennzeichen,
|
---|
| 41 | st_simplify(wkb_geometry, 0.01) AS geom_simple
|
---|
| 42 | FROM ax_flurstueck
|
---|
| 43 | LIMIT 200;
|
---|
| 44 |
|
---|
| 45 |
|
---|
| 46 | -- Mengenanalyse zum Finden der passenden Toleranz
|
---|
| 47 | -- Je eine Spalte zaehlt die durchschnittliche Anzahl von Punkten im Geometriefeld
|
---|
| 48 | -- Anzezeigt wird: ursprüngliche Anzahl und Simplify mit 1mm, 1cm und 5 cm.
|
---|
| 49 |
|
---|
| 50 | -- Flurstueck
|
---|
| 51 | CREATE VIEW analyse_simple_flst
|
---|
| 52 | AS
|
---|
| 53 | SELECT
|
---|
| 54 | count(gml_id) AS anzahl,
|
---|
| 55 | round(avg(ST_npoints(wkb_geometry)),2) AS punktanzahl_orig,
|
---|
| 56 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.001))),2) AS punktanzahl_simply_mm,
|
---|
| 57 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.01))),2) AS punktanzahl_simply_cm,
|
---|
| 58 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.05))),2) AS punktanzahl_simply_5cm
|
---|
| 59 | FROM ax_flurstueck
|
---|
| 60 | ;
|
---|
| 61 |
|
---|
| 62 | -- Nutzungsart Wald
|
---|
| 63 | CREATE VIEW analyse_simple_wald
|
---|
| 64 | AS
|
---|
| 65 | SELECT
|
---|
| 66 | count(gml_id) AS anzahl,
|
---|
| 67 | round(avg(ST_npoints(wkb_geometry)),2) AS punktanzahl_orig,
|
---|
| 68 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.001))),2) AS punktanzahl_simply_mm,
|
---|
| 69 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.01))),2) AS punktanzahl_simply_cm,
|
---|
| 70 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.05))),2) AS punktanzahl_simply_5cm
|
---|
| 71 | FROM ax_wald
|
---|
| 72 | ;
|
---|
| 73 |
|
---|
| 74 | -- Nutzungsart Wohnflaeche
|
---|
| 75 | CREATE VIEW analyse_simple_wohn
|
---|
| 76 | AS
|
---|
| 77 | SELECT
|
---|
| 78 | count(gml_id) AS anzahl,
|
---|
| 79 | round(avg(ST_npoints(wkb_geometry)),2) AS punktanzahl_orig,
|
---|
| 80 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.001))),2) AS punktanzahl_simply_mm,
|
---|
| 81 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.01))),2) AS punktanzahl_simply_cm,
|
---|
| 82 | round(avg(ST_npoints(st_simplify(wkb_geometry, 0.05))),2) AS punktanzahl_simply_5cm
|
---|
| 83 | FROM ax_wohnbauflaeche
|
---|
| 84 | ;
|
---|
| 85 |
|
---|
| 86 |
|
---|
| 87 | --
|
---|
| 88 | -- Tabelle Anz.Punkte 1 mm 1 cm 5 cm
|
---|
| 89 | -- ----------------- -------- ------ ------ ------ ------
|
---|
| 90 | -- Musterkarte RLP:
|
---|
| 91 | -- ax_flurstueck 2369 20.45 10.24 9.06 8.80
|
---|
| 92 | -- ax_wald 89 20.70 11.53 11.15 11.08
|
---|
| 93 | -- ax_wohnbauflaeche 76 28.37 12.59 11.53 10.91
|
---|
| 94 | -- Lage/Lippe:
|
---|
| 95 | -- ax_flurstueck 23589 30.55 12.60 11.57 11.08
|
---|
| 96 | -- ax_wald 1207 61.44 27.88 27.24 26.28
|
---|
| 97 | -- ax_wohnbauflaeche 2357 44.41 18.60 16.55 15.54
|
---|
| 98 |
|
---|
| 99 |
|
---|
| 100 | -- Die Mengenanalyse zeigt eine grosse Optimierung bereits im Millimeter-Bereich.
|
---|
| 101 |
|
---|
| 102 |
|
---|
| 103 | -- END -- |
---|