Updating table from another table with function ST_CONTAINS in PostGIS

Question

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

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.

Answer 1

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

• PostgreSQL COALESCE
• PostGIS ST_CONTAINS

---

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