3

Using PostgreSQL 12.

I have a table of polygons, a table of points, and a table of lines. All tables have GIST indexes on the geometry column. There is a view that includes both the lines and points, view_points_and_lines.

There is a script that I run on these that is fairly intensive, and I do not want to run it if there are more than 100_000 total features (points and lines) in the targeted polygons (combined).

Thus, before I run the "intensive query", I would like to determine whether the total count of the features in the targeted polygons is < 100_000. Note that if a feature is included in multiple polygons, it should be counted multiple times.

However, running a query like

SELECT COUNT(tf.*) FROM view_points_and_lines tf JOIN polygons sf ON ST_Intersects(tf.geom, sf.geom)

can be very slow itself, especially if there many polygons and much more than 100_000 points.

I have also tried returning a Boolean like:

SELECT COUNT(tf.*) < 100000 FROM view_points_and_lines tf JOIN polygons sf ON ST_Intersects(tf.geom, sf.geom)

but that does not seem to speed up the query, and the query plan is the same.

Given that I really only need a Boolean value, and the count can be approximate, is there any way to speed up this query?

5
  • Have you tried using a different indexing than GiST? E.g. SP-GiST - postgis.net/docs/using_postgis_dbmanagement.html 4.6.3
    – Encomium
    May 27, 2021 at 19:13
  • @Encomium I have not! I will give that a try, though it will take a while before I can report back :)
    – Avocado
    May 27, 2021 at 19:25
  • Cool, curious to hear how it goes. From the docs: "The performance tests reveal that SP-GiST indexes are especially beneficial when there are many overlapping objects, that is, with so-called “spaghetti data”."
    – Encomium
    May 27, 2021 at 19:27
  • ON tf.geom && sf.geom and ST_Intersects(tf.geom, sf.geom)
    – ziggy
    May 27, 2021 at 20:27
  • 1
    @ziggy tried it out - a huge improvement for some of the queries (like >10x!) Thanks! Hopefully in conjunction w/ SP-Gist index it will be even faster
    – Avocado
    May 27, 2021 at 21:32

1 Answer 1

1

Update as per comment:

To count the total amount of intersections with any of of the polygons, run a similar boolean COUNT condition like yours on both tables independently (as per the last paragraph below), and SUM them up:

SELECT SUM(_cnt) < 100000
FROM   (
    SELECT COUNT(e.*) AS _cnt
    FROM   polygons AS ply
    JOIN   <points> AS e
      ON   ply.geom && e.geom AND ST_Intersects(ply.geom, e.geom)
    UNION ALL
    SELECT COUNT(e.*) AS _cnt
    FROM   polygons AS ply
    JOIN   <lines> AS e
      ON   ply.geom && e.geom AND ST_Intersects(ply.geom, e.geom)
) q
;

Old answer:

You'd need a GROUP BY with accompanying HAVING filter to retrieve only those polygons (by <id> column) with less than or equal to 100.000 intersecting features

SELECT sf.<id>
FROM   view_points_and_lines AS tf
JOIN   polygons AS sf
  ON   ST_Intersects(tf.geom, sf.geom)
GROUP BY
       1
HAVING COUNT(tf.*) <= 100000
;

However, it is very likely that the unioned relations (in your View) are the key issue here; due to the union of multiple relations, the planner supposedly resolves to using the index on the polygons table to gather the intersection counts. Instead, you want each of the implicitly involved joined relations being index scanned.

Try

SELECT id
FROM   (
    SELECT ply.<id> AS id
           COUNT(e.*) AS cnt
    FROM   polygons AS ply
    JOIN   <points> AS e
      ON   ST_Intersects(ply.geom, e.geom)
    GROUP BY
           1
    UNION ALL
    SELECT ply.<id> AS id,
           COUNT(e.*) AS cnt
    FROM   polygons AS ply
    JOIN   <lines> AS e
      ON   ST_Intersects(ply.geom, e.geom)
    GROUP BY
           1
) q
GROUP BY
       id
HAVING SUM(cnt) <= 100000
;
3
  • hi @geozelot, thanks for the answer! I see now my question was unclear. What I meant to ask about is how to determine if the total count of features over all polygons is < 100_000
    – Avocado
    May 28, 2021 at 18:26
  • 1
    @Avocado oh I see...but it doesn't change much. See my update. Include an EXPLAIN ANALYZE output of your query if that makes no difference.
    – geozelot
    May 28, 2021 at 20:04
  • 1
    @Avocado btw. under certain circumstances (e.g shape of polygons), a simple && bbox intersection could be enough of an approximation.
    – geozelot
    May 28, 2021 at 20:07

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.