Skip to main content
added 1 character in body
Source Link
geozelot
  • 30.9k
  • 4
  • 34
  • 57
  • in 9 out of 10 cases if you could use a buffer, don't!
  • computational complexity increases with M * n computation steps (with M & n being the amount of vertices included per geometry); with M = 100000 (input Polygon), the difference of n = 1 (Point) and n = 32 (default buffer Polygon) per geometry comparison is massive
  • whatever expression you use, in any direct or indirect filter conditions (WHERE, JOIN, ORDER BY, ...), needs to be covered explicitly by an index to gain index driven performance; this includes the ST_Transform result
  • depending on volatility, and in non-fenced queries, PostgreSQL' s query planerplanner is perfectly able to cache simple expressions and function results; this should be the case with your repeated polygon creation - in either way, this is the least of your concerns, performance-wise
  • in 9 out of 10 cases if you could use a buffer, don't!
  • computational complexity increases with M * n computation steps (with M & n being the amount of vertices included per geometry); with M = 100000 (input Polygon), the difference of n = 1 (Point) and n = 32 (default buffer Polygon) per geometry comparison is massive
  • whatever expression you use, in any direct or indirect filter conditions (WHERE, JOIN, ORDER BY, ...), needs to be covered explicitly by an index to gain index driven performance; this includes the ST_Transform result
  • depending on volatility, and in non-fenced queries, PostgreSQL' s query planer is perfectly able to cache simple expressions and function results; this should be the case with your repeated polygon creation - in either way, this is the least of your concerns, performance-wise
  • in 9 out of 10 cases if you could use a buffer, don't!
  • computational complexity increases with M * n computation steps (with M & n being the amount of vertices included per geometry); with M = 100000 (input Polygon), the difference of n = 1 (Point) and n = 32 (default buffer Polygon) per geometry comparison is massive
  • whatever expression you use, in any direct or indirect filter conditions (WHERE, JOIN, ORDER BY, ...), needs to be covered explicitly by an index to gain index driven performance; this includes the ST_Transform result
  • depending on volatility, and in non-fenced queries, PostgreSQL' s query planner is perfectly able to cache simple expressions and function results; this should be the case with your repeated polygon creation - in either way, this is the least of your concerns, performance-wise
Source Link
geozelot
  • 30.9k
  • 4
  • 34
  • 57

Some notes about your queries and ideas:

  • in 9 out of 10 cases if you could use a buffer, don't!
  • computational complexity increases with M * n computation steps (with M & n being the amount of vertices included per geometry); with M = 100000 (input Polygon), the difference of n = 1 (Point) and n = 32 (default buffer Polygon) per geometry comparison is massive
  • whatever expression you use, in any direct or indirect filter conditions (WHERE, JOIN, ORDER BY, ...), needs to be covered explicitly by an index to gain index driven performance; this includes the ST_Transform result
  • depending on volatility, and in non-fenced queries, PostgreSQL' s query planer is perfectly able to cache simple expressions and function results; this should be the case with your repeated polygon creation - in either way, this is the least of your concerns, performance-wise

Some considerations:

  • you absolutely want to use ST_DWithin; geometric proximity searches can usually truthy out early, while intersections and the likes always need a full scan over the Cartesian product of vertices
  • PostgreSQL cannot resolve dynamic index conditions; with a CASE statement as condition, PostgreSQL will fall back to sequentially scanning for matches

With all this in mind, you generally want to run sth. like this:

SELECT *
FROM   <points> AS pt
WHERE  ST_Transform(pt.geom, <SRID>) && ST_Expand(ST_Transform(<POLYGON>, <SRID>), 1000)
 AND   ST_DWithin(
         ST_Transform(pt.geom, <SRID>),
         ST_Transform(<POLYGON>, <SRID>),
         CASE
           WHEN p.class = 'City' THEN 1000
           WHEN p.class = 'Town' THEN 500
           ELSE 0
         END
       )
;

or, with a join on a CTE

WITH
  poly AS MATERIALIZED (
    SELECT ST_Transform(<PLYGON>, <SRID>) AS geom
  )
SELECT *
FROM   <points> AS pt
JOIN   poly
  ON   ST_Transform(pt.geom, <SRID>) && ST_Expand(poly.geom, 1000)
WHERE  ST_DWithin(
         ST_Transform(pt.geom, <SRID>),
         poly.geom,
         CASE
           WHEN p.class = 'City' THEN 1000
           WHEN p.class = 'Town' THEN 500
           ELSE 0
         END
       )
;

where the index gets utilized for all matches in the largest proximity via an explicit && bbox filter, so that calculations are only done on that results set.

Note that

  • a functional index on the transformed result is mandatory for index driven performance, i.e.

    CREATE INDEX ON <points> USING GIST ( ST_Transform(<points>.geom, <SRID>) );
    
  • the same could be achieved using the GEOGRAPHY type, for when a single projection is out-scoped

  • this would ultimately be improved by ST_SubDivide'ing your Polygon into a(n indexed) table


Depending on a multitude of factors (with some of the more obvious being table size, category size and approx. result set size) it may be more performant to run different queries, e.g. a UNION ALL of two separate ST_DWithin calls on category filters - but that is some later stage optimization and probably not necessary with the general optimization in the above query.