[postgis-users] Optimizing ST_Intersection

[vinod.khare at gmail.com]

Thanks all. Both these solutions work for me.

On Tue, Sep 27, 2011 at 10:23 PM, Stephen Woodbridge <
woodbri at swoodbridge.com> 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, 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<postgis-users at postgis.refractions.net>
>>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>>
>>>
>>>  ______________________________**_________________
>> postgis-users mailing list
>> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>
>
> ______________________________**_________________
> postgis-users mailing list
> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110928/dbcbbd5c/attachment.html>