How to detect overlapping linestrings in PostGIS?

Question

I'm looking for the fastest way to detect whether a given linestring overlaps another linestring. (As reference, let's say my wife and I both go for a run and take different routes, I'd like to detect the segments our different runs had in common.) Here's my current approach.

Given two tables: r1 and s1, both containing a geo_data column of linestring, first narrow the data (taking advantage of gist index) with r1.geo_data && s1.geo_data, then use ST_Contains to see if s1 is overlapping r1, i.e.

r1.geo_data && s1.geo_data and ST_Contains(ST_Buffer(r1.geo_data,ST_Length(r1.geo_data)*.002),s1.geo_data)

This approach works, but it's extremely CPU-intensive and slow. Is there a better approach?

Answer 1

You should not bother about using &&. That functionality is built in for example ST_Contains.

You should also avoid using buffer in this way.

What is it really that you want to get. Now you are selecting the linestrings where s1 is totally contained in the buffered line from r1. But from you write, what you want is all lines in s1 that is intersecting with the line(s) in r1.

What I don't understand is also why the lines is in different tables. What is the difference between the lines in s1 and r1.

aha, now I think I understand. You want the parts of the linestrings in s1 tat is shared with the lines in r1. Then you want the intersecting part of the linestring, not just all linestrings that is intersecting. s that what you want.

Ok.

If you, as I thought first, want all lines in s1 that intersects with lines in r1 or comes closer than 0.2*(length on line) than you can do:

SELECT * from r1,s1 WHERE ST_DWithin(r1.geodata, s1.geodata,0.2*ST_Length(s1.geodata));

That will use any gist indexes present. But why do you want a longer line to catch lines wider than a short line?

If, what you want is the intersecting part of the line in s1 and r1, then you should use ST_Intersection together with ST_Intersects to only put work on lines intersecting. In this case you will also have to use ST_Buffer if you want the calculation to be forgiving about inaccuracy.

HTH Nicklas