Thanks. I started using GIST. It works.
Thanks, Sairam Krishnamurthy +1 612 859 8161 On Thu, May 5, 2011 at 8:39 PM, Ben Madin <[email protected]>wrote: > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
