• 金仓数据库 KingbaseGIS 使用手册（8.5. 栅格波段存取函数、8.6. Raster Pixel Accessors and Setters）

    8.5. 栅格波段存取函数

    8.5.1. ST_BandMetaData

    ST_BandMetaData —返回指定的栅格波段的基本元数据信息，如果没有指定栅格波段，默认为1。

    用法

    record ST_BandMetaData(raster rast, integer bandnum=1);

    描述

    返回指定的栅格波段的基本元数据信息，如果没有指定栅格波段，默认为1。返回的元数据个数如下： pixeltype| nodatavalue | isoutdb | path.

    注意

    如果栅格没有包含波段，那么将会抛出错误。

    注意

    如果波段没有NODATA 值，那么NODATA值会被设置为NULL。

    样例

    SELECT rid, (foo.md).*
FROM (SELECT rid, ST_BandMetaData(rast,1) As md FROM dummy_rast WHERE rid=2) As foo;

rid| pixeltype | nodatavalue | isoutdb | path
---+-----------+-------------+---------+--------
2  | 8BUI      | 0           | f       |

    参考

    ST_MetaData, ST_BandPixelType

    8.5.2. ST_BandNoDataValue

    ST_BandNoDataValue —返回给定栅格波段的NODATA值，如果没有给出指定波段，则默认为取1。

    用法

    double precision ST_BandNoDataValue(raster rast, integer bandnum=1);

    描述

    返回给定栅格波段的NODATA值，如果没有给出指定波段，则默认为取1。

    样例

    SELECT ST_BandNoDataValue(rast,1) As bnval1,
ST_BandNoDataValue(rast,2) As bnval2, ST_BandNoDataValue(rast,3) As bnval3
FROM dummy_rast
WHERE rid = 2;

bnval1  | bnval2 | bnval3
--------+--------+--------
0       | 0      | 0

    参考

    ST_NumBands

    8.5.3. ST_BandIsNoData

    ST_BandIsNoData —如果波段只设置了NODATA值，则返回true。

    用法

    boolean ST_BandIsNoData(raster rast, integer band, boolean forceChecking=true);
boolean ST_BandIsNoData(raster rast, boolean forceChecking=true);

    描述

    如果波段只设置了NODATA值，则返回true。 如果没有指定波段位置，则默认为波段1。 如果最后一个参数forceChecking值为true，那么整个波段将会一个像素一个像素进行校验；否则该函数简单返回该波段的的isnodata标识。 如果没有指定该参数，那么默认参数值是FALSE。

    样例

    -- Create dummy table with one raster column
create table dummy_rast (rid integer, rast raster);
-- Add raster with two bands, one pixel/band. In the first band,
-- nodatavalue = pixel value = 3.
-- In the second band, nodatavalue = 13, pixel value = 4
insert into dummy_rast values(1,
(
'01' -- little endian (uint8 ndr)
||
'0000' -- version (uint16 0)
||
'0200' -- nBands (uint16 0)
||
'17263529ED684A3F' -- scaleX (float64 0.000805965234044584)
||
'F9253529ED684ABF' -- scaleY (float64 -0.00080596523404458)
||
'1C9F33CE69E352C0' -- ipX (float64 -75.5533328537098)
||
'718F0E9A27A44840' -- ipY (float64 49.2824585505576)
||
'ED50EB853EC32B3F' -- skewX (float64 0.000211812383858707)
||
'7550EB853EC32B3F' -- skewY (float64 0.000211812383858704)
||
'E6100000' -- SRID (int32 4326)
||
'0100' -- width (uint16 1)
||
'0100' -- height (uint16 1)
||
'6' -- hasnodatavalue and isnodata value set to true.
||
'2' -- first band type (4BUI)
||
'03' -- novalue==3
||
'03' -- pixel(0,0)==3 (same that nodata)
||
'0' -- hasnodatavalue set to false
||
'5' -- second band type (16BSI)
||
'0D00' -- novalue==13
||
'0400' -- pixel(0,0)==4
)::raster
);
select st_bandisnodata(rast, 1) from dummy_rast where rid = 1; -- Expected true
select st_bandisnodata(rast, 2) from dummy_rast where rid = 1; -- Expected false

    参考

    ST_BandNoDataValue, ST_NumBands, ST_SetBandNoDataValue, ST_SetBandIsNoData

    8.5.4. ST_BandPath

    ST_BandPath — 返回存储在文件系统中的波段的文件路径。如果没有指定是哪个波段，默认是波段1。

    用法

    text ST_BandPath(raster rast, integer bandnum=1);

    描述

    返回存储在文件系统中的波段的文件路径。如果没有指定是哪个波段，默认是波段1。如果波段是数据库中的波段，将抛出一个错误。

    8.5.5. ST_BandPixelType

    ST_BandPixelType —返回给定波段的像素类型，如果没有指定是哪个波段，默认是波段1。

    用法

    text ST_BandPixelType(raster rast, integer bandnum=1);

    描述

    返回给定波段的像素类型，如果没有指定是哪个波段，默认是波段1。

    总计有11种波段类型，支持的波段类型如下：

    • 1BB - 1-bit boolean

    • 2BUI - 2-bit unsigned integer

    • 4BUI - 4-bit unsigned integer

    • 8BSI - 8-bit signed integer

    • 8BUI - 8-bit unsigned integer

    • 16BSI - 16-bit signed integer

    • 16BUI - 16-bit unsigned integer

    • 32BSI - 32-bit signed integer

    • 32BUI - 32-bit unsigned integer

    • 32BF - 32-bit float

    • 64BF - 64-bit float

    样例

    SELECT ST_BandPixelType(rast,1) As btype1,
ST_BandPixelType(rast,2) As btype2, ST_BandPixelType(rast,3) As btype3
FROM dummy_rast
WHERE rid = 2;

btype1 | btype2 | btype3
-------+--------+--------
8BUI   | 8BUI   | 8BUI

    参考

    ST_NumBands

    8.5.6. ST_HasNoBand

    ST_HasNoBand — 根据给定的波段，如果没有该波段，则返回true。如果没有指定波段，则默认指的是波段1。

    用法

    boolean ST_HasNoBand(raster rast, integer bandnum=1);

    描述

    根据给定的波段，如果没有该波段，则返回true。如果没有指定波段，则默认指的是波段1。

    样例

    SELECT rid, ST_HasNoBand(rast) As hb1, ST_HasNoBand(rast,2) as hb2,
ST_HasNoBand(rast,4) as hb4, ST_NumBands(rast) As numbands
FROM dummy_rast;

rid| hb1| hb2| hb4| numbands
---+----+----+----+----------
1  | t  | t  | t  | 0
2  | f  | f  | t  | 3

    参考

    ST_NumBands

    8.6. Raster Pixel Accessors and Setters

    8.6.1. ST_PixelAsPolygon

    ST_PixelAsPolygon — 返回包含指定行和列数的像素的多边形几何对象。

    用法

    geometry ST_PixelAsPolygon(raster rast, integer columnx, integer rowy);

    描述

    返回包含指定行和列数的像素的多边形几何对象。

    样例

    -- get raster pixel polygon
SELECT i,j, ST_AsText(ST_PixelAsPolygon(foo.rast, i,j)) As b1pgeom FROM
dummy_rast As foo
CROSS JOIN generate_series(1,2) As i CROSS JOIN generate_series(1,1) As j
WHERE rid=2;

i  | j | b1pgeom
---+---+-----------------------------------------------------------------------------
1  | 1 | POLYGON((3427927.75 5793244,3427927.8 5793244,3427927.8 5793243.95,...
2  | 1 | POLYGON((3427927.8 5793244,3427927.85 5793244,3427927.85 5793243.95, ..

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygons, ST_PixelAsPoint, ST_PixelAsPoints, ST_PixelAsCentroid, ST_PixelAsCentroids,ST_Intersection, ST_AsText

    8.6.2. ST_PixelAsPolygons

    ST_PixelAsPolygons —返回一个包含一个栅格波段的每个像元的多边形几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。

    用法

    setof record ST_PixelAsPolygons(raster rast, integer band=1, boolean exclude_nodata_value=TRUE);

    描述

    返回一个包含一个栅格波段的每个像元的多边形几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。

    注意

    ST_PixelAsPolygons 返回每个像元的多边形几何对象。这个函数和ST_DumpAsPolygons是不同的，每个几何对象代表一个或更多像素值相同的几何对象。

    注意

    当exclude_nodata_value = TRUE时，只有像素值不是NODATA值的像素会返回多边形几何对象。

    样例

    -- get raster pixel polygon
SELECT (gv).x, (gv).y, (gv).val, ST_AsText((gv).geom) geom
FROM (SELECT ST_PixelAsPolygons(
ST_SetValue(ST_SetValue(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0, 0, 0.001,
-0.001, 0.001, 0.001, 4269), '8BUI'::text, 1, 0),2, 2, 10),
1, 1, NULL)
) gv
) foo;

x | y | val| geom
--+---+----+------------------------------------------------------------------------
1 | 1 |    | POLYGON((0 0,0.001 0.001,0.002 0,0.001 -0.001,0 0))
1 | 2 | 1  | POLYGON((0.001 -0.001,0.002 0,0.003 -0.001,0.002 -0.002,0.001 -0.001))
2 | 1 | 1  | POLYGON((0.001 0.001,0.002 0.002,0.003 0.001,0.002 0,0.001 0.001))
2 | 2 | 10 | POLYGON((0.002 0,0.003 0.001,0.004 0,0.003 -0.001,0.002 0))

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygon, ST_PixelAsPoint, ST_PixelAsPoints, ST_PixelAsCentroid, ST_PixelAsCentroids,ST_AsText

    8.6.3. ST_PixelAsPoint

    ST_PixelAsPoint —返回像元左上角的point几何对象。

    用法

    geometry ST_PixelAsPoint(raster rast, integer columnx, integer rowy);

    描述

    返回像元左上角的point几何对象。

    样例

    SELECT ST_AsText(ST_PixelAsPoint(rast, 1, 1)) FROM dummy_rast WHERE rid = 1;

st_astext
----------------
POINT(0.5 0.5)

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygon, ST_PixelAsPolygons, ST_PixelAsPoints, ST_PixelAsCentroid, ST_PixelAsCentroids

    8.6.4. ST_PixelAsPoints

    ST_PixelAsPoints —返回一个包含一个栅格波段的每个像元的点几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。点几何对象的坐标是像元的左上角。

    用法

    geometry ST_PixelAsPoints(raster rast, integer band=1, boolean exclude_nodata_value=TRUE);

    描述

    返回一个包含一个栅格波段的每个像元的点几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。点几何对象的坐标是像元的左上角。

    注意

    当exclude_nodata_value = TRUE,只有那些像素值不为NODATA值的像素返回点几何对象。

    样例

    SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsPoints(rast, 1)).* FROM
dummy_rast WHERE rid = 2) foo;

x | y | val | st_astext
---+---+-----+------------------------------
1 | 1 | 253 | POINT(3427927.75 5793244)
2 | 1 | 254 | POINT(3427927.8 5793244)
3 | 1 | 253 | POINT(3427927.85 5793244)
4 | 1 | 254 | POINT(3427927.9 5793244)
5 | 1 | 254 | POINT(3427927.95 5793244)
1 | 2 | 253 | POINT(3427927.75 5793243.95)
2 | 2 | 254 | POINT(3427927.8 5793243.95)
3 | 2 | 254 | POINT(3427927.85 5793243.95)
4 | 2 | 253 | POINT(3427927.9 5793243.95)
5 | 2 | 249 | POINT(3427927.95 5793243.95)
1 | 3 | 250 | POINT(3427927.75 5793243.9)
2 | 3 | 254 | POINT(3427927.8 5793243.9)
3 | 3 | 254 | POINT(3427927.85 5793243.9)
4 | 3 | 252 | POINT(3427927.9 5793243.9)
5 | 3 | 249 | POINT(3427927.95 5793243.9)
1 | 4 | 251 | POINT(3427927.75 5793243.85)
2 | 4 | 253 | POINT(3427927.8 5793243.85)
3 | 4 | 254 | POINT(3427927.85 5793243.85)
4 | 4 | 254 | POINT(3427927.9 5793243.85)
5 | 4 | 253 | POINT(3427927.95 5793243.85)
1 | 5 | 252 | POINT(3427927.75 5793243.8)
2 | 5 | 250 | POINT(3427927.8 5793243.8)
3 | 5 | 254 | POINT(3427927.85 5793243.8)
4 | 5 | 254 | POINT(3427927.9 5793243.8)
5 | 5 | 254 | POINT(3427927.95 5793243.8)

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygon, ST_PixelAsPolygons, ST_PixelAsPoint, ST_PixelAsCentroid, ST_PixelAsCentroids

    8.6.5. ST_PixelAsCentroid

    ST_PixelAsCentroid — 返回栅格某个像元的中心点（point geometry类型）。

    用法

    geometry ST_PixelAsCentroid(raster rast, integer columnx, integer rowy);

    描述

    返回栅格某个像元的中心点（point geometry类型）。

    样例

    SELECT ST_AsText(ST_PixelAsCentroid(rast, 1, 1)) FROM dummy_rast WHERE rid = 1;

st_astext
--------------
POINT(1.5 2)

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygon, ST_PixelAsPolygons, ST_PixelAsPoint, ST_PixelAsPoints, ST_PixelAsCentroids

    8.6.6. ST_PixelAsCentroids

    ST_PixelAsCentroids — 返回一个包含一个栅格波段的中心（点几何对象）的点几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。点几何对象表示的是像元的中心。

    用法

    geometry ST_PixelAsCentroids(raster rast, integer band=1, boolean exclude_nodata_value=TRUE);

    描述

    返回一个包含一个栅格波段的中心（点几何对象）的点几何对象的记录集，该记录集包含列value，每个像元的X和Y栅格坐标。点几何对象表示的是像元的中心。

    注意

    当exclude_nodata_value = TRUE,只有那些像素值不为NODATA值的像素返回点几何对象。

    样例

    SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM
dummy_rast WHERE rid = 2) foo;

x | y | val | st_astext
--+---+-----+--------------------------------
1 | 1 | 253 | POINT(3427927.775 5793243.975)
2 | 1 | 254 | POINT(3427927.825 5793243.975)
3 | 1 | 253 | POINT(3427927.875 5793243.975)
4 | 1 | 254 | POINT(3427927.925 5793243.975)
5 | 1 | 254 | POINT(3427927.975 5793243.975)
1 | 2 | 253 | POINT(3427927.775 5793243.925)
2 | 2 | 254 | POINT(3427927.825 5793243.925)
3 | 2 | 254 | POINT(3427927.875 5793243.925)
4 | 2 | 253 | POINT(3427927.925 5793243.925)
5 | 2 | 249 | POINT(3427927.975 5793243.925)
1 | 3 | 250 | POINT(3427927.775 5793243.875)
2 | 3 | 254 | POINT(3427927.825 5793243.875)
3 | 3 | 254 | POINT(3427927.875 5793243.875)
4 | 3 | 252 | POINT(3427927.925 5793243.875)
5 | 3 | 249 | POINT(3427927.975 5793243.875)
1 | 4 | 251 | POINT(3427927.775 5793243.825)
2 | 4 | 253 | POINT(3427927.825 5793243.825)
3 | 4 | 254 | POINT(3427927.875 5793243.825)
4 | 4 | 254 | POINT(3427927.925 5793243.825)
5 | 4 | 253 | POINT(3427927.975 5793243.825)
1 | 5 | 252 | POINT(3427927.775 5793243.775)
2 | 5 | 250 | POINT(3427927.825 5793243.775)
3 | 5 | 254 | POINT(3427927.875 5793243.775)
4 | 5 | 254 | POINT(3427927.925 5793243.775)
5 | 5 | 254 | POINT(3427927.975 5793243.775)

    参考

    ST_DumpAsPolygons, ST_PixelAsPolygon, ST_PixelAsPolygons, ST_PixelAsPoint, ST_PixelAsPoints, ST_PixelAsCentroid

    8.6.7. ST_Value

    ST_Value — 返回给定波段的行和列的像元的值。波段如果没有指定是哪个，默认从1开始。如果exclude_nodata_value 值为false，那么所有包含NODATA值的像元都会返回value值，如果exclude_nodata_value没有指定，那么这个参数值将从栅格元数据中读取。

    用法

    double precision ST_Value(raster rast, geometry pt,
   boolean exclude_nodata_value=true);
double precision ST_Value(raster rast, integer bandnum, geometry pt,
   boolean exclude_nodata_value=true);
double precision ST_Value(raster rast, integer columnx, integer rowy,
   boolean exclude_nodata_value=true);
double precision ST_Value(raster rast, integer bandnum,
   integer columnx, integer rowy, boolean exclude_nodata_value=true);

    描述

    返回给定波段的行和列的像元的值。波段如果没有指定是哪个，默认从1开始。 如果exclude_nodata_value值为false，那么所有包含NODATA值的像元都会返回value值，如果这个值为true，那么只有不包含NODATA值的像素会应用本函数。

    样例

    -- the srid of your geometry should be same as for your raster
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

-- general fictitious example using a real table
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

--- Get all values in bands 1,2,3 of each pixel --
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
:
:

-- Get all values in bands 1,2,3 of each pixel same as above
-- but returning the upper left point point of each pixel
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
:

-- Get a polygon formed by union of all pixels
-- that fall in a particular value range
-- and intersect particular polygon
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)
WHERE
ST_Intersects(
pixpolyg,
AND y <= ST_Height(rast) ) As foo
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

    8.6.8. ST_NearestValue

    ST_NearestValue — 返回给定波段的给定像元的最近的非NODATA的像元的值，给定像元可以通过行和列的位置或者栅格所在的空间参考系的几何坐标指定。

    用法

    double precision ST_NearestValue(raster rast, integer bandnum, geometry pt,
   boolean exclude_nodata_value=true);
double precision ST_NearestValue(raster rast, geometry pt,
   boolean exclude_nodata_value=true);
double precision ST_NearestValue(raster rast, integer bandnum,
   integer columnx, integer rowy, boolean exclude_nodata_value=true);
double precision ST_NearestValue(raster rast,
   integer columnx, integer rowy, boolean exclude_nodata_value=true);

    描述

    返回给定波段的给定像元的最近的非NODATA的像元的值，给定像元可以通过行和列的位置或者栅格所在的空间参考系的几何坐标指定。 如果指定的像元是NODATA的，那么这个函数会返回找到离改点最近的像元的值。 波段如果没有指定是哪个，默认从1开始。如果exclude_nodata_value值为false，那么所有包含NODATA值的像元都会返回value值， 如果exclude_nodata_value没有指定，那么这个参数值将从栅格元数据中读取。

    注意

    ST_NearestValue 是函数ST_Value的简易替换。

    样例

    -- pixel 2x2 has value
SELECT
ST_Value(rast, 2, 2) AS value,
ST_NearestValue(rast, 2, 2) AS nearestvalue
FROM (
SELECT
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 0.
),
2, 3, 0.
),
3, 5, 0.
),
4, 2, 0.
),
5, 4, 0.
) AS rast
) AS foo;

value | nearestvalue
------+--------------
1     | 1

-- pixel 2x3 is NODATA
SELECT
ST_Value(rast, 2, 3) AS value,
ST_NearestValue(rast, 2, 3) AS nearestvalue
FROM (
SELECT
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 0.
),
2, 3, 0.
),
3, 5, 0.
),
4, 2, 0.
),
5, 4, 0.
) AS rast
) AS foo;
value | nearestvalue
------+--------------
1     | 1

    ;参考

    ST_Neighborhood, ST_Value

    8.6.9. ST_Neighborhood

    ST_Neighborhood —返回一个2维双精度的不包含NODATA值的像元值数组，像元是在给定波段像元周围。像元坐标用行列位置指定或几何点指定，参考系以栅格所处参考系为准。

    用法

    double precision[][] ST_Neighborhood(raster rast, integer bandnum,
   integer columnX, integer rowY, integer distanceX, integerdistanceY,
   boolean exclude_nodata_value=true);
double precision[][] ST_Neighborhood(raster rast,
   integer columnX, integer rowY, integer distanceX, integer distanceY,
   booleanexclude_nodata_value=true);
double precision[][] ST_Neighborhood(raster rast, integer bandnum, geometry pt,
   integer distanceX, integer distanceY, booleanexclude_nodata_value=true);
double precision[][] ST_Neighborhood(raster rast, geometry pt,
   integer distanceX, integer distanceY, boolean exclude_nodata_value=true);

    描述

    返回一个2维双精度的不包含NODATA值的像元值数组，像元是在给定波段像元周围。 像元坐标用行列位置指定或几何点指定，参考系以栅格所处参考系为准。 参数distanceX和distanceY定义了指定像元在x轴和y轴方向上的像元个数。 例如，我想知道我感兴趣的像元在x轴方向上3个像素单位，y轴方向上2个像素单位以内的所有像素值。返回2维数组的中心点，就是提供的参数点（感兴趣的的点）。

    波段如果没有指定是哪个，默认从1开始。如果exclude_nodata_value值为false，那么所有包含NODATA值的像元都会返回value值， 如果exclude_nodata_value没有指定，那么这个参数值将从栅格元数据中读取。

    注意

    返回2维数组的沿着每个轴方向的元素个数是2 * (distanceX|distanceY) + 1， 因此对于distanceX和 distanceY都是1的情况，返回的数组将是3x3的。

    注意

    输出的2维数组可以用于任意的内置的栅格处理函数，例如ST_Min4ma, ST_Sum4ma, ST_Mean4ma。

    样例

    -- pixel 2x2 has value
SELECT
ST_Neighborhood(rast, 2, 2, 1, 1)
FROM (
SELECT
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 1, ARRAY[
[0, 1, 1, 1, 1],
[1, 1, 1, 0, 1],
[1, 0, 1, 1, 1],
[1, 1, 1, 1, 0],
[1, 1, 0, 1, 1]
]::double precision[],
1
) AS rast
) AS foo;

st_neighborhood
---------------------------------
{{NULL,1,1},{1,1,NULL},{1,1,1}}

-- pixel 2x3 is NODATA
SELECT
ST_Neighborhood(rast, 2, 3, 1, 1)
FROM (
SELECT
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 1, ARRAY[
[0, 1, 1, 1, 1],
[1, 1, 1, 0, 1],
[1, 0, 1, 1, 1],
[1, 1, 1, 1, 0],
[1, 1, 0, 1, 1]
]::double precision[],
1
) AS rast
) AS foo;

st_neighborhood
------------------------------
{{1,1,1},{1,NULL,1},{1,1,1}}

-- pixel 3x3 has value
-- exclude_nodata_value = FALSE
SELECT
ST_Neighborhood(rast, 3, 3, 1, 1, false)
FROM (
SELECT
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 1, ARRAY[
[0, 1, 1, 1, 1],
[1, 1, 1, 0, 1],
[1, 0, 1, 1, 1],
[1, 1, 1, 1, 0],
[1, 1, 0, 1, 1]
]::double precision[],
1
) AS rast
) AS foo;

st_neighborhood
---------------------------
{{1,0,1},{1,1,1},{0,1,1}}

    参考

    ST_NearestValue, ST_Min4ma, ST_Max4ma, ST_Sum4ma, ST_Mean4ma, ST_Range4ma, ST_Distinct4ma, ST_StdDev4ma

    8.6.10. ST_SetValue

    ST_SetValue — 根据给定波段的给定像元，设定其值，返回一个新的修改后的栅格。

    波段如果没有指定是哪个，默认从1开始。

    用法

    raster ST_SetValue(raster rast, integer bandnum, geometry geom, double precision newvalue);
raster ST_SetValue(raster rast, geometry geom, double precision newvalue);
raster ST_SetValue(raster rast, integer bandnum, integer columnx, integer rowy, double precision newvalue);
raster ST_SetValue(raster rast, integer columnx, integer rowy, double precision newvalue);

    描述

    根据给定波段的给定像元，设定其值，返回一个新的修改后的栅格。如果没有指定波段，默认是波段1。

    ST_SetValue() 现在支持包括点在内的任意几何类型对象。几何参数geom是一个ST_SetValues()变量geomval[]的包装。

    样例

    -- Geometry example
SELECT (foo.geomval).val, ST_AsText(ST_Union((foo.geomval).geom))
FROM (SELECT ST_DumpAsPolygons(
ST_SetValue(rast,1,
ST_Point(3427927.75, 5793243.95),
50)
) As geomval
FROM dummy_rast
where rid = 2) As foo
WHERE (foo.geomval).val < 250
GROUP BY (foo.geomval).val;

val  | st_astext
-----+-------------------------------------------------------------------
50   | POLYGON((3427927.75 5793244,3427927.75 5793243.95,3427927.8 579324 ...
249  | POLYGON((3427927.95 5793243.95,3427927.95 5793243.85,3427928 57932 ...

-- Store the changed raster --
UPDATE dummy_rast SET rast = ST_SetValue(rast,1, ST_Point(3427927.75, 5793243.95),100)
WHERE rid = 2 ;

    参考

    ST_Value, ST_DumpAsPolygons

    8.6.11. ST_SetValues

    ST_SetValues —根据给定的栅格波段值，设置新值，返回被设定新波段值的栅格

    用法

    raster ST_SetValues(raster rast, integer nband, integer columnx, integer rowy,
   double precision[][] newvalueset, boolean[][]noset=NULL, boolean keepnodata=FALSE);
raster ST_SetValues(raster rast, integer nband, integer columnx, integer rowy,
   double precision[][] newvalueset, double precisionnosetvalue, boolean keepnodata=FALSE);
raster ST_SetValues(raster rast, integer nband, integer columnx, integer rowy,
   integer width, integer height, double precisionnewvalue, boolean keepnodata=FALSE);
raster ST_SetValues(raster rast, integer columnx, integer rowy,
   integer width, integer height, double precision newvalue, booleankeepnodata=FALSE);
raster ST_SetValues(raster rast, integer nband,
   geomval[] geomvalset, boolean keepnodata=FALSE);

    描述

    根据给定的栅格波段值，设置新值，返回被设定新波段值的栅格。

    如果参数keepnodata 值是 TRUE, 哪些像元值是NODATA的将不会设置成对应的newvalueset变量值。

    对于函数变体1，指定被修改值的像素是由参数columnx, rowy像素坐标和数组变量newvalueset的维度决定的。

    参数noset 可以防止值在newvalueset 内的像素被设置值。参考样例1。

    函数变体2与函数变体1类似，但是使用的是一个简单双精度nosetvalue数组代替了逻辑值数组noset。 若nosetvalue值在newvalueset数组中，则会被跳过不修改。with the nosetvalue value with be skipped. 参考样例2。

    对于函数变体3，被设定值的像元是由参数columnx, rowy 像元坐标, width 和height参数决定的.参考样例。

    函数变体4和函数变体3基本相同，区别是它假定栅格波段的第一个像素会被设置值。

    对于函数变体5，数组变量geomval用于指定被修改的像素范围。 如果数组内的所有几何对象都是POINT或MULTIPOINT类型，这个函数会使用快捷办法，也就是直接使用每个点的经纬度来进行设置。 否则这些几何对象会被转换成栅格，然后再遍历一次按照栅格的方法处理。

    样例 : Variant 1

    /*
The ST_SetValues() does the following...

+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 1 | 1 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |     =>    | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, ARRAY[[9, 9], [9, 9]]::double precision[][]
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 1 | 1
2 | 2 | 9
2 | 3 | 9
3 | 1 | 1
3 | 2 | 9
3 | 3 | 9

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 9 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][]
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 9
1 | 2 | 9
1 | 3 | 9
2 | 1 | 9
2 | 2 |
2 | 3 | 9
3 | 1 | 9
3 | 2 | 9
3 | 3 | 9

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1,
ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][],
ARRAY[[false], [true]]::boolean[][]
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 9
1 | 2 | 1
1 | 3 | 9
2 | 1 | 9
2 | 2 |
2 | 3 | 9
3 | 1 | 9
3 | 2 | 9
3 | 3 | 9

/*
The ST_SetValues() does the following...

+ - + - + - +          + - + - + - +
|   | 1 | 1 |          |   | 9 | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |    =>    | 1 |   | 9 |
+ - + - + - +          + - + - + - +
| 1 | 1 | 1 |          | 9 | 9 | 9 |
+ - + - + - +          + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_SetValue(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, NULL
),
1, 1, 1,
ARRAY[[9, 9, 9], [9, NULL, 9], [9, 9, 9]]::double precision[][],
ARRAY[[false], [true]]::boolean[][],
TRUE
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 |
1 | 2 | 1
1 | 3 | 9
2 | 1 | 9
2 | 2 |
2 | 3 | 9
3 | 1 | 9
3 | 2 | 9
3 | 3 | 9

    样例 : Variant 2

    /*
The ST_SetValues() does the following...

+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 1 | 1 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |     =>    | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[-1, -1, -1], [-1, 9, 9], [-1, 9, 9]]::double precision[][], -1
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 1 | 1
2 | 2 | 9
2 | 3 | 9
3 | 1 | 1
3 | 2 | 9
3 | 3 | 9


/*
This example is like the previous one.
Instead of nosetvalue = -1, nosetvalue = NULL
The ST_SetValues() does the following...

+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 1 | 1 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |     =>    | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 1, 1, ARRAY[[NULL, NULL, NULL], [NULL, 9, 9], [NULL, 9, 9]]::double precision[][], ←
NULL::double precision
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 1 | 1
2 | 2 | 9
2 | 3 | 9
3 | 1 | 1
3 | 2 | 9
3 | 3 | 9

    样例 : Variant 3

    /*
The ST_SetValues() does the following...

+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 1 | 1 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |     =>    | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, 2, 2, 9
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 1 | 1
2 | 2 | 9
2 | 3 | 9
3 | 1 | 1
3 | 2 | 9
3 | 3 | 9

    样例 : Variant 4

    /*
The ST_SetValues() does the following...

+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 1 | 1 |
+ - + - + - +           + - + - + - +
| 1 |   | 1 |     =>    | 1 |   | 9 |
+ - + - + - +           + - + - + - +
| 1 | 1 | 1 |           | 1 | 9 | 9 |
+ - + - + - +           + - + - + - +

*/
SELECT
(poly).x,
(poly).y,
(poly).val
FROM (
SELECT
ST_PixelAsPolygons(
ST_SetValues(
ST_SetValue(
ST_AddBand(
ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0
),
1, 2, 2, NULL
),
1, 2, 2, 2, 2, 9, TRUE
)
) AS poly
) foo
ORDER BY 1, 2;

x | y | val
--+---+-----
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 1 | 1
2 | 2 |
2 | 3 | 9
3 | 1 | 1
3 | 2 | 9
3 | 3 | 9

    样例 : Variant 5

    WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0,
0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1, 4 -1, 4 -4, 1 -4, 1 -1))'::geometry geom UNION
ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0, 5 0, 5 -1, 1 -1, 1 -4, 0 -4, 0 0))'::geometry geom
UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0, 4 4, 4 -4)'::geometry
)
SELECT
rid, gid, ST_DumpValues(ST_SetValue(rast, 1, geom, gid))
FROM foo t1
CROSS JOIN bar t2
ORDER BY rid, gid;

rid |gid| st_dumpvalues
----+---+----------------------------------------------------------------------------------------------------------------------------------------------
1   | 1 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,1,NULL, NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL}}")
1   | 2 | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
1   | 3 | (1,"{{3,3,3,3,3},{3,NULL,NULL,NULL,NULL},{3,NULL,NULL,NULL,NULL},{3,NULL,NULL, NULL,NULL},{NULL,NULL,NULL,NULL,NULL}}")
1   | 4 | (1,"{{4,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL, NULL},{NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,4}}")
(4 rows)

-- The following shows that geomvals later in the array can overwrite prior geomvals

WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0,
0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1, 4 -1, 4 -4, 1 -4, 1 -1))'::geometry geom UNION
ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0, 5 0, 5 -1, 1 -1, 1 -4, 0 -4, 0 0))'::geometry geom
UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0, 4 4, 4 -4)'::geometry
)
SELECT
t1.rid, t2.gid, t3.gid, ST_DumpValues(ST_SetValues(rast, 1, ARRAY[ROW(t2.geom, t2.gid),
ROW(t3.geom, t3.gid)]::geomval[]))
FROM foo t1
CROSS JOIN bar t2
CROSS JOIN bar t3
WHERE t2.gid = 1
AND t3.gid = 2
ORDER BY t1.rid, t2.gid, t3.gid;

rid | gid | gid | st_dumpvalues
----+-----+-----+----------------------------------------------------------------------------------------------------------------------
1   | 1   | 2   | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,2,2,NULL},{ NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
(1 row)

-- This example is the opposite of the prior example

WITH foo AS (
SELECT 1 AS rid, ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0,
0) AS rast
), bar AS (
SELECT 1 AS gid, 'SRID=0;POINT(2.5 -2.5)'::geometry geom UNION ALL
SELECT 2 AS gid, 'SRID=0;POLYGON((1 -1, 4 -1, 4 -4, 1 -4, 1 -1))'::geometry geom UNION
ALL
SELECT 3 AS gid, 'SRID=0;POLYGON((0 0, 5 0, 5 -1, 1 -1, 1 -4, 0 -4, 0 0))'::geometry geom
UNION ALL
SELECT 4 AS gid, 'SRID=0;MULTIPOINT(0 0, 4 4, 4 -4)'::geometry
)
SELECT
t1.rid, t2.gid, t3.gid, ST_DumpValues(ST_SetValues(rast, 1, ARRAY[ROW(t2.geom, t2.gid),
ROW(t3.geom, t3.gid)]::geomval[]))
FROM foo t1
CROSS JOIN bar t2
CROSS JOIN bar t3
WHERE t2.gid = 2
AND t3.gid = 1
ORDER BY t1.rid, t2.gid, t3.gid;

rid | gid | gid | st_dumpvalues
----+-----+-----+-----------------------------------------------------------------------------------
1   | 2   | 1   | (1,"{{NULL,NULL,NULL,NULL,NULL},{NULL,2,2,2,NULL},{NULL,2,1,2,NULL},{NULL,2,2,2,NULL},{NULL,NULL,NULL,NULL,NULL}}")
(1 row)

    参考

    ST_Value, ST_SetValue, ST_PixelAsPolygons

    8.6.12. ST_DumpValues

    ST_DumpValues — 以2维数组形式，返回指定波段的值。

    用法

    setof record ST_DumpValues( raster rast , integer[] nband , boolean exclude_nodata_value=true );
double precision[][] ST_DumpValues( raster rast , integer nband , boolean exclude_nodata_value=true );

    描述

    以2维数组形式，返回指定波段的值。如果参数nband是NULL，或者没有提供，那么所有的栅格波段都不会被处理。

    样例

    WITH foo AS (
SELECT ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0), 2, '32BF', 3, -9999), 3, '16BSI', 0, 0) AS rast
)
SELECT
(ST_DumpValues(rast)).*
FROM foo;

nband| valarray
-----+------------------------------------------------------
1    | {{1,1,1},{1,1,1},{1,1,1}}
2    | {{3,3,3},{3,3,3},{3,3,3}}
3    | {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
(3 rows)

WITH foo AS (
SELECT ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0),
1, '8BUI', 1, 0), 2, '32BF', 3, -9999), 3, '16BSI', 0, 0) AS rast
)
SELECT
(ST_DumpValues(rast, ARRAY[3, 1])).*
FROM foo;

nband| valarray
-----+------------------------------------------------------
3    | {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
1    | {{1,1,1},{1,1,1},{1,1,1}}
(2 rows)

    参考

    ST_Value, ST_SetValue, ST_SetValues

    8.6.13. ST_PixelOfValue

    ST_PixelOfValue — 根据给定的输入值，返回所有匹配的像素的columnx，rowy坐标记录。

    用法

    setof record ST_PixelOfValue( raster rast , integer nband , double precision[] search , boolean exclude_nodata_value=true );
setof record ST_PixelOfValue( raster rast , double precision[] search , boolean exclude_nodata_value=true );
setof record ST_PixelOfValue( raster rast , integer nband , double precision search , boolean exclude_nodata_value=true );
setof record ST_PixelOfValue( raster rast , double precision search , boolean exclude_nodata_value=true );

    描述

    根据给定的输入值，返回所有匹配的像素的columnx，rowy坐标记录，如果没有指定波段，那么默认为波段1。

    样例

    SELECT
(pixels).*
FROM (
SELECT
ST_PixelOfValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_SetValue(
ST_AddBand(
ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
'8BUI'::text, 1, 0
),
1, 1, 0
),
2, 3, 0
),
3, 5, 0
),
4, 2, 0
),
5, 4, 255
)
, 1, ARRAY[1, 255]) AS pixels
) AS foo;

val | x | y
----+---+---
1   | 1 | 2
1   | 1 | 3
1   | 1 | 4
1   | 1 | 5
1   | 2 | 1
1   | 2 | 2
1   | 2 | 4
1   | 2 | 5
1   | 3 | 1
1   | 3 | 2
1   | 3 | 3
1   | 3 | 4
1   | 4 | 1
1   | 4 | 3
1   | 4 | 4
1   | 4 | 5
1   | 5 | 1
1   | 5 | 2
1   | 5 | 3
255 | 5 | 4
1   | 5 | 5
  • 相关阅读:
    2022-09-07 mysql/stonedb-查询优化器逻辑记录及优化器魔改
    C++：类模板的应用实现动态数组
    杭电oj 2044 一只小蜜蜂，C语言
    Docker搭建Plex流媒体服务并播放自己本地视频
    Spring事务和事务的传播机制（JavaEE进阶系列7）
    java——注释与空行
    JavaScript重点知识总结二
    优秀的 OKR 案例参考
    基于Mybatis-Plus实现Geometry字段在PostGis空间数据库中的使用
    内联函数在头文件内定义
  • 原文地址：https://blog.csdn.net/arthemis_14/article/details/126346274