[postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Brent,

Of course I will be happy to ☺  This is how it looks now;

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_UpperleftX(mymodel.deposition.rast) = ST_UpperleftX(mymodel.concentrated.rast) AND
                         ST_UpperleftY(mymodel.deposition.rast) = ST_UpperleftY(mymodel.deposition.rast) ) foo ;

The query has been running now for several hours. I am therefore becoming less hopeful.

There are 19584 256x256 tiles in each of the tables. Each table only has three columns which were created when I imported the tifs – they are rid, rast and filename.
I am unfamiliar with building indexes so this could be slow because they lack the appropriate indexing. The number of cells with values exceeding zero will be several million in each of the 5Million cell tifs and all data are stored as doubles.

I’m not sure at what point I should realise it is not going to work!  The simpler add query is also running which makes me suspect this may not be working.

Sadly I may have to resort to doing this manually in QGIS for the 135 untiled tiffs in each table ☹

I’ll keep you posted.

Best wishes

Darrel




From: Brent Wood [mailto:pcreso at yahoo.com]
Sent: 24 November 2015 18:41
To: Darrel Maddy <darrel.maddy at newcastle.ac.uk<mailto:darrel.maddy at newcastle.ac.uk>>
Subject: Re: [postgis-users] Help with SQL query?

Hi Darrel,

This has been a useful thread for others than yourself!

Can you post the entire final query once it is working? Otherwise we need to read through all the emails to build it up as you have done with advice from the various correspondents.

Thanks,

  Brent Wood
________________________________
From: Darrel Maddy <darrel.maddy at newcastle.ac.uk<mailto:darrel.maddy at newcastle.ac.uk>>
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
Sent: Wednesday, November 25, 2015 2:00 AM
Subject: Re: [postgis-users] Help with SQL query?

Dear Birgit,

Very many thanks. The query is now running – so it is no longer giving an error. I will not know for a few hours whether it is calculating the necessary values but it seems progress is being made.

I have a book coming on SQL – I clearly need to do my homework more thoroughly ☺

I will confirm when it stops!

Best wishes

Darrel



From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Birgit Laggner
Sent: 24 November 2015 12:19
To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Help with SQL query?

Hi Darrel,

my PostGIS version is too old for testing, but if I read the documentation right, then your expression has to be SQL. And IF THEN ELSE etc. is not SQL as far as I know - SQL has CASE WHEN.

So, I would assume, you would need to write your expression like this:

'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END'

I am curious if this helps with your error...

Regards,

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