Gist is an r-tree like index, only use if on the geometry column.
-Steve On 6/6/2012 11:10 AM, Ed Linde wrote:
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 <wood...@swoodbridge.com <mailto:wood...@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@postgis. refractions.net <mailto:postgis-users@postgis.refractions.net> http://postgis.refractions. net/mailman/listinfo/postgis- users <http://postgis.refractions.net/mailman/listinfo/postgis-users> _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
