[pgpointcloud] Retrieval seems slow

[Roger Bedell]

Hi Paul,
Yes, that did the trick. Now it takes about 1 second or less. Thanks for
the fast reply - I took the dog for a walk and came back to the answer!

Best regards,
Roger


On Sat, Aug 31, 2013 at 5:00 PM, Paul Ramsey <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)
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/pgpointcloud
>
>
>
>
>


-- 
Roger Bedell
Coordinate Solutions Inc.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20130831/e66f5814/attachment.html>