[GIS] PostGIS – ST_Collect but only include POLYGONs not POINTs

optimizationpostgisunion

In PostGSI, is there anyway to turn a GEOMETRYCOLLECTION into a MULTIPOLYGON by dropping all non-POLYGONs/non-MULTIPOLYGONs?

I'm doing a query that cuts a bunch of geometries (using ST_Intersection) and I want to group them together. I'm using ST_Collect to turn this into a GEOMETRYCOLLECTION. I could use ST_Union, but that's slower. Is there an alternative approach where I can drop all the non-POLYGONs/non-MULTIPOLYGONs from a GEOMETRY collection and just return a MULTIPOLYGON?

I know would be less accurate than ST_Union, but it should be faster. For what I'm doing a small hit in accuracy is worth it if it speeds it up.

Best Answer

ST_CollectionExtract is what you are looking for:

http://postgis.net/docs/manual-2.0/ST_CollectionExtract.html

Related Solutions

PostGIS – Joining Small Polygons to Form Larger Polygon using PostGIS

ST_Union would work, but your line work is almost assuredly not clean. So the boundaries of your little thingies don't all perfectly like up. You can gently snap them to a grid to try and increase the odds that vertexes line up, but I bet that you'll still have a few cases that don't work. Either they will be beyond tolerance or, more likely, there will be places where the vertices aren't paired, so there's a line on one side and a vertex on the other.

 CREATE TABLE merged AS
 SELECT ST_Union(ST_SnapToGrid(the_geom,0.0001)) 
 FROM parishes
 GROUP BY county_name;

If you have PostGIS 2.0, building a topology structure with a tolerance could get you to the answer you are looking for, if you have some luck.

[GIS] Merge polygons in postgis

SQL:

UPDATE coverage SET the_geom = ST_Union(
(SELECT the_geom FROM coverage WHERE id = :polygon_id),
(SELECT the_geom FROM coverage WHERE id = :sliver_id)
)
WHERE id = :polygon_id;

Is that what You meant?

Best Answer

Related Solutions

PostGIS – Joining Small Polygons to Form Larger Polygon using PostGIS

[GIS] Merge polygons in postgis

Related Question