[postgis-users] Overlapping linestrings

[Adam McManus]

Birgit,

Thanks for the reply. After reading my initial question, I think I could
have been clearer. I'm not looking simply for an intersection, I'm trying
to find a linestring that's contained within another linestring. For
example, if I drive a mile in my car that would be r1. Then I have several
shorter distances in s1, I want to find the shorter distances that are
along the same linestring as r1. That's why I'm using the buffer, just to
add a bit of fuzziness since I don't expect the exact points within all the
linestrings to be the same.

With some further experimentation, I've discovered the big bottleneck
appears to be the ST_Buffer call (it gets executed in a nested loop once a
larger data set is involved). I've written a stored procedure that computes
the ST_Buffer geometry once and stores it in a variable. With this
approach, performance is much better.

Hopefully this makes the explanation of what I'm trying to accomplish a bit
clearer, but I think I have a workable solution. If there's a better way,
I'd definitely be interested in hearing it.

Thanks,

--
Adam

On Mon, Feb 6, 2012 at 4:39 AM, Birgit Laggner
<birgit.laggner at vti.bund.de>wrote:

>  Hi Adam,
>
> why do you calculate a buffer for the first linestring? If you only want
> to know if there is any overlapping part of the two linestrings, I would
> expect that ST_Intersects(r1.geo_data,s1.geo_data) would do the job best.
> You could combine it with
> ST_Geometrytype(ST_Intersection(r1.geo_data,s1.geo_data))='ST_LineString'
> in order to to find out if they only cross or really overlap.
>
> Hope that helps,
>
> Birgit.
>
>
> Am 03.02.2012 21:07, schrieb Adam McManus:
>
> I'm looking for the fastest way to detect whether a given linestring
> overlaps another linestring. 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?
>
>  Thanks,
>
>  --
> Adam McManus
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120206/662fe319/attachment.html>