[postgis-users] Nearest line from a point

[Stephen Woodbridge]

On 8/21/2011 1:07 AM, Jaime Casanova wrote:
> 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
>

I don't use st_dwithin() that much although it is supposed to be faster 
than using:

     where line&&    st_expand(myPoint, bufferSizeInYourProjectionUnits)

But what I did find interesting is that in your explain it seemed to be 
using ::geography casts instead of ::geometry, not that that explains 
anything to me. Is your spatial column defined as geography or geometry?

-Steve