Thanks all. Both these solutions work for me. On Tue, Sep 27, 2011 at 10:23 PM, Stephen Woodbridge < [email protected]> wrote:
> Hi Paul, > > If the pipeline is a very long linestring then would it make sense to first > chop the linestring into multiple shorter segments and then intersect each > of them against the roads. I would think that this could be done with a join > between SRF to chop the linestring into segments and the roads table. > > I would have to think about the SQL to do this a little bit, but I think > this would be faster still. Thoughts? > > -Steve > > > On 9/27/2011 5:11 PM, Paul Ramsey wrote: > >> http://www.sql-tutorial.net/**SQL-JOIN.asp<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 >>> postgis-users@postgis.**refractions.net<[email protected]> >>> 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<[email protected]> >> 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<[email protected]> > http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users> >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
