[postgis-users] st_intersection polygons only

[Rémi Cura]

If you now you want to get only polygon, better use
ST_CollectionExtract(...,3),
instead of explicitly getting the type, cheking ...

Cheers,
Rémi-C


2014-02-27 22:50 GMT+01:00 Hugues François <hugues.francois at irstea.fr>:

> Hello,
>
> Maybe I don't understand your problem but in my mind you can select only
> polygons with a single query using st_dump,  like this one :
>
> WITH foo AS (
>         SELECT a.gid, b.gid, (st_dump(st_intersection(a.geom,
> b.geom))).geom from a, b
>         WHERE st_intersects(a.geom, b.geom)
> )
>
> SELECT a.gid, b.gid, st_union(geom) AS geom from foo
> WHERE st_geometrytype(geom) LIKE  'ST_Polygon'
> GROUP BY a.gid, b.gid
>
> HTH
>
> Hugues
>
> De : postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] De la part de Willy-Bas Loos
> Envoyé : jeudi 27 février 2014 20:01
> À : PostGIS Users Discussion
> Objet : Re: [postgis-users] st_intersection polygons only
>
> so this kinda works, but:
> * it now returns NULL as a geometry where it should return no record at all
> * it is about a factor 20 (!) slower than st_intersection
> Besides that it can only be used for multipolygons.
> And i would need a better name for it :)
> Here's the code, i would really appreciate any comments that could help
> improve it:
>
> create or replace function intersection_x (ageom geometry, bgeom geometry)
> returns geometry as $$
> declare
> t_result geometry; --the result of st_intersection
> t_out geometry; --the output
> t_type text; --the geometrytype of the result
> t_i integer; --a counter
> begin
> select st_intersection(ageom, bgeom) into t_result;
> select st_geometrytype(t_result) into t_type;
> if t_type = 'ST_Polygon' then
>     --cast to multi
>     select st_multi(t_result) into t_out;
> elsif t_type = 'ST_GeometryCollection' then
>     --extract polys
>     t_out:=st_geomfromtext('GEOMETRYCOLLECTION EMPTY'); --so that st_merge
> will work
>     for t_i in 1..st_numgeometries(t_result) loop
>         select st_geometrytype(st_geometryn(t_result, t_i)) into t_type;
>         if t_type = 'ST_Polygon' then
>             select st_union(st_multi(st_geometryn(t_result, t_i)), t_out)
> into t_out;
>         elsif t_type = 'ST_MultiPolygon' then
>             select st_union(t_result,t_out) into t_out;
>         else
>             continue; --next geometry of the collection
>         end if;
>     end loop;
>     if t_out=st_geomfromtext('GEOMETRYCOLLECTION EMPTY') then
>         return null; --would like to return no record
>     end if;
> elsif t_type = 'ST_MultiPolygon' then
>     t_out:=t_result;
> else
>     return null;
>     --would like to return no record at all.
> end if;
>
> return t_out;
> end
> $$ language plpgsql stable strict;
>
> On Thu, Feb 27, 2014 at 5:01 PM, Willy-Bas Loos <willybas at gmail.com>
> wrote:
> Hi,
> When overlaying 2 tables that have polygons with st_intersection, i
> sometimes get linestrings or points back, or a geometrycollection with a
> combination of types.
> What i actually want, is that i only get the overlapping parts (that is,
> overlapping interiors in DE9IM speak) in the geometrytype of the combined
> geometries.
> Mostly multipolygon will do fine, so that would make that function a lot
> simpler.
> Now i could make a function that does that, filtering results by their
> geometrytype.
> I just wanted to check if something similar already exists?
> Cheers,
> WBL
>
>
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
>
>
>
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140228/5ea377b3/attachment.html>