[postgis-users] Nearest line from a point

[Jaime Casanova]

On Sat, Aug 20, 2011 at 2:58 PM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:
> On 8/20/2011 3:34 PM, Jaime Casanova wrote:
>>
>> On Sat, Aug 20, 2011 at 7:36 AM, Charles Galpin<cgalpin at lhsw.com>  wrote:
>>>
>>> I assume you mean it takes a long time? The key is to create a buffer
>>> around the point that is as big as you feel necessary to make a
>>> match, and then in your where clause only match lines that intersect
>>> this. That way the index is used to limit the number of
>>> candidates to get the distance for.  Something like
>>>
>>> where line&&  st_expand(myPoint, bufferSizeInYourProjectionUnits)
>>>
>>
>> st_dwithin() doesn't do this automatically?
>> but i should admit it wasn't useful for me... it still require a seq
>> scan on the whole table
>>
>
> Have you create a gist (not a btree) index on your geometry column?
>

Yes, I have... Here was posted my problem
(http://postgis.refractions.net/pipermail/postgis-users/2011-August/030559.html)
and this is the EXPLAIN ANALYZE using st_dwithin() in the query
(http://explain.depesz.com/s/rbX)

the table has a GiST index on the table transmitter_mv

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación