[postgis-users] Optimizing ST_Intersection

[Stephen Woodbridge]

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