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

[Kevin Neufeld]

pcreso at pcreso.com wrote:
> Thanks for the suggestions, much appreciated as always!!
>
>
> That sounds interesting. I'm currently storing XY points in a 2d geometry, with a separate depth column. I could convert these to 3D points to retain the z value of each point when stored as a multipoint.
>
> I have not used 3D geometries before, so a quick followup to ask about any caveats or advice you may have to offer on this approach, basically generating the 3D points, then using Kevin's approach to binning these say every 0.5 degrees.
>
> Does ST_SnapToGrid work on 3D geometries? I only want them snapped by XY, not Z.
>   

http://postgis.refractions.net/documentation/manual-svn/ST_SnapToGrid.html
ST_SnapToGrid works on 2D, 3D, and 4D geometries.  If you pass in higher 
dimensional geometries, it defaults to just snapping XY.  Use the 4th 
variant if you want to snap all dimensions.

> My Z units are m, my X&Y are degrees. Any issues there?
>
> I assume any transform() operations will simply pass through the Z value unchanged?
>   

Caution here.  The Z and M ordinates can be used to store anything, 
really - so no problem there.  The Z value is often used to store time, 
for example.  But (and folks can correct me if I'm wrong since the docs 
don't actually specify) transforms operate on *all* dimensions. 

postgis=# select st_asewkt(st_transform('SRID=3005;POINT(1240227 495643 
5)', 4001));
                              st_asewkt                              
----------------------------------------------------------------------
 SRID=4001;POINT(-122.689717977579 49.4258574870912 533.550271589169)
(1 row)

So, it's best to transform to your projection of choice, then add the Z 
value to your geometries so your Z values remain unchanged.  You can 
still group your geometries by degrees later if you wish.
...
GROUP BY ST_SnapToGrid(ST_Transform(the_geom, 4326), 0.5)

-- Kevin