How to verify that at least one point fall into a polygon with Postgis? I have two tables where table A has a geometry (4326) column that contains surfaces (~20M). A second table B has ~ 500K points (4326). What I want to do is "flagging" (adding a boolean column) every single surface in A that contains at least one of the points in B.

I did this:

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;


But I got all "flagged" values as true (which is impossible).

With joins/lateral I didn't go far too.

Any idea?
You're close. I wouldn't use EXISTS, which is really only useful for reducing a set. I would go with CROSS JOIN LATERAL and then coalesce the result. See how this performs.

SELECT a.id, 
  coalesce(result, false) AS flag
FROM a
CROSS JOIN LATERAL (
  SELECT true
  FROM b 
  WHERE ST_Contains(a.geom, b.geom)
  LIMIT 1
) AS r(result);


This should work on an index if you have a spatial index on a.geom and b.geom (and you should have indexes for this workload). This also looks like you're caching the results for mere performance improvements. I would suggest under normal circumstances a MATERIALIZED VIEW.

Alternatively you should be able to go,

SELECT 
  a.id, 
  bool_or(b.geom IS NOT NULL) AS flag
FROM a 
LEFT OUTER JOIN b
  ON ST_Contains(a.geom, b.geom)
GROUP BY a.id;