[postgis-users] value of spatial index on 300, 000, 000 point geometries

[Kevin Neufeld]

Yes, I had to store a table of 3D DEM point data (~170million) in a 
database once.   As Paul mentioned, there can be great benefits of 
grouping your points into multipoint objects.  Consider that if you 
convert your points into groups of 200, the index could be up to 200 
times smaller.

Due to the properties of my dataset (that the points were more or less 
evenly distributed over the province of BC), a query like this worked 
for me:

-- Grouping points based on a grid of 1 km)
CREATE TABLE point_groups AS
SELECT ST_Collect(the_geom) AS the_geom
FROM points
GROUP BY ST_SnapToGrid(the_geom, 1000)

Of course, it depends on your use case, because now you have to deal 
with trying to identify an individual point in your collection.  For me, 
I didn't care about individual points per se, I just needed swathes of 
points in a particular area of interest - so grouping them worked great.

-- Kevin

Paul Ramsey wrote:
> For the reasons you point out, there's some drawbacks. However, the
> spatial index does provide you a means to get spatial subsets much
> more quickly than a full table scan. With 1.4 you could also store
> your points as a geohash, which is pretty compact and can be indexed
> and searched with a b-tree.
>
> The question of what indexing buys you does rely a good deal on use case.
>
> If you're doing random access on 400M points, the fastest bet by far
> is to convert from single POINTs to MULTIPOINT patches of a few
> hundred points. kneufeld can elaborate on the multifarious benefits.
>
> P.
>
> On Tue, May 5, 2009 at 4:02 PM,  <pcreso at pcreso.com> wrote:
>   
>> Hi,
>>
>> I'm currently working with a table storing around 300,000,000 point geometries. No, it's not particularly fast :-)
>>
>> I'm unsure of the value in creating a spatial index effectively comprising a BBOX on these, as the index then requires twice as many coordinates as the actual data.
>>
>> Can anyone advise on the merits of spatial indexes on points?
>>
>> Thanks,
>>
>>   Brent Wood
>> _______________________________________________
>> 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
>