1

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.

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.

  • Thank you, this seems just like what I needed! BUT could you elaborate further on exactly where this line should be? It seems like it would need to be somewhere inside the case? – MJoer Jan 4 at 14:23
  • I have tried what you suggested. As it is now the new polygon layer has the values and the interest values now calculated from the case and the building layer only has the interest values from running the case beforehand. So to clarify it is the interest value field that is to be joined with? When I do try this, I end up with geometry invalid erros, but that seems to be something for me to fix. Also thank you very much for your patience and answer! – MJoer Jan 5 at 10:26
  • 1
    if you are using QGIS then use "fix geometries", if PostGIS you can use: select ST_MakeValid(geom) from buildings where ST_IsValid(geom) = false;check out the answer from ThingumaBob, it is compact and all is done in one step. – muri Jan 7 at 8:38
  • @muri ...you had it mentioned already, the key is the LIMIT 1 ,) – ThingumaBob Jan 7 at 8:57
1

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
  );

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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