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