4

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?

  • What version of postgis are you using? – Roberto Ribeiro Jan 16 '18 at 10:43
5

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.

  • 1
    I'd add only that because ST_Relate is so generic, it's not able to take advantage of a spatial index in any way. But if you know that your relationship involves intersection, as this one does, you can manually add an index check back into your query: WHERE a.geom && b.geom AND ST_Relate(a.geom, b.geom, .... – dbaston Jan 16 '18 at 15:37
4

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.

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.