[postgis-users] st_intersection polygons only

[Willy-Bas Loos]

Hi Remi,

2 yeas later, but i had to look this up again.
I just wanted to say that this is a great solution.

Here's an example:
with a(id,geom) as (
values (1, st_geomfromewkt('SRID=28992;POLYGON((0 0,0 2,0 1,1 1,1 0,0 0))'))
)
SELECT st_isvalid(a.geom)
    , st_astext(st_makevalid(a.geom)) as valid
    , st_astext(ST_CollectionExtract(st_makevalid(a.geom),3)) as valid_poly
from a

Cheers,

WBL

On Fri, Feb 28, 2014 at 9:56 AM, Rémi Cura <remi.cura at gmail.com> wrote:

> 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@
>> 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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 
Willy-Bas Loos
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160914/5607da40/attachment.html>