[postgis-users] Intersection of LineStrings

[Stephen Woodbridge]

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