Speeding up script to flag nodes within 100 feet of segments using PostGIS?

Question

I'm working with two datasets, attempting to flag when an intersection is within 100 feet of a network subset. There are over 40,000 intersections and my network is ~ 1400 segments.

The problem with the SQL script below (that I think works), is that the runtime is far too long. I'm fairly new to PostGIS and feel there is likely a faster way to do this. Basically, I'd like the each intersection (row) to have a 1 or 0 where 1 indicates the presence of a network segment within 100 feet.

Anyway to speed this up?

UPDATE intersections g
SET facility_flag =
      CASE
      WHEN EXISTS(
        SELECT 1
        FROM network_subset d
        WHERE ST_INTERSECTS(g.geom,
          ST_BUFFER(d.geom, 100))
      )
        THEN 1
        ELSE 0
      END
    ;

Answer 1

As per my comment above:

Better use ST_DWithin for proximity searches:

UPDATE intersections AS g
  SET facility_flag = EXISTS (
    SELECT 1
    FROM   network_subset AS d
    WHERE ST_DWithin(g.geom, d.geom, 100)
  )::INT
;

This assumes your data is projected in a CRS with feet as unit.

Here, the Boolean result of EXISTS is (explicitly) cast to INT. EXISTS will stop execution of its query when it hits the first positive comparison.

---

Make sure you have spatial indexes in place, and run standard table maintenance (VACUUM ANALYZE <table>;) before executing the UPDATE.

For very large tables (hundreds or thousands of times the size of yours) it can be beneficial to actually create a new table with the update logic implemented instead of an UPDATE. A LATERAL JOIN, as stated by @JohnPowell, might be the way to go then.