[postgis-users] Nearest line from a point

[Jaime Casanova]

On Sun, Aug 21, 2011 at 8:25 AM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:
> 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?
>

i just see that the explain i posted here was using st_makepoint in
one of the tables instead of a column, here is the right one:
http://explain.depesz.com/s/kNA

But the problem is the same: a seq scan instead of using the GiST
index (there is one of these indexes in both tables involved).
and answering your question the column is of type geometry so i don't
know why the casts to geography (this also happens in the explain i'm
posting here), maybe because of the SRID i choose? (which is 4326)

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