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. enter image description here

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.


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;
  • So how should I modify my query?
    – Aparichith
    Jul 20 '17 at 10:09
  • i edited answer. Jul 20 '17 at 11:16
  • Would ST_Node be of any help to you? I'll link to my blog post dealing with its use. You might find it necessary to ST_Union the lines, the ST_Node them and ST_Dump them again. Jul 20 '17 at 11:23
  • @BarışSerkanAKIN Still it detects points where flyover/underpass is there (Two lines looks like connected but they are not actually connected. A sub road passes under main road or something like).
    – Aparichith
    Jul 20 '17 at 11:54
  • Hi @Dharshan. Sql Query detects spatial intersections, doesn't know your model. You can add some condition after where like a.road_type<>'Subroad'. You can also select multiple query for each condition , then union all. Jul 20 '17 at 12:23

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.