[postgis-users] Query performanace against huge point table

[Santosh Gaikwad]

Hi Kevin,

How I can query the table which has been created by using

CREATE TABLE new_pt_tbl AS
SELECT collect(geom) AS my_multi_pt
FROM pt_tbl
GROUP BY ST_SnapToGrid(geom, <grid_size>);

As it contains only my_multi_pt geometry. I want to run the following
query against this table.

Select *
   from table
   where
     st_dwithin(
       transform(the_geom,2163),
       transform(GeomFromText('POINT(-122.0527 37.323)',4326),2163),
       1000
     );

And the above table also needs to be in 2163 srid projection. Can you
explain me in detail?

I am working on whole USA data. Initially I thought to separate data
state wise into different tables but it will not be useful as we are
finding out the locations from a given point with a certain radius.
Currently I have stored whole data in a table but it really affecting
the performance. The functional index and clustering have helped me in
increasing the performance.

I would appreciate your help on this issue.


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 10:45 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Query performanace against huge point table

Yes, clustering your table based on your GIST index does help because it

physically orders the rows in your table based on the spatial index.  
The disk head won't have far to move to retrieve the next page. 

I've worked with a table of about 170 million points (not as large as 
your 300m :) ) before and I was running into the same problem.  We were 
able to significantly speed up index queries by grouping points in sets 
of about 100 - 200 based on some arbitrary grid.  Instead of having an 
index entry for every point, now you have one entry for every 100 
points, reducing the size of your index by 100!  Of course, you've added

the complexity of having to deal with MultiPoints, but depending on your

use cases, this may or may not be an issue.
ie.
CREATE TABLE new_pt_tbl AS
SELECT collect(geom) AS my_multi_pt
FROM pt_tbl
GROUP BY ST_SnapToGrid(geom, <grid_size>);

Cheers,
Kevin

Santosh Gaikwad wrote:
> 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
>   
>
_______________________________________________
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/20071123/bdbbd54d/attachment.vcf>