[postgis-users] st_dwithin with 4 parameters does not use spatial index

[Ben Madin]

Markus,

I'm only speculating, but the index is managed in the units of long / lat, and as they are not directly related to metres, in order to use the index it would have to transform all the index values on the fly, and either it can't / doesn't know how (therefore doesn't bother), or in the eyes of the query planner this is more expensive than just a full table scan. 

Maybe attaching the results of the explain would help others wiser than I am?

cheers

Ben


On 25/09/2011, at 11:25 PM, Markus Innerebner wrote:

> HI all
> 
> I migrated to postgis 1.5.3 in order to use the st_dwithin function with
> 4 parameters that supports meters as unit shown as Q1, while query Q2 is
> the old function, that does not support meters for the projection format
> 4326.
> 
> The problem is, that query Q1 does not use the spatial index, while Q2
> does it. Any idea what it could be??
> 
> Thanks for your answer
> 
> Markus
> 
> ---------------------------
> Q1: 
>    SELECT NT.id
>    FROM 
>      it_nodes N,
>      it_nodes NT
>    WHERE
>      N.ID='22' AND ST_DWITHIN(N.GEOMETRY,NT.GEOMETRY,500,true)=true
> 
> Q2:
>    SELECT NT.id
>    FROM 
>      it_nodes N,
>      it_nodes NT
>    WHERE
>      N.ID='22' AND ST_DWITHIN(N.GEOMETRY,NT.GEOMETRY,0.005)=true
> 
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users