Determining if line A is subset of line B in PostGIS?

Question

I have two datasets of linestring features, both of which are from the same source and nearly identical but with minor changes. One of the changes that I need to identify is when one line feature is split into multiple separate features, but with otherwise identical geometry.

So the original line, we'll call it B, is defined as the following sequence of points:

[V, W, X, Y, Z]

Line A is defined as:

[V, W, X]

How can I write a SQL query that will return True if line A is a subset of B ? I thought the ST_Covers function would work since every point in A is in B, in the same order, but the function seems to only work with polygons and has strange behavior around edges

JasonInVegas · Accepted Answer · 2016-08-23T21:08:49.383

I believe the general case of ST_RELATE(A, B, '****1****') should handle even where A == [a,w,x] where a is on vw. recall the third arguement in the ST_RELATE() operator is the classic DI-9M intersection model discussed here.

So, in SQL it would look like:

SELECT 
     A.id, B.id
FROM
     dataset_a A, dataset_b B
WHERE
     ST_RELATE(A.geom, B.geom, '****1****')
;

Pay close attention to the third argument as it needs 9 characters in it, the 5th of which is the number 1 -- indicating that you are testing for a one-dimensional boundary-on-boundary spatial relationship.

EDIT: I missed the case where the A shares a portion of B but has additional extensions away from B.

To ensure that A and B are related by a one-dimensional boundary and A is wholly a part of B, try this:

SELECT 
     A.id, B.id
FROM
     dataset_a A, dataset_b B
WHERE
     ST_RELATE(A.geom, B.geom, '****1****')
    and ST_LENGTH(ST_INTERSECTION(A.geom, B.geom)) = ST_LENGTH(A.geom)
;

This is saying "A is part of B" and "the length of A which is a part of B is the entire length of A."

YES, you have to answer both criteria;
No, this query is not going to perform well;
Yes, I am sorry about that performance expectation gap.

score 2 · Answer 2 · answered Aug 17 '16 at 11:01

2

Try using st_contains instead. However if line A is made of a line with points [a,v, w,x] it will return false, but if it contains [v,w,x] then it will return true as it evaluates the geometry to be contained within the geometry of the other. Here is the docs for it

http://postgis.net/docs/manual-1.4/ST_Contains.html

answered Aug 17 '16 at 11:01

Liam G

2,156
11
11

It looks like that works for the situation described above, but runs into trouble when line A is defined as [a,w,x] where a is the midpoint between v & w. So on the same line but not every point is explicitly included in B's geometry – wmebane Aug 17 '16 at 18:53
Yeh that's correct, if any of the points are not part of the line that it is being evaluated against, it will return false. That situation becomes a bit tricky, what if a and x are on the line but w, the mid point is off the line. Do you consider this a subset of line B? You almost need to split line A up and see if each point pair is a subset perhaps? – Liam G Aug 17 '16 at 20:58

score 1 · Answer 3 · edited Apr 13 '17 at 12:33

Similar to this answer, here is a test query to give an idea of the different interactions of the relationship functions. ST_Covers should return the result that you want, just make sure you have the geometries the right way around. You may also want to play around with ST_Overlaps, ST_Contains and ST_Within.

SELECT DESCRIPTION
    , ST_Intersects(LINE, BASE) "Intersects"
    , ST_Within(LINE, BASE) "Within"
    , ST_Contains(LINE, BASE) "Contains"
    , ST_Touches(LINE, BASE) "Touches"
    , ST_Disjoint(LINE, BASE) "Disjoint"
    , ST_Crosses(LINE, BASE) "Crosses"
    , ST_Overlaps(LINE, BASE) "Overlaps"
    , ST_Covers(LINE, BASE) "Covers"
FROM (VALUES
    ('Disjoint',         ST_GeomFromText('LINESTRING(0 10, 40 10)', 0))
    ,('Overlap',         ST_GeomFromText('LINESTRING(30 0, 40 0, 50 0)', 0))
    ,('Internal On',     ST_GeomFromText('LINESTRING(10 0, 20 0)', 0))
    ,('End On',          ST_GeomFromText('LINESTRING(0 0, 10 0)', 0))
    ,('Completely Over', ST_GeomFromText('LINESTRING(-10 0, 0 0, 10 0, 20 0, 30 0, 40 0, 50 0)', 0))
    ,('Over No Vertex',  ST_GeomFromText('LINESTRING(-10 0, 50 0)', 0))
    ,('Partial On',      ST_GeomFromText('LINESTRING(20 10, 20 0, 30 0)', 0))
    ,('Crosses',         ST_GeomFromText('LINESTRING(20 10, 20 0, 20 -10)', 0))
) L(DESCRIPTION, LINE)
,(VALUES(ST_GeomFromText('LINESTRING(0 0, 10 0, 20 0, 30 0, 40 0)')))P(BASE);

Determining if line A is subset of line B in PostGIS?

3 Answers3