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, <[email protected]> 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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
