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