[GIS] How to check how many line intersects a intersecting point in same geometry

intersectionlinestringpostgisspatialite

I want to know that how many line intersects a point. In figure below it can be seen that 3 lines crosses a point. 2 red lines and 1 blue line. I want to know in the same geometry that many lines crosses an intersecting point.

I have tried it with st_intersection but its not working. Suppose if I have a point similar to that in the picture the query should return 3.

Is there any way in to check how many lines crosses an intersection point?

what I have tried is:

select Count(Distinct a2.id) as no_of_line_intersecting,
st_intersection(a1.geometry,a2.geometry) 
 as geom from Area a1,Area a1 where
ST_Touches(a1.geoemtry, a2.geometry)

But I am not getting the right results. Does somebody have any idea about this? I am using spatialite

enter image description here

Best Answer

If I understood correctly, what you have is one line table and you want each intersection points containing the number of line that intersect them ? If so, I think the following query could do the job

WITH intersection_points AS (
         SELECT (ROW_NUMBER() OVER ())::int as id,
                (ST_Dump(ST_Intersection(l1.geom, l2.geom))).geom
         FROM line_table l1
           JOIN line_table l2
           ON ST_Intersects(l1.geom, l2.geom)
         )
SELECT i1.id, count(i2.id) as intersection_count
FROM intersection_points i1
  LEFT JOIN intersection_points i2
  ON ST_DWithin(i1.geom, i2.geom, 0.000001)
GROUP BY i1.id;

I'm using here ST_DWithin with a low tolerance to avoid any topological inconsistencies that could induce wrong results. You can use ST_Intersects if you want but I think this one is more robust.

The first table returns intersection points where the line table self-intersects. The - (ROW_NUMBER() OVER ())::int as id - assign for each entry the row number, cast it to integer type, to get an id for the newly created points.

I didn't test it so I don't know if it works properly, but I hope it does.

Related Question