[postgis-users] ST_DWITHIN indices

[Ben Madin]

Sairam,

On 06/05/2011, at 5:50 AM, Sairam Krishnamurthy wrote:

> Table structure: lat AS double, lon AS double, spatialPoint AS point. 
> 
> I have a query that uses ST_DWITHIN. I was under the impression that this function will use the gist index on spatialPoint. 
> 
> Index query:
> CREATE INDEX "table_spatial_index" ON "table" USING btree ("spatialPoint");

St_DWithin does use a bounding box if there are suitable indexes. I'm only using 1.5, so things might be different if you are using 2.0 or < 1.3, but I think if you want to use a GIST index you need to create a GIST index, not a b-tree. I thought - and I stress I'm not an expert on indexes that b-tree was one-dimensional.

> Select query: 
> SELECT lat, lon FROM "table" WHERE ST_DWITHIN("table"."spatialPoint", ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);


Also, I'd caution against using ST_DWithin and non-projected data systems. you won't actually be seeing a circle, even if you do appear to be close to the equator. 

cheers

Ben






> 
> Query plan:
> 
> EXPLAIN SELECT lat, lon FROM "EVI250m" WHERE ST_DWITHIN("EVI250m"."spatialPoint", ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
>                                                                                                                                                             
>  QUERY PLAN                                                                                                                                                 
>             
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> ------------
>  Seq Scan on "EVI250m"  (cost=0.00..4757082.00 rows=1 width=16)
>    Filter: (("spatialPoint" && '0103000020E61000000100000005000000000000A0224DB03F000000C05D6B3240000000A0224DB03F00000020EE6B3240000000C050DDB03F00000020EE6
> ) AND _st_dwithin("spatialPoint", '0101000020E61000002A7288B83995B03FCFF753E3A56B3240'::geometry, 0.0011::double precision) AND ('0101000020E61000002A7288B83
> recision)))
> (2 rows)
> 
> 
> Any thoughts ?
> 
> 
> 
> Thanks,
> Sairam Krishnamurthy