説明

指定したバンドの指定した列Xと行Yまたはジオメトリポイントに対応するピクセルの値を返します。バンド番号は1始まりで、指定しない場合には1と仮定します。exclude_nodata_valueがTRUEに設定された場合には、nodataピクセルだけが考慮されます。exclude_nodata_valueがFALSEに設定された場合には、全てのピクセルが考慮されます。

機能強化: 2.0.0 exclude_nodata_value optional argument was added.

例

-- PostGISジオメトリのポイントを指定してラスタの値を得ます
-- ジオメトリのSRIDはラスタと同じにします
SELECT rid, ST_Value(rast, foo.pt_geom) As b1pval, ST_Value(rast, 2, foo.pt_geom) As b2pval
FROM dummy_rast CROSS JOIN (SELECT ST_SetSRID(ST_Point(3427927.77, 5793243.76), 0) As pt_geom) As foo
WHERE rid=2;

 rid | b1pval | b2pval
-----+--------+--------
   2 |    252 |     79
   
   
-- 実際のテーブルを使う一般的な仮想的な例
SELECT rid, ST_Value(rast, 3, sometable.geom) As b3pval
FROM sometable
WHERE ST_Intersects(rast,sometable.geom);
				

SELECT rid, ST_Value(rast, 1, 1, 1) As b1pval, 
    ST_Value(rast, 2, 1, 1) As b2pval, ST_Value(rast, 3, 1, 1) As b3pval
FROM dummy_rast
WHERE rid=2;

 rid | b1pval | b2pval | b3pval
-----+--------+--------+--------
   2 |    253 |     78 |     70
				

-- 1番、2番、3番バンドの全ての値を得ます
SELECT x, y, ST_Value(rast, 1, x, y) As b1val, 
	ST_Value(rast, 2, x, y) As b2val, ST_Value(rast, 3, x, y) As b3val
FROM dummy_rast CROSS JOIN
generate_series(1, 1000) As x CROSS JOIN generate_series(1, 1000) As y
WHERE rid =  2 AND x <= ST_Width(rast) AND y <= ST_Height(rast);

 x | y | b1val | b2val | b3val
---+---+-------+-------+-------
 1 | 1 |   253 |    78 |    70
 1 | 2 |   253 |    96 |    80
 1 | 3 |   250 |    99 |    90
 1 | 4 |   251 |    89 |    77
 1 | 5 |   252 |    79 |    62
 2 | 1 |   254 |    98 |    86
 2 | 2 |   254 |   118 |   108
 :
 :
				

-- 上の例と同じに、1番、2番、3番バンドの全ての値を得ますが、
-- 返り値をピクセル毎の左上隅のポイントを返します
SELECT ST_AsText(ST_SetSRID(
	ST_Point(ST_UpperLeftX(rast) + ST_ScaleX(rast)*x, 
		ST_UpperLeftY(rast) + ST_ScaleY(rast)*y), 
		ST_SRID(rast))) As uplpt
    , ST_Value(rast, 1, x, y) As b1val, 
	ST_Value(rast, 2, x, y) As b2val, ST_Value(rast, 3, x, y) As b3val
FROM dummy_rast CROSS JOIN
generate_series(1,1000) As x CROSS JOIN generate_series(1,1000) As y
WHERE rid =  2 AND x <= ST_Width(rast) AND y <= ST_Height(rast);

            uplpt            | b1val | b2val | b3val
-----------------------------+-------+-------+-------
 POINT(3427929.25 5793245.5) |   253 |    78 |    70
 POINT(3427929.25 5793247)   |   253 |    96 |    80
 POINT(3427929.25 5793248.5) |   250 |    99 |    90
:
				

-- 指定した範囲内の値を持ち、指定したポリゴンにインタセクトする、
-- 全てのピクセルの結合によって形成されるポリゴンを得ます
SELECT ST_AsText(ST_Union(pixpolyg)) As shadow
FROM (SELECT ST_Translate(ST_MakeEnvelope(
		ST_UpperLeftX(rast), ST_UpperLeftY(rast), 
			ST_UpperLeftX(rast) + ST_ScaleX(rast),
			ST_UpperLeftY(rast) + ST_ScaleY(rast), 0
			), ST_ScaleX(rast)*x, ST_ScaleY(rast)*y
		) As pixpolyg, ST_Value(rast, 2, x, y) As b2val
	FROM dummy_rast CROSS JOIN
generate_series(1,1000) As x CROSS JOIN generate_series(1,1000) As y
WHERE rid =  2 
	AND x <= ST_Width(rast) AND y <= ST_Height(rast)) As foo
WHERE  
	ST_Intersects(
		pixpolyg, 
		ST_GeomFromText('POLYGON((3427928 5793244,3427927.75 5793243.75,3427928 5793243.75,3427928 5793244))',0)
		) AND b2val != 254;


		shadow
------------------------------------------------------------------------------------
 MULTIPOLYGON(((3427928 5793243.9,3427928 5793243.85,3427927.95 5793243.85,3427927.95 5793243.9,
 3427927.95 5793243.95,3427928 5793243.95,3427928.05 5793243.95,3427928.05 5793243.9,3427928 5793243.9)),((3427927.95 5793243.9,3427927.95 579324
3.85,3427927.9 5793243.85,3427927.85 5793243.85,3427927.85 5793243.9,3427927.9 5793243.9,3427927.9 5793243.95,
3427927.95 5793243.95,3427927.95 5793243.9)),((3427927.85 5793243.75,3427927.85 5793243.7,3427927.8 5793243.7,3427927.8 5793243.75
,3427927.8 5793243.8,3427927.8 5793243.85,3427927.85 5793243.85,3427927.85 5793243.8,3427927.85 5793243.75)),
((3427928.05 5793243.75,3427928.05 5793243.7,3427928 5793243.7,3427927.95 5793243.7,3427927.95 5793243.75,3427927.95 5793243.8,3427
927.95 5793243.85,3427928 5793243.85,3427928 5793243.8,3427928.05 5793243.8,
3427928.05 5793243.75)),((3427927.95 5793243.75,3427927.95 5793243.7,3427927.9 5793243.7,3427927.85 5793243.7,
3427927.85 5793243.75,3427927.85 5793243.8,3427927.85 5793243.85,3427927.9 5793243.85,
3427927.95 5793243.85,3427927.95 5793243.8,3427927.95 5793243.75)))
				

--- Checking all the pixels of a large raster tile can take a long time.
--- You can dramatically improve speed at some lose of precision by orders of magnitude 
--  by sampling pixels using the step optional parameter of generate_series.  
--  This next example does the same as previous but by checking 1 for every 4 (2x2) pixels and putting in the last checked
--  putting in the checked pixel as the value for subsequent 4
	
SELECT ST_AsText(ST_Union(pixpolyg)) As shadow
FROM (SELECT ST_Translate(ST_MakeEnvelope(
		ST_UpperLeftX(rast), ST_UpperLeftY(rast), 
			ST_UpperLeftX(rast) + ST_ScaleX(rast)*2,
			ST_UpperLeftY(rast) + ST_ScaleY(rast)*2, 0
			), ST_ScaleX(rast)*x, ST_ScaleY(rast)*y
		) As pixpolyg, ST_Value(rast, 2, x, y) As b2val
	FROM dummy_rast CROSS JOIN
generate_series(1,1000,2) As x CROSS JOIN generate_series(1,1000,2) As y
WHERE rid =  2 
	AND x <= ST_Width(rast)  AND y <= ST_Height(rast)  ) As foo
WHERE  
	ST_Intersects(
		pixpolyg, 
		ST_GeomFromText('POLYGON((3427928 5793244,3427927.75 5793243.75,3427928 5793243.75,3427928 5793244))',0)
		) AND b2val != 254;

		shadow
------------------------------------------------------------------------------------
 MULTIPOLYGON(((3427927.9 5793243.85,3427927.8 5793243.85,3427927.8 5793243.95,
 3427927.9 5793243.95,3427928 5793243.95,3427928.1 5793243.95,3427928.1 5793243.85,3427928 5793243.85,3427927.9 5793243.85)),
 ((3427927.9 5793243.65,3427927.8 5793243.65,3427927.8 5793243.75,3427927.8 5793243.85,3427927.9 5793243.85,
 3427928 5793243.85,3427928 5793243.75,3427928.1 5793243.75,3427928.1 5793243.65,3427928 5793243.65,3427927.9 5793243.65)))
				

関連情報

ST_DumpAsPolygons, ST_NumBands, ST_PixelAsPolygon, ST_ScaleX, , ST_ScaleY, ST_UpperLeftX, ST_UpperLeftY, ST_SRID, ST_AsText, , ST_Point, ST_MakeEnvelope, ST_Intersects, ST_Intersection