Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
I'm not sure I understand your solution, but we handle it by 1. Taking the buffer of one linestring; 2. running st_instersection() on the buffer and the other linestring. When it's done with many geometries, it is exceedingly slow. –  canisrufus Feb 3 '12 at 21:04
    
Perhaps this will help clarify. Let's assume I run 1 mile down the street and that is stored in r1. I have another table of previous runs in s1. I want to return all runs in s1 that overlap r1. I exclude s1 data not in r1 bounding box. Then, I use Contains on a buffered version of my run to detect s1 runs that are inside that buffer. Does that help? –  amcmanus Feb 3 '12 at 21:25
    
Yeah, that clarifies it for me. No advice for you, though.. –  canisrufus Feb 3 '12 at 22:31
    
Thanks. I'll be sure to update if I come up with a faster solution. –  amcmanus Feb 3 '12 at 22:51
add comment

1 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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.