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 ;