PostGIS: Split polygons into intersecting and non-intersecting and grouping on overlap

Question

I have a big query that I am looking to optimize. I am matching millions (expanding daily) of rows with Points against a mostly read-only table of (tens of thousands of) areas with (Multi)Polygons. The areas table contains 3 or more layers of overlapping areas of decreasing size (think country -> county -> municipality) in addition to user-defined areas that may overlap arbitrarily. Most of these polygons are made up of <300 points, some of 5-30 thousand points.

In the query I am joining positions with areas to aggregate an array of area UUIDs SELECT positions.id, array_agg(areas.uuid) ... to back a materialized view. I would like to refresh this materialized view about every fifteen minutes, but currently the query takes a couple of minutes to finish. I have managed a 2x orders of magnitude speedup by creating a materialized view of areas with subdivided geometry, but I am worried that this too will slow down over time.

My idea to speed this up even further is as follows: Pre-compute a table of all overlapping areas with the UUID array populated, like in the image below, so that each point will only ever be contained by ONE polygon with the UUIDs already aggregated. This will probably also benefit from subdivision.

Is this likely to result in a good speedup? How would I construct this query?

This question seems to be almost exactly what I am asking, but I lack the expertise to account for holes and MultiPolygons. Additionally I tried to correlate the resulting geometry collection with my areas to aggregate UUIDs and am not getting the results I expect:

WITH geometry AS (
    SELECT (ST_Dump(ST_Polygonize(geom))).geom AS geom FROM (
        SELECT ST_Union(geom) AS geom FROM (
            SELECT ST_ExteriorRing(geography::geometry) AS geom FROM areas
        ) AS noded_lines
    ) AS polygons
)

SELECT array_agg(areas.uuid) id, ST_AsText(geom)
FROM geometry
-- geom should be contained by at least one area
INNER JOIN areas ON ST_Contains(geom, areas.geography::geometry)
GROUP BY geom

This query returns just 37 rows, where each of the aggregated UUID arrays only contain one entry. This is obviously incorrect for 800+ areas where I know for a fact every split polygon should be contained within at least 3 areas.

I'll go for the simple subdivision to get decent speedup for now, but I would very much like to try this optimization if it can be made to work. If only for my own sanity.

Answer 1

From the doc of ST_Polygonize, you should try to use ST_Node before calling it, notably to create points where there is intersection between the lines.

Also, for query performance there is a lot to look at:

• First, be sure to have proper indexes, to do ANALYZE on your tables, and look at EXPLAIN to be sure your request uses indexes and does what you want to do and doesn't have useless steps.
• Secondly, you probably need to parallelize requests at some point. Some request are processed natively in parallel for most steps in the latest postgres versions, but sometime you still have some bottlenecks. Try to look at your CPU usage to see if there is something to gain here. If there is, you can launch multiple queries in parallel to treat parts of your points, preferably by geographic group (for exemple by geohash) so you will have all the points of a same zone treated at the same time and you can have slight cache performance improvments because all the polygons that you need for a point are already loaded by the previous points (not sure but can help if your ref table is above your cache size).
• Lastly, you can tweak your db parameters to use best your server power. You can take a look at an old answer of mine if you don't know what parameter to use: ST_DWithin exponentially slow. Cannot find what I am doing wrong