[pgpointcloud] Retrieval seems slow

[Paul Ramsey]

Bruce,
I may well have failed to document it. No excuses :(
P

-- 
Paul Ramsey
http://cleverelephant.ca/
http://postgis.net/


On Saturday, 31 August, 2013 at 8:16 AM, Bruce Rindahl wrote:

> Paul
> Is creating a spatial index like this a separate step? I missed that in your tutorial.
> Thanks
> Bruce 
> On Aug 31, 2013 8:00 AM, "Paul Ramsey" <pramsey at cleverelephant.ca (mailto:pramsey at cleverelephant.ca)> wrote:
> > Roger,
> > 
> > I'm going to guess you don't have an "appropriate" spatial index on the table, so it has to scan the whole thing twice: once to figure out how big the buffer is (PDAL inefficiency) and once to pull the data.
> > 
> > CREATE INDEX pa_gix ON all_returns USING GIST ( geometry(pa) );
> > 
> > P.
> > 
> > --
> > Paul Ramsey
> > http://cleverelephant.ca/
> > http://postgis.net/
> > 
> > 
> > On Saturday, 31 August, 2013 at 6:55 AM, Roger Bedell wrote:
> > 
> > > Hello again,
> > > 
> > > Ok, finally got a reasonable number of points to import into our database. Took a while because of a hardware failure, but now it is done.
> > > 
> > > I imported about 2500 LAS files, each one about 8mb into a single table. The resulting table has 55 million records (counted not estimated). Each record has 400 points. Database size is about 375gb.
> > > 
> > > I am attempting to do the following from pcpipeline:
> > > 
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <Pipeline version="1.0">
> > > <Writer type="drivers.las.writer">
> > > <Option name="filename">test_small.las</Option>
> > > <Option name="spatialreference">EPSG:4326</Option>
> > > <Reader type="drivers.pgpointcloud.reader">
> > > <Option name="connection">host='ogisrv102' port='8080' dbname='NRCS_LIDAR' user='postgres'password='5Tb3CwBu353w' </Option>
> > > <Option name="table">all_returns</Option>
> > > <Option name="column">pa</Option>
> > > <Option name="srid">4326</Option>
> > > <Option name="where">PC_Intersects(pa, ST_MakeEnvelope(-97.27021, 34.46225, -97.26987, 34.46251, 4326))</Option>
> > > </Reader>
> > > </Writer>
> > > </Pipeline>
> > > 
> > > 
> > > Start time 8:24, end time 8:30 for the program to get here:
> > > 
> > > F:\PDAL64>pdal64\pcpipeline -i export_pg_to_las_test.xml
> > > Requested to read 700 points
> > > Requested to write 700 points
> > > Buffer capacity is 700
> > > 0
> > > 
> > > 
> > > Finally completes at 08:35:
> > > 
> > > F:\PDAL64>pdal64\pcpipeline -i export_pg_to_las_test.xml
> > > Requested to read 700 points
> > > Requested to write 700 points
> > > Buffer capacity is 700
> > > 0.100
> > > 
> > > 
> > > 
> > > 
> > > The new database server has reasonable horsepower. 2 Xeon e5-2690 CPUs, 32gb RAM. Postgresql is 9.2.4, 64 bit on a Windows 2008 Server OS. The 64 bit pgpointcloud build for Windows was provided by Regina. I built the 64 bit PDAL.
> > > 
> > > 11 minutes seems excessive for extracting 700 points. Am I missing something, or is this to be expected?
> > > 
> > > Thanks for any insight,Roger
> > > 
> > > --
> > > Roger Bedell
> > > Coordinate Solutions Inc.
> > > _______________________________________________
> > > pgpointcloud mailing list
> > > pgpointcloud at lists.osgeo.org (mailto:pgpointcloud at lists.osgeo.org) (mailto:pgpointcloud at lists.osgeo.org)
> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/pgpointcloud
> > 
> > 
> > 
> > 
> > _______________________________________________
> > pgpointcloud mailing list
> > pgpointcloud at lists.osgeo.org (mailto:pgpointcloud at lists.osgeo.org)
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/pgpointcloud
>