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
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
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.