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