2

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?

enter image description here

2 Answers 2

4

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)
  )
2
  • Thanks. This worked. However, it significantly reduced the performance of the view. It now takes 18 seconds to refresh instead of less than 1 second. I can live with that as it is a materialized view that will only get refreshed when the data is downloaded.
    – Fezter
    Oct 4, 2023 at 23:48
  • @Fezter, I posted another solution that hopefully perform better.
    – LauriK
    Oct 5, 2023 at 5:52
0

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.

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.