[postgis-users] Query performanace against huge point table

[Santosh Gaikwad]

Hi Kevin, 

Thank you very much for your help. I created now the functional index of
"transform(the_geom,2163)" on my geometry column. It is faster now. I
would like to know whether clustering of indices helps in speeding up
the query.

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
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Kevin Neufeld
Sent: Wednesday, November 21, 2007 2:23 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Query performanace against huge point table

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
>   
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Santosh Gaikwad.vcf
Type: text/x-vcard
Size: 145 bytes
Desc: Santosh Gaikwad.vcf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071121/631bdefd/attachment.vcf>