[postgis-users] ST_DWITHIN indices

[Sairam Krishnamurthy]

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 <lists at remoteinformation.com.au>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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110506/72e997c6/attachment.html>