ST_DWithin(...) is a simple sql wrapper around the following sql string:
"SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND
ST_Distance($1, $2) < $3"
where $1,$2,and $3 are your parameters.
So, unless you have a GIST functional index of "transform(the_geom,
2163)" on your geometry column, your query won't use any indexes.
I suggest to either create the mentioned function index or create a new
geometry column of your points in the 2163 projection and do something
like this:
SELECT *
FROM table a,
(SELECT transform(GeomFromText('POINT(-122.0527
37.323)',4326),2163) AS the_geom) b
WHERE ST_DWithIn(a.the_geom, b.the_geom, 1000);
Cheers,
Kevin
Santosh Gaikwad wrote:
Hi,
I have a point table which contains 300 millions of records. I am
using following query to fetch the records from the table within
certain radius.
Select *
from table
where
st_dwithin(
transform(the_geom,2163),
transform(GeomFromText('POINT(-122.0527 37.323)',4326),2163),
1000
);
But it takes long time to get the result. I am seeking help.
Thanks & Regards,
Santosh Gaikwad
Senior Software Developer
Saama Technologies (India) Pvt. Ltd.,
Unit No.101-102, First floor,
Weikfield IT CITI INFO PARK,
Weikfield estates,
Pune Nagar Road,
Pune - 411 014. India
Phone : +91 20 66071397
Mobile: +91-9422005927
E-mail :[EMAIL PROTECTED]
http://www.saama.com
_______________________________________________
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
