6

Is there a way of using PostgreSQL + PostGIS for finding the number of self intersections in a linestring?

I have a linestring that goes forward but in circles, and I need to find the number of times it crosses itself.

2
  • 2
    please add more details to your questions there are many ways to find self intersecting lines in postgis. What are you trying to achieve?
    – Mapperz
    Commented Jul 21, 2014 at 20:10
  • ST_IsValidReason probably helps, example documentation shows only one error per geometry so maybe you need to run it in recursive loop . see postgis.net/docs/manual-dev/ST_IsValidReason.html Commented Nov 12, 2014 at 14:05

2 Answers 2

2

I don't know for sure, but I think something like this might solve your problem (this wont work on multilines, but you could stdump them or squish them) :

SELECT  ID, 
        COUNT(*) AS theCOunt
FROM
(
  SELECT  id, 
          ST_Intersection(A.geom, B.geom) AS geom
  FROM
  (
    SELECT  xx.id,
            xx.id2,
            st_makeline(xx.l_start, xx.l_end) AS geom
     FROM 
     ( 
       SELECT  x.id,
               generate_series(1, st_npoints(x.geom) - 1) AS id2,
               st_pointn(x.geom, generate_series(1, st_npoints(x.geom) - 1)) AS l_start,
               st_pointn(x.geom, generate_series(2, st_npoints(x.geom))) AS l_end
       FROM 
       ( 
         SELECT  DATA_TABLE.id,
                 DATA_TABLE.geom 
         FROM DATA_TABLE
        ) AS X
     ) AS XX
 ) AS A
 INNER JOIN
 (
    SELECT  xx.id,
            xx.id2,
            st_makeline(xx.l_start, xx.l_end) AS geom
     FROM 
     ( 
       SELECT  x.id,
               generate_series(1, st_npoints(x.geom) - 1) AS id2,
               st_pointn(x.geom, generate_series(1, st_npoints(x.geom) - 1)) AS l_start,
               st_pointn(x.geom, generate_series(2, st_npoints(x.geom))) AS l_end
       FROM 
       ( 
         SELECT  DATA_TABLE.id,
                 DATA_TABLE.geom 
         FROM DATA_TABLE
        ) AS X
     ) AS XX
 ) AS B
ON A.ID = b.ID
   AND
   A.id2 <> B.id2
   AND
   A.id2 <> B.id2-1
   AND
   A.id2 < B.id2
   AND
   ST_INTERSECTS(A.geom, B.geom)
1
  • or something like that
    – ike
    Commented Jul 30, 2014 at 23:12
1

How about this (assume test is your linestring table):

First nodify the linestring, e.g. create vertices at each intersection while keeping the original vertices in tact.

CREATE TABLE node AS SELECT id, ST_Node(wkb_geometry) as wkb_geometry FROM test;

Count all distinct points in the new linestrings. Substracting the initial number of vertices from noded number should give you number of intersections.

SELECT id, COUNT(id) FROM (SELECT DISTINCT (ST_DumpPoints(wkb_geometry)).geom, id FROM node) tmp  group by id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.