[postgis-users] Query performanace against huge point table
Santosh Gaikwad
Santosh.Gaikwad at saama.com
Thu Nov 22 21:27:33 PST 2007
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>
More information about the postgis-users
mailing list