Changes between Version 5 and Version 6 of UsersWikiSimplifyPreserveTopology
- Timestamp:
- 04/08/12 07:13:19 (14 months ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
UsersWikiSimplifyPreserveTopology
v5 v6 15 15 16 16 {{{ 17 #!sh 17 18 shp2pgsql -IiD -g geom -s 2154 DEPARTEMENT.SHP departement | psql 18 19 }}} … … 28 29 == Steps == 29 30 30 ''Steps are divided into individual queries for the sake of clarity. The process could be done in a PL/PGSQL function to generalize it'''31 ''Steps are divided into individual queries for the sake of clarity. One big query summarize them at the end of this page'' 31 32 32 33 1. First extract the input multipolygons into polygons, keeping their departement code. This will allow us to associate attributes to each part of multipolygons at the end of the process. 33 34 34 35 {{{ 36 #!sql 35 37 create table poly as ( 36 38 select gid, code_dept, (st_dump(geom)).* … … 42 44 43 45 {{{ 46 #!sql 44 47 create table rings as ( 45 48 select st_exteriorRing((st_dumpRings(geom)).geom) as g … … 52 55 53 56 {{{ 57 #!sql 54 58 create table simplerings as ( 55 59 select st_simplifyPreserveTopology(st_linemerge(st_union(g)), 10000) as g … … 61 65 62 66 {{{ 67 #!sql 63 68 create table simplelines as ( 64 69 select (st_dump(g)).geom as g … … 69 74 5.rebuild the polygons, first by polygonizing the lines, with a distinct clause to eliminate overlaping segments that may prevent polygon to be created, then dump the collection of polygons into individual parts, in order to rebuild our layer. 70 75 {{{ 76 #!sql 71 77 create table simplepolys as ( 72 78 select (st_dump(st_polygonize(distinct g))).geom as g … … 78 84 79 85 {{{ 86 #!sql 80 87 alter table simplepolys add column gid serial primary key; 81 88 create index simplepolys_geom_gist on simplepolys using gist(g); … … 85 92 86 93 {{{ 94 #!sql 87 95 create table simpledep as ( 88 96 select code_dept, g … … 98 106 8. Second method is based on percentage of overlaping area comparison. 99 107 {{{ 108 #!sql 100 109 create table simpledep as ( 101 110 select distinct on (d.code_dept) d.code_dept, s.g as geom … … 114 123 115 124 {{{ 125 #!sql 116 126 create table simple_departement as ( 117 127 select code_dept, st_collect(geom) as geom … … 131 141 [[Image(SPT_islands_removed.png)]] 132 142 143 == One big query == 144 145 {{{ 146 #!sql 147 with poly as ( 148 select gid, code_dept, (st_dump(geom)).* 149 from departement 150 ) select distinct on (d.code_dept) d.code_dept, qux.geom 151 from ( 152 select (st_dump(st_polygonize(distinct baz.geom))).geom as geom 153 from ( 154 select (st_dump(geom)).geom as geom 155 from ( 156 select st_simplifyPreserveTopology(st_linemerge(st_union(geom)), 10000) as geom 157 from ( 158 select st_exteriorRing((st_dumpRings(geom)).geom) as geom 159 from poly 160 ) as foo 161 ) as bar 162 ) as baz 163 ) as qux, 164 poly d 165 where st_intersects(d.geom, qux.geom) 166 order by d.code_dept, st_area(st_intersection(qux.geom, d.geom))/st_area(qux.geom) desc; 167 }}} 168 133 169 Nicolas Ribot.
