# Changes between Version 5 and Version 6 of UsersWikiSimplifyPreserveTopology

Show
Ignore:
Timestamp:
04/08/12 07:13:19 (14 months ago)
Comment:

--

Unmodified
Removed
Modified
• ## UsersWikiSimplifyPreserveTopology

v5 v6
1515
1616{{{
17#!sh
1718shp2pgsql -IiD -g geom -s 2154 DEPARTEMENT.SHP departement | psql
1819}}}

2829== Steps ==
2930
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''
3132
32331. 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.
3334
3435{{{
36#!sql
3537create table poly as (
3638    select gid, code_dept, (st_dump(geom)).*

4244
4345{{{
46#!sql
4447create table rings as (
4548    select st_exteriorRing((st_dumpRings(geom)).geom) as g

5255
5356{{{
57#!sql
5458create table simplerings as (
5559    select st_simplifyPreserveTopology(st_linemerge(st_union(g)), 10000) as g

6165
6266{{{
67#!sql
6368create table simplelines as (
6469    select (st_dump(g)).geom as g

69745.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.
7075{{{
76#!sql
7177create table simplepolys as (
7278    select (st_dump(st_polygonize(distinct g))).geom as g

7884
7985{{{
86#!sql
8087alter table simplepolys  add column gid serial primary key;
8188create index simplepolys_geom_gist on simplepolys  using gist(g);

8592
8693{{{
94#!sql
8795create table simpledep as (
8896    select code_dept, g

981068. Second method is based on percentage of overlaping area comparison.
99107{{{
108#!sql
100109create table simpledep as (
101110        select distinct on (d.code_dept) d.code_dept, s.g as geom

114123
115124{{{
125#!sql
116126create table simple_departement as (
117127        select code_dept, st_collect(geom) as geom

131141[[Image(SPT_islands_removed.png)]]
132142
143== One big query ==
144
145{{{
146#!sql
147with 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,
164poly d
165where st_intersects(d.geom, qux.geom)
166order by d.code_dept, st_area(st_intersection(qux.geom, d.geom))/st_area(qux.geom) desc;
167}}}
168
133169Nicolas Ribot.