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:[email protected]]
Sent: 24 November 2015 18:41
To: Darrel Maddy
<[email protected]<mailto:[email protected]>>
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
<[email protected]<mailto:[email protected]>>
To: PostGIS Users Discussion
<[email protected]<mailto:[email protected]>>
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:[email protected]] On Behalf Of
Birgit Laggner
Sent: 24 November 2015 12:19
To: [email protected]<mailto:[email protected]>
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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
