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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
