Find LineStrings with common segments in PostGIS 2.3

Question

I would like to find LineStrings with common segments ('collinear', i.e. LineStrings that have 2 or more consecutive points in common) with PostGIS.

I'm not shure if I'm missing something, but ST_Touches as well as ST_Overlaps seem to return True for 2 geometries that allready share only one common point.

Is there any PostGIS function I'm missing or any other solution to this?

Answer 1

In the PostGIS documentation, Chapter 4., 4.3.6. Dimensionally Extended 9 Intersection Model (DE-9IM) (https://postgis.net/docs/manual-2.3/using_postgis_dbmanagement.html#DE-9IM) we read the following introduction:

"It is sometimes the case that the typical spatial predicates (ST_Contains, ST_Crosses, ST_Intersects, ST_Touches, ...) are insufficient in and of themselves to adequately provide that desired spatial filter."

This is exactly what applies here. Simply spoken, you have to examine the dimensionality of the intersection result of your two geometries. The Wikipedia article referred to in the PostGIS docs discusses this in detail: https://en.wikipedia.org/wiki/DE-9IM, which I recommend for further reading.

The OVERLAPS pattern for dim(a) == dim(b) == 1 (two LineStrings) would be '1*T***T**' [(11) in the Wikipedia article], so a query like

... WHERE ST_Relate(a.geom, b.geom, '1*T***T**')

should give you the desired result.

Answer 2

ST_Touches only returns true for lines that intersect start or end points, not the space along the line, so it should not work on for your case, as expected. ST_Overlaps, however, should work exactly as you are intending:

SELECT ST_Overlaps('LINESTRING(0 0, 2 0)'::geometry,
                   'LINESTRING(1 0, 3 0)'::geometry)

>>> t

SELECT ST_Overlaps('LINESTRING(0 0, 2 0)'::geometry,
                   'LINESTRING(1 0, 2 1, 2 0, 3 0)'::geometry)

>>> f

I just tested it both on PotGIS 2.2.0 and 2.3.2, so it should be working on yours too. Regardless, you can go one step deeper and work with ST_Relate. This function tests for DE-9IM topology, so you should be able to control exactly what you want.

Specifically, you want interior-interior interaction, so your matrix should look something like '1********' (the other cells will depend on whatever other topological restrictions you may have). Notice that this also accepts lines being completely within another, a special case of collinearity, where ST_Overlaps does not.

SELECT ST_Overlaps('LINESTRING(0 0, 2 0)'::geometry,
                   'LINESTRING(1 0, 2 0)'::geometry)

>>> f

SELECT ST_Relate('LINESTRING(0 0, 2 0)'::geometry,
                 'LINESTRING(1 0, 2 0)'::geometry,
                 '1********')

>>> t

If not even ST_Relate is working, then you might have topological problems with your data.