Michal ZimmermannPieces of knowledge from the world of GIS.

PostGIS: Count Line Self-Intersections

Is there a way of using PostgreSQL + PostGIS for finding the number of self intersections in a linestring? was a question that made me think of this problem. I came up with a solution that takes just a few lines of code.

Assume the following geometries:

    id integer NOT NULL,
    wkb_geometry geometry(LineString,5514)
COPY test2 (id, wkb_geometry) FROM stdin;
1   01020000208A15000004000000CCDC7845E339EEBFF2003B4A8A08E1BFE4154DAB7C31DCBF24C2042773E3E53F2287BA2CC591E43F604749BFE3B2E2BF2AE9770A11B8F0BF9C91435D56C0C63F
2   01020000208A1500000600000050212BF9E63EC03F1FA046FD69F1EA3F504D44212915EA3F74A99EDF44E3F33F2CE2805DFAB1F33F805D24B1B189DC3F9834DE5938C1F53FB56F1FBF8AAFEC3F24D0C85B4666EA3FF311B0D8D75BE93F306EAA073894D23FA841B27E3404F33F

Note that those geometries are valid while not being simple, thus, ST_IsValidReason() wouldn’t help much. What if we compared it to their single counterparts? Those would have had vertices at intersections. Once you know the original number of vertices and the number of simple geometry vertices, it is fairly easy to subtract those two.

WITH noded AS (
    SELECT DISTINCT (ST_DumpPoints(ST_Node(wkb_geometry))).geom, id
    FROM test
) tmp  group by id
test AS (
    SELECT id, COUNT(id)
        FROM (
            SELECT DISTINCT (ST_DumpPoints(wkb_geometry)).geom, id
            FROM test
        ) tmp  group by id

SELECT noded.id, noded.count - test.count cnt FROM noded JOIN test USING (id);

This query gives you geometry id and the difference in number of vertices between the original and simple geometry. Note the DISTINCT in the noded CTE - with ST_Node() you get one vertex x number of intersecting lines for each intersection. DISTINCT gives you just one of them.

The query result on my test table:

id cnt
1 1
2 2