[pgpointcloud] [EXTERNAL] Retrieval seems slow

[Smith, Michael ERDC-RDE-CRREL-NH]

Roger,

That seems very slow to me. With the oracle driver, I was able to extract an area of 0.352 sq miles with 1047792 points and it took 11.9 seconds. The pg driver is much newer than the oracle driver however and hasn't had as much testing but that is a lot slower than I would expect. One note, we do store our data with 100k points per block/row. For oracle we've found that more points per row is more efficient.

One thing I would check is the access plan of your spatial query. Is it using a spatial index to access the correct rows or is it using a full table scan. You could change your where option to set to a set of primary keys and see if its a lot faster.

I'd also check that your PDAL is compiled into release mode. That can also make a huge difference.

Mike

--
Michael Smith
US Army Corps
Remote Sensing GIS/Center

From: Roger Bedell <rbedell at coordinatesolutions.com<mailto:rbedell at coordinatesolutions.com>>
Date: Saturday, August 31, 2013 9:55 AM
To: "pgpointcloud at lists.osgeo.org<mailto:pgpointcloud at lists.osgeo.org>" <pgpointcloud at lists.osgeo.org<mailto:pgpointcloud at lists.osgeo.org>>
Cc: David Lowther <dlowther at coordinatesolutions.com<mailto:dlowther at coordinatesolutions.com>>
Subject: [EXTERNAL] [pgpointcloud] Retrieval seems slow
Resent-From: Michael Smith <michael.smith at usace.army.mil<mailto:michael.smith at usace.army.mil>>

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20130831/fac3a9d9/attachment-0001.html>