# Counting self intersections of linestring using PostGIS?

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.

• 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

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)
``````
• or something like that – ike Jul 30 '14 at 23:12

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;
``````