[postgis-users] Query performanace against huge point table

[Kevin Neufeld]

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 :Santosh.Gaikwad at saama.com
>
> http://www.saama.com
>
>  
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>