1

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

2

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 – mimetype Nov 13 '17 at 16:39
  • Oh ok, I leave the answer if someone finds it useful. – BFlat Nov 13 '17 at 16:59
1

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 – mimetype Nov 14 '17 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... – ThingumaBob Nov 14 '17 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 – mimetype Nov 14 '17 at 9:34
  • 1
    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)? – ThingumaBob Nov 14 '17 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 – mimetype Nov 14 '17 at 11:11

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.