[postgis-users] Intersection of LineStrings

[Ed Linde]

Hi Steve,
What indexes can I put on the linestring column? Is there an option of
using a R-Tree index?

Thanks,
Ed

On Wed, Jun 6, 2012 at 4:54 PM, Stephen Woodbridge
<woodbri at swoodbridge.com>wrote:

> On 6/6/2012 9:57 AM, Ed Linde wrote:
>
>> Hi All,
>> Simple question. I have a table with half a million linestring
>> geometries, and I want to compute the
>> intersection points and also the linestring pairs that intersect with
>> each other.
>> How can I do this? Is there an index that can avoid having to do a
>> self-join and computing
>> the intersections?
>>
>
> Create a spatial index on your table of linestrings and do a self join
>
> select a.gid, b.gid, st_intersection(a.the_geom, b.the_geom)
>  from linestrings a, linestrings b
>  where a.the_geom && b.the_geom and st_intersects(a.the_geom, b.the_geom);
>
> You might get away with using this instead for the where:
>
>  where st_dwithin(a.the_geom, b.the_geom, 0.0);
>
> Which might be faster.
>
> The result will potentially be a collection if there is more then one
> intersection and/or might be a linestring if the intersection is an overlap
> along some part of the two strings.
>
> -Steve
>
> ______________________________**_________________
> postgis-users mailing list
> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120606/b9811db4/attachment.html>