[postgis-users] PostGIS and LIDAR Massive Point Sets

[David Blasby]

collin wrote:

> Glad to hear you've had good experiences with large datasets.
> Another question: what is your recommended method for inserting point 
> data?  Even with transactions turned off, the INSERT command is 
> painfully slow.  Is there any way to use COPY FROM with point data?  The 
> shapefile loader doesn't work, becuase our dataset would crash 
> Arcview/ArcMap.  Any help appreciated.
> 
> If it looks promising, I can try recommending that we help out 
> financially with implementing WKB.  No guarantees.

Using COPY is the fastest way to get data into postgresql.  The 
shp2pgsql command has an option that outputs in this format.

The main benifit is that postgresql doesnt have to parse incomming 
"INSERT" commands.

Paul Ramsey and I were talking about WKB support in postgis last night. 
  I was originally thinking of put a bounding box in the database with 
the WKB, but I dont think that is necessary.

The WKB representation for a 2d point has 5 bytes of overhead (endian 
flag and a 4-byte type tag) for a total of 21 bytes.  WKB of a 3d point 
also has 5 bytes of overhead, for a total of 29 bytes.  Postgresql has a 
further 4 bytes of overhead.

I was also thinking we could use 4-byte floats for the index instead of 
8-byte doubles.  This will reduce the index size by half, but make the 
index somewhat less selective.  Each bounding box will then be 4 floats 
(16 bytes).


Total size of the database will be "about":

for the actual points:
(number of points * 26)

for the spatial index:
(number of points * log(number of points) ) * 16

dave