# How to check how many line intersects a intersecting point in same geometry

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

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.

• Cannot use ST_dump sorry I forgot to tell that I am using spatialite Commented Nov 13, 2017 at 16:39
• Oh ok, I leave the answer if someone finds it useful. Commented Nov 13, 2017 at 16:59

Try grouping by the intersection:

``````    SELECT count(DISTINCT a.id),
ST_Intersection(a.geom, b.geom) AS geom
FROM <table> AS a,
<table> AS b
WHERE ST_Touches(a.geom, b.geom)
AND a.id <> b.id
OR ST_Crosses(a.geom, b.geom)
AND a.id <> b.id
GROUP BY ST_Intersection(a.geom, b.geom)
``````

With `ST_Touches` and `ST_Crosses` in `OR` condition this should return all points that intersect in a point geometry (since `ST_Intersects` could return all types of intersection geometries, e.g. if two lines overlap). And: with those two `ST_Intersection` this is probably going to be awfully slow for large tables.

• Yes its very slow and takes too much time and I have already tried this with ST_intersection grouping but does not give right results Commented Nov 14, 2017 at 7:26
• Could you elaborate on the false results? I wouldn´t dare say this is final, but I haven´t come across a better solution than this or similar (based on your topology) yet; intersections are always coslty, and, in general, to count them all, you´ll have to find them all... Commented Nov 14, 2017 at 9:31
• What do you mean by false results?. Actually my end target is to remove the blue line which is orthogonal to the point therefore, I want to know a point where more then two lines intersects Commented Nov 14, 2017 at 9:34
• I mean you said grouping by ST_Intersection would 'not give right results'...how are they wrong? If e.g. some intersections are missing, some lines may not touch (as @BFlat suggested). Knowing your data is crucial; if, for example, none of your lines actually cross each other (but do intersect in their endpoints ONLY), ST_Crosses would be useless. You can also use group_concat(a.id) to get a string of intersecting ids per intersection instead of getting the actual ST_Intersection in the SELECT to slightly speed things up for very large tables (if that would be sufficient to know)? Commented Nov 14, 2017 at 10:52
• Well I have made two queries instead of making one complex. The first query uses st_touches and ST_intersection to get the intersecting point and next query uses the point given by first query and checks how many ids are intersecting on this point using ST_intersects. Now its working Commented Nov 14, 2017 at 11:11