I'm currently trying to optimize my code and I wanted to know if someone could enlighten me concerning the use of ST_Intersects.

Basically, I'm trying to assign some points to each road sections based on a hypothetical intersection in order to split those roads (the points have been calculated using ST_StartPoint and ST_EndPoint). The problem is: the network is huge.

I came up with several ideas and I wanted to know what is the best one in terms of robustness as well as quickness.

The first one (the most obvious):

SELECT r.id, ST_Collect(n.geom) as blade, r.geom
FROM roads r, nodes n
WHERE ST_Intersects(r.geom, n.geom)
GROUP BY r.id, r.geom

Then, I thought about using a left join based on the intersection:

SELECT r.id, ST_Collect(n.geom) as blade, r.geom
FROM roads r
LEFT JOIN nodes n
ON ST_Intersects(r.geom, n.geom)
GROUP BY r.id, r.geom

Finally, the last one I'm considering, it uses a temporary table to find the intersection between bounding boxes, then apply the real intersection with a significantly decreased subset:

WITH intersection AS (
     SELECT r.id, n.id as idnode, n.geom as geomnode, r.geom
     FROM roads r, nodes n
     LEFT JOIN nodes n
     ON r.geom && n.geom
SELECT i.id, ST_Collect(i.geomnode) as blade, i.geom
FROM intersection i, intersection i2
WHERE ST_Intersects(i.geom, i2.geomnode) AND i.id = i2.id
GROUP BY i.id, i.geom
  • The answer by LR is excellent and you should accept it. As a general observation, questions like this, a very decent one, should be accompanied by an EXPLAIN, whcih will often throw light on the underlying issues. Sep 27 '17 at 19:55

Your number 2 option is best, in your list. The last one you could end up with a ton of false positives that get materialized (e.g a long road that is slanted would have a huge bounding box) and since CTEs materialize first that could be huge.

ST_Intersects already include && in it.

Now that said, there is a particular ugliness you need to understand about trying to intersect points with lines even if you composed them from the road.

It's because since PostGIS doesn't use a precision model, your points you think intersect your line, almost never intersect the line unless they fall on a vertex of the line. They might be off by like a millimeter.

That said, I would use ST_DWithin instead of ST_Intersects.

SELECT r.id, ST_Collect(n.geom) as blade, r.geom
   FROM roads r, nodes n
    WHERE ST_DWithin(r.geom, n.geom,0.0000001)
     GROUP BY r.id, r.geom;

It'll be a little slower, but more likely to give you the results you are expecting. Now what you plan to do about this after you have your blade points, you'll probably need to use ST_Split in conjunction with ST_Snap.

I covered this in my last talk at FOSS4G, Code excepted from this slide http://www.postgis.us/presentations/FOSS4G2017_PostGISSpatialTricks.html#/6/3

SELECT L.gid, D.ordinality As sub_id, D.geom::geometry(LINESTRING,26986) AS geom
   mbta_lines AS L
    (  -- form a multipoint of all the nodes 
       -- close enough to line to be considered on the line
            ST_Union(N.geom ORDER BY L.geom <-> N.geom) AS geom 
        FROM mbta_stations AS N 
        WHERE ST_DWithin(L.geom, N.geom, 10) 
-- snap the LINE to the MP which forces nodes to be injected to the line
-- then split at these node location and dump multilinestring into individual lines
        COALESCE(  ST_Split(ST_Snap(L.geom, MP.geom, 10), MP.geom), L.geom) 
  • an order by in the st_union? I have never seen that. what purpose does it serve here
    – ziggy
    Sep 27 '17 at 18:30
  • In your case probably none. The ORDER BY would force the points to be order in the MULTIPOINT such that the points closest to the line are first. This is because ST_Snap result depends on the order it snaps the MULTIPOINT. In most cases it doesn't matter. In rare case you could get diferent results if the same set of points are ordered differently in the multipoint.
    – LR1234567
    Sep 27 '17 at 18:56
  • Great answer. I hadn't seen WITH ORDINALITY before. I love writing CTEs, as they are very clear, but the materialization (the optimization fence) is a big gotcha if you are not careful. Sep 27 '17 at 19:53
  • Thank you for that answer! This works well and I guess it's best to spare a bit of quickness for robustness. Is there a reason you snap the line to the points and not the other way around ?
    – BFlat
    Sep 28 '17 at 10:25
  • 1
    Yes the objective was to add the points to the line string as vertices so when you ST_Split, it can split at the point. If you tried to snap the point to the line, no new vertices would be created, and you'd still have the issue of tolerance when you do ST_Split.
    – LR1234567
    Sep 28 '17 at 13:52

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.