2

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?

  • Do you want If a box's lower surface is x=1, do you want a point on x=1 to return true or false? – Evan Carroll Jun 28 '18 at 7:19
  • 1
    Yes, just if the point is included or not – Randomize Jun 28 '18 at 7:21
  • This also looks like you're caching the results for mere performance improvements. I would suggest under normal circumstances a MATERIALIZED VIEW – Evan Carroll Jun 28 '18 at 7:44
2

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;
  • select in the lateral join is missing of columns? – Randomize Jun 28 '18 at 7:42
  • @Randomize fixed. – Evan Carroll Jun 28 '18 at 7:43
0

The problem is that ST_Contains will return a value in any case (i.e. if the spatial relationship is true/false). Instead, I would write the query as follows:

   SELECT 
     a.id, 
     count(b.geom)>0 as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id

or alternatively:

   SELECT 
     a.id, 
     CASE WHEN count(b.geom)>0 THEN 1 ELSE 0 END as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id

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.