3

I have large polygon data set and I want to label every one with true if polygon contains point or false if it does not. Polygon can contain multiple points, but if just one point fall into polygon it should be true. With large polygon data set (about 100 mil polygon and 10 mil points) what is the most efficient time-consuming way to do this using postgis?

This is what I ended up with:

select distinct on (poly.id) poly.id, case when st_contains(poly.geom, point.geom) is true then true else false end as contains 
from polygon poly
left join public.poi point
on st_contains(poly.geom, point.geom);
3
  • please show us your SQL that you tries so far
    – Ian Turton
    Commented May 21, 2021 at 13:22
  • I added a code snipped.
    – DrJacoby
    Commented May 21, 2021 at 13:50
  • I'm assuming you have spatial indexes?
    – Ian Turton
    Commented May 21, 2021 at 14:00

1 Answer 1

3

You won't get any better than the general query structure, if getting all polygons with a flag in a SELECT is your goal.

Nonetheless, some improvements can be applied:

SELECT DISTINCT ON (ply.geom)
       COALESCE(ST_Intersects(ply.geom, pt.geom), FALSE) AS flag
FROM   poi AS pt
LEFT JOIN
       polygon AS ply
  ON   ply.geom && pt.geom
;
  • use ST_Intersects for point-in-polygon
  • obviously you don't need the CASE when ST_Intersects returns BOOL already
  • even if PG may be able to cache the spatial relationship function (ST_Intersects), there's no need to call it twice; if it doesn't cache, you save 50% of operations with joining on bbox intersection (&&)

However, if you intend to run an UDPATE, better add a BOOL column with DEFAULT FALSE, or run an UPDATE setting the whole table to FALSE, then

UPDATE polygon AS ply
  SET  flag = TRUE
FROM   poi AS pt
WHERE  ST_Intersects(ply.geom, pt.geom)
;
3
  • Thanks, good answer!
    – DrJacoby
    Commented May 24, 2021 at 7:57
  • Sometimes ST_Intersects(ply.geom, pt.geom) returns null. Do you have any idea why is this happening?
    – DrJacoby
    Commented Jun 18, 2021 at 8:17
  • 1
    @DrJacoby that's because of the LEFT JOIN - whenever a poi has no && intersection at all with any of the polygons, ST_Intersects is executed with a NULL value, and returns it. I added a fallback.
    – geozelot
    Commented Jun 18, 2021 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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