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 Jul 21 '14 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 – simpleuser001 Nov 12 '14 at 14:05
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 Jul 30 '14 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, privacy policy and cookie policy

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