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
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?
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.