[postgis-users] st_intersection polygons only

[Willy-Bas Loos]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140227/91073ba7/attachment.html>