[第五篇] PostGIS：“我让PG更完美！”
概要
本篇文章主要分为栅格构造函数、栅格数据访问函数、栅格波段访问函数、栅格像素访问和设置函数、栅格数据编辑函数、栅格波段编辑函数、栅格波段统计和分析函数、栅格输入输出函数、栅格代数函数、栅格高程函数、栅格转几何图形函数、栅格数据处理操作符、栅格空间参考函数、拓扑构造函数、拓扑访问函数、拓扑编辑函数、拓扑处理函数、几何拓扑构造函数、几何拓扑访问函数、几何拓扑编辑函数、几何拓扑输出函数、拓扑空间参考函数，这22部分。
Raster Reference
Raster Support Data types
geomval、addbandarg、rastbandarg、raster、reclassarg、summarystats、unionarg
Raster Constructors
ST_AddBand
Returns a raster with the new band(s) of given type added with given initial value in the given index location.
If no index is specifified, the band is added to the end.
//语法
(1) raster ST_AddBand(raster rast, addbandarg[] addbandargset);
(2) raster ST_AddBand(raster rast, integer index, text pixeltype, double precision initialvalue=0, double precision nodataval=NULL);
(3) raster ST_AddBand(raster rast, text pixeltype, double precision initialvalue=0, double precision nodataval=NULL);
(4) raster ST_AddBand(raster torast, raster fromrast, integer fromband=1, integer torastindex=at_end);
(5) raster ST_AddBand(raster torast, raster[] fromrasts, integer fromband=1, integer torastindex=at_end);
(6) raster ST_AddBand(raster rast, integer index, text outdbfile, integer[] outdbindex, double precision nodataval=NULL);
(7) raster ST_AddBand(raster rast, text outdbfile, integer[] outdbindex, integer index=at_end, double precision nodataval=NULL);
//示例
-- Add another band of type 8 bit unsigned integer with pixels initialized to 200
UPDATE dummy_rast
SET rast = ST_AddBand(rast,'8BUI'::text,200)
WHERE rid = 1;
ST_AsRaster
Converts a PostGIS geometry to a PostGIS raster.
//语法
raster ST_AsRaster(geometry geom, raster ref, text pixeltype, double precision value=1, double precision nodataval=0, boolean
touched=false);
raster ST_AsRaster(geometry geom, raster ref, text[] pixeltype=ARRAY[’8BUI’], double precision[] value=ARRAY[1], double
precision[] nodataval=ARRAY[0], boolean touched=false);
raster ST_AsRaster(geometry geom, double precision scalex, double precision scaley, double precision gridx, double precision gridy, text pixeltype, double precision value=1, double precision nodataval=0, double precision skewx=0, double precision
skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, double precision scalex, double precision scaley, double precision gridx=NULL, double precision gridy=NULL, text[] pixeltype=ARRAY[’8BUI’], double precision[] value=ARRAY[1], double precision[] nodataval=ARRAY[0], double precision skewx=0, double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, double precision scalex, double precision scaley, text pixeltype, double precision value=1,
double precision nodataval=0, double precision upperleftx=NULL, double precision upperlefty=NULL, double precision skewx=0,
double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, double precision scalex, double precision scaley, text[] pixeltype, double precision[]
value=ARRAY[1], double precision[] nodataval=ARRAY[0], double precision upperleftx=NULL, double precision upperlefty=NULL,
double precision skewx=0, double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, integer width, integer height, double precision gridx, double precision gridy, text pixeltype, double precision value=1, double precision nodataval=0, double precision skewx=0, double precision skewy=0, boolean
touched=false);
raster ST_AsRaster(geometry geom, integer width, integer height, double precision gridx=NULL, double precision gridy=NULL,
text[] pixeltype=ARRAY[’8BUI’], double precision[] value=ARRAY[1], double precision[] nodataval=ARRAY[0], double precision skewx=0, double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, integer width, integer height, text pixeltype, double precision value=1, double precision
nodataval=0, double precision upperleftx=NULL, double precision upperlefty=NULL, double precision skewx=0, double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, integer width, integer height, text[] pixeltype, double precision[] value=ARRAY[1], double precision[] nodataval=ARRAY[0], double precision upperleftx=NULL, double precision upperlefty=NULL, double precision
skewx=0, double precision skewy=0, boolean touched=false);
//示例
-- this will output a black circle taking up 150 x 150 pixels --
SELECT ST_AsPNG(ST_AsRaster(ST_Buffer(ST_Point(1,5),10),150, 150));
ST_Band
Returns one or more bands of an existing raster as a new raster. Useful for building new rasters from existing rasters.
//语法
raster ST_Band(raster rast, integer[] nbands = ARRAY[1]);
raster ST_Band(raster rast, integer nband);
raster ST_Band(raster rast, text nbands, character delimiter=,);
//示例
-- Make 2 new rasters: 1 containing band 1 of dummy, second containing band 2 of dummy and ←-
then reclassified as a 2BUI
SELECT ST_NumBands(rast1) As numb1, ST_BandPixelType(rast1) As pix1,
ST_NumBands(rast2) As numb2, ST_BandPixelType(rast2) As pix2
FROM (
SELECT ST_Band(rast) As rast1, ST_Reclass(ST_Band(rast,3), '100-200):1, [200-254:2', '2 ←-
BUI') As rast2
FROM dummy_rast
WHERE rid = 2) As foo;
numb1 | pix1 | numb2 | pix2
-------+------+-------+------
1 | 8BUI | 1 | 2BUI
ST_Tile
Returns a set of rasters resulting from the split of the input raster based upon the desired dimensions of the output
rasters.
//语法
setof raster ST_Tile(raster rast, int[] nband, integer width, integer height, boolean padwithnodata=FALSE, double precision nodataval=NULL);
setof raster ST_Tile(raster rast, integer nband, integer width, integer height, boolean padwithnodata=FALSE, double precision
nodataval=NULL);
setof raster ST_Tile(raster rast, integer width, integer height, boolean padwithnodata=FALSE, double precision nodataval=NULL);
//示例
WITH foo AS (
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI',
1, 0), 2, '8BUI', 10, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 3, 0, 1, -1, 0, 0, 0), 1, '8BUI',
2, 0), 2, '8BUI', 20, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 6, 0, 1, -1, 0, 0, 0), 1, '8BUI',
3, 0), 2, '8BUI', 30, 0) AS rast UNION ALL
PostGIS 3.0.5dev Manual 483 / 841
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, -3, 1, -1, 0, 0, 0), 1, '8BUI',
4, 0), 2, '8BUI', 40, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 3, -3, 1, -1, 0, 0, 0), 1, '8BUI',
5, 0), 2, '8BUI', 50, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 6, -3, 1, -1, 0, 0, 0), 1, '8BUI',
6, 0), 2, '8BUI', 60, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, -6, 1, -1, 0, 0, 0), 1, '8BUI',
7, 0), 2, '8BUI', 70, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 3, -6, 1, -1, 0, 0, 0), 1, '8BUI',
8, 0), 2, '8BUI', 80, 0) AS rast UNION ALL
SELECT ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(3, 3, 6, -6, 1, -1, 0, 0, 0), 1, '8BUI',
9, 0), 2, '8BUI', 90, 0) AS rast
), bar AS (
SELECT ST_Union(rast) AS rast FROM foo
), baz AS (
SELECT ST_Tile(rast, 3, 3, TRUE) AS rast FROM bar
)
SELECT
ST_DumpValues(rast)
FROM baz;
st_dumpvalues
------------------------------------------
(1,"{{1,1,1},{1,1,1},{1,1,1}}")
(2,"{{10,10,10},{10,10,10},{10,10,10}}")
(1,"{{2,2,2},{2,2,2},{2,2,2}}")
(2,"{{20,20,20},{20,20,20},{20,20,20}}")
(1,"{{3,3,3},{3,3,3},{3,3,3}}")
(2,"{{30,30,30},{30,30,30},{30,30,30}}")
(1,"{{4,4,4},{4,4,4},{4,4,4}}")
(2,"{{40,40,40},{40,40,40},{40,40,40}}")
(1,"{{5,5,5},{5,5,5},{5,5,5}}")
(2,"{{50,50,50},{50,50,50},{50,50,50}}")
(1,"{{6,6,6},{6,6,6},{6,6,6}}")
(2,"{{60,60,60},{60,60,60},{60,60,60}}")
(1,"{{7,7,7},{7,7,7},{7,7,7}}")
(2,"{{70,70,70},{70,70,70},{70,70,70}}")
(1,"{{8,8,8},{8,8,8},{8,8,8}}")
(2,"{{80,80,80},{80,80,80},{80,80,80}}")
(1,"{{9,9,9},{9,9,9},{9,9,9}}")
(2,"{{90,90,90},{90,90,90},{90,90,90}}")
(18 rows
Raster Accessors
ST_GeoReference
Returns the georeference meta data in GDAL or ESRI format as commonly seen in a world fifile. Default is
GDAL.
//语法
text ST_GeoReference(raster rast, text format=GDAL);
//示例
SELECT ST_GeoReference(rast, 'ESRI') As esri_ref, ST_GeoReference(rast, 'GDAL') As gdal_ref
FROM dummy_rast WHERE rid=1;
esri_ref | gdal_ref
--------------+--------------
2.0000000000 | 2.0000000000
0.0000000000 : 0.0000000000
0.0000000000 : 0.0000000000
3.0000000000 : 3.0000000000
1.5000000000 : 0.5000000000
2.0000000000 : 0.5000000000
ST_Height
Returns the height of the raster in pixels.
//语法
integer ST_Height(raster rast);
//示例
SELECT rid, ST_Height(rast) As rastheight
FROM dummy_rast;
rid | rastheight
-----+------------
1 | 20
2 | 5
ST_MetaData
Returns basic meta data about a raster object such as pixel size, rotation (skew), upper, lower left, etc.
//语法
record ST_MetaData(raster rast);
//示例
SELECT rid, (foo.md).*
FROM (SELECT rid, ST_MetaData(rast) As md
FROM dummy_rast) As foo;
rid | upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid | numbands
----+------------+------------+-------+-----
1 | 0.5 | 0.5 | 10 | 20 | 2 | 3 | 0 | 0 | 0 | 0
2 | 3427927.75 | 5793244 | 5 | 5 | 0.05 | -0.05 | 0 | 0 | 0 | 3
ST_ScaleX
Returns the X component of the pixel width in units of coordinate reference system.
//语法
float8 ST_ScaleX(raster rast);
//示例
SELECT rid, ST_ScaleX(rast) As rastpixwidth
FROM dummy_rast;
rid | rastpixwidth
-----+--------------
1 | 2
2 | 0.05
ST_ScaleY
Returns the Y component of the pixel height in units of coordinate reference system.
//语法
float8 ST_ScaleY(raster rast);
//示例
SELECT rid, ST_ScaleY(rast) As rastpixheight
FROM dummy_rast;
rid | rastpixheight
-----+---------------
1 | 3
2 | -0.05
ST_SRID
Returns the spatial reference identififier of the raster as defifined in spatial_ref_sys table.
//语法
integer ST_SRID(raster rast);
//示例
SELECT ST_SRID(raster) As srid
FROM dummy_rast WHERE rid=1;
srid
----------------
0
ST_Width
Returns the width of the raster in pixels.
//语法
integer ST_Width(raster rast);
//示例
SELECT ST_Width(rast) As rastwidth
FROM dummy_rast WHERE rid=1;
rastwidth
----------------
10
ST_WorldToRasterCoord
Returns the upper left corner as column and row given geometric X and Y (longitude and latitude)
or a point geometry expressed in the spatial reference coordinate system of the raster.
//语法
record ST_WorldToRasterCoord(raster rast, geometry pt);
record ST_WorldToRasterCoord(raster rast, double precision longitude, double precision latitude);
//示例
SELECT
rid, (ST_WorldToRasterCoord(rast,3427927.8,20.5)).*, (ST_WorldToRasterCoord(rast,ST_GeomFromText('POINT(3427927.8 20.5)',ST_SRID(rast)))).*
FROM dummy_rast;
rid | columnx | rowy | columnx | rowy
-----+---------+-----------+---------+-----------
1 | 1713964 | 7 | 1713964 | 7
2 | 2 | 115864471 | 2 | 115864471
Raster Band Accessors
Returns basic meta data for a specifific raster band. band num 1 is assumed if none-specifified.
//语法
(1) record ST_BandMetaData(raster rast, integer band=1);
(2) record ST_BandMetaData(raster rast, integer[] band);
//示例
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 | outdbbandnum
-----+-----------+---- --------+---------+------+--------------
2 | 8BUI | 0 | f | |
ST_BandNoDataValue
Returns the value in a given band that represents no data. If no band num 1 is assumed.
//语法
double precision ST_BandNoDataValue(raster rast, integer bandnum=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_BandIsNoData
Returns true if the band is fifilled with only nodata values.
//语法
boolean ST_BandIsNoData(raster rast, integer band, boolean forceChecking=true);
boolean ST_BandIsNoData(raster rast, boolean forceChecking=true);
//示例
-- 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_BandFileSize
Returns the fifile size of a band stored in fifile system. If no bandnum specifified, 1 is assumed.
//语法
bigint ST_BandFileSize(raster rast, integer bandnum=1);
//示例
SELECT ST_BandFileSize(rast,1) FROM dummy_rast WHERE rid = 1;
st_bandfilesize
-----------------
240574
ST_BandPixelType
Returns the type of pixel for given band. If no bandnum specifified, 1 is assumed.
//语法
text ST_BandPixelType(raster rast, integer bandnum=1);
//示例
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
Raster Pixel Accessors and Setters
ST_PixelAsPolygon
Returns the polygon geometry that bounds the pixel for a particular row and column
//语法
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_PixelAsPolygons
Returns the polygon geometry that bounds every pixel of a raster band along with the value, the X and
the Y raster coordinates of each pixel.
//语法
setof record ST_PixelAsPolygons(raster rast, integer band=1, boolean exclude_nodata_value=TRUE);
//示例
-- get raster pixel polygon
-- 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_PixelAsPoint
Returns a point geometry of the pixel’s upper-left corner.
//语法
geometry ST_PixelAsPoint(raster rast, integer columnx, integer rowy);
//示例
SELECT ST_AsText(ST_PixelAsPoint(rast, 1, 1)) FROM dummy_rast WHERE rid = 1;
st_astext
----------------
POINT(0.5 0.5)
ST_PixelAsCentroid
Returns the centroid (point geometry) of the area represented by a pixel.
//语法
geometry ST_PixelAsCentroid(raster rast, integer x, integer y);
//示例
SELECT ST_AsText(ST_PixelAsCentroid(rast, 1, 1)) FROM dummy_rast WHERE rid = 1;
st_astext
--------------
POINT(1.5 2)
ST_SetValue
Returns modifified raster resulting from setting the value of a given band in a given columnx, rowy pixel or the
pixels that intersect a particular geometry. Band numbers start at 1 and assumed to be 1 if not specifified.
//语法
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);
//示例
-- 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 ;
Raster Editors
ST_SetGeoReference
Set Georeference 6 georeference parameters in a single call. Numbers should be separated by white
space. Accepts inputs in GDAL or ESRI format. Default is GDAL.
//语法
raster ST_SetGeoReference(raster rast, text georefcoords, text format=GDAL);
raster ST_SetGeoReference(raster rast, double precision upperleftx, double precision upperlefty, double precision scalex, double
precision scaley, double precision skewx, double precision skewy);
//示例
WITH foo AS (
SELECT ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0) AS rast
)
SELECT
0 AS rid, (ST_Metadata(rast)).*
FROM foo
UNION ALL
SELECT
1, (ST_Metadata(ST_SetGeoReference(rast, '10 0 0 -10 0.1 0.1', 'GDAL'))).*
FROM foo
UNION ALL
SELECT
2, (ST_Metadata(ST_SetGeoReference(rast, '10 0 0 -10 5.1 -4.9', 'ESRI'))).*
FROM foo
UNION ALL
SELECT
3, (ST_Metadata(ST_SetGeoReference(rast, 1, 1, 10, -10, 0.001, 0.001))).*
FROM foo
rid | upperleftx | upperlefty | width | height | scalex | scaley | skewx |
skewy | srid | numbands
-----+--------------------+--------------------+-------
0 | 0 | 0 | 5 | 5 | 1 | -1 | 0 |
0 | 0 | 0
1 | 0.1 | 0.1 | 5 | 5 | 10 | -10 | 0 |
0 | 0 | 0
2 | 0.0999999999999996 | 0.0999999999999996 | 5 | 5 | 10 | -10 | 0 |
0 | 0 | 0
3 | 1 | 1 | 5 | 5 | 10 | -10 | 0.001 |
0.001 | 0 | 0
ST_SetSRID
Sets the SRID of a raster to a particular integer srid defifined in the spatial_ref_sys table.
//语法
raster ST_SetSRID(raster rast, integer srid);
ST_Resize
Resize a raster to a new width/height.
//语法
raster ST_Resize(raster rast, integer width, integer height, text algorithm=NearestNeighbor, double precision maxerr=0.125);
raster ST_Resize(raster rast, double precision percentwidth, double precision percentheight, text algorithm=NearestNeighbor,
double precision maxerr=0.125);
raster ST_Resize(raster rast, text width, text height, text algorithm=NearestNeighbor, double precision maxerr=0.125);
//示例
WITH foo AS(
SELECT
1 AS rid,
ST_Resize(
ST_AddBand(
ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
, 1, '8BUI', 255, 0
)
, '50%', '500') AS rast
UNION ALL
SELECT
2 AS rid,
ST_Resize(
ST_AddBand(
ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
, 1, '8BUI', 255, 0
)
, 500, 100) AS rast
UNION ALL
SELECT
3 AS rid,
ST_Resize(
ST_AddBand(
ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
, 1, '8BUI', 255, 0
)
, 0.25, 0.9) AS rast
), bar AS (
SELECT rid, ST_Metadata(rast) AS meta, rast FROM foo
)
SELECT rid, (meta).* FROM bar
rid | upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid | numbands
-----+------------+------------+-------+--------+--
1 | 0 | 0 | 500 | 500 | 1 | -1 | 0 | 0 | 0 | 1
2 | 0 | 0 | 500 | 100 | 1 | -1 | 0 | 0 | 0 | 1
3 | 0 | 0 | 250 | 900 | 1 | -1 | 0 | 0 | 0 | 1
(3 rows)
ST_Transform
Reprojects a raster in a known spatial reference system to another known spatial reference system using spec
ifified resampling algorithm. Options are NearestNeighbor, Bilinear, Cubic, CubicSpline, Lanczos defaulting to NearestNeighbor.
//语法
raster ST_Transform(raster rast, integer srid, text algorithm=NearestNeighbor, double precision maxerr=0.125, double precision
scalex, double precision scaley);
raster ST_Transform(raster rast, integer srid, double precision scalex, double precision scaley, text algorithm=NearestNeighbor,
double precision maxerr=0.125);
raster ST_Transform(raster rast, raster alignto, text algorithm=NearestNeighbor, double precision maxerr=0.125);
//示例
SELECT ST_Width(mass_stm) As w_before, ST_Width(wgs_84) As w_after,
ST_Height(mass_stm) As h_before, ST_Height(wgs_84) As h_after
FROM
( SELECT rast As mass_stm, ST_Transform(rast,4326) As wgs_84
, ST_Transform(rast,4326, 'Bilinear') AS wgs_84_bilin
FROM aerials.o_2_boston
WHERE ST_Intersects(rast,
ST_Transform(ST_MakeEnvelope(-71.128, 42.2392,-71.1277, 42.2397, 4326),26986) )
LIMIT 1) As foo;
w_before | w_after | h_before | h_after
----------+---------+----------+---------
200 | 228 | 200 | 170
Raster Band Editors
ST_SetBandNoDataValue
Sets the value for the given band that represents no data. Band 1 is assumed if no band is specifified.
To mark a band as having no nodata value, set the nodata value = NULL.
//语法
raster ST_SetBandNoDataValue(raster rast, double precision nodatavalue);
raster ST_SetBandNoDataValue(raster rast, integer band, double precision nodatavalue, boolean forcechecking=false);
//示例
-- change just first band no data value
UPDATE dummy_rast
SET rast = ST_SetBandNoDataValue(rast,1, 254)
WHERE rid = 2;
-- change no data band value of bands 1,2,3
UPDATE dummy_rast
SET rast =
ST_SetBandNoDataValue(
ST_SetBandNoDataValue(
ST_SetBandNoDataValue(
rast,1, 254)
,2,99),
3,108)
WHERE rid = 2;
-- wipe out the nodata value this will ensure all pixels are considered for all processing ←-
functions
UPDATE dummy_rast
SET rast = ST_SetBandNoDataValue(rast,1, NULL)
WHERE rid = 2;
ST_SetBandIsNoData
Sets the isnodata flflag of the band to TRUE.
//语法
raster ST_SetBandIsNoData(raster rast, integer band=1);
//示例
-- 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)
||
'4' -- hasnodatavalue set to true, isnodata value set to false (when it should be 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 false
PostGIS 3.0.5dev Manual 548 / 841
select st_bandisnodata(rast, 1, TRUE) from dummy_rast where rid = 1; -- Expected true
-- The isnodata flag is dirty. We are going to set it to true
update dummy_rast set rast = st_setbandisnodata(rast, 1) where rid = 1;
select st_bandisnodata(rast, 1) from dummy_rast where rid = 1; -- Expected true
ST_SetBandPath
ST_SetBandPath — Update the external path and band number of an out-db band.
//语法
raster ST_SetBandPath(raster rast, integer band, text outdbpath, integer outdbindex, boolean force=false);
//示例
WITH foo AS (
SELECT
ST_AddBand(NULL::raster, '/home/pele/devel/geo/postgis-git/raster/test/regress/loader/
Projected.tif', NULL::int[]) AS rast
)
SELECT
1 AS query, *
FROM ST_BandMetadata( (SELECT rast FROM foo),
ARRAY[1,3,2]::int[]
)
UNION ALL
SELECT
2,
*
FROM ST_BandMetadata( (
SELECT
ST_SetBandPath(
rast,
2,
'/home/pele/devel/geo/postgis-git/raster/test/regress/loader/Projected2.tif',
1 ) AS rast
FROM foo
),
ARRAY[1,3,2]::int[]
)
ORDER BY 1, 2;
query | bandnum | pixeltype | nodatavalue | isoutdb |
path |
outdbbandnum
-------+---------+-----------+-------------+---------+-------------
1 | 1 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected.tif | 1
1 | 2 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected.tif | 2
1 | 3 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected.tif | 3
2 | 1 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected.tif | 1
2 | 2 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected2.tif | 1
2 | 3 | 8BUI | | t | /home/pele/devel/geo/postgis-git/
raster/test/regress/loader/Projected.tif | 3
ST_SetBandIndex
Update the external band number of an out-db band.
//语法
raster ST_SetBandIndex(raster rast, integer band, integer outdbindex, boolean force=false);
//示例
WITH foo AS (
SELECT
ST_AddBand(NULL::raster, '/home/pele/devel/geo/postgis-git/raster/test/regress/loader/ ←-
Projected.tif', NULL::int[]) AS rast
)
SELECT
1 AS query, *
FROM ST_BandMetadata( (SELECT rast FROM foo),
ARRAY[1,3,2]::int[]
)
UNION ALL
SELECT
2,
*
FROM ST_BandMetadata( (
SELECT
ST_SetBandIndex(
rast,
2,
1 ) AS rast
FROM foo
),
ARRAY[1,3,2]::int[]
)
ORDER BY 1, 2;
query | bandnum | pixeltype | nodatavalue | isoutdb | ←-
path | ←-
outdbbandnum
-------+---------+-----------+-------------+---------+---------------------------------------------------------------------------------+-------------- ←-
1 | 1 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 1
1 | 2 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 2
1 | 3 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 3
2 | 1 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 1
2 | 2 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 1
2 | 3 | 8BUI | | t | /home/pele/devel/geo/postgis-git/ ←-
raster/test/regress/loader/Projected.tif | 3
Raster Band Statistics and Analytics
ST_Count
Returns the number of pixels in a given band of a raster or raster coverage. If no band is specifified defaults to band
\1. If exclude_nodata_value is set to true, will only count pixels that are not equal to the nodata value.
//语法
bigint ST_Count(raster rast, integer nband=1, boolean exclude_nodata_value=true);
bigint ST_Count(raster rast, boolean exclude_nodata_value);
bigint ST_Count(text rastertable, text rastercolumn, integer nband=1, boolean exclude_nodata_value=true);
bigint ST_Count(text rastertable, text rastercolumn, boolean exclude_nodata_value);
//示例
--example will count all pixels not 249 and one will count all pixels. --
SELECT rid, ST_Count(ST_SetBandNoDataValue(rast,249)) As exclude_nodata,
ST_Count(ST_SetBandNoDataValue(rast,249),false) As include_nodata
FROM dummy_rast WHERE rid=2;
rid | exclude_nodata | include_nodata
-----+----------------+----------------
2 | 23 | 25
ST_SummaryStats
Returns summarystats consisting of count, sum, mean, stddev, min, max for a given raster band of a raster
or raster coverage. Band 1 is assumed is no band is specifified.
//语法
summarystats ST_SummaryStats(raster rast, boolean exclude_nodata_value);
summarystats ST_SummaryStats(raster rast, integer nband, boolean exclude_nodata_value);
summarystats ST_SummaryStats(text rastertable, text rastercolumn, boolean exclude_nodata_value);
summarystats ST_SummaryStats(text rastertable, text rastercolumn, integer nband=1, boolean exclude_nodata_value=true);
//示例
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
Raster Inputs
ST_RastFromWKB
Return a raster value from a Well-Known Binary (WKB) raster.
//语法
raster ST_RastFromWKB(bytea wkb);
//示例
SELECT (ST_Metadata(
ST_RastFromWKB(
'\001\000\000\000\000\000\000\000\000\000\000\000@\000\000\000\000\000\000\010@ ←-
\000\000\000\000\000\000\340?\000\000\000\000\000\000\340?\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\012\000\000\000\012\000\024\000':: bytea
)
)).* AS metadata;
upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid |
numbands
------------+------------+-------+--------+--------+--
0.5 | 0.5 | 10 | 20 | 2 | 3 | 0 | 0 | 10 | 0
ST_RastFromHexWKB
Return a raster value from a Hex representation of Well-Known Binary (WKB) raster.
//语法
raster ST_RastFromHexWKB(text wkb);
//示例
SELECT (ST_Metadata(
ST_RastFromHexWKB(
'010000000000000000000000400000000000000840000000000000
E03F000000000000E03F000000000000000000000000000000000A0000000A001400' )
)).* AS metadata;
upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid | ←-
numbands
------------+------------+-------+--------+---
0.5 | 0.5 | 10 | 20 | 2 | 3 | 0 | 0 | 10 | 0
Raster Outputs
ST_AsBinary/ST_AsWKB
Return the Well-Known Binary (WKB) representation of the raster.
//语法
bytea ST_AsBinary(raster rast, boolean outasin=FALSE);
bytea ST_AsWKB(raster rast, boolean outasin=FALSE);
//示例
SELECT ST_AsBinary(rast) As rastbin FROM dummy_rast WHERE rid=1;
rastbin
---------------------------------------------------------------------------------
\001\000\000\000\000\000\000\000\000\000\000\000@\000\000\000\000\000\000\010@
\000\000\000\000\000\000\340?\000\000\000\000\000\000\340?\000\000\000\000\000\000\000\000\000\00
ST_AsHexWKB
Return the Well-Known Binary (WKB) in Hex representation of the raster.
//语法
bytea ST_AsHexWKB(raster rast, boolean outasin=FALSE);
//示例
SELECT ST_AsHexWKB(rast) As rastbin FROM dummy_rast WHERE rid=1;
st_ashexwkb
----------------------------------
010000000000000000000000400000000000000840000000000000
E03F000000000000E03F000000000000000000000000000000000A0000000A001400
ST_AsGDALRaster
Return the raster tile in the designated GDAL Raster format. Raster formats are one of those supported
by your compiled library. Use ST_GDALDrivers() to get a list of formats supported by your library。
//语法
bytea ST_AsGDALRaster(raster rast, text format, text[] options=NULL, integer srid=sameassource);
//示例
SELECT ST_AsGDALRaster(ST_Union(rast), 'JPEG', ARRAY['QUALITY=50']) As rastjpg
FROM dummy_rast
WHERE rast && ST_MakeEnvelope(10, 10, 11, 11);
ST_AsJPEG
Return the raster tile selected bands as a single Joint Photographic Exports Group (JPEG) image (byte array). If
no band is specifified and 1 or more than 3 bands, then only the fifirst band is used. If only 3 bands then all 3 bands are used and mapped to RGB.
//语法
bytea ST_AsJPEG(raster rast, text[] options=NULL);
bytea ST_AsJPEG(raster rast, integer nband, integer quality);
bytea ST_AsJPEG(raster rast, integer nband, text[] options=NULL);
bytea ST_AsJPEG(raster rast, integer[] nbands, text[] options=NULL);
bytea ST_AsJPEG(raster rast, integer[] nbands, integer quality);
//示例
-- output first 3 bands 75% quality
SELECT ST_AsJPEG(rast) As rastjpg
FROM dummy_rast WHERE rid=2;
-- output only first band as 90% quality
SELECT ST_AsJPEG(rast,1,90) As rastjpg
FROM dummy_rast WHERE rid=2;
-- output first 3 bands (but make band 2 Red, band 1 green, and band 3 blue, progressive ←-
and 90% quality
SELECT ST_AsJPEG(rast,ARRAY[2,1,3],ARRAY['QUALITY=90','PROGRESSIVE=ON']) As rastjpg
FROM dummy_rast WHERE rid=2;
ST_AsPNG
Return the raster tile selected bands as a single portable network graphics (PNG) image (byte array). If 1, 3, or 4
bands in raster and no bands are specifified, then all bands are used. If more 2 or more than 4 bands and no bands specifified, then only band 1 is used. Bands are mapped to RGB or RGBA space.
//语法
bytea ST_AsPNG(raster rast, text[] options=NULL);
bytea ST_AsPNG(raster rast, integer nband, integer compression);
bytea ST_AsPNG(raster rast, integer nband, text[] options=NULL);
bytea ST_AsPNG(raster rast, integer[] nbands, integer compression);
bytea ST_AsPNG(raster rast, integer[] nbands, text[] options=NULL);
//示例
SELECT ST_AsPNG(rast) As rastpng
FROM dummy_rast WHERE rid=2;
-- export the first 3 bands and map band 3 to Red, band 1 to Green, band 2 to blue
SELECT ST_AsPNG(rast, ARRAY[3,1,2]) As rastpng
FROM dummy_rast WHERE rid=2;
ST_AsTIFF
Return the raster selected bands as a single TIFF image (byte array). If no band is specifified or any of specifified
bands does not exist in the raster, then will try to use all bands.
//语法
bytea ST_AsTIFF(raster rast, text[] options=”, integer srid=sameassource);
bytea ST_AsTIFF(raster rast, text compression=”, integer srid=sameassource);
bytea ST_AsTIFF(raster rast, integer[] nbands, text compression=”, integer srid=sameassource);
bytea ST_AsTIFF(raster rast, integer[] nbands, text[] options, integer srid=sameassource);
//示例
SELECT ST_AsTIFF(rast, 'JPEG90') As rasttiff
FROM dummy_rast WHERE rid=2;
Raster Processing-Map Algebra（栅格代数函数）
ST_Clip
Returns the raster clipped by the input geometry. If band number not is specifified, all bands are processed. If crop
is not specifified or TRUE, the output raster is cropped.
//语法
raster ST_Clip(raster rast, integer[] nband, geometry geom, double precision[] nodataval=NULL, boolean crop=TRUE);
raster ST_Clip(raster rast, integer nband, geometry geom, double precision nodataval, boolean crop=TRUE);
raster ST_Clip(raster rast, integer nband, geometry geom, boolean crop);
raster ST_Clip(raster rast, geometry geom, double precision[] nodataval=NULL, boolean crop=TRUE);
raster ST_Clip(raster rast, geometry geom, double precision nodataval, boolean crop=TRUE);
raster ST_Clip(raster rast, geometry geom, boolean crop);
//示例
-- Clip the first band of an aerial tile by a 20 meter buffer.
SELECT ST_Clip(rast, 1,
ST_Buffer(ST_Centroid(ST_Envelope(rast)),20)
) from aerials.boston
WHERE rid = 4;
ST_ColorMap
Creates a new raster of up to four 8BUI bands (grayscale, RGB, RGBA) from the source raster and a specifified
band. Band 1 is assumed if not specifified.
//语法
raster ST_ColorMap(raster rast, integer nband=1, text colormap=grayscale, text method=INTERPOLATE);
raster ST_ColorMap(raster rast, text colormap, text method=INTERPOLATE);
//示例
-- setup test raster table --
DROP TABLE IF EXISTS funky_shapes;
CREATE TABLE funky_shapes(rast raster);
INSERT INTO funky_shapes(rast)
WITH ref AS (
SELECT ST_MakeEmptyRaster( 200, 200, 0, 200, 1, -1, 0, 0) AS rast
)
SELECT
ST_Union(rast)
FROM (
SELECT
ST_AsRaster(
ST_Rotate(
ST_Buffer(
ST_GeomFromText('LINESTRING(0 2,50 50,150 150,125 50)'),
i*2
),
pi() * i * 0.125, ST_Point(50,50)
),
ref.rast, '8BUI'::text, i * 5 ) AS rast
FROM ref
CROSS JOIN generate_series(1, 10, 3) AS i ) AS shapes;
ST_Grayscale
Creates a new one-8BUI band raster from the source raster and specifified bands representing Red, Green and
Blue.
//语法
(1) raster ST_Grayscale(raster rast, integer redband=1, integer greenband=2, integer blueband=3, text extenttype=INTERSECTION);
(2) raster ST_Grayscale(rastbandarg[] rastbandargset, text extenttype=INTERSECTION);
//示例
SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
SET postgis.enable_outdb_rasters = True;
WITH apple AS (
SELECT ST_AddBand(
ST_MakeEmptyRaster(350, 246, 0, 0, 1, -1, 0, 0, 0),
'/tmp/apple.png'::text,
NULL::int[]
) AS rast
)
SELECT
ST_AsPNG(rast) AS original_png,
ST_AsPNG(ST_Grayscale(rast)) AS grayscale_png
FROM apple;
ST_Intersection
Returns a raster or a set of geometry-pixelvalue pairs representing the shared portion of two rasters or the
geometrical intersection of a vectorization of the raster and a geometry.
//语法
setof geomval ST_Intersection(geometry geom, raster rast, integer band_num=1);
setof geomval ST_Intersection(raster rast, geometry geom);
setof geomval ST_Intersection(raster rast, integer band, geometry geomin);
raster ST_Intersection(raster rast1, raster rast2, double precision[] nodataval);
raster ST_Intersection(raster rast1, raster rast2, text returnband, double precision[] nodataval);
raster ST_Intersection(raster rast1, integer band1, raster rast2, integer band2, double precision[] nodataval);
raster ST_Intersection(raster rast1, integer band1, raster rast2, integer band2, text returnband, double precision[] nodataval);
//示例
SELECT
foo.rid,
foo.gid,
ST_AsText((foo.geomval).geom) As geomwkt, (foo.geomval).val
FROM (
SELECT
A.rid, g.gid,
ST_Intersection(A.rast, g.geom) As geomval
FROM dummy_rast AS A
CROSS JOIN (
VALUES
(1, ST_Point(3427928, 5793243.85) ),
(2, ST_GeomFromText('LINESTRING(3427927.85 5793243.75,3427927.8 5793243.75,3427927.8 ←-
5793243.8)')),
(3, ST_GeomFromText('LINESTRING(1 2, 3 4)'))
) As g(gid,geom)
WHERE A.rid = 2
) As foo;
ST_Reclass
Creates a new raster composed of band types reclassifified from original. The nband is the band to be changed. If
nband is not specifified assumed to be 1. All other bands are returned unchanged. Use case: convert a 16BUI band to a 8BUI and so forth for simpler rendering as viewable formats.
//语法
raster ST_Reclass(raster rast, integer nband, text reclassexpr, text pixeltype, double precision nodataval=NULL);
raster ST_Reclass(raster rast, reclassarg[] VARIADIC reclassargset);
raster ST_Reclass(raster rast, text reclassexpr, text pixeltype);
//示例
ALTER TABLE dummy_rast ADD COLUMN reclass_rast raster;
UPDATE dummy_rast SET reclass_rast = ST_Reclass(rast,2,'0-87:1-10, 88-100:11-15,
101-254:0-0', '4BUI',0) WHERE rid = 2;
SELECT i as col, j as row, ST_Value(rast,2,i,j) As origval,
ST_Value(reclass_rast, 2, i, j) As reclassval,
ST_Value(reclass_rast, 2, i, j, false) As reclassval_include_nodata
FROM dummy_rast CROSS JOIN generate_series(1, 3) AS i CROSS JOIN generate_series(1,3) AS j
WHERE rid = 2;
col | row | origval | reclassval | reclassval_include_nodata
-----+-----+---------+------------+---------------------------
1 | 1 | 78 | 9 | 9
2 | 1 | 98 | 14 | 14
3 | 1 | 122 | | 0
1 | 2 | 96 | 14 | 14
2 | 2 | 118 | | 0
3 | 2 | 180 | | 0
1 | 3 | 99 | 15 | 15
2 | 3 | 112 | | 0
3 | 3 | 169 | | 0
Raster Processing-DEM (Elevation)
ST_Aspect
Returns the aspect (in degrees by default) of an elevation raster band. Useful for analyzing terrain.
//语法
raster ST_Aspect(raster rast, integer band=1, text pixeltype=32BF, text units=DEGREES, boolean interpolate_nodata=FALSE);
raster ST_Aspect(raster rast, integer band, raster customextent, text pixeltype=32BF, text units=DEGREES, boolean interpolate_nodata=FALSE);
//示例
WITH foo AS (
SELECT ST_SetValues(
ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '32BF', 0, -9999),
1, 1, 1, ARRAY[
[1, 1, 1, 1, 1],
[1, 2, 2, 2, 1],
[1, 2, 3, 2, 1],
[1, 2, 2, 2, 1],
[1, 1, 1, 1, 1]
]::double precision[][]
) AS rast
)
SELECT
ST_DumpValues(ST_Aspect(rast, 1, '32BF'))
FROM foo
st_dumpvalues ←-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ←-
----------------------------------
(1,"{{315,341.565063476562,0,18.4349479675293,45},{288.434936523438,315,0,45,71.5650482177734},{270,270,-1,90,90},{251.565048217773,225,180,135,108.434951782227},{225,198.43495178 ←-
2227,180,161.565048217773,135}}")
(1 row)
ST_Slope
Returns the slope (in degrees by default) of an elevation raster band. Useful for analyzing terrain.
//语法
raster ST_Slope(raster rast, integer nband=1, text pixeltype=32BF, text units=DEGREES, double precision scale=1.0, boolean
interpolate_nodata=FALSE);
raster ST_Slope(raster rast, integer nband, raster customextent, text pixeltype=32BF, text units=DEGREES, double precision
scale=1.0, boolean interpolate_nodata=FALSE);
//示例
WITH foo AS (
SELECT ST_SetValues(
ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 0), 1, '32BF', 0, -9999),
1, 1, 1, ARRAY[
[1, 1, 1, 1, 1],
[1, 2, 2, 2, 1],
[1, 2, 3, 2, 1],
[1, 2, 2, 2, 1],
[1, 1, 1, 1, 1]
]::double precision[][]
) AS rast
)
SELECT
ST_DumpValues(ST_Slope(rast, 1, '32BF'))
FROM foo
st_dumpvalues
----------------------------------------------------------------------------------------
(1,"{{10.0249881744385,21.5681285858154,26.5650520324707,21.5681285858154,10.0249881744385},{21.5681285858154,35.2643890380859,36.8698959350586,35.2643890380859,21.5681285858154}, ←-
{26.5650520324707,36.8698959350586,0,36.8698959350586,26.5650520324707},{21.5681285858154,35.2643890380859,36.8698959350586,35.2643890380859,21.5681285858154},{10.0249881744385,21. ←-
5681285858154,26.5650520324707,21.5681285858154,10.0249881744385}}")
(1 row)
Raster Processing-Raster to Geometry
ST_Polygon
Returns a multipolygon geometry formed by the union of pixels that have a pixel value that is not no data value.
If no band number is specifified, band num defaults to 1.
//语法
geometry ST_Polygon(raster rast, integer band_num=1);
//示例
-- by default no data band value is 0 or not set, so polygon will return a square polygon
SELECT ST_AsText(ST_Polygon(rast)) As geomwkt
FROM dummy_rast
WHERE rid = 2;
geomwkt
--------------------------------------------
MULTIPOLYGON(((3427927.75 5793244,3427928 5793244,3427928 5793243.75,3427927.75 ←-
5793243.75,3427927.75 5793244)))
-- now we change the no data value of first band
UPDATE dummy_rast SET rast = ST_SetBandNoDataValue(rast,1,254)
WHERE rid = 2;
SELECt rid, ST_BandNoDataValue(rast)
from dummy_rast where rid = 2;
-- ST_Polygon excludes the pixel value 254 and returns a multipolygon
SELECT ST_AsText(ST_Polygon(rast)) As geomwkt
FROM dummy_rast
WHERE rid = 2;
geomwkt
---------------------------------------------------------
MULTIPOLYGON(((3427927.9 5793243.95,3427927.85 5793243.95,3427927.85 5793244,3427927.9
5793244,3427927.9 5793243.95)),((3427928 5793243.85,3427928 5793243.8,3427927.95
5793243.8,3427927.95 5793243.85,3427927.9 5793243.85,3427927.9 5793243.9,3427927.9
5793243.95,3427927.95 5793243.95,3427928 5793243.95,3427928 5793243.85)),((3427927.8
5793243.75,3427927.75 5793243.75,3427927.75 5793243.8,3427927.75 5793243.85,3427927.75
5793243.9,3427927.75 5793244,3427927.8 5793244,3427927.8 5793243.9,3427927.8
5793243.85,3427927.85 5793243.85,3427927.85 5793243.8,3427927.85 5793243.75,3427927.8
5793243.75)))
-- Or if you want the no data value different for just one time
SELECT ST_AsText(
PostGIS 3.0.5dev Manual 627 / 841
ST_Polygon(
ST_SetBandNoDataValue(rast,1,252)
) ) As geomwkt
FROM dummy_rast
WHERE rid =2;
geomwkt
---------------------------------
MULTIPOLYGON(((3427928 5793243.85,3427928 5793243.8,3427928 5793243.75,3427927.85
5793243.75,3427927.8 5793243.75,3427927.8 5793243.8,3427927.75 5793243.8,3427927.75
5793243.85,3427927.75 5793243.9,3427927.75 5793244,3427927.8 5793244,3427927.85
5793244,3427927.9 5793244,3427928 5793244,3427928 5793243.95,3427928 5793243.85)
,(3427927.9 5793243.9,3427927.9 5793243.85,3427927.95 5793243.85,3427927.95
5793243.9,3427927.9 5793243.9)))
Box3D
Returns the box 3d representation of the enclosing box of the raster.
//语法
box3d Box3D(raster rast);
//示例
SELECT
rid,
Box3D(rast) AS rastbox
FROM dummy_rast;
rid | rastbox
----+---------------------------------------
1 | BOX3D(0.5 0.5 0,20.5 60.5 0)
2 | BOX3D(3427927.75 5793243.5 0,3427928 5793244 0)
ST_DumpAsPolygons
Returns a set of geomval (geom,val) rows, from a given raster band. If no band number is specifified,
band num defaults to 1.
//语法
setof geomval ST_DumpAsPolygons(raster rast, integer band_num=1, boolean exclude_nodata_value=TRUE)
//示例
-- this syntax requires PostgreSQL 9.3+
SELECT val, ST_AsText(geom) As geomwkt
FROM (
SELECT dp.*
FROM dummy_rast, LATERAL ST_DumpAsPolygons(rast) AS dp
WHERE rid = 2
) As foo
WHERE val BETWEEN 249 and 251
ORDER BY val;
val | geomwkt
-----+--------------------------------------------------------------------------
249 | POLYGON((3427927.95 5793243.95,3427927.95 5793243.85,3427928 5793243.85,
3427928 5793243.95,3427927.95 5793243.95))
250 | POLYGON((3427927.75 5793243.9,3427927.75 5793243.85,3427927.8 5793243.85,
3427927.8 5793243.9,3427927.75 5793243.9))
250 | POLYGON((3427927.8 5793243.8,3427927.8 5793243.75,3427927.85 5793243.75,
3427927.85 5793243.8, 3427927.8 5793243.8))
251 | POLYGON((3427927.75 5793243.85,3427927.75 5793243.8,3427927.8 5793243.8,
3427927.8 5793243.85,3427927.75 5793243.85))
Raster Processing-Raster Operators
&&
Returns TRUE if A’s bounding box intersects B’s bounding box.
//语法
boolean &&( raster A , raster B );
boolean &&( raster A , geometry B );
boolean &&( geometry B , raster A );
//示例
SELECT A.rid As a_rid, B.rid As b_rid, A.rast && B.rast As intersect
FROM dummy_rast AS A CROSS JOIN dummy_rast AS B LIMIT 3;
a_rid | b_rid | intersect
-------+-------+---------
2 | 2 | t
2 | 3 | f
2 | 1 | f
&<
Returns TRUE if A’s bounding box is to the left of B’s.
//语法
boolean &<( raster A , raster B );
//示例
SELECT A.rid As a_rid, B.rid As b_rid, A.rast &< B.rast As overleft
FROM dummy_rast AS A CROSS JOIN dummy_rast AS B;
a_rid | b_rid | overleft
------+-------+----------
2 | 2 | t
2 | 3 | f
2 | 1 | f
3 | 2 | t
3 | 3 | t
3 | 1 | f
1 | 2 | t
1 | 3 | t
1 | 1 | t
&>
Returns TRUE if A’s bounding box is to the right of B’s.
//语法
boolean &>( raster A , raster B );
//示例
SELECT A.rid As a_rid, B.rid As b_rid, A.rast &> B.rast As overright
FROM dummy_rast AS A CROSS JOIN dummy_rast AS B;
a_rid | b_rid | overright
-------+-------+----------
2 | 2 | t
2 | 3 | t
2 | 1 | t
3 | 2 | f
3 | 3 | t
3 | 1 | f
1 | 2 | f
1 | 3 | t
1 | 1 | t
=
Returns TRUE if A’s bounding box is the same as B’s. Uses double precision bounding box.
//语法
boolean =( raster A , raster B );
@
Returns TRUE if A’s bounding box is contained by B’s. Uses double precision bounding box.
//语法
boolean @( raster A , raster B );
boolean @( geometry A , raster B );
boolean @( raster B , geometry A );
//示例
SELECT A.rid As a_rid, B.rid As b_rid, A.rast && B.rast As intersect
FROM dummy_rast AS A CROSS JOIN dummy_rast AS B LIMIT 3;
a_rid | b_rid | intersect
-------+-------+---------
2 | 2 | t
2 | 3 | f
2 | 1 | f
~=
Returns TRUE if A’s bounding box is the same as B’s.
//语法
boolean ~=( raster A , raster B );
//示例
SELECT ST_AddBand(prec.rast, alt.rast) As new_rast
FROM prec INNER JOIN alt ON (prec.rast ~= alt.rast);
~
Returns TRUE if A’s bounding box is contains B’s. Uses double precision bounding box.
//语法
boolean ~( raster A , raster B );
boolean ~( geometry A , raster B );
boolean ~( raster B , geometry A );
//示例
SELECT A.rid As a_rid, B.rid As b_rid, A.rast && B.rast As intersect
FROM dummy_rast AS A CROSS JOIN dummy_rast AS B LIMIT 3;
a_rid | b_rid | intersect
-------+-------+---------
2 | 2 | t
2 | 3 | f
2 | 1 | f
Raster and Raster Band Spatial Relationships
ST_Contains
Return true if no points of raster rastB lie in the exterior of raster rastA and at least one point of the interior of
rastB lies in the interior of rastA.
//语法
boolean ST_Contains( raster rastA , integer nbandA , raster rastB , integer nbandB );
boolean ST_Contains( raster rastA , raster rastB );
//示例
-- specified band numbers
SELECT r1.rid, r2.rid, ST_Contains(r1.rast, 1, r2.rast, 1) FROM dummy_rast r1 CROSS JOIN
dummy_rast r2 WHERE r1.rid = 1;
NOTICE: The first raster provided has no bands
rid | rid | st_contains
-----+-----+-------------
1 | 1 |
1 | 2 | f
ST_Covers
Return true if no points of raster rastB lie outside raster rastA.
//语法
boolean ST_Covers( raster rastA , integer nbandA , raster rastB , integer nbandB );
boolean ST_Covers( raster rastA , raster rastB );
//示例
SELECT r1.rid, r2.rid, ST_Covers(r1.rast, 1, r2.rast, 1) FROM dummy_rast r1 CROSS JOIN ←-
dummy_rast r2 WHERE r1.rid = 2;
rid | rid | st_covers
-----+-----+-----------
2 | 1 | f
2 | 2 | t
ST_Intersects
Return true if raster rastA spatially intersects raster rastB.
//语法
boolean ST_Intersects( raster rastA , integer nbandA , raster rastB , integer nbandB );
boolean ST_Intersects( raster rastA , raster rastB );
boolean ST_Intersects( raster rast , integer nband , geometry geommin );
boolean ST_Intersects( raster rast , geometry geommin , integer nband=NULL );
boolean ST_Intersects( geometry geommin , raster rast , integer nband=NULL );
//示例
-- different bands of same raster
SELECT ST_Intersects(rast, 2, rast, 3) FROM dummy_rast WHERE rid = 2;
st_intersects
---------------
t
ST_Within
Return true if no points of raster rastA lie in the exterior of raster rastB and at least one point of the interior of
rastA lies in the interior of rastB.
//语法
boolean ST_Within( raster rastA , integer nbandA , raster rastB , integer nbandB );
boolean ST_Within( raster rastA , raster rastB );
//示例
SELECT r1.rid, r2.rid, ST_Within(r1.rast, 1, r2.rast, 1) FROM dummy_rast r1 CROSS JOIN ←-
dummy_rast r2 WHERE r1.rid = 2;
rid | rid | st_within
-----+-----+-----------
2 | 1 | f
2 | 2 | t
Topology Types
getfaceedges_returntype、TopoGeometry、validatetopology_returntype
Topology Constructors
CreateTopology
Creates a new topology schema and registers this new schema in the topology.topology table.
//语法
integer CreateTopology(varchar topology_schema_name);
integer CreateTopology(varchar topology_schema_name, integer srid);
integer CreateTopology(varchar topology_schema_name, integer srid, double precision prec);
integer CreateTopology(varchar topology_schema_name, integer srid, double precision prec, boolean hasz);
//示例
SELECT topology.CreateTopology('ri_topo',3438) As topoid;
topoid
------
2
ST_CreateTopoGeo
Adds a collection of geometries to a given empty topology and returns a message detailing success.
//语法
text ST_CreateTopoGeo(varchar atopology, geometry acollection);
//示例
-- Populate topology --
SELECT topology.ST_CreateTopoGeo('ri_topo',
ST_GeomFromText('MULTILINESTRING((384744 236928,384750 236923,384769 236911,384799 ←-
236895,384811 236890,384833 236884,
384844 236882,384866 236881,384879 236883,384954 236898,385087 236932,385117 236938,
385167 236938,385203 236941,385224 236946,385233 236950,385241 236956,385254 236971,
385260 236979,385268 236999,385273 237018,385273 237037,385271 237047,385267 237057,
385225 237125,385210 237144,385192 237161,385167 237192,385162 237202,385159 237214,
385159 237227,385162 237241,385166 237256,385196 237324,385209 237345,385234 237375,
385237 237383,385238 237399,385236 237407,385227 237419,385213 237430,385193 237439,
385174 237451,385170 237455,385169 237460,385171 237475,385181 237503,385190 237521,
385200 237533,385206 237538,385213 237541,385221 237542,385235 237540,385242 237541,
385249 237544,385260 237555,385270 237570,385289 237584,385292 237589,385291 ←-
237596,385284 237630))',3438)
);
st_createtopogeo
----------------------------
Topology ri_topo populated
-- create tables and topo geometries --
CREATE TABLE ri.roads(gid serial PRIMARY KEY, road_name text);
SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE');
TopoGeo_AddPolygon
Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces.
Returns face identififiers.
//语法
SETOF integer TopoGeo_AddPolygon(varchar atopology, geometry apoly, float8 tolerance);
Topology Editors
ST_AddIsoNode
Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the
node is still created.
//语法
integer ST_AddIsoNode(varchar atopology, integer aface, geometry apoint);
ST_AddIsoEdge
Adds an isolated edge defifined by geometry alinestring to a topology connecting two existing isolated
nodes anode and anothernode and returns the edge id of the new edge.
//语法
integer ST_AddIsoEdge(varchar atopology, integer anode, integer anothernode, geometry alinestring);
ST_AddEdgeNewFaces
Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new
faces.
//语法
integer ST_AddEdgeNewFaces(varchar atopology, integer anode, integer anothernode, geometry acurve);
ST_AddEdgeModFace
Add a new edge and, if in doing so it splits a face, modify the original face and add a new face.
//语法
integer ST_AddEdgeModFace(varchar atopology, integer anode, integer anothernode, geometry acurve);
Topology Accessors
GetEdgeByPoint
Finds the edge-id of an edge that intersects a given point.
//语法
integer GetEdgeByPoint(varchar atopology, geometry apoint, float8 tol1);
//示例
SELECT topology.GetEdgeByPoint('ma_topo',geom, 1) As with1mtol, topology.GetEdgeByPoint(' ←-
ma_topo',geom,0) As withnotol
FROM ST_GeomFromEWKT('SRID=26986;POINT(227622.6 893843)') As geom;
with1mtol | withnotol
-----------+-----------
2 | 0
GetFaceByPoint
Finds the face-id of a face that intersects a given point.
//语法
integer GetFaceByPoint(varchar atopology, geometry apoint, float8 tol1);
//示例
SELECT topology.GetFaceByPoint('ma_topo',geom, 10) As with1mtol, topology.GetFaceByPoint(' ←-
ma_topo',geom,0) As withnotol
FROM ST_GeomFromEWKT('POINT(234604.6 899382.0)') As geom;
with1mtol | withnotol
-----------+-----------
1 | 0
GetNodeByPoint
Finds the node-id of a node at a point location.
//语法
integer GetNodeByPoint(varchar atopology, geometry apoint, float8 tol1);
//示例
SELECT topology.GetNodeByPoint('ma_topo',geom, 1) As nearnode
FROM ST_GeomFromEWKT('SRID=26986;POINT(227591.9 893900.4)') As geom;
nearnode
----------
2
GetTopologyID
Returns the id of a topology in the topology.topology table given the name of the topology.
//语法
integer GetTopologyID(varchar toponame);
//示例
SELECT topology.GetTopologyID('ma_topo') As topo_id;
topo_id
---------
1
Topology Processing
Polygonize
Finds and registers all faces defifined by topology edges.
//语法
text Polygonize(varchar toponame);
AddNode
Adds a point node to the node table in the specifified topology schema and returns the nodeid of new node. If point
already exists as node, the existing nodeid is returned.
//语法
integer AddNode(varchar toponame, geometry apoint, boolean allowEdgeSplitting=false, boolean computeContainingFace=false);
//示例
SELECT topology.AddNode('ma_topo', ST_GeomFromText('POINT(227641.6 893816.5)', 26986) ) As
nodeid;
-- result --
nodeid
--------
4
AddEdge
Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specifified topology schema using the specifified linestring geometry and returns the edgeid of the new (or existing) edge.
//语法
integer AddEdge(varchar toponame, geometry aline)
AddFace
Registers a face primitive to a topology and gets its identififier.
//语法
integer AddFace(varchar toponame, geometry apolygon, boolean force_new=false);
//示例
-- first add the edges we use generate_series as an iterator (the below
-- will only work for polygons with < 10000 points because of our max in gs)
SELECT topology.AddEdge('ma_topo', ST_MakeLine(ST_PointN(geom,i), ST_PointN(geom, i + 1) )) ←-
As edgeid
FROM (SELECT ST_NPoints(geom) AS npt, geom
FROM
(SELECT ST_Boundary(ST_GeomFromText('POLYGON((234896.5 899456.7,234914
899436.4,234946.6 899356.9,234872.5 899328.7,
234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4,
234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As geom
) As geoms) As facen CROSS JOIN generate_series(1,10000) As i
WHERE i < npt;
-- result --
edgeid
--------
3456789
10
11
12
(10 rows)
-- then add the face -
SELECT topology.AddFace('ma_topo',
ST_GeomFromText('POLYGON((234896.5 899456.7,234914 899436.4,234946.6 899356.9,234872.5
899328.7,
234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4,
234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As faceid;
-- result --
faceid
--------
1
TopoGeometry Constructors
CreateTopoGeom
Creates a new topo geometry object from topo element array - tg_type: 1:[multi]point, 2:[multi]line,
3:[multi]poly, 4:collection.
//语法
topogeometry CreateTopoGeom(varchar toponame, integer tg_type, integer layer_id, topoelementarray tg_objs);
topogeometry CreateTopoGeom(varchar toponame, integer tg_type, integer layer_id);
//示例
INSERT INTO ri.ri_roads(road_name, topo) VALUES('Unknown', topology.CreateTopoGeom('ri_topo ←-
',2,2,'{{1,2}}'::topology.topoelementarray);
toTopoGeom
Converts a simple Geometry into a topo geometry.
//语法
topogeometry toTopoGeom(geometry geom, varchar toponame, integer layer_id, float8 tolerance);
topogeometry toTopoGeom(geometry geom, topogeometry topogeom, float8 tolerance)
//示例
-- creates topology not allowing any tolerance
SELECT topology.CreateTopology('topo_boston_test', 2249);
-- create a new table
CREATE TABLE nei_topo(gid serial primary key, nei varchar(30));
--add a topogeometry column to it
SELECT topology.AddTopoGeometryColumn('topo_boston_test', 'public', 'nei_topo', 'topo', ' ←-
MULTIPOLYGON') As new_layer_id;
new_layer_id
-----------
1
--use new layer id in populating the new topogeometry column
-- we add the topogeoms to the new layer with 0 tolerance
INSERT INTO nei_topo(nei, topo)
SELECT nei, topology.toTopoGeom(geom, 'topo_boston_test', 1)
FROM neighborhoods
WHERE gid BETWEEN 1 and 15;
--use to verify what has happened --
SELECT * FROM
topology.TopologySummary('topo_boston_test');
-- summary--
Topology topo_boston_test (5), SRID 2249, precision 0
61 nodes, 87 edges, 35 faces, 15 topogeoms in 1 layers
Layer 1, type Polygonal (3), 15 topogeoms
Deploy: public.nei_topo.topo
-- Shrink all TopoGeometry polygons by 10 meters
UPDATE nei_topo SET topo = ST_Buffer(clearTopoGeom(topo), -10);
-- Get the no-one-lands left by the above operation
-- I think GRASS calls this "polygon0 layer"
SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id)
FROM topo_boston_test.face f
WHERE f.face_id > 0 -- don't consider the universe face
AND NOT EXISTS ( -- check that no TopoGeometry references the face
SELECT * FROM topo_boston_test.relation
WHERE layer_id = 1 AND element_id = f.face_id
);
TopoElementArray_Agg
Returns a topoelementarray for a set of element_id, type arrays (topoelements).
//语法
topoelementarray TopoElementArray_Agg(topoelement set tefield)
//示例
SELECT topology.TopoElementArray_Agg(ARRAY[e,t]) As tea
FROM generate_series(1,3) As e CROSS JOIN generate_series(1,4) As t;
tea
--------------------------------------------------------------------------
{{1,1},{1,2},{1,3},{1,4},{2,1},{2,2},{2,3},{2,4},{3,1},{3,2},{3,3},{3,4}}
TopoGeometry Editors
clearTopoGeom
Clears the content of a topo geometry.
//语法
topogeometry clearTopoGeom(topogeometry topogeom);
//示例
-- Shrink all TopoGeometry polygons by 10 meters
UPDATE nei_topo SET topo = ST_Buffer(clearTopoGeom(topo), -10);
TopoGeom_addElement
Adds an element to the defifinition of a TopoGeometry.
//语法
topogeometry TopoGeom_addElement(topogeometry tg, topoelement el);
//示例
-- Add edge 5 to TopoGeometry tg
UPDATE mylayer SET tg = TopoGeom_addElement(tg, '{5,2}');
TopoGeom_remElement
Removes an element from the defifinition of a TopoGeometry.
//语法
topogeometry TopoGeom_remElement(topogeometry tg, topoelement el);
//示例
-- Remove face 43 from TopoGeometry tg
UPDATE mylayer SET tg = TopoGeom_remElement(tg, '{43,3}');
toTopoGeom
Adds a geometry shape to an existing topo geometry.
TopoGeometry Accessors
GetTopoGeomElementArray
Returns a topoelementarray (an array of topoelements) containing the topological elements and type of the given TopoGeometry (primitive elements).
//语法
topoelementarray GetTopoGeomElementArray(varchar toponame, integer layer_id, integer tg_id);
topoelementarray topoelement GetTopoGeomElementArray(topogeometry tg);
GetTopoGeomElements
Returns a set of topoelement objects containing the topological element_id,element_type of the
given TopoGeometry (primitive elements).
//语法
setof topoelement GetTopoGeomElements(varchar toponame, integer layer_id, integer tg_id);
setof topoelement GetTopoGeomElements(topogeometry tg);
TopoGeometry Outputs
AsGML
Returns the GML representation of a topogeometry.
//语法
text AsGML(topogeometry tg);
text AsGML(topogeometry tg, text nsprefix_in);
text AsGML(topogeometry tg, regclass visitedTable);
text AsGML(topogeometry tg, regclass visitedTable, text nsprefix);
text AsGML(topogeometry tg, text nsprefix_in, integer precision, integer options);
text AsGML(topogeometry tg, text nsprefix_in, integer precision, integer options, regclass visitedTable);
text AsGML(topogeometry tg, text nsprefix_in, integer precision, integer options, regclass visitedTable, text idprefix);
text AsGML(topogeometry tg, text nsprefix_in, integer precision, integer options, regclass visitedTable, text idprefix, int gmlversion);
//示例
CREATE TABLE visited (
element_type integer, element_id integer,
unique(element_type, element_id)
);
AsTopoJSON
Returns the TopoJSON representation of a topogeometry.
//语法
text AsTopoJSON(topogeometry tg, regclass edgeMapTable);
//示例
CREATE TEMP TABLE edgemap(arc_id serial, edge_id int unique);
-- header
SELECT '{ "type": "Topology", "transform": { "scale": [1,1], "translate": [0,0] }, "objects ←-
": {'
-- objects
UNION ALL SELECT '"' || feature_name || '": ' || AsTopoJSON(feature, 'edgemap')
FROM features.big_parcels WHERE feature_name = 'P3P4';
-- arcs
PostGIS 3.0.5dev Manual 691 / 841
WITH edges AS (
SELECT m.arc_id, e.geom FROM edgemap m, city_data.edge e
WHERE e.edge_id = m.edge_id
), points AS (
SELECT arc_id, (st_dumppoints(geom)).* FROM edges
), compare AS (
SELECT p2.arc_id,
CASE WHEN p1.path IS NULL THEN p2.geom
ELSE ST_Translate(p2.geom, -ST_X(p1.geom), -ST_Y(p1.geom))
END AS geom
FROM points p2 LEFT OUTER JOIN points p1
ON ( p1.arc_id = p2.arc_id AND p2.path[1] = p1.path[1]+1 )
ORDER BY arc_id, p2.path
), arcsdump AS (
SELECT arc_id, (regexp_matches( ST_AsGeoJSON(geom), '\[.*\]'))[1] as t
FROM compare
), arcs AS (
SELECT arc_id, '[' || array_to_string(array_agg(t), ',') || ']' as a FROM arcsdump
GROUP BY arc_id
ORDER BY arc_id
)
SELECT '}, "arcs": [' UNION ALL
SELECT array_to_string(array_agg(a), E',\n') from arcs
-- footer
UNION ALL SELECT ']}'::text as t;
-- Result:
{ "type": "Topology", "transform": { "scale": [1,1], "translate": [0,0] }, "objects": {
"P3P4": { "type": "MultiPolygon", "arcs": [[[-1]],[[6,5,-5,-4,-3,1]]]}
}, "arcs": [
[[25,30],[6,0],[0,10],[-14,0],[0,-10],[8,0]],
[[35,6],[0,8]],
[[35,6],[12,0]],
[[47,6],[0,8]],
[[47,14],[0,8]],
[[35,22],[12,0]],
[[35,14],[0,8]]
]}
Topology Spatial Relationships
Equals
Returns true if two topogeometries are composed of the same topology primitives.
//语法
boolean Equals(topogeometry tg1, topogeometry tg2);
Intersects
Returns true if any pair of primitives from the two topogeometries intersect.
//语法
boolean Intersects(topogeometry tg1, topogeometry tg2);
使用技巧
- 查看本机安装的PostGIS版本；
SELECT PostGIS_full_version();
- Note
一旦安装了postgis，它需要在每个你想要使用它的数据库中启用，也就是使用如下命令进行安装扩展：
CREATE EXTENSION postgis;
或者在PGAdmin4里面进行安装；
