[postgis-users] Simplifying a multipolygon layer, keeping polygon connection

[Nicolas Ribot]

On 11 April 2012 11:23, Sandro Santilli <strk at keybit.net> wrote:
> On Wed, Apr 11, 2012 at 11:14:48AM +0200, Nicolas Ribot wrote:
>
>> Yes, here are some stats:
>>
>> Mac OS X Snow Leopard, SSD drive, 8 gb ram
>> shared_buffers = 5120MB
>> work_mem = 1024MB
>>
>> POSTGIS="2.0.0 r9605" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23
>> September 2009" GDAL="GDAL 1.8.0, released 2011/01/12" LIBXML="2.7.8"
>> TOPOLOGY RASTER
>>
>> Number of objects in departement layer: 96 Num points  47036,
>>
>> 1°) Using spatial functions:
>> First run: 8953.493 ms, second run: 8808.133 ms
>>
>> 2°) Using Topology
>> First run: 13743 ms, second run: 14028 ms
>>
>> (server stopped and restarted between runs.
>
> Which steps involved by the two methods ?
>
> I can see some steps are in common:
>  1: Loading of input
>  2: Linking of faces to attribute (area_orig/area_intersection)
>
> Could the first step be taken out and the second be
> profiled separately ?
>

Here are the tests I ran:

a working table is created from the initial departement table, by
dumping out the polygon:

create table expl_dep as (
	select gid, code_dept, (st_dump(geom)).*
    from departement
);

This table is then used as input.

___________________________
Method 1: using one SQL query:

select (st_dump(st_polygonize(distinct geom))).geom as geom
from (
    select (st_dump(st_simplifyPreserveTopology(st_linemerge(st_union(geom)),
10000))).geom as geom
    from (
        select st_exteriorRing((st_dumpRings(geom)).geom) as geom
        from expl_dep
        ) as foo
) as bar;

-- takes 6.1 to 6.4 seconds

Face linking:

select d.gid, d.code_dept, s.geom
from departement d, simple_dep s
where st_intersects(d.geom, s.geom)
and st_area(st_intersection(d.geom, s.geom))/st_area(s.geom) > 0.5;

-- takes 1.2 to 1.3 s

________________________________________
Method 2, using topology wrapped in a function:
(topo1 and topo2 are created outside the function):

create or replace function testSimplTopo() returns text as $$

    select ST_CreateTopoGeo('topo1',ST_Collect(geom))
    from expl_dep;

    select ST_CreateTopoGeo('topo2', geom)
    from (
           select ST_Collect(st_simplifyPreserveTopology(geom, 10000)) as geom
           from topo1.edge_data
    ) as foo;

$$ language sql;

-- takes 12.0 to 12.3 seconds.

Face linking:

with simple_face as (
       select st_getFaceGeometry('topo2', face_id) as geom
       from topo2.face
       where face_id > 0
) select d.gid, d.code_dept, s.geom
from simple_face s, departement d
where st_intersects(d.geom, s.geom)
and st_area(st_intersection(s.geom, d.geom))/st_area(s.geom) > 0.5;

-- takes 1.2 to 1.3, like the other query

By the way, I tried to put calls to select dropTopology() into the
functions I wrote, but it fails.
Is it because it runs in the same transaction as the one that created the topo ?

Nicolas