[postgis-users] Optimizing ST_Intersection

[Paul Ramsey]

http://www.sql-tutorial.net/SQL-JOIN.asp

SELECT ST_AsText(ST_Intersection(r.the_geom, p.the_geom)
FROM roads r JOIN pipes p ON ST_Intersects(r.the_geom, p.the_geom)
WHERE p.pipeid = 1;


On Tue, Sep 27, 2011 at 1:56 PM, vinod.khare at gmail.com
<vinod.khare at gmail.com> wrote:
> I have a table containing all roads in the US. Naturally, this table is very
> large with more that 20 million rows. I want to find the intersection of
> these roads with a single linestring (representing a pipeline).
>
> SELECT ST_AsText(ST_Intersection(
>     (SELECT the_geom FROM roads)
>     (SELECT the_geom FROM pipes WHERE pipeid = 1)));
>
> I have created a gist index on roads but the query planner does not use it.
> The query takes a really long time. Is there some way to optimize this?
>
> thanks,
> Vinod.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>