detecting all intersecting points between lines

Question

I am using bellow query to get all the intersecting points from my line_string table.

select a.line_id,(st_dump(st_intersection(a.geom, b.geom))).geom from 
roads a, roads b where st_touches(a.geom, b.geom);

Though it detects most of the intersecting points, its not detecting all the intersecting points.

In the above image blue dots are all detected points, where as red boxes are missed in detection.

There will be 2 possibilities.

One is marked in green, where roads are not intersecting (fly-over/under pass roads). Where geometry is not touching. OR two lines are not snapped/connected correctly.

But in the above image I cross checked many non detected lines, but they are perfectly snapped/connected.

I can use ST_Intersects in place of ST_Touches But the problem is,(If i am not wrong) it matches geometry spatially which leads to detecting intersecting points in flyover / underpass roads also.

Can anyone help me in understanding why its not detecting all intersecting points and how to correct it?

I also need to detect start or end point which is not intersected with other lines as intersecting point.

Answer 1

ST_Touches explain that:

Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.

So you can detect points start and end point of lines but not interior vertex by using ST_Touches.

You should use ST_Intersects for your aim :

Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

But Return of ST_Intersection will be LineString.

So your query should be like :

select a.line_id,st_intersection(a.geom, b.geom) from 
roads a, roads b where st_intersects(a.geom, b.geom)
and geometrytype(st_intersection(a.geom,b.geom))='POINT'
and a.geom&&b.geom -- to accelerate query
and a.line_id!=b.line_id;