[postgis-users] st_intersection polygons only

[Hugues François]

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