1

I am very new to PostGIS. I read that it should work faster than QGIS, so I thought I'd give it a go. I want to completely dissolve a shapefile of 1.2m polygons in the same way as QGIS built in dissolve function works.

This is my current code, which is very basic (filetodissolve is the table):

SELECT ST_Union(geom)
FROM filetodissolve f;

I've been running this for 1h 30m now and is showing no sign of stopping. Is there any method to speed this up.

18
  • 1
    Yes to the name of your table. With schema, for example public.yourtablename
    – Bera
    Commented Feb 2, 2023 at 14:45
  • 1
    If you do not need extremely accurate result, try this trick gis.stackexchange.com/questions/222976/….
    – user30184
    Commented Feb 2, 2023 at 14:48
  • 1
    I found a suggestion to use ST_Buffer(St_Collect(wkb_geometry), 0) in some old comment. That might also be worth trying. Using SET work_mem=50000; for giving more memory was also suggested. If you test with 10000 or 100000 features you will get preliminary results faster.
    – user30184
    Commented Feb 2, 2023 at 15:55
  • 1
    Do a research on 'add postgis gist index'
    – Kasper
    Commented Feb 2, 2023 at 16:21
  • 2
    Before adding a spatial index it might be good to check if it is missing gis.stackexchange.com/questions/241599/….
    – user30184
    Commented Feb 2, 2023 at 16:37

1 Answer 1

5

This is basically the same answer as to this question. It uses ST_ClusterDBSCAN to assign each cluster of intersecting/adjacent polygons an id and union based on id:

create index table123_index on test.table123 using GIST(geom); --Make sure you have a spatial index

create table test.table123_dissolved as
with clusters
    as (select st_clusterdbscan(geom,0,2) over() cluster_id, geom from test.table123)

select st_union(geom) geom 
from clusters 
where cluster_id is not null --Where there are adjacent polygons that have been assigned a cluster id
group by cluster_id

union

select geom from clusters where cluster_id is null --Polygons that are separate from all others get no cluster id
;
alter table test.table123_dissolved add column id serial;

With my test data it finishes in 120 s for 3 million features enter image description here

(I canceled Dissolve in QGIS after 20 min/35 % finished.)

6
  • Thankyou. I have been running the code for 18hs now. I have almost 1.3m polygons. The polygon layer is 1.2GB and is the extent of the UK. Will any of this change the speed of the union? Commented Feb 3, 2023 at 8:52
  • Do you have large complex multipolygons? Try converting them to single parts using ST_DUMP. But first try my answer of a subset of your 1.3 million polygons to make sure it is working
    – Bera
    Commented Feb 3, 2023 at 9:05
  • 1
    No I did a multipart to single part conversion in QGIS before I loaded the file into PostGIS/pgAgmin4 Commented Feb 3, 2023 at 9:11
  • 1
    I dont know if it makes any difference but you could try subdividing the geometries
    – Bera
    Commented Feb 3, 2023 at 9:14
  • This is a good approach if the data contains disjoint clumps of polygons. Probably won't help if all/many of the polygons are touching, though.
    – dr_jts
    Commented Feb 3, 2023 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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