PostGIS intersects and case function

Question

Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons that each have a specific value that needs to be tied to the building these polygons intersect with. So far it is set up by first creating a new table (named temp) with only the buildings that intersects with the polygons:

SELECT buildings.geom as geom_build, polygons.geom as geom_poly, polygons.value, buildings.id
into temp
From buildings, polygons
Where st_intersects(buildings.geom, polygons.geom);

and then the values are divided into sections using a case, that will later be tied to the buildings:

ALTER TABLE temp ADD column IF NOT EXISTS "interest" integer

and

UPDATE temp set interest =
(Case
    When temp.value <= 5 THEN 1
    When temp.value BETWEEN 6 AND 10 THEN 2
    When temp.value BETWEEN 11 AND 15 THEN 3
    When temp.value BETWEEN 16 AND 20 THEN 4
    When temp.value >= 21 then 5
End);

These 1, 2, 3, 4 and 5 are the values of interest and they are paired back to the buildings table by matching buildings id. There can be multiple polygons under one building, and it seems it is the value of the last (in the iteration) polygon that intersects with the building that is assigned.

My wish is to always assign the lowest value from however many polygons that may intersect to the building instead of getting the value that came from the last polygon in the iteration. Or in other words, if more than one condition in the case were to be true isolated from eachother, I want some condition returns to have higher priority than others.

For example if a building intersects with three polygons, where the first polygon has the value 2, the second has the value 7 and the third one has the value 12. If the third polygon was the last to go through the case, then the building would end up with the case return value '3', as the first two returns would be overwritten. Is there a way to make sure a return of '1' will never be overwritten by a higher number?

update:

I solved it by using the following sql lines:

Select id, MIN(interestvalue)
into tempTwo
from temp
GROUP BY id;

And then matching this min interest value back to the buildings using the ID.

Answer 1

You can use:
Order By value asc limit 1;
This will order your polygon value column ascendingly, then choose the first row of the order which is in this case the lowest.
Or easier try this: create a new column (interest) in the polygon layer and apply your case statement already there. Then you can use QGIS and apply "join attributes by location summary" process, where your input would be the new polygon layer, the join layer is the buildings layer, the geometric predicate is intersection and the field to summarize is the value, and finally the summary to calculate is min (minimum value). I think the result would be your original buildings layer with an addition column with the interest value which corresponds to minimum interest values of the intersection polygons.

Answer 2

Or, update your buildings table directly:

UPDATE buildings AS a
  SET interest = (
    SELECT CASE
             WHEN b.value <= 5
                THEN 1
             WHEN ...
           END
    FROM polygons AS b
    WHERE ST_Intersects(a.geom, b.geom)
    ORDER BY b.value ASC
    LIMIT 1
  );