On Sun, Aug 21, 2011 at 8:25 AM, Stephen Woodbridge <[email protected]> wrote: > On 8/21/2011 1:07 AM, Jaime Casanova wrote: >> >> On Sat, Aug 20, 2011 at 2:58 PM, Stephen Woodbridge >> <[email protected]> wrote: >>> >>> On 8/20/2011 3:34 PM, Jaime Casanova wrote: >>>> >>>> On Sat, Aug 20, 2011 at 7:36 AM, Charles Galpin<[email protected]> >>>> 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
