[postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Brent,

Many thanks. The data are tiled (256x256) hence the large number of rows from the original 135 tifs. I did not build any indexes however, so I will do some reading and see how best to approach that (the threads you listed look useful so thanks for that).

I will run some additional mini queries limited to just one comparison and check using QGIS as you suggest – I probably should have done that first!

My workstation has 64GB Ram and I would be surprised if it was significantly caching to disk. I also have a hexacore intel extreme processor so I would not expect this to be hardware limited. I must confess I expected it to finish within a couple of hours.

Anyhow very many thanks. I will continue to explore and report back hopefully with positive news.

Darrel


From: Brent Wood [mailto:pcreso at yahoo.com]
Sent: 24 November, 2015 7:36 PM
To: Darrel Maddy <darrel.maddy at newcastle.ac.uk>; postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Help with SQL query?

Indexing can improve performance by 100s of x, without them things can be slow. Also, did you tile the images when you imported them? If not, then each iteration is working through all the pixels in the image, rather than a small subset. Essentially with tiles, you have a deep (long) table rather than a wide one. RDBMSs work better with lots of small records than a few wide ones, especially when indexes are used.

This might help:
http://gis.stackexchange.com/questions/43053/how-to-speed-up-queries-for-raster-databases

and see the raster tutorial they mention for the SRTM data, as to how that is loaded into Postgis:
https://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01

To test the logic (the syntax is correct or it wouldn't be working) you could add to the "where" clause an extra filter so that only a small subset of the entire dataset is included (like just one QGIS operation) then compare this with the QGIS result.

That would be much faster that testing on the entire dataset. Once you know it is correct for the test case(s), then you can run it on the complete set.

Note that some queries can build up large in-memory objects, so make sure your system is not swapping to disk, as that will also slow things down (hugely).

Cheers

Brent
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151124/5f14c969/attachment.html>