<div dir="ltr"><div><div><div><div><div>Hi Remi,<br><br></div>2 yeas later, but i had to look this up again.<br></div>I just wanted to say that this is a great solution.<br><br></div>Here's an example:<br>with a(id,geom) as (<br>values (1, st_geomfromewkt('SRID=28992;POLYGON((0 0,0 2,0 1,1 1,1 0,0 0))'))<br>)<br>SELECT st_isvalid(a.geom)<br> , st_astext(st_makevalid(a.geom)) as valid<br> , st_astext(ST_CollectionExtract(st_makevalid(a.geom),3)) as valid_poly<br>from a<br><br></div>Cheers,<br><br></div>WBL<br><div><div><div><div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Feb 28, 2014 at 9:56 AM, Rémi Cura <span dir="ltr"><<a target="_blank" href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>></span> wrote:<br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><div dir="ltr"><div>If you now you want to get only polygon, better use ST_CollectionExtract(...,3),<br></div>instead of explicitly getting the type, cheking ...<br><br>Cheers,<br>Rémi-C<br></div><div class="gmail-HOEnZb"><div class="gmail-h5"><div class="gmail_extra"><br>
<br><div class="gmail_quote">2014-02-27 22:50 GMT+01:00 Hugues François <span dir="ltr"><<a target="_blank" href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>></span>:<br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">
Hello,<br>
<br>
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 :<br>
<br>
WITH foo AS (<br>
SELECT a.gid, b.gid, (st_dump(st_intersection(a.<wbr>geom, b.geom))).geom from a, b<br>
WHERE st_intersects(a.geom, b.geom)<br>
)<br>
<br>
SELECT a.gid, b.gid, st_union(geom) AS geom from foo<br>
WHERE st_geometrytype(geom) LIKE 'ST_Polygon'<br>
GROUP BY a.gid, b.gid<br>
<br>
HTH<br>
<br>
Hugues<br>
<br>
De : <a target="_blank" href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.<wbr>osgeo.org</a> [mailto:<a target="_blank" href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@<wbr>lists.osgeo.org</a>] De la part de Willy-Bas Loos<br>
Envoyé : jeudi 27 février 2014 20:01<br>
À : PostGIS Users Discussion<br>
Objet : Re: [postgis-users] st_intersection polygons only<br>
<div><div><br>
so this kinda works, but:<br>
* it now returns NULL as a geometry where it should return no record at all<br>
* it is about a factor 20 (!) slower than st_intersection<br>
Besides that it can only be used for multipolygons.<br>
And i would need a better name for it :)<br>
Here's the code, i would really appreciate any comments that could help improve it:<br>
<br>
create or replace function intersection_x (ageom geometry, bgeom geometry) returns geometry as $$<br>
declare<br>
t_result geometry; --the result of st_intersection<br>
t_out geometry; --the output<br>
t_type text; --the geometrytype of the result<br>
t_i integer; --a counter<br>
begin<br>
select st_intersection(ageom, bgeom) into t_result;<br>
select st_geometrytype(t_result) into t_type;<br>
if t_type = 'ST_Polygon' then<br>
--cast to multi<br>
select st_multi(t_result) into t_out;<br>
elsif t_type = 'ST_GeometryCollection' then<br>
--extract polys<br>
t_out:=st_geomfromtext('<wbr>GEOMETRYCOLLECTION EMPTY'); --so that st_merge will work<br>
for t_i in 1..st_numgeometries(t_result) loop<br>
select st_geometrytype(st_geometryn(<wbr>t_result, t_i)) into t_type;<br>
if t_type = 'ST_Polygon' then<br>
select st_union(st_multi(st_<wbr>geometryn(t_result, t_i)), t_out) into t_out;<br>
elsif t_type = 'ST_MultiPolygon' then<br>
select st_union(t_result,t_out) into t_out;<br>
else<br>
continue; --next geometry of the collection<br>
end if;<br>
end loop;<br>
if t_out=st_geomfromtext('<wbr>GEOMETRYCOLLECTION EMPTY') then<br>
return null; --would like to return no record<br>
end if;<br>
elsif t_type = 'ST_MultiPolygon' then<br>
t_out:=t_result;<br>
else<br>
return null;<br>
--would like to return no record at all.<br>
end if;<br>
<br>
return t_out;<br>
end<br>
$$ language plpgsql stable strict;<br>
<br>
On Thu, Feb 27, 2014 at 5:01 PM, Willy-Bas Loos <<a target="_blank" href="mailto:willybas@gmail.com">willybas@gmail.com</a>> wrote:<br>
Hi,<br>
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.<br>
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.<br>
Mostly multipolygon will do fine, so that would make that function a lot simpler.<br>
Now i could make a function that does that, filtering results by their geometrytype.<br>
I just wanted to check if something similar already exists?<br>
Cheers,<br>
WBL<br>
<br>
<br>
--<br>
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth<br>
<br>
<br>
<br>
--<br>
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth<br>
</div></div>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-<wbr>bin/mailman/listinfo/postgis-<wbr>users</a><br>
</blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a target="_blank" rel="noreferrer" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-<wbr>bin/mailman/listinfo/postgis-<wbr>users</a><br></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature"><div dir="ltr">Willy-Bas Loos<br></div></div>
</div></div></div></div></div></div></div></div>