1

I need to dissolve some large datasets using PostGIS but it takes a lot of time.

What I have already tried:

My datasets are some RF coverage with 5M of features and 2 ~ 5GB of data.

My postgis version is

POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

And my PostgreSQL version is

PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

Is there a way to improve st_union performance?

  • Large, complex features take time to assemble. I doubt there's much room for optimization. – Vince Aug 30 '18 at 12:57
  • 1
    If you need the result fast but it does not need to be extremely accurate you could try the rasterize-polygonize route like in gis.stackexchange.com/questions/222976/… – user30184 Aug 30 '18 at 13:13
1

Said like that, it is indeed complicated to help you, I don't think there is much room to big improvement in with st_union by itself (maybe what user30184 said would be simpler).

But if you give us much infos on the exact problem and the part of the request that is slow, we might see something?

By experience, the ST_Union seems to be a good way at first, but as soon as the data gets bigger it becomes useless. It is usually better to work locally, instead of doing one big structure.

For example, I recently had to change some code: I wanted to test if some roads were inside at least one of multiple overlapping polygon (buffer around some other lines), and I ended changing my request from st_union to something like:

SELECT rt.*
FROM roads rt
LEFT JOIN LATERAL
        (
            SELECT *
                FROM buffer as buf
                WHERE ST_Intersect(rt.geom, buf.geometry)
                ORDER BY ST_MaxDistance(rt.geom, buf.geom_line)
                LIMIT 1
        ) as buf
    ON TRUE
    WHERE buf.id IS NOT NULL; 

Which was way better in term of computational time, instead of doing the union, then doing the ST_Intersect request). So it may be a clue.

Maybe what you can use this kind of approach to dissolve only intersecting polygons between them, to form distinct polygones that would be easier for postgres to compute?

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.