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, [email protected] <[email protected]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
