st_intersection polygons only

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

st_intersection polygons only

Willy-Bas Loos-3
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

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_intersection polygons only

Willy-Bas Loos-3
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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_intersection polygons only

Willy-Bas Loos-3

It's not so slow, my coleage changed the testdata.


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_intersection polygons only

hugfr
In reply to this post by Willy-Bas Loos-3
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 : [hidden email] [mailto:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>:
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 : [hidden email] [mailto:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_intersection polygons only

Willy-Bas Loos-3
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 <[hidden email]> 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 <[hidden email]>:
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 : [hidden email] [mailto:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



--
Willy-Bas Loos

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users