[postgis-users] PostGIS and LIDAR Massive Point Sets

[Jonathan W. Lowe]

Collin,

I haven't benchmarked the process, but yes, you can use \copy table_name to
'output_file' to create a tab-delimited file that includes a point column. 
Likewise, for loading, you can load a tab-delimited file into a waiting
database table with the \copy table_name from 'input_file'

Here's some sample tab-delimited contents of a text file named bart_stn_out.unl
representing local BART stations:

0       Rockridge BART  SRID=26943;POINT(1845873.40614687 650679.425961887)
1       Ashby BART      SRID=26943;POINT(1844270.02726776 651647.404827549)
2       Downtown Berkeley BART  SRID=26943;POINT(1844449.52036206
653421.97285701)
3       North Berkeley BART     SRID=26943;POINT(1843122.57180181
653966.768283577)
4       El Cerrito BART SRID=26943;POINT(1841797.79812916 657195.995248745)

Using the command: \copy bart_stn from 'bart_stn_out.unl'
it's possible to load the following table with the above listed data.

             Table "bart_stn"
  Column  |       Type        | Modifiers
----------+-------------------+-----------
 gid      | smallint          |
 name     | character varying |
 the_geom | geometry          |
Indexes: bart_stn_geom_idx,
         bart_stn_oid_idx
Check constraints: "$2" ((geometrytype(the_geom) = 'POINT'::text) OR (the_geom
IS NULL))
                   "$1" (srid(the_geom) = 26943)

Hope this helps.  Good luck with your project.

Jonathan

-- 
Jonathan W. Lowe
email - jlowe at giswebsite.com
website - http://www.giswebsite.com/jlowe/

Quoting collin <collin at socrates.Berkeley.EDU>:

> 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.
> 
> Collin
> GIS Informatics Researcher
> UC Berkeley
> 
> 
> ---------- David Blasby wrote ----------
> 
> >Date: Thu, 12 Feb 2004 14:35:59 -0800
> >From: David Blasby <dblasby at refractions.net>
> >Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> >To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> >Subject: Re: [postgis-users] PostGIS and LIDAR Massive Point Sets
> >
> >collin at socrates.Berkeley.EDU wrote:
> >
> >  
> >
> >>I am participating in a project to improve LIDAR processing.  We have a
> >>20 gigabyte dataset of filtered x,y,z points in ascii files.
> >>
> >>I have questions:
> >>1. Can PostGIS handle that kind of data at usable speeds? We are using a
> >>new Sun Blade server running solaris.
> >>2. What data types are the point arrays? text? float? Text could cause an
> >>unusable overhead on a dataset this massive.
> >>3. How large a dataset has anyone tried so far? And what have been the
> >>problems/limitations?
> >>
> >>Any help, commentary, or opinions would be appreciated.
> >>    
> >>
> >
> >
> >I've been using multi-gigabyte datasets in PostGIS for quite a while and
> >havent had any problems.   The speed depends on what you're doing -
> >searching 20 million rows for a small-ish bounding box is pretty much
> >instant.
> >
> >The geometry data is stored in its own datastructure.  The details are
> >in postgis.h and there's documentation on the website.  There is quite a
> >bit of overhead for storing single points.
> >
> >The structure is holding unused data (offsetX,offsetY,scale, bvol.zmin,
> >bvol.zmax, and type should be char), so some of the overhead could be
> >minimized.
> >
> >If this overhead (after you take out the unused data) is an issue, we
> >could upgrade the WKB type so it was indexable, and auto-convertable to
> >and from the PostGIS types (so you have all the functionality of
> >PostGIS).  I discussed this on the list a little while ago.
> >Unfortunately, this would be several days of development.  I've been
> >wanting someone to either do this or hire us to do it since it would be
> >very nice feature.
> >
> >dave
> >
> >
> >
> >_______________________________________________
> >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
> 
>