2

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
2

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?

3
  • Thank you, @geozelot ! This indeed does what I want/need. New for me was the Join on the DWithin function. – BramBroccoli Jun 9 '20 at 9:14
  • 1
    @BramBroccoli glad it helped! And it's really important that you now know about ST_DWithin; that function is about the most performant in terms of proximity searches, and among the most versatile in applications in PostGIS in general. Get familiar with the way the distance parameter is handled (-> CRS units), and get used to give it a quick though about if you can use it instead of other means, for all sorts of tasks. – geozelot Jun 9 '20 at 9:23
  • Very helpfull and informative @geozelot. I definitely will considered it for more projects! – BramBroccoli Jun 9 '20 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.