PostGIS ST_Union with IF statement

Question

I have a dataset containing buildings where each building has a certain value/score. What I want to do is make clusters of these buildings depending on their values/scores. What I did so far is; I created a buffer around each building, so it is possible to determine which and how many other building are intersected by a building. This buffer has the corresponding value/score of the building where the buffer is built around.

What I want to do now, is that I want to dissolve these buffers as one, when/if the buildings that the buffer intersects have the same or higher value/score than the building the buffer is being built around (origin building).

So I'm thinking that a 'simple' if or where statement should do the trick. However, I'm a beginner in PostGIS and therefore I'm not sure how and where to write this in PostGIS.

My SQL query so far is:

        WITH sum AS (
WITH buffertest AS (
    SELECT Building_score, Building_ID, ST_Buffer(geom, 30)::geometry(polygon,28992) AS buffer 
     FROM postgis_export)

        SELECT b.Building_score AS bufferscores, b.Building_ID AS bufferID,  a.Building_score AS building, b.buffer 
FROM postgis_export a, buffertest b WHERE ST_intersects(b.buffer, a.geom)  AND b.gebouw_sco - a.gebouw_sco <= 0 )

        SELECT bufferscores, bufferid, SUM(gebouw), COUNT(*), buffer 
FROM sum 
GROUP BY bufferscores, bufferid, buffer 
ORDER BY bufferid

Answer 1

Two things:

• as you tried already, in SQL conditional relations can be translated into JOIN conditions in most cases
• proximity searches are better implemented using optimized functions on the initial geometries; derived geometries are not covered by the index

With that in mind, you could run

SELECT a."Building_ID",
       COUNT(b.*),
       SUM(b."Building_Score"),
       ST_Union(ST_Buffer(b.geom, 30)) AS geom
FROM   postgis_export AS a
JOIN   postgis_export AS b
  ON   ST_DWithin(a.geom, b.geom, 30)
WHERE  a."Building_Score" <= b."Building_Score"
GROUP BY
       1
-- HAVING COUNT(b.*) > 1
;

For each building in a, this will

• find all buildings in b (both are aliases for the postgis_export table; a self join) that are in proximity of 30 meters (using ST_DWithin on the original indexed geometries) and with a "Building_Score" greater or equal to the current row in a
• sum up their "Building_Score"
• union (dissolve) buffers around them

Note that this will also create buffer polygons for buildings that do not pass the condition (e.g. any isolated building, or those surrounded by buildings with lower "Building_Score"). If you only want results with 'clusters' of more than one bulding, un-comment the HAVING statement.

Note also that I am not sure what values you want to aggregate exactly...you seem to have mixed your real column names and dummy names in your query?