3

I tried to update an attribute 'solved' of a polygon table (table name: polygon) by using an attribute 'solved' of another point table (table name: point).

enter image description here

What I want to do is that if ALL the points in a polygon has 'yes' in attribute 'solved', then the corresponding polygon will also have 'yes' in it's attribute 'solved', otherwise the polygon attribute 'solved' should have the value 'No'.

There are only 3 types of value for the attribute 'solved' in the point table: 'Yes', 'No' or Null.

So what I think how to build my SQL now, in other words, if a polygon contains any point with value 'No' its attribute solved, or has no value I the attribute, the polygon should return 'No' if its attribute solved. Otherwise the polygon will have the value 'Yes' in the attribute.

So I wrote something like that but it is not working:

UPDATE polygon
IF ST_CONTAINS (point.geom, polygon.geom) AND (point.solved = 'No' OR point.solved is null) 
SET solved = 'No'
ELSE 
SET solved = 'Yes'
FROM point, polygon;
END IF;

I got this error

ERROR: syntax error at or near "ST_CONTAINS"

Anyone has any clue what is wrong with that? Am I thinking correctly?

I know that this can be done easily in QGIS but I would like to figure it out since it will be part of my trigger when I update the point table.

2
  • 1
    You need to conform to the SQL language when you write SQL code, and UPDATE doesn't support an IF. Note that the JOIN keyword is the modern way to reference multiple tables (it's also more powerful than old-school table list in a FROM, and easier to optimize). – Vince Sep 20 '20 at 14:32
  • 1
    I strongly recommend to change your columns to a boolean field; that way, you can utilize common, internal boolean checks, statements and functions that would make your endevour a lot easier, a lot more idiomatic, more performant, and saves some storage space. – geozelot Sep 20 '20 at 18:56
2

I will write it this way :

UPDATE polygon
SET solved = COALESCE(point.solved, 'No')
FROM point
WHERE ST_CONTAINS (polygon.geom, point.geom)
;

Another way to do this is :

UPDATE polygon
SET solved = upd.solved
FROM (
  SELECT
    polygon.id,
    COALESCE(point.solved, 'No') AS solved
  FROM polygon, point
  WHERE ST_CONTAINS (polygon.geom, point.geom)
) upd
WHERE polygon.id = upd.id
;

Documentation links


EDIT

The queries above works if only one point is inside one polygon. Here is the solution with multiple points inside a polygon, and if all the points have the 'Yes' value inside a polygon, then update the polygon's solved value at 'Yes'.

UPDATE polygon
SET solved =
  CASE
    WHEN 'No' = ANY(upd.agg_solved) THEN 'No'
    ELSE 'Yes'
  END
FROM (
  SELECT
    polygon.id,
    ARRAY_AGG(COALESCE(point.solved, 'No')) AS agg_solved
  FROM polygon, point
  WHERE ST_CONTAINS (polygon.geom, point.geom)
  GROUP BY polygon.id
) upd
WHERE polygon.id = upd.id
;
  • ARRAY_AGG(COALESCE(point.solved, 'No')) : construct an array of solved values for each polygon id, so for each polygon ;
  • 'No' = ANY(upd.agg_solved) : if 'No' exists in the array, the expression is True, then, in our query, the result is 'No'.
4
  • yes, the SQL works now, thanks. But it solves my problem just partially. I would like that if all the points inside a polygon have the value "Yes" then the polygon will also have value "Yes". With your SQL the polygon can have the value "Yes" even just only one of the points inside the polygon has the value "Yes". – Cliff Lau Sep 20 '20 at 18:29
  • 3
    @CliffLau: answer edited. – J. Monticolo Sep 20 '20 at 20:07
  • I tested it as a query and put this also in a trigger, both worked! Thanks a lot! – Cliff Lau Sep 25 '20 at 6:10
  • @CliffLau: so, if the answer worked, please check this answer as accepted. – J. Monticolo Sep 25 '20 at 6:55

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.