[postgis-users] Query performanace against huge point table

[Kevin Neufeld]

Hi Santosh,

Hmmm. I think the collecting-points-by-some-grid solution was rather 
particular to our use case - we didn't have any attribution on our 
points, we were just interested in the z value of the points 
themselves.  This clearly won't work for you if you retain different 
attribution on every point. 

As for the 2163 SRID, it's the Lambert Azimuthal Equal-Area projection 
for the U.S. coordinate reference system.  Regina has some notes on it 
here: 
http://postgis.refractions.net/pipermail/postgis-users/2007-August/016668.html
as well as some references to SRID descriptions here:
http://postgis.refractions.net/pipermail/postgis-users/2007-June/016008.html

As Paul originally posted here 
(http://postgis.refractions.net/pipermail/postgis-users/2007-July/016262.html), 
I agree that you will probably get better performance by storing your 
original geometry in 2163, rather than in lat / long, avoiding your 
transforms.

Cheers,
Kevin

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