[pgpointcloud] Retrieval seems slow

[Paul Ramsey]

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)
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/pgpointcloud