1

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
    ;
  • 4
    can't validate right now so no answer, but you should be able to do UPDATE intersections AS g SET facility_flag = EXISTS ( SELECT 1 FROM network_subset AS d WHERE ST_DWithin(g.geom, d.geom, 100) )::INT, assuming your geometries are projected in a CRS with feet as units. even if the cast doesn't work, ST_DWithin on indexed columns is the way to go here. – ThingumaBob Jul 13 at 19:53
  • Cool cool, thank you. I'll try this out. Oh and yes, tables indexed on geom.. – bikingman Jul 13 at 21:41
  • 25.1 seconds - thank you – bikingman Jul 15 at 12:54
  • 1
    @ThingumaBob you should probably add that as an answer. The combination of EXISTS and ST_Dwithin does the work here. I wonder if a LATERAL JOIN with LIMIT 1 would also work, though, I suspect, the need to also use ST_DWithin would result in a similar runtime. – John Powell Jul 15 at 15:52
  • @JohnPowell hm, probably, but maybe more since you'd need to convert to 0 if the join yields no result. but you're right, let me write up an answer – ThingumaBob Jul 15 at 16:12
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.

  • isn't an update query, in a way like a lateral? would there really be a boost in performance using lateral on a new table creation? – ziggy Jul 26 at 22:57
  • @ziggy technically they can be very similar, but in terms of database transactions, an UPDATE is generally a very costly operation (needless to say, it also adds itself to the overall workflow); I haven't completely thought this through, but when creating a table anew, the LATERAL JOIN would create a more performant query plan than a DISTINCT...INNER JOIN due to it stopping execution when it reaches the LIMIT [1] condition; however, it is very well possible to use an EXISTS here as well (it generally beats any JOIN in performance) – ThingumaBob Aug 6 at 12:06

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.