<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Following the documentation I have crafted some GDAL to load, and
SQL to query, some rasters.</p>
<p>I note that :<br>
</p>
<p>1. If, when loading using GDAL, I don't tile the rasters I get a
table with a single row (toddriver_2019_2p5m)</p>
<p>2. If, when loading using GDAL, I specify a tile size (eg -t
256*256) I get a table with multiple rows (toddriver_2019_3p0m)</p>
<p>Now if I execute ST_SummaryStats against 1 I get a single row
which looks correct.:</p>
<p class="MsoNormal"><font face="monospace">WITH data AS (<br>
SELECT '1' as band,<br>
'SRID=4283;POLYGON ((133.84747311077376
-23.74668364533433, 133.84747311077376 -23.7409001580403,
133.85607301067603 -23.7409001580403, 133.85607301067603
-23.746683645334333, 133.84747311077376
-23.74668364533433))'::geometry as geom<br>
)<br>
SELECT (stats).sum as sum,<br>
(stats).mean as mean,<br>
(stats).stddev as stddev,<br>
(stats).min as min,<br>
(stats).max as max,<br>
(stats).count as count<br>
FROM (SELECT
ST_SummaryStats(ST_Clip(ST_Band(raster,d.band),geom,true)) As
stats<br>
FROM gis.toddriver_2019_2p5m<br>
INNER JOIN data as d<br>
ON ST_Intersects(d.geom,ST_Band(raster,d.band))<br>
) As foo;</font></p>
<p class="MsoNormal">If I execute the above against 2) I get
multiple summary statistics rows, one for each of the tiles in the
table.</p>
<p class="MsoNormal">To return the summary stats for 2 across all
rows I did the following:</p>
<p class="MsoNormal"><font face="monospace">WITH data AS (<br>
SELECT '1' as band,<br>
'SRID=4283;POLYGON ((133.84747311077376
-23.74668364533433, 133.84747311077376 -23.7409001580403,
133.85607301067603 -23.7409001580403, 133.85607301067603
-23.746683645334333, 133.84747311077376
-23.74668364533433))'::geometry as geom<br>
)<br>
SELECT sum((stats).sum) as sum,<br>
avg((stats).mean) as mean,<br>
sqrt(sum((stats).stddev*(stats).stddev)/min(groups)) as
stddev,<br>
min((stats).min) as min,<br>
max((stats).max) as max,<br>
sum((stats).count) as count<br>
FROM (SELECT row_number() over () as groups,<br>
ST_SummaryStats(ST_Clip(ST_Band(raster,d.band),geom,true)) As
stats<br>
FROM gis.toddriver_2019_3p0m as t<br>
INNER JOIN data as d<br>
ON ST_Intersects(d.geom,ST_Band(raster,d.band))<br>
) As f;</font></p>
<p class="MsoNormal"></p>
<p></p>
<pre class="moz-signature" cols="72">I have tested this SQL against tiled and untiled versions of the same raster and the result looks correct.
Even so, I could like confirmed that this approach is correct for tiled rasters?
regards
Simon</pre>
<p></p>
<pre class="moz-signature" cols="72">--
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) <a class="moz-txt-link-abbreviated" href="http://www.spdba.com.au">www.spdba.com.au</a>
(m) <a class="moz-txt-link-abbreviated" href="mailto:simon@spdba.com.au">simon@spdba.com.au</a> </pre>
</body>
</html>