source: trunk/data/konvert/postnas_0.5/alkis_test_simplify.sql @ 163

Revision 53, 3.6 KB checked in by frank.jaeger, 11 years ago (diff)
Line 
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
21CREATE VIEW simplify_optimierbare_tabellen
22AS
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
37CREATE VIEW flst_simple
38AS
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
51CREATE VIEW analyse_simple_flst
52AS
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
63CREATE VIEW analyse_simple_wald
64AS
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
75CREATE VIEW analyse_simple_wohn
76AS
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 --
Note: See TracBrowser for help on using the repository browser.