ST_SummaryStats
上一個  11.9. 網格圖層頻帶統計與分析  下一個

名稱

ST_SummaryStats — 針對網格圖層或網格圖層範圍的指定頻帶，傳回包含計數、總和、平均值、標準差、最小值和最大值的統計摘要。若未指定頻帶，則預設為頻帶 1。

概要

summarystats ST_SummaryStats(raster rast, boolean exclude_nodata_value);

summarystats ST_SummaryStats(raster rast, integer nband, boolean exclude_nodata_value);

描述

針對網格圖層或網格圖層範圍的指定頻帶，傳回包含計數、總和、平均值、標準差、最小值和最大值的統計摘要。若未指定頻帶，nband 預設為 1。

[Note]

預設情況下，僅考慮不等於 nodata 值的像素值。將 exclude_nodata_value 設定為 false 可取得所有像素的計數。
[Note]

預設情況下，將取樣所有像素。為了獲得更快的響應，請將 sample_percent 設定為小於 1 的值。

已變更：3.1.0 移除 ST_SummaryStats(rastertable, rastercolumn, ...) 變體。請改用 ST_SummaryStatsAgg。

可用性：2.0.0

範例：單個網格圖塊

SELECT rid, band, (stats).*
FROM (SELECT rid, band, ST_SummaryStats(rast, band) As stats
    FROM dummy_rast CROSS JOIN generate_series(1,3) As band
     WHERE rid=2) As foo;

 rid | band | count | sum  |    mean    |  stddev   | min | max
-----+------+-------+------+------------+-----------+-----+-----
   2 |    1 |    23 | 5821 | 253.086957 |  1.248061 | 250 | 254
   2 |    2 |    25 | 3682 |     147.28 | 59.862188 |  78 | 254
   2 |    3 |    25 | 3290 |      131.6 | 61.647384 |  62 | 254

範例：彙總與感興趣的建築物相交的像素

這個範例在 PostGIS Windows 64 位元系統上，使用所有波士頓建築物和航空圖塊（每個圖塊 150x150 像素，約 134,000 個圖塊），以及約 102,000 個建築物記錄，耗時 574 毫秒。

WITH
-- our features of interest
   feat AS (SELECT gid As building_id, geom_26986 As geom FROM buildings AS b
    WHERE gid IN(100, 103,150)
   ),
-- clip band 2 of raster tiles to boundaries of builds
-- then get stats for these clipped regions
   b_stats AS
    (SELECT  building_id, (stats).*
FROM (SELECT building_id, ST_SummaryStats(ST_Clip(rast,2,geom)) As stats
    FROM aerials.boston
        INNER JOIN feat
    ON ST_Intersects(feat.geom,rast)
 ) As foo
 )
-- finally summarize stats
SELECT building_id, SUM(count) As num_pixels
  , MIN(min) As min_pval
  ,  MAX(max) As max_pval
  , SUM(mean*count)/SUM(count) As avg_pval
    FROM b_stats
 WHERE count > 0
    GROUP BY building_id
    ORDER BY building_id;
 building_id | num_pixels | min_pval | max_pval |     avg_pval
-------------+------------+----------+----------+------------------
         100 |       1090 |        1 |      255 | 61.0697247706422
         103 |        655 |        7 |      182 | 70.5038167938931
         150 |        895 |        2 |      252 | 185.642458100559

範例：網格圖層範圍

-- stats for each band --
SELECT band, (stats).*
FROM (SELECT band, ST_SummaryStats('o_4_boston','rast', band) As stats
    FROM generate_series(1,3) As band) As foo;

 band |  count  |  sum   |       mean       |      stddev      | min | max
------+---------+--------+------------------+------------------+-----+-----
    1 | 8450000 | 725799 | 82.7064349112426 | 45.6800222638537 |   0 | 255
    2 | 8450000 | 700487 | 81.4197705325444 | 44.2161184161765 |   0 | 255
    3 | 8450000 | 575943 |  74.682739408284 | 44.2143885481407 |   0 | 255

-- For a table -- will get better speed if set sampling to less than 100%
-- Here we set to 25% and get a much faster answer
SELECT band, (stats).*
FROM (SELECT band, ST_SummaryStats('o_4_boston','rast', band,true,0.25) As stats
    FROM generate_series(1,3) As band) As foo;

 band |  count  |  sum   |       mean       |      stddev      | min | max
------+---------+--------+------------------+------------------+-----+-----
    1 | 2112500 | 180686 | 82.6890480473373 | 45.6961043857248 |   0 | 255
    2 | 2112500 | 174571 |  81.448503668639 | 44.2252623171821 |   0 | 255
    3 | 2112500 | 144364 | 74.6765884023669 | 44.2014869384578 |   0 | 255