[postgis-users] Help with a multipolygon filtering function

[Nicklas Avén]

As I understand it you want to find (and remove) parts of multipolygons
that has no neighbour multi part within a certain distance.

First, in your code you are usin the geometry instead of the double
value as teh last parameter in ST_DWithin

to get a list of all polygons in a multipolygon you use st_dump.

To find those parts far away you could do something like:


SELECT a.the_geom, a.id FROM 
(SELECT (st_dump(the_geom)).geom as the_geom, id from myTable) as a
left join 
(SELECT (st_dump(the_geom)).geom as the_geom, id from myTable) as b
on st_dwithin(a.the_geom, b.the_geom, theDistance)
where b.id is null;

but if you want to find the geometries instead that does have neighbor
parts to use, be careful not to just på "is not null" at the end. There
is probably some other way to do it, but a safe way should be to do
just:

Select * from myTable where id not in 
(
SELECT a.the_geom, a.id FROM 
(SELECT (st_dump(the_geom)).geom as the_geom, id from myTable) as a
left join 
(SELECT (st_dump(the_geom)).geom as the_geom, id from myTable) as b
on st_dwithin(a.the_geom, b.the_geom, theDistance)
where b.id is null
);


Untested, but the below should recreate the multipolygons in a table
(myTable) with  without the too far away parts. I hope it works :-)

Select st_collectionextract(st_collect((st_dump(the_geom)).geom), 3)
from 
(Select (st_dump(the_geom)).geom as the_geom,(st_dump(the_geom)).path as
the_path, id from myTable) as whole_table
 left join
(SELECT a.the_path, a.id FROM 
(SELECT (st_dump(the_geom)).geom as the_geom ,(st_dump(the_geom)).path
as the_path, id from myTable) as a
left join 
(SELECT (st_dump(the_geom)).geom as the_geom, id from myTable) as b
on st_dwithin(a.the_geom, b.the_geom, theDistance)
where b.id is null) too_far_away 
on whole_table.id=too_far_away.id and
whole_table.the_path=too_far_away.path
where too_far_away.path is null
group by whole_table.id;


HTH
Nicklas



I want to clean up some multipolygons and remove any polygons over a
certain
distance by using a postgis function.

This is the closest I've come:

    CREATE OR REPLACE FUNCTION filter_polygons_within(geometry, double
precision)
      RETURNS geometry AS
    $BODY$
    SELECT ST_Multi(ST_Collect(final_geom.poly)) AS filtered_geom
    FROM (
        SELECT DISTINCT a.poly
        FROM (
            SELECT ST_GeometryN(ST_Multi($1), generate_series(1,
ST_NumGeometries(ST_Multi($1)))) AS poly,
            (SELECT ST_GeometryN(ST_Multi($1), generate_series(1,
ST_NumGeometries(ST_Multi($1))))) as poly_b
        ) AS a
        WHERE a.poly != a.poly_b
        AND ST_DWithin(a.poly, a.poly_b, $1)
    ) as final_geom
    $BODY$
    LANGUAGE 'sql' IMMUTABLE;

However, the where clause isn't working for me - any ideas on how to
achieve
this?  I'm not sure on how to compare all polygons with each other, I
know
how to get a list of polygons;

    ST_GeometryN(ST_Multi($1), generate_series(1,
ST_NumGeometries(ST_Multi($1))))

I'm just not sure how to compare each one with another and return the
distinct polygon results.

Thanks in advance,

Ross