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

[Paul Ramsey]

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
>