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