Re: [postgis-users] Help with SQL query?

[Paragon Corporation]

That timing seems much slower than I recall.

 

FWIW expression based mapalgebra as I recall is slower than using the call back 
function approach.  So you could try wrapping your CASE in a call back function.

 

However I think something else might be going on here and postgres might be 
repeating work.  I forgot under what conditions it decides to reevaluate a 
function call, I just remember being really surprised by it.

 

To avoid that, you can try using a CTE, also you don't need that ST_Union call 
which for larger number of rasters is expensive, and you might even generate a 
raster that is too big to compute.

 

I'm also guessing your rasts are all tiled the same, so you really don't need 
ST_Intersects, just use the same box operator

 

So try this:

 

WITH  foo AS (

  SELECT ST_SummaryStats( ST_MapAlgebra(deposition.rast, concentrated.rast, 
'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) ) As st

FROM mymodel.deposition INNER JOIN mymodel.concentrated ON ( 
deposition.rast  ~=  concentrated.rast )

WHERE deposition.rid=1

 

)

SELECT SUM( (st).sum )

FROM foo;

 

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Wednesday, November 25, 2015 5:06 PM
To: PostGIS Users Discussion ; Brent Wood 

Subject: Re: [postgis-users] Help with SQL query?

 

Dear Brent,

 

I must confess that my attempts to do this are so far proving very unsuccessful

 

If  I run the following query:

 

SELECT  (ST_SummaryStats(ST_Union(rast))).sum AS sum

FROM  (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN 
[rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast

FROM mymodel.deposition, mymodel.concentrated

WHERE ST_Intersects(deposition.rast, concentrated.rast) AND 
deposition.rid=1 ) foo ;

 

It takes around 30 seconds to complete as I assume it is only looking at one 
tile(they are 256x256 pixels) i.e. rid 1. It is not easy to check the sum – for 
that I need one complete raster.

 

For the record this was marginally faster than

SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum

FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN 
[rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast

FROM mymodel.deposition, mymodel.concentrated

WHERE mymodel.deposition.filename='10_depo.tif' AND 
ST_UpperleftX(mymodel.deposition.rast) = 
ST_UpperleftX(mymodel.concentrated.rast) AND 

 ST_UpperleftY(mymodel.deposition.rast) = 
ST_UpperleftY(mymodel.deposition.rast) ) foo ;

Even after I built indexes for the clauses after the WHERE.

 

Now there are 144 tiles in each of the rasters I want to perform this operation 
on.  Logic would therefore suggest this should take ~4500s

 

However when I perform the following query

 

SELECT  (ST_SummaryStats(ST_Union(rast))).sum AS sum

FROM  (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN 
[rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast

FROM mymodel.deposition, mymodel.concentrated

WHERE ST_Intersects(deposition.rast, concentrated.rast) AND 
deposition.filename='10_depo.tif' ) foo ;

 

The query is still running after 18000s!  I must therefore assume I have done 
something wrong but as you may have guessed the answer eludes me.

 

Any further suggestions would be welcome but I will continue to try and find a 
solution as I have 135 rasters to perform this operations on now and 
potentially many thousands more in the future.

 

Darrel

 

.

 

I

 

 

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: 24 November 2015 19:52
To: Brent Wood  >; 
postgis-users@lists.osgeo.org  
Subject: Re: [postgis-users] Help with SQL query?

 

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:pcr...@yahoo.com] 
Sent: 24 November, 2015 7:36 PM
To: Darrel Maddy 

Re: [postgis-users] liblwgeom library namings in Postgis-2.2.0

[Sandro Santilli]

On Wed, Nov 25, 2015 at 12:54:53PM +0530, Bandaru Muralikrishna wrote:
> Hi,
> 
> After building Postgis-2.2.0 sources, the generated libraries are
> liblwgeom-2.2.so.2.2.0  and liblwgeom-2.2.2.dylib on Linux and OS X
> resp. Are the names correct?
> 
> The generated library names for Postgis-2.1.8 looked fine. i.e
> liblwgeom-2.1.8.so on Linux and liblwgeom-2.1.8.dylib on OS X.
> 
> Is this expected change for liblwgeom library naming conventions with
> postgis-2.2.0 ?

liblwgeom-2.2.so.2.2.0 is expected, not sure what happens on OS X.

--strk; 
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users