Finding road segements which are not split at intersection.(Query Optimization )

Posted on

Question :

I have a query in which I am trying to find the road segments which intersect but are not splitted at intersection,I have also created gist index on geom column. Here is postgerSQL srcipt

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
 -- The "vertical" start node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
 ) OR (
 -- The "vertical" end node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
 )

Logic is working fine but it takes a lot of time to complete this task, can someone let me know how can I optimize this query.

Answer :

I just added st_intersects(a.geom,b.geom) at the start of second and third queries, as st_intersects is fast so it only calculates st_intersection where st_intersects returns ‘True’ hence performance of query is enhanced. Below is the query after modification.

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" --nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), 
ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal" 
--nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

But I think there is still some room for improvement.

Leave a Reply

Your email address will not be published. Required fields are marked *