I'm working in Postgres 9.6. I have a table that looks like this:

 gid        | integer                     | not null default nextval('usgovt_gid_seq'::regclass)
 category   | character varying(12)       |
 geom       | geometry(MultiPolygon,5070) |
    "usgovt_pkey" PRIMARY KEY, btree (gid)
    "ix_usgovt_category" btree (category)
    "usgovt_geom_idx" gist (geom)

I want to retrieve the union of every polygon with a category of 1:

SELECT ST_Union(geom) AS geom
FROM usgovt
WHERE category='1'
GROUP BY category;

However this query is EXTREMELY slow (half an hour and hasn't finished yet), and this is on a reasonably powerful machine, and I've tweaked Postgres's memory settings etc.

Why is this slow? Shouldn't it be instant? Since this is a single table, presumably the polygons can't overlap, so it's pretty much just an additive operation. PostGIS just needs to add them together to make a single large MultiPolygon.

Or perhaps polygons can overlap within a table? If so, is there some way to tell PostGIS that these polygons don't overlap, so it can just add them together?

  • Union removes overlapping areas and also common boundaries. It is not extremely light especially if geometries have lots of vertices. – user30184 Apr 24 '17 at 12:02
  • 2
    Perhaps you do not want to make a union with internal boundaries removed but just a collection with postgis.net/docs/ST_Collect.html. What Union is for PostGIS and in OGC standards is Merge for ESRI which makes confusion sometimes. – user30184 Apr 24 '17 at 12:33
  • Could you draw a sketch about what you have and what is your goal? I may understand you wrong. – user30184 Apr 24 '17 at 13:37
  • Polygons can overlap within a table. – amball Apr 25 '17 at 17:53
  • @user30184 the postgis st_union behaves more like the ESRI dissolve tool... – ziggy Apr 7 '18 at 3:29

PostGIS has no way to know that the polygons have no overlaps, so it's performing the same expensive overlay calculations that it would need to do for a set of polygons with arbitrary overlaps and intersections.

It's possible to perform a much faster union using with the following approach, though it's difficult to implement well in SQL:

  1. Extract all segments of all polygons
  2. Remove duplicate segments
  3. Polygonize the result
  4. Remove polygons representing holes
  • Thanks. For my purposes I can skip 2 since I know there aren't any duplicates! How would I go about 1 and 3? – Richard Apr 24 '17 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.