2

In the table there are several overlapping LineString objects. I would like to extract the overlapped segments, but ST_Intersection function returns Point geometry instead of LineString geometry. When i use ST_Difference between the same pair of geometry it returns LineString geometry. I have no idea why...

"LINESTRING(632089.750744759 494497.266400368,632103.021656775 494445.107106351)"
"LINESTRING(632103.021656775 494445.107106351,632105.975288636 494433.498307163)"
"LINESTRING(632089.750744759 494497.266400368,632097.05179906 494468.570720864)"
"LINESTRING(632097.05179906 494468.570720864,632105.975288636 494433.498307163)"
"LINESTRING(632089.750744759 494497.266400368,632100.343463809 494455.633335008)"
"LINESTRING(632100.343463809 494455.633335008,632105.975288636 494433.498307163)"
1
  • 1
    The accepted answer is good, but you could also try to make small buffer around one of your linestring sets using st_buffer(). Oct 14 at 11:52
2

The reason for you seeing point geometries is that the linestrings aren't "perfectly" on top of each other. If you snap on to each other, you will see that the intersection is indeed a linestring.

This query will return a point intersection

SELECT ST_AsText(
    ST_Intersection(
        ST_GeomFromText('LINESTRING(632089.750744759 494497.266400368,632103.021656775 494445.107106351)'),
        ST_GeomFromText('LINESTRING(632089.750744759 494497.266400368,632097.05179906 494468.570720864)')
    )
);

Whereas this query using ST_Snap will return what you would expect.

SELECT ST_AsText(
    ST_Intersection(
        ST_Snap(
            ST_GeomFromText('LINESTRING(632089.750744759 494497.266400368,632103.021656775 494445.107106351)'),
            ST_GeomFromText('LINESTRING(632089.750744759 494497.266400368,632097.05179906 494468.570720864)'),
            0.001
        ),
        ST_GeomFromText('LINESTRING(632089.750744759 494497.266400368,632097.05179906 494468.570720864)')
    )
);

This is the dbfiddle link.

2
  • 1
    Thanks for response. Your tip improved my case, but in case of some linestring pair i still receive point geometry... LINESTRING(632097.05179906 494468.570720864,632105.975288636 494433.498307163) LINESTRING(632089.750744759 494497.266400368,632100.343463809 494455.633335008)
    – AdamS
    Oct 14 at 9:37
  • No worries @AdamS - I believe the reasons for this is because the vertices are too far apart from each other which makes it trickier. Check out the last fiddle I added dbfiddle.uk/… Oct 14 at 11:14

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.