How to prevent duplicates from being generated in Spatial Join in PostGIS

Question

I'm trying to create a spatial view which meets the following criteria:

Sentinel hotspots registered within 2km of powerline (feeder)

AND

QRFS point located within 4km radius of sentinel hotspots

AND

Fire danger index rating Moderate or above.

I have been somewhat successful creating the query which is creating the buffers that I want. However, I need to modify the spatial join because it is duplicating the buffer for every line that it intersects. I simply want one buffer.

This is my code:

SELECT  row_number() OVER () AS gid,
    sub_qry.geom
   FROM (   SELECT (st_dump(st_union(st_buffer(sentinel.geom, 2000::double precision)::geometry))).geom::geography AS geom
                FROM dea_hotspot_centroid sentinel
                JOIN bom_fire_danger_ratings ratings ON st_intersects(ratings.geom, sentinel.geom)
                JOIN gis.qrfs_permitted_burn qrfs  ON st_dwithin(qrfs.geom, sentinel.geom, 4000)
                WHERE 
                    ratings.day = 1::double precision 
                    AND NOT (sentinel.power >=50 AND sentinel.confidence >=80)
                    AND (ratings.fire_risk in ('Moderate', 'Extreme','Severe','Catastrophic'))
         ) sub_qry
     JOIN feeders f ON st_intersects(sub_qry.geom, ST_Union(f.geom))

In the below image, the buffer is created around the hotspots (orange circles). There are 13 powerlines which intersect the buffer.

How can I modify the code to only create one polygon instead of duplicating it for each powerline which it intersects?

Answer 1

You could try to select first all the sentinel that meets the conditions and after that create the buffers. You can use EXISTS subquery expression to test if there exists a row in another table that fulfills a condition.

Something like this:

SELECT
  (st_dump(st_union(st_buffer(sentinel.geom, 2000)))).geom geom
FROM
  dea_hotspot_centroid sentinel
WHERE
  EXISTS (
    SELECT 1 FROM bom_fire_danger_ratings ratings
    WHERE st_intersects(ratings.geom, sentinel.geom) AND ratings.fire_risk in ('Moderate', 'Extreme','Severe','Catastrophic')
  )
  AND EXISTS (
    SELECT 1 FROM gis.qrfs_permitted_burn qrfs
    WHERE st_dwithin(qrfs.geom, sentinel.geom, 4000)
  )
  AND EXISTS (
    SELECT 1 FROM feeders
    WHERE st_dwithin(feeders.geom, sentinel.geom, 2000)
  )

Answer 2

Joins can be made just like you did but then select only distinct sentinels before buffering. This might utilize spatial indexes better than my previous solution.

SELECT
  (st_dump(st_union(st_buffer(unique_sentinels.geom, 2000)))).geom geom
FROM (
  SELECT DISTINCT ON (sentinel.id)
    sentinel.id,
    sentinel.geom
  FROM
    dea_hotspot_centroid sentinel
    JOIN bom_fire_danger_ratings ratings
      ON st_intersects(ratings.geom, sentinel.geom)
    JOIN gis.qrfs_permitted_burn qrfs
      ON st_dwithin(qrfs.geom, sentinel.geom, 4000)
    JOIN feeders
      ON st_dwithin(feeders.geom, sentinel.geom, 2000)
  WHERE
    ratings.day = 1::double precision 
    AND NOT (sentinel.power >=50 AND sentinel.confidence >=80)
    AND ratings.fire_risk in ('Moderate', 'Extreme','Severe','Catastrophic')
) unique_sentinels

You could run EXPLAIN ANALYZE for queries to see how those are actually performed and which indexes are used.