**PostGIS Raster Working Specifications for PostGIS 2.0**

**Quick Links**

**Objective 2.0.01 - Being able to get and set the rotation of a raster**

**ST_SetRotation(raster, angle)**

Set the rotation of the raster. This method actually derive values for pixelsizex, pixelsizey, skewx and skewy based on the provided rotation angle.

See discussion in http://trac.osgeo.org/postgis/ticket/482

Open Question:PR: The angle should be provided in radian or in degree?

**ST_Rotation(raster) -> float64**

Return the georeference's rotation angle in (degree or radiant?) derived from the pixel size and the skew.

PR: I think getting the rotation get no sence since the result of pixelsizes and skew is not necessarily a rotation. It make sence to set it though.

**Objective 2.0.02 - Being able to create a raster as the expression of another raster**

**ST_MapAlgebra**

Returns a one band raster which pixel values are the mathematical expression of another raster band.

The expression is any PostgreSQL valid expression returning a number. This expression can contain conditional expressions like CASE or any user-defined function. In the one raster version of ST_MapAlgebra, the value of the current pixel is expressed in the expression by "rast". E.g. 'cos(rast)' or 'rast / 2'

An extra nodata value expression, applying only to nodata values pixel, can be provided.

It is not possible yet to refer to neighbour pixel values.

In the one raster version of ST_MapAlgebra, the resulting raster conserve the size, the georeference, the alignment and the SRID of the provided raster.

The pixel type of the resulting raster can be specified as '1BB', '2BUI', '4BUI', '8BSI', '8BUI', '16BSI', '16BUI', '32BSI', '32BUI', '32BF' or '64BF'. If the expression results in a value out of the range of the specified pixel type, it is truncated.

Variants

The first series of variant allow specifying the number of the band used in the expression.

1) ST_MapAlgebra(rast raster, band integer, expression text, nodatavalueexpr text, pixeltype text)

2) ST_MapAlgebra(rast raster, band integer, expression text, nodatavalueexpr text)

3) ST_MapAlgebra(rast raster, band integer, expression text, pixeltype text)

4) ST_MapAlgebra(rast raster, band integer, expression text)

The second series of variant default the band to 1.

5) ST_MapAlgebra(rast raster, expression text, nodatavalueexpr text, pixeltype text)

6) ST_MapAlgebra(rast raster, expression text, nodatavalueexpr text)

7) ST_MapAlgebra(rast raster, expression text, pixeltype text)

8) ST_MapAlgebra(rast raster, expression text)

Implementation details

Only the first variant should be implemented in C. Others are PL/pgSQL variants. The C implementation should follow the PL/pgSQL script of the one raster version of ST_MapAlgebra implemented at the top of http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_mapalgebra.sql

Optimizations should be implemented in many situations:

-When the raster is only no data values (tested with ST_BandIsNoDataValue() to be implemented)

-When the expression resume to the provided raster 'rast'.

Specifications for the two rasters version of ST_MapAlgebra are descibed in Objective 2.0.03.

**Objective 2.0.05 - Being able to clip a raster.**

**ST_Clip**

Returns the subset of a raster as a raster.

All metadata are copied from the source raster (except ulx, uly, width and height which must be computed).

Variants

1) ST_Clip(raster, ulx float8, uly float8, width int, height int, nodata float8 DEFAULT null) -> raster

2) ST_Clip(raster, band, ulx float8, uly float8, width int, height int, nodata float8 DEFAULT null) -> raster

3) ST_Clip(raster, geometry, nodata float8 DEFAULT null, trim boolean DEFAULT false) -> raster

4) ST_Clip(raster, band, geometry, nodata float8 DEFAULT null, trim boolean DEFAULT false) -> raster

Variant 1 and 2 take the upper left corner, the width and the height of the desired extent.

Variant 3 and 4 set every pixels outside the provided geometry to nodata. The 'trim' parameter determines if the resulting raster extent should be the one of the original raster or the one of the intersection between the raster and the geometry, thus trimming nodata values as much as possible.

Variants 2 and 4 returns only the selected band in the clipped raster. Variant 1 and 3 returns all bands.

When no nodata value is provided, the resulting raster keeps its nodata value. If the raster does not have a nodata value, the minimum possible value for the pixeltype of each band is used as nodata value.

If the geometry is totally included into one pixel (a point for example), only this pixel is kept in the returned raster.

Implementation details

All variants are implemented as a wrapper around ST_MapAlgebra(raster, ST_AsRaster(geometry)).

This function is useful to optimize ST_Intersection(raster, geometry). The raster to be polygonised before proceeding to a vector intersection should first be clipped to the minimal intersecting area using ST_Clip().

**Objective 2.0.06 - Being able to add a band to a raster**

**ST_AddBand**

Add a band to a raster. The new band can be filled with nodata values or comes from another raster. The index where to insert the new band, the pixel type, the initial value and the nodata value can all be specified.

When the index is not specified, the raster is added as the last band of the raster.

If no nodata value is provided the added band has no nodata value.

If no initial value is provided the initial value is 0.

Variants

The first series of variant add the new band at the specified index.

1) ST_AddBand(rast raster, index int, pixeltype text, initialvalue float8, nodataval float8)

2) ST_AddBand(rast raster, index int, pixeltype text, initialvalue float8)

3) ST_AddBand(rast raster, index int, pixeltype text)

The second series of variant add the new band as the last band.

4) ST_AddBand(rast raster, pixeltype text, initialvalue float8, nodataval float8)

5) ST_AddBand(rast raster, pixeltype text, initialvalue float8)

6) ST_AddBand(rast raster, pixeltype text)

The third series of variant add a band as the copy of another raster band.

7) ST_AddBand(rast1 raster, rast2 raster, band int, index int)

8) ST_AddBand(rast1 raster, rast2 raster, band int)

9) ST_AddBand(rast1 raster, rast2 raster)

Variant 8 and 9 add the rast2 band as the last band of rast1.

Implementation details

Only variant 1 should be implemented as a C functions. Others variants are PL/pgSQL variants.

Variant 7 is useful to optimize ST_MapAlgebra.

**Objective FV.01 - Being able to return a JPEG, a TIFF, a PNG or any image format supported by GDAL - ***Done*

*Done*

**ST_bytea(raster, integer) -> raster** -- the integer parameters is the band number of the raster.

What is does?

**Open Question:** When exporting a multiband raster to JPEG, TIFF, PNG, SVG or KML, how should we specify the band number in the exporting function.

~~There is two options to select the band to convert from a multiband raster in all the ST_AsFormat functions. ~~

~~ 1. Precede each call with ST_Band() to return a selected band.~~
~~ Pros: This is a general function that can be called before any function that would otherwise require a band parameter.~~
~~ Cons: This implies creating a temporary raster. This might be more elegant and general but is this too much overhead comparing with having a band parameter?~~

~~ 2. Add a band parameter to each ST_AsFormat function.~~
~~ Pros: Hypothetically less overhead.~~
~~ Cons: Every functions implying access to a band should then have this parameter when in most case it would be equal to 1. In many cases it makes no sence to have to specify a band parameter since it is the whole raster that we want to export, including all the bands.~~

~~Pierre: More I think about it more I think that the first option is the best one...~~

~~mloskot: Perhaps there is a compromise in form of two sets of functions: 1) ST_As* which always burn the whole raster (all bands) 2) ST_BandAs* which takes number of band as a parameter and return only this requested band.~~

**ST_Band(raster, integer) -> raster** -- the integer parameters are the band number of the rasters.

Return a single band from a multiband raster. If "band" is greater than the value returned by ST_GetNumBands(), the function returns the last band. This function should be used to select a band before converting it to JPEG, TIFF, PNG, SVG or KML with the corresponding function. e.g. **ST_AsTIFF(ST_Band(raster, band))
**

A complete implementation of ST_Band should include the following:

- ST_Band(rast raster, nbands int[]) -> raster

nbands is an array of 1-based band indices of the bands to copy into the output raster

For a raster rast with 3 bands:

ST_Band(rast, ARRAY[1,3,2]) ST_Band(rast, ARRAY[3,2,1])You can rearrange the bands as above. You can also duplicate the bands:

ST_Band(rast, ARRAY[1,2,3,2,1])

- ST_Band(rast raster, nband int) -> raster

nband is a single integer of the 1-based band index of the band to copy into the output raster

ST_Band(rast, 1) ST_Band(rast, 3)

- ST_Band(rast raster, nbands text) -> raster

nbands is a comma separated string of 1-based band indices indicating the bands to copy into the output raster

ST_Band(rast, '1,2') ST_Band(rast, '1,2,3, 1, 1 , 2')

- ST_Band(rast raster, nbands text, delimiter char) -> raster

nbands is a user-specified delimiter separated string of 1-based band indices indicating the bands to copy into the output raster

ST_Band(rast, '1,2', ',') ST_Band(rast, '1,2,3, 1, 1 , 2', ',')

- ST_Band(rast raster) -> raster

the band to extract is automatically assumed to be one.

ST_Band(rast)

If an index is outside the valid range of band indices for a raster (less than 1 or greater than the value returned by ST_NumBands), the function will fail and return.

** Open Question: ** Should the function fail if an index is invalid? How should this work when providing more than one indices to the function?

**ST_AsJPEG(raster, quality) -> JPEG as "bytea"**

The JPEG format has several limitations:

- JPEG only allows 1 (greyscale) or 3 (RGB) bands of data

- JPEG only supports 8BUI pixeltype

- JPEG cannot embed spatial reference information within the file but can have an associated world file

To address the limitations:

- Use ST_Band to specify which band(s) should be passed to the ST_AsJPEG function. Variations of ST_AsJPEG are made available that allows specifying a band index. If a raster whose number of specified bands does not equal 1 or 3 is provided, a warning is raised and the first or the first three bands are used.

- Throw an exception if any of the specified bands is not 8BUI. The user should use ST_Reclass to convert any non-8BUI bands to 8BUI.

- Nothing can be done.

A proposed set of variations of the ST_AsJPEG function:

- ST_AsJPEG(rast raster, options text[])

rast: the raster with one or three bands in 8BUI pixel type to generate a JPEG image from

options: array of creation options to pass to the GDAL JPEG driver

ST_AsJPEG(rast, ARRAY['QUALITY=90', 'PROGRESSIVE=ON'])

- ST_AsJPEG(rast raster)

Like #1 above but use the driver's default creation options

- ST_AsJPEG(rast raster, nbands int[], options text[])

nbands: an integer array specifying the band indices of the raster to include in the JPEG file

ST_AsJPEG(rast, ARRAY[1,3,6], ARRAY['QUALITY=50'])

- ST_AsJPEG(rast raster, nbands int[])

Like #3, but use the default creation options

ST_AsJPEG(rast, ARRAY[1,3,6])

- ST_AsJPEG(rast raster, nbands int[], quality int)

quality: number between 10 and 100 indicating image quality

ST_AsJPEG(rast, ARRAY[1,2,3], 90)

- ST_AsJPEG(rast raster, nband int, options text[])

nband: index of the band to include

ST_AsJPEG(rast, 2, ARRAY['QUALITY=25'])

- ST_AsJPEG(rast raster, nband int, quality int)

ST_AsJPEG(rast, 5, 75)

- ST_AsJPEG(rast raster, nband int)

ST_AsJPEG(rast, 4)

*OLD NOTES*

~~Return the raster as a JPEG encoded as a PostgreSQL bytea. By default quality is set to 75, but this option can be used to select other values. Values must be in the range 10-100. Low values result in higher compression ratios, but poorer image quality. Values above 95 are not meaningfully better quality but can but substantially larger. (copied from http://www.gdal.org/frmt_jpeg.html)~~

**Open Question:** Is JPEG export limited to raster having 8 bit unsigned integer pixeltype (8BUI)?

~~See how GDAL do it. It converts only 8 bits rasters. Should we do the same?~~

~~Otherwise, how do we convert other types to 8BUI? e.g. 16BUI or 8BSI?~~

~~Pierre: It might be more simple to ignore pixeltypes other than 8BUI but it would be very convenient to have a way to quickly export elevation data for example as a JPEG. It would be nice to have an elegant solution to this. Maybe something inspired from MapServer.~~

~~Proposition one (Pierre): ST_AsJPEG could simply (optionally when the pixeltype is not 8BUI) map the ST_Maximum() and ST_Minimum() value to 0-255. ST_Maximum() and ST_Minimum() are not in the spec yet but this could be on nice usage of it. They will imply caching the min and max when importing and editing. Both function should ignore the NoDataValues. They could also be two parameters passed to ST_AsJPEG(raster, quality, min, max).~~

~~Proposition two: There could also be just one parameter (string) defining a mapping method:~~

~~ * Method "None": No mapping. This is possible only for 8BUI.~~

~~ * Method "MaxMinValue": Use the Max and the Min cached in the raster. e.g. for 16BSI (min, max) -> (-2033, 2456) -> (round((-2033 - -2033)/(2456 - -2033)*255), round((2456 - -2033)/(2456 - -2033)*255)) -> (0, 255).~~

This is equivalent to ST_AsJPEG(raster, quality, ST_Minimum(rast), ST_Maximum(rast))

~~ * Method "MaxMinType": Use the Max and the Min allowed by the type. e.g. for 16BSI (min, max) -> (-2033, 2456) -> (round((-2033 - -32768)/(32767 - -32768)*255), round((2456 - -32768)/(32767 - -32768)*255)) -> (120, 137)~~

This would be equivalent to ST_AsJPEG(raster, quality, ST_BandPixelTypeMin(rast), ST_BandPixelTypeMax(rast)). Both functions (ST_BandPixelTypeMin & ST_BandPixelTypeMax) are not yet planned and I could not find an SQL query that returns the equivalent range for a type. One possible solution.

~~mloskot: ATM, I have no thoughts on this issue.~~

**Open Question:** Is JPEG export limited to raster having 1 or 3 bands?

~~See how GDAL do it. It converts only 1 or 3 band rasters. Should we do the same? In this case 1 band rasters would be exported as a greyscale JPEG having R G and B identical and 3 band rasters would be interpreted as R, G and B.~~

~~Pierre: I think the answer should be yes. I don't see how we could have a 2 band raster fit into RGB.~~

~~mloskot: I agree, the answer should be yes.~~

**Here is an attempt to define the different versions of the function:**

~~The most minimalistic versions of the function should assume band 1, 2 and 3 as being r, g, b and the quality equal to 75:~~

~~ ST_AsJPEG(raster) -quality = 75~~

~~A variant allow specifying the quality:~~

~~ ST_AsJPEG(raster, integer)~~

~~Another variant should enable us to specify which band correspond to the r, the g and the b:~~

~~ ST_AsJPEG(raster, integer, integer, integer) - raster, rband, gband, bband, quality=75~~

~~ ST_AsJPEG(raster, integer, integer, integer, integer) - raster, rband, gband, bband, quality~~

~~Another version should be designed to be used with a future ST_Band(raster) function. In this case there is no attempt to extract r, g or b band from any passed raster:~~

~~ ST_AsJPEG(raster, raster, raster)~~

~~ ST_AsJPEG(raster, raster, raster, integer) -with the quality param~~

~~Another series should allow converting 1 band raster with pixel of type 8BUI to a grayscale JPEG (Carefull study of the GDAL behavior when converting a single band to JPEG should be done before confirming these functions):~~

~~ ST_AsJPEG(raster, "GRAYSCALE") - convert only band 1 with quality = 75~~

~~ ST_AsJPEG(raster, "GRAYSCALE", integer) - convert only band 1 with specified quality~~

~~ ST_AsJPEG(raster, integer, "GRAYSCALE") - allow specifying the band number to convert~~

~~ ST_AsJPEG(raster, integer, "GRAYSCALE", integer) - allow specifying the band number to convert and the quality~~

~~Another series should allow converting 1 band raster of ANY pixel type to a grayscale JPEG. Pixel types different than 8BUI should be mapped according to specified min, max values and a mapping mode: "MaxMinValue" (default) or "MaxMinType".~~

~~ ST_AsJPEG(raster, "GRAYSCALE", min, max, text) - convert only band 1 with quality = 75~~

~~ ST_AsJPEG(raster, "GRAYSCALE", integer, min, max, text) - convert only band 1 with specified quality~~

~~ ST_AsJPEG(raster, integer, "GRAYSCALE", min, max, text) - allow specifying the band number to convert~~

~~ ST_AsJPEG(raster, integer, "GRAYSCALE", integer, min, max, text) - allow specifying the band number to convert and the quality~~

**ST_AsTIFF(raster, compression) -> TIFF as "bytea"**

Return the raster as a TIFF encoded as a PostgreSQL bytea. If raster is a multiband raster and no band were selected with ST_Band() every band are written to the resulting TIFF.

compression=[JPEG/LZW/PACKBITS/DEFLATE/CCITTRLE/CCITTFAX3/CCITTFAX4/NONE]: Set the type of compression to use. None is the default. The CCITT compression should only be used with 1bit (NBITS=1) data. JPEG should only be used with Byte data. When using JPEG add a number specifying the quality. 75 is the default. e.g. ST_AsTIFF(raster, "JPEG60") (copied from http://www.gdal.org/frmt_gtiff.html)

A proposed implementation of the ST_AsTIFF functions.

The TIFF format is probably the most robust available for converting rasters to GDAL rasters. Not only does it support all PostGIS Raster pixel types, it also provides plenty of creation options and possibly no issues with the number of bands. The only limitation found is that there can only be one NODATA value for all bands.

*If the compression parameter/option is specified to JPEG, all bands must be of pixel type 8BUI. If the compression parameter/option is specified to one of the CCITT options, all bands must be of pixel type 1BB. If any band violates the restriction, an exception is raised.*

The next three functions are the most basic of the ST_AsTIFF functions.

- ST_AsTIFF(rast raster, options text[], srs text) -> bytea

The most generic version of this function. All other ST_AsTIFF functions call this function.

This function will check that all bands of the raster to be converted has the same NODATA value. If there are more than one possible NODATA values, a WARNING will be raised and the output TIFF will use the NODATA value of the first band with a NODATA value.

options: the GDAL creation options found in the Creation Options section of the GDAL TIFF driver

srs: the user-specified OGC WKT or the proj4 text for a spatial reference to embed in the GDAL raster. TIFF is one of the formats that supports embedding the spatial reference within the image file.

ST_AsTIFF(rast, ARRAY['COMPRESS=DEFLATE', 'ZLEVEL=9'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsTIFF(rast, ARRAY['COMPRESS=DEFLATE', 'ZLEVEL=9'], 'PROJCS["NAD83 / California Albers",GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","6269"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4269"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Albers_Conic_Equal_Area"],PARAMETER["standard_parallel_1",34],PARAMETER["standard_parallel_2",40.5],PARAMETER["latitude_of_center",0],PARAMETER["longitude_of_center",-120],PARAMETER["false_easting",0],PARAMETER["false_northing",-4000000],AUTHORITY["EPSG","3310"],AXIS["X",EAST],AXIS["Y",NORTH]]')

- ST_AsTIFF(rast raster, options text[]) -> bytea

This one removes the user-specified srs argument. The output TIFF's spatial reference will be set to the same as the input raster, if possible.

ST_AsTIFF(rast, ARRAY['COMPRESS=DEFLATE', 'ZLEVEL=9'])

- ST_AsTIFF(rast raster) -> bytea

The simplest implementation of this function. Since the options argument has been removed, the output TIFF will be created with default options. Like the prior function, the spatial reference of the TIFF will be set to the same as the input raster.

ST_AsTIFF(rast)

The next three functions add a band index argument to filter the raster's bands before generating the output TIFF.

- ST_AsTIFF(rast raster, nbands int[], options text[], srs text) -> bytea

ST_AsTIFF(rast, ARRAY[3,1,2], ARRAY['COMPRESS=DEFLATE', 'ZLEVEL=9'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs')

- ST_AsTIFF(rast raster, nbands int[], options text[]) -> bytea

This one removes the user-specified srs argument. The output TIFF's spatial reference will be set to the same as the input raster, if possible.

ST_AsTIFF(rast, ARRAY[3,1,2], ARRAY['COMPRESS=DEFLATE', 'ZLEVEL=9'])

- ST_AsTIFF(rast raster, nbands int[]) -> bytea

Since the options argument has been removed, the output TIFF will be created with default options. Like the prior function, the spatial reference of the TIFF will be set to the same as the input raster.

ST_AsTIFF(rast, ARRAY[3,1,2])

The next two functions add a compression argument. If the compression desired is JPEG or DEFLATE, the user can specify a quality as part of the compression string.

Examples are:

JPEG90 JPEG DEFLATE8 DEFLATE

- ST_AsTIFF(rast raster, compression text, srs text) -> bytea

This function will parse the compression string for the compression type and the compression quality. It will also inspect to make sure that the pixel types of the raster's bands are appropriate for the compression type. This is primarily for JPEG and CCITT compression types, which only support 8BUI and 1BB respectively.

ST_AsTIFF(rast, 'JPEG90', '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsTIFF(rast, 'JPEG', '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsTIFF(rast, 'LZMA', '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs')

- ST_AsTIFF(rast raster, compression text) -> bytea

The output TIFF will be created with default options. Like the prior function, the spatial reference of the TIFF will be set to the same as the input raster.

ST_AsTIFF(rast, 'LZMA')

The next two functions include band index and compression arguments

- ST_AsTIFF(rast raster, nbands int[], compression text, srs text) -> bytea

ST_AsTIFF(rast, ARRAY[2], 'JPEG90', ARRAY['BIGTIFF=IF_NEEDED'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsTIFF(rast, ARRAY[1,3], 'JPEG', ARRAY['BIGTIFF=IF_NEEDED'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsTIFF(rast, ARRAY[3,1,2], 'LZMA', ARRAY['BIGTIFF=IF_NEEDED'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs')

- ST_AsTIFF(rast raster, nbands int[], compression text) -> bytea

ST_AsTIFF(rast, ARRAY[3,2], 'DEFLATE9')

The output TIFF will be created with default options. The spatial reference of the TIFF will be set to the same as the input raster.

**Open Question:** What if we want to export only the first two band of a three band layer?

~~Maybe we need a ST_RasterFromBands(band1, band2, etc...) to reconstitute a multiband raster from multiple sources (having the same width, height, pixelsize, etc...)~~

~~mloskot: or ST_RasterFromBands(bands) where bands is ARRAY[int]. For instance, ST_RasterFromBands(ARRAY[1,3]) will burn new raster from 1 and 3 bands of input raster.~~

**ST_AsPNG(raster, band) -> PNG as "bytea"**

Like the JPEG raster format, the PNG format has limitations:

- PNG only allows 1 (greyscale) or 3 (RGB) bands of data

- PNG only supports 8BUI and 16BUI pixeltypes. Any other pixeltype will be written as 8BUI, though the results are probably useless

- PNG cannot embed spatial reference information within the file but can have an associated world file

Like JPEG, the limitations can be resolved:

- Use ST_Band to specify which band(s) should be passed to the ST_AsPNG function. If a raster whose number of specified bands does not equal 1 or 3 is provided, a warning is raised and the first or the first three bands are used.

- Throw an exception if any of the specified bands is not 8BUI or 16BUI. The user should use ST_Reclass to convert any non-8BUI or 16BUI bands to 8BUI or 16BUI.

- Nothing can be done within this function. ST_Georeference() can be used to the contents of the associated world file

A proposed set of variations of the ST_AsPNG function:

- ST_AsPNG(rast raster, options text[])

rast: the raster with one or three bands in 8BUI or 16BUI pixel type to generate a PNG image from

options: array of creation options to pass to the GDAL PNG driver

ST_AsPNG(rast, ARRAY['ZLEVEL=9'])

- ST_AsPNG(rast raster)

Like #1 above but use the driver's default creation options

- ST_AsPNG(rast raster, nbands int[], options text[])

nbands: an integer array specifying the band indices of the raster to include in the PNG file

ST_AsPNG(rast, ARRAY[3,1,2], ARRAY['ZLEVEL=9'])

- ST_AsPNG(rast raster, nbands int[])

Like #3, but use the default creation options

ST_AsPNG(rast, ARRAY[3])

- ST_AsPNG(rast raster, nbands int[], compression int)

compression: number between 1 and 9 indicating the amount of time to spend on compression. 1 is fastest with least compression. 9 is slowest with best compression

ST_AsPNG(rast, ARRAY[2,1,3], 3)

- ST_AsPNG(rast raster, nband int, options text[])

nband: index of the band to include

ST_AsPNG(rast, 2, ARRAY['ZLEVEL=5'])

- ST_AsPNG(rast raster, nband int, compression int)

ST_AsPNG(rast, 1, 8)

- ST_AsPNG(rast raster, nband int)

ST_AsPNG(rast, 1)

**ST_AsGDALRaster(raster, band int, type text, options text) -> bytea**

Use GDAL to convert the raster into one of the format suported by GDAL.

This is a generic interface to outputting a supported and installed GDAL raster:

- ST_AsGDALRaster(rast raster, format text, options text[], srs text) -> bytea

This is the most generic and GDAL-specific method to convert a raster to a GDAL raster. All other version of ST_AsGDALRaster and other format specific functions (ST_AsJPEG, ST_AsTIFF and ST_AsPNG) are all wrappers around this function. Reference information for the format and options arguments of a particular format are specified at: http://gdal.org/formats_list.html. The arguments specified are:

format: the GDAL format code. e.g. GTiff, JPEG, PNG

options: the GDAL creation options found in the

Creation Optionssection of a specified format. e.g. COMPRESS=JPEG, JPEG_QUALITY=90

srs: the user-specified OGC WKT or the proj4 text for a spatial reference to embed in the GDAL raster. Not all formats support embedding this information. e.g. the non-empty value for the srtext or proj4text column from the spatial_ref_sys table.

ST_AsGDALRaster(rast, 'GTiff', ARRAY['COMPRESS=JPEG', 'JPEG_QUALITY=90'], '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-4000000 +ellps=GRS80 +datum=NAD83 +units=m +no_defs') ST_AsGDALRaster(rast, 'GTiff', ARRAY['COMPRESS=JPEG', 'JPEG_QUALITY=90'], 'PROJCS["NAD83 / California Albers",GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","6269"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4269"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Albers_Conic_Equal_Area"],PARAMETER["standard_parallel_1",34],PARAMETER["standard_parallel_2",40.5],PARAMETER["latitude_of_center",0],PARAMETER["longitude_of_center",-120],PARAMETER["false_easting",0],PARAMETER["false_northing",-4000000],AUTHORITY["EPSG","3310"],AXIS["X",EAST],AXIS["Y",NORTH]]')

- ST_AsGDALRaster(rast raster, format text, options text[]) -> bytea

This one removes the user-specified srs argument. The output GDAL raster's spatial reference will be set to the same as the input raster, if possible.

ST_AsGDALRaster(rast, 'JPEG', ARRAY['QUALITY=50']) ST_AsGDALRaster(rast, 'PNG', ARRAY['ZLEVEL=7'])

- ST_AsGDALRaster(rast raster, format text) -> bytea

The simplest implementation of this function. Since the options argument has been removed, the output GDAL raster will be created with default options. Like the prior function, the spatial reference of the GDAL raster will be set to the same as the input raster.

ST_AsGDALRaster(rast, 'JPEG')

**ST_GDALDrivers() -> set of record**

As each GDAL installation may be different and ST_AsGDALRaster can be used to support formats other than GTiff, JPEG and PNG, a method is needed to expose to the end user the possible GDAL formats capable of being exported. This function will output the following columns.

idx: the internal GDAL index number

short_name: the GDAL format code. This is the value to pass to the format paramenter of ST_AsGDALRaster

long_name: the full name of the GDAL format

create_options: the creation options available for the format as an XML string.

The formats outputted from ST_getGDALDrivers have been filtered to only those that the GDAL capabilities CreateCopy and Virtual IO support.

**Open Question:** Should the GDAL raster process be capable of supporting the GDAL capability Create? As the GDAL raster process writes nothing to a file in the filesystem (via Virtual IO), should there be support for writing the output GDAL raster temporarily to the filesystem? If so, how is it done in other PostgreSQL extensions in a secure manner?

**ST_srtext(rast raster) -> text**

~~A helper function to get the value of column srtext or proj4text for a raster with an SRID. By default, the srtext is returned. If srtext is not available but proj4text is, the proj4text is returned.
~~

~~This function may be removed based upon the capabilities of SPI. It may not be possible to remove this function as the srs function argument of ST_AsGDALRaster can be NULL, thereby instructing the function to not embed any spatial reference information into the output GDAL raster.
~~

**ST_Reclass(rast raster, VARIADIC argset reclassarg[]) -> raster**

Due to limitations in the JPEG (8BUI) and PNG (8BUI and 16BUI) raster formats regarding supported pixel/data types, a method must be provided that can convert a band of a larger data type to 8BUI, amongst other uses. ST_Reclass allows raster's band pixel values to be remapped from one range of numbers to another as well as between pixel types, e.g. 32BF to 8BUI.

ST_Reclass returns a duplicate of the submitted raster with the bands specified to be reclassed being processed. This means that if a raster with 5 bands are submitted and band 1 is to be reclassed, the output raster will have 5 bands with band 1 reclassified. The other four bands will not be touched.

- ST_Reclass(rast raster, VARIADIC argset reclassarg[]) -> raster

rast: the raster whose specified bands are to be reclassified

reclassarg: a new custom type defining the parameters required for reclassifying a band's pixel values.

CREATE TYPE reclassarg AS ( nband int, reclassexpr text, pixeltype text, nodata double );

nband: index of the band to reclass (1-based)

reclassexpr: reclassification expression indicating the ranges to convert from and to. More than one expression can be provided by separating the expression with a comma (,). The values provided can be of any valid numeric type.

rangefrom:rangeto[, rangefrom:rangeto]

0-100:0-10 0-100:0-10, 101-1000:11-100 0-100:0-10, 101-1000:11-100, 1001-10000:101-1000

In the last example above, the default evaluation of the ranges is

0 <= x < 100 reclassified to 0 <= y <= 10 101 <= x < 1000 reclassified to 11 <= y <= 100 1001 <= x < 10000 reclassified to 101 <= y <= 1000

To change the evaluation of rangefrom, use square brackets and parentheses.

1. [a-b] = a <= x <= b 2. (a-b] = a < x <= b 3. [a-b) = a <= x < b 4. (a-b) = a < x < b

#3 above is the default evaluation of x in the range a-b. The use of square brackets and parentheses are optional, so the examples below would be permitted. Missing notations substitute the appropriate notation from #3 above.

[a-b = a <= x < b (a-b = a < x < b a-b] = a <= x <= b a-b) = a <= x < b

Two special cases are also available for use when x may be outside the range of a-b. This situation is possible if your range is based upon an approximation, such as from ST_ApproxMinMax.

]a-b or )a-b = x < a, rule matches a-b[ or a-b( = x >= b, rule matches

pixeltype: the reclassified band's pixel type, e.g. 8BUI, 16BUI, 32BF

nodata: the nodata value of the reclassified band. If the source band has a nodata value, all source pixel value equal to the source nodata value will be converted to the reclassified band's nodata value. If set to NULL, the reclassified band will NOT have a nodata value specified.

ST_Reclass(rast, ROW(1, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', NULL)); ST_Reclass(rast, ROW(1, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', 1001)); ST_Reclass(rast, ROW(1, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', 1001), ROW(2, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', 1001), ROW(3, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', 1001), ROW(5, '0-100:0-10, 101-1000:11-100, 1001-10000:101-1000', '16BUI', 1001) )

An expanded example

SELECT ST_Reclass( ST_Band(rast, ARRAY[1,1,1]), ROW(1, LEAST(covmin, 0)::text || '-0:0,0-' || GREATEST(covmax, 0)::text || ':0-255', '8BUI'), ROW(2, LEAST(covmin, 0)::text || '-0:200,0-' || GREATEST(covmax, 0)::text' || ':0-255','8BUI'), ROW(3, LEAST(covmin, 0)::text || '-0:255,0-' || (GREATEST(covmax, 0)/2)::text' || ':0,' || (GREATEST(covmax, 0)/2)::text' || ':' || GREATEST(covmax, 0)::text || ':0-255', '8BUI') ) FROM mycoverage

- ST_Reclass(rast raster, nband int, reclassexpr text, pixeltype text, nodata double) -> raster

provides a method to process just one band of a raster

ST_Reclass(rast, 1, '0-100:0-10', '8BUI', 11)

- ST_Reclass(rast raster, nband int, reclassexpr text, pixeltype text) -> raster

nodata parameter removed so reclassified band will NOT have a nodata value set

ST_Reclass(rast, 1, '0-100:0-10', '8BUI')

- ST_Reclass(rast raster, reclassexpr text, pixeltype text) -> raster

nband parameter removed so reclassified band is assumed to be 1. nodata parameter removed so reclassified band has NO nodata value.

ST_Reclass(rast, '0-100:0-10', '8BUI')

- ST_Reclass(rast raster, reclassexpr text, pixeltype text, nodata double) -> raster

nband parameter removed so reclassified band is assumed to be 1

ST_Reclass(rast, '0-100:0-10', '8BUI', 11)

**Objective FV.02 - Being able to intersect vector and raster to produce raster.**

**ST_Intersects(raster, raster) -> boolean**

This function uses the same tests as the two geometry version of ST_Intersects where tests see if two rasters overlap, touches or has one within the other. If any condition is true, the two rasters intersect.

For this description, the rasters are called A and B and the bands tested are assumed to be 1.

Preliminary criteria before the real work begins:

- make sure that rasters A and B have the same SRID. if not, return false.

- make sure that the convex hulls of A and B intersect. if not, return false.

Special case where a raster may be fully contained within another raster's cell (the entirety of A within a cell of B):

- using every third pixel by row and column, test each selected cell's geopoint (excluding no data by default unless exclude_nodata_value = FALSE) of A to that of B for overlap.

- if a cell of A does overlap a cell of B, return true.

- if no cell of A overlaps with B, continue to additional testing

Actual testing involves the use of calculating the intersection of grid lines between A and B

- Using every third A's column and B's row, calculate the intersection point of each pair of grid lines.

- If intersection point doesn't exist or is outside the bounds of the end points comprising each grid line used, go back to step 1.

- If intersection point exists and within bounds, sample the area around the intersection point by offset the intersection point by a small amount (1/10th of the smaller scale of A and B) for 360 degrees starting from 00:00. sampled points are 0, 45, 90, 135, 180, 225, 270 and 315 degrees.

- At each sample point, test to see if the geopoint has non-nodata (unless including nodata) values in both A and B. if so, return true.

- At the same time as step 4, build an adjacency matrix for the intersection to see if two non-overlapping pixels from A and B touch.

- Once all sample points have been tested and no overlapping pixels found, the adjacency matrix is checked to see if any sampled pixel of A touched a sampled pixel of B. If two pixels touched, return true.

- If after all the searching and testing, nothing intersects or touches, return false.

A set of ST_Intersects functions for rasters:

- ST_Intersects(raster rastA, raster rastB, integer bandA DEFAULT NULL, integer bandB DEFAULT NULL) -> boolean

If bandA and bandB are NULL, only the convex hulls of the rasters will be tested. If bandA or bandB are provided, both parameters must be provided and not NULL.

- ST_Intersects(raster rastA, integer bandA, raster rastB, integer bandB) -> boolean

A refactored set of St_Intersects() for testing a raster and a geometry. The first set converts the geometry to a raster to see if the two rasters intersect.

- ST_Intersects(rast raster, geom geometry, nband integer DEFAULT NULL) -> boolean

- ST_Intersects(rast raster, nband integer, geom geometry) -> boolean

The second set of ST_Intersects() for testing a raster and a geometry converts the raster to a geometry and sees if the geometries intersect.

- ST_Intersects(geom geometry, rast raster, nband DEFAULT NULL) -> boolean

The order in which the geometry and raster are passed into ST_Intersects determines which method of testing is used. If geometry is first, the raster is converted to a set of geometries. If raster is first, the geometry is converted to a raster.

These set of functions are required because there are cases where ST_Intersects(raster, geometry) != ST_Intersects(geometry, raster).

In the image above, the black border denotes the boundary of a triangle polygon and the red within is the raster version of the geometry. The polygon touches the raster in blue while the red raster does not.

**ST_AsRaster(geometry, pixeltype, val, nodataval, ulx, uly, width, height, pixelsizex, pixelsizey, skewx, skewy) -> raster**

ST_AsRaster provides the ability convert a geometry into a raster. To create the raster, the X and Y scale or the width and height of the raster must be provided.

The output raster will be in the same coordinate system as the source geometry. The only exception is for ST_AsRaster variations with a raster input parameter.

- ST_AsRaster(

geom geometry,

scalex double precision, scaley double precision,

gridx double precision DEFAULT NULL, gridy double precision DEFAULT NULL,

pixeltype text[] DEFAULT ARRAY['8BUI']::text[],

value double precision[] DEFAULT ARRAY[1]::double precision[],

nodataval double precision[] DEFAULT ARRAY[0]::double precision[],

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

geom: the geometry to convert to a raster. Can be any valid PostGIS geometry.

scalex: the scale/pixel size in the X axis of the output raster. If scalex and scaley are zero (0), the output raster's scale will be autocomputed.

scaley: the scale/pixel size in the Y axis of the output raster. If scalex and scaley are zero (0), the output raster's scale will be autocomputed.

pixeltype: array of pixel types for each band. Each array element is a band.

value: array of values to burn into the raster for the geometry. Each array element is a band.

nodataval: array of nodata values to burn into the raster. Each array element is a band. If an array element is null, that band will not have a nodata value.

upperleftx: the X value of the upper-left corner of the output raster

upperlefty: the Y value of the upper-left corner of the output raster

gridx: the X coordinate of a point on the grid to which the raster will be aligned. Value is in the raster's world coordinates.

gridy: the Y coordinate of a point on the grid to which the raster will be aligned. Value is in the raster's world coordinates.

skewx: the skew along the X axis of the raster. by default, the skew along the X axis is zero.

skewy: the skew along the Y axis of the raster. by default, the skew along the X axis is zero.

touched: from the GDAL man page for gdal_rasterize: "Enables the ALL_TOUCHED rasterization option so that all pixels touched by lines or polygons will be updated not just those one the line render path, or whose center point is within the polygon. Defaults to disabled for normal rendering rules."

*If the number of elements for pixeltype, value and nodataval do not match, the minimum number of elements will be used.*

- ST_AsRaster(

geom geometry,

scalex double precision, scaley double precision,

pixeltype text[],

value double precision[] DEFAULT ARRAY[1]::double precision[],

nodataval double precision[] DEFAULT ARRAY[0]::double precision[],

upperleftx double precision DEFAULT NULL, upperlefty double precision DEFAULT NULL,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

width integer, height integer,

gridx double precision DEFAULT NULL, gridy double precision DEFAULT NULL,

pixeltype text[] DEFAULT ARRAY['8BUI']::text[],

value double precision[] DEFAULT ARRAY[1]::double precision[],

nodataval double precision[] DEFAULT ARRAY[0]::double precision[],

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

width integer, height integer,

pixeltype text[],

value double precision[] DEFAULT ARRAY[1]::double precision[],

nodataval double precision[] DEFAULT ARRAY[0]::double precision[],

upperleftx double precision DEFAULT NULL, upperlefty double precision DEFAULT NULL,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

scalex double precision, scaley double precision,

gridx double precision, gridy double precision,

pixeltype text,

value double precision DEFAULT 1,

nodataval double precision DEFAULT 0,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

scalex double precision, scaley double precision,

pixeltype text,

value double precision DEFAULT 1,

nodataval double precision DEFAULT 0,

upperleftx double precision DEFAULT NULL, upperlefty double precision DEFAULT NULL,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

width integer, height integer,

gridx double precision, gridy double precision,

pixeltype text,

value double precision DEFAULT 1,

nodataval double precision DEFAULT 0,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

width integer, height integer,

pixeltype text,

value double precision DEFAULT 1,

nodataval double precision DEFAULT 0,

upperleftx double precision DEFAULT NULL, upperlefty double precision DEFAULT NULL,

skewx double precision DEFAULT 0, skewy double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

ref raster,

pixeltype text[] DEFAULT ARRAY['8BUI']::text[],

value double precision[] DEFAULT ARRAY[1]::double precision[],

nodataval double precision[] DEFAULT ARRAY[0]::double precision[],

touched boolean DEFAULT FALSE

) -> raster

- ST_AsRaster(

geom geometry,

ref raster,

pixeltype text,

value double precision DEFAULT 1,

nodataval double precision DEFAULT 0,

touched boolean DEFAULT FALSE

) -> raster

~~ * Rasterize the provided geometry to a raster created using the specified parameters.~~
~~ * Implemented as a wrapper around GDAL like ST_DumpAsPolygons() does.~~
~~ * ST_AsRaster is necessary to implement ST_Intersection(geometry, raster, band) -> raster and an eventual ST_BurnToRaster(raster, geometry) -> raster where the provided geometry is first rasterized to the same alignment as the other raster involved.~~
~~ * Each geometry of a table is rasterized as one raster. To produce a global unique raster including all the geometries of a table (loosing overlaps by the way), a user must use the planned ST_Union aggregate function to merge all the rasters together optionally in a GROUP BY query grouping some rows together.~~

~~ * The raster is burned with the specified value converted (or truncated) with a warning to the provided pixeltype. The hasnodatavalue flag of the resulting raster must be set and the nodata value is set to the provided value (expect in variant 10 and 12).~~

~~ * Alignment, width, height and pixelsize are optionally computed:~~

~~ -From the vector extent of the geometry,~~

~~ -Imposed with parameters,~~

~~ -From another provided raster.~~

~~ * Alignment can be optionally specified as:~~

~~ -A x and a y world coordinates specifying an arbitrary pixel corner. Although it can be, it IS NOT necessarily the coordinates of the upper left corner pixel.~~

~~ -A ulx and a uly world coordinates specifying the upperleft corner of the raster. This IS NECESSARILY the upper left corner of the upperleft pixel. In this case a width and a height must also be provided.~~

~~ -An existing raster. The x and y are derived from the provided raster and the resulting raster has the same ulx, uly, width and height as the provided raster. A 'CROP' option allows cropping the resulting raster to the minimal extent of the geometry keeping the x and y alignment of the provided raster.~~

~~ -Default alignment is the upper left corner of the envelope of the geometry. This might result in table where all rasters are misaligned, but this is useful when reconverting to raster a set of polygons vectorized from rasters (with ST_DumpAsPolygon() or ST_Intersection()).~~

~~ * Pixelsize can be optionally specified as:~~

~~ -One or two floating point numbers. If only one is provided, both x and y pixelsizes are assigned the same value.~~

~~ -A width and a height (integers). In this case the x pixelsize is the x extent divided by the provided width and the y pixelsize is the y extent divided by the provided height. This is useful only when the alignment is specified as the upper left corner of the raster.~~

~~ -“FIRST_SEGMENT_LENGTH”. The pixelsize is set to the length of the first line segment encountered in the geometry. This is useful when reconverting to raster polygons vectorized from rasters (with ST_DumpAsPolygon() or ST_Intersection()). In this case, all segments are of the same length which is the original raster pixel size. This is useful only when alignment is not specified. If the geometry is a point, return an error.~~

~~ -Default is the smallest of the width or height of the extent of the source geometry divided by 250. If the smallest of the width or height of the extent of the source geometry is zero then a warning is reported an no NULL is returned.~~

~~ * It would be interesting to have a 'ADD_WEIGHTING_INFO' option to create a second band with the length of the line or the area of polygon (or the distance to the center of points) intersecting each pixel. This band could then be used in a ST_Union(rast, 'MAX_LENGTH') or a ST_Union(rast, 'MAX_AREA') function burning the value of the line having the longest intersection with the pixel or the value of the polygon having the biggest intersecting area. For this to be useful ST_Union should work with a ST_MapAlgebra(rast1, rast2) able to refer to pixel value in any band (e.g. rast1.2 referring to the pixel value in the second band). The ST_Union(rast, 'MAX_LENGTH') aggregate could then be implemented with the following state expression: 'CASE WHEN rast1.2 > rast2.2 THEN rast1.1 ELSE rast2.1 ENDIF'. Otherwise we would have to imbricate four Mapalgebra functions. ST_Union(rast, 'MAX_COMBINED_LENGTH') could use a ST_MapAlgebra(rast1, rast2) able to take a custom user function. The state function would accumulate, in a temporary table, the total intersecting length or area for a same value over every overlapping pixels and the final function would determine which of the values from the temporary table would be the right one to burn in the final raster pixel. Variations of ST_Union which could then be easily implemented could be: ST_Union(rast, 'WEIGHTED') to weight the value with the length, the area or the count, ST_Union(rast, 'MOST_FREQUENT') to burn the value for the most frequent points values.~~

~~ ~~
**Variants**

~~ * There are many variants because we are creating a raster from scratch. We want to make it easy (serie one and four) to convert geometries to raster and at the same time we want to have flexibility to control the resulting raster properties (series two and three).~~
~~ * Variant 3, 5 and 13 are the most useful.~~
~~ * Variant 6, 7, 8, 9, 10, 11, 12 are also useful.~~
~~ * Variant 1, 2, 4 are useful for quick conversion.~~
~~ * All variant should be pl/pgsql variant of variant 9 which is the only one needing to be implemented as a rt_pg functions.~~

~~ * The ~~
**first series of variant** get their alignment from the extent of the geometry.

~~ 1) ST_AsRaster(geometry, pixeltype, val, nodataval) – 0, alignment is computed from the geometry envelope, pixel size is computed by dividing the extent by 250. This variant is optional and discouraged as it results in many unaligned rasters for a table with many geometries.~~

~~ 2) ST_AsRaster(geometry, pixeltype, val, nodataval, pixelsize float8) – 1, x and y are computed from upper left corner it the geometry envelope. This variant is optional and discouraged as it results in many unaligned rasters for a table with many geometries.~~

~~ 3) ST_AsRaster(geometry, pixeltype, val, nodataval, ‘FIRST_SEGMENT_LENGTH’) – 0, ulx and uly are get from the envelope, pixel size from the length of the first segment. This variant is useful only to rasterize geometries produced by ST_DumpAsPolygon or ST_Intersection. Otherwise it discouraged as it would result in many unaligned rasters for a table with many geometries.~~

~~ * The ~~
**second series of variant** have their alignment specified by an arbitrary pixel corner of the desired raster.

~~ 4) ST_AsRaster(geometry, pixeltype, val, nodataval, x float8, y float8) – 2, pixel size is computed by dividing the extent by 250. This variant is optional and discouraged as it results in many unaligned rasters for a table with many geometries.~~

~~ 5) ST_AsRaster(geometry, pixeltype, val, nodataval, x float8, y float8, pixelsize) – 3, this is one of the preferred variant when the geometry is not the result of ST_DumpAsPolygon or ST_Intersection.~~

~~ 6) ST_AsRaster(geometry, pixeltype, val, nodataval, x float8, y float8, pixelsizex, pixelsizey) - 4, this is one of the preferred variant when the geometry is not the result of ST_DumpAsPolygon or ST_Intersection.~~
~~ 7) ST_AsRaster(geometry, pixeltype, val, nodataval, x float8, y float8, pixelsizex, pixelsizey, skewx, skewy) – 6, this is one of the preferred variant when the geometry is not the result of ST_DumpAsPolygon or ST_Intersection.~~

~~ * The ~~
**third series of variant** have their alignment specified by the upper left corner of the upper left pixel of the desired raster.

~~ 8) ST_AsRaster(geometry, pixeltype, val, nodataval, ulx, uly, width, height, pixelsize) – 2,2,1, this is useful to rasterize geometries to a common raster extent. Some geometries might be outside the extent of the desired raster extent resulting in a nodata value raster.~~

~~ 9) ST_AsRaster(geometry, pixeltype, val, nodataval, ulx, uly, width, height, pixelsizex, pixelsizey, skewx, skewy) – 2,2,4, this is useful to rasterize geometries to a common raster extent. Some geometries might be outside the extent of the desired raster extent resulting in a nodata value raster. This is the only variant which must be implemented in the tr_pg layer. All other variant must be implemented in pl/PgSQL and derive the argument to call this variant.~~

~~ * The ~~
**fourth series of variant** get their alignment (and size for variant 10 and 12) from an existing raster. Variant 10 and 11 keep the same extent as the provided raster and variant 12 and 13 have their extent cropped to the extent of the geometry.

~~ 10) ST_AsRaster(geometry, val, raster) – 0, the target raster metadata (including pixeltype, hasnodatavalue and nodatavalue) are copied from the provided raster~~

~~ 11) ST_AsRaster(geometry, pixeltype, val, nodataval, raster) – 0, the target raster metadata are copied from the provided raster.~~

~~ 12) ST_AsRaster(geometry, val, raster, “CROP”) – 0, the target raster alignment and pixel size are copied from the provided raster (including pixeltype, hasnodatavalue and nodatavalue) but the extent is reduced to the geometry extent. We should not create a (too big) raster and crop at the end. The final extent should be computed BEFORE burning.~~

~~ 13) ST_AsRaster(geometry, pixeltype, val, nodataval, raster, “CROP”) – 0, the target raster alignment and pixel size are copied from the provided raster but the extent is reduced to the geometry extent. We should not create a (too big) raster and crop at the end. The final extent should be computed BEFORE burning.~~

~~ ~~
**Questions**

~~ -How does GDAL/ArcGIS choose the pixel size and the ul of the final raster?~~

~~ -How does GDAL/ArcGIS allow selecting the value column -What if it is a text column?~~

~~ -How does GDAL/ArcGIS allow giving a thickness to points and lines -No thickness is given. All pixels intersecting the geometry are burned following a selected method.~~

~~ -How does GDAL/ArcGIS select which value to assign to the pixel when two or more features intersect with the pixel?~~

**ST_Intersection(raster, band, geometry) -> raster**

ST_Intersection is plpgsql function wrapping the two-raster ST_MapAlgebra functions. Performance should be about the same as calling ST_MapAlgebra directly except for the case where returnband is BOTH as that results in two separate ST_MapAlgebra calls.

A set of ST_Intersection functions for raster, raster.

1. ST_Intersection( rast1 raster, nband1 int, rast2 raster, nband2 int, returnband text DEFAULT 'BOTH', otheruserfunc regprocedure DEFAULT NULL );

returnband: can be FIRST, SECOND, BOTH, OTHER

- FIRST returns the band of rast1 in the intersecting extent. returning raster will have one band.
- SECOND returns the band of rast2 in the intersecting extent. returning raster will have one band.
- BOTH returns the bands of rast1 and rast2 in the intersection extent. returning raster will have two bands.
- OTHER returns the computed band based upon rast1 and rast2 in the intersecting extent. returning raster will have one band. If OTHER, must provide a regprocedure to otherfunc

otheruserfunc: function to call when returnband = OTHER. Function format must be identical to tworastuserfunc of 2-raster ST_MapAlgebraFct.

2. ST_Intersection( rast1 raster, nband1 int, rast2 raster, nband2 int, otheruserfunc regprocedure );

3. ST_Intersection( rast1 raster, rast2 raster, returnband text DEFAULT 'BOTH', otheruserfunc regprocedure DEFAULT NULL );

4. ST_Intersection( rast1 raster, rast2 raster, otheruserfunc regprocedure );

A set of ST_Intersection functions for raster, geometry (converted to raster).

1. ST_Intersection( rast raster, nband int, geom geometry, extent text DEFAULT 'INTERSECTION', otheruserfunc regprocedure DEFAULT NULL )

extent: can be INTERSECTION, FIRST, SECOND, UNION though FIRST and INTERSECTION will probably be the most commonly used

2. ST_Intersection( rast raster, nband int, geom geometry, otheruserfunc regprocedure );

3. ST_Intersection( rast raster, geom geometry, extent text DEFAULT 'INTERSECTON', otheruserfunc regprocedure DEFAULT NULL );

4. ST_Intersection( rast raster, geom geometry, otheruserfunc regprocedure );

~~ The first series of variant return a raster having the same extent as the provided raster.
~~

~~ Variant 1: ST_Intersection(geometry, val, raster, band, pixeltype, nodatavalue) -> raster
~~

~~ Variant 2: ST_Intersection(raster, band, geometry, val, pixeltype, nodatavalue) -> raster
~~

~~ Variant 3: ST_Intersection(geometry, val, raster, pixeltype, nodatavalue) -> raster
~~

~~ Variant 4: ST_Intersection(raster, geometry, val, pixeltype, nodatavalue) -> raster
~~

~~ The second series of variant return a raster having the minimal extent.
~~

~~ Variant 5: ST_Intersection(geometry, val, raster, band, pixeltype, nodatavalue, 'TRIM') -> raster
~~

~~ Variant 6: ST_Intersection(raster, band, geometry, val, pixeltype, nodatavalue, 'TRIM') -> raster
~~

~~ Variant 7: ST_Intersection(geometry, val, raster, pixeltype, nodatavalue, 'TRIM') -> raster
~~

~~ Variant 8: ST_Intersection(raster, geometry, val, pixeltype, nodatavalue, 'TRIM') -> raster
~~

~~ Returns a two bands raster the first band containing only the pixels from the provided raster intersecting with the geometry and the second band containing the same area filled with the provided value.
~~

~~ The second band gets its pixeltype and nodatavalue from the parameters.
~~

~~ Non intersecting pixels are filled with nodata values.
~~

~~ Variant 1 return a raster having the same extent as the provided raster.
~~

~~ Variant 3, 4, 7 and 8 defaults the band number to 1.
~~

~~ Variant 5 to 8 "trim" or "crop" the raster to the withvalue extent (removing extra nodata value pixels surrounding the extent of the resulting withvalue extent).
~~

~~ ~~**Open questions**

~~ PR: Shoud we return one raster per raster/geometry couple or split the raster into as many small rasters as there are areas sharing a same value? The second behavior seems more coherent with the present behavior of ST_Intersection(raster, geometry) -> geometry even if this would produce tons of small two bands rasters.
~~

~~ ~~**Implementation details**

~~ Rasterize the geometry as a new raster (ST_AsRaster(geometry, pixeltype, val, nodataval, raster)) and then copy only pixels for which both raster bands have a value.
~~ Should be implemented as a wrapper around ST_MapAlgebra after rasterizing the geometry to a raster having the same alignment as the raster.

**Objective FV.03 - Implement all the necessary versions of ST_MapAlgebra**

Different versions of ST_MapAlgebra are planned:

One raster versions:

1) **ST_MapAlgebraExpr(**rast raster, band int, pixeltype text, expression text, nodataexpr text**) -** A one raster version taking an **expression** of one pixel at a time. Already implemented...

2) **ST_MapAlgebraFct(**rast raster, band int, pixeltype text, funcname text[, funcargs text]**) -** A one raster version taking a **user defined function** (with optional parameters) of one pixel at a time. The function is a user defined PL/pgSQL function taking a float8 value and returning a value. Code was developped by David Zwarg, needs to be integrated in the source tree. This version is much faster than 1) but requires the user to write a PL/pgSQL function.

3) **ST_MapAlgebraFctNgb(**rast raster, band int, pixeltype text, radius int, funcname text[, funcargs text]**) -** A one raster version taking a **user defined function** (with optional parameters) of the set of first, second, etc... **neighbours** of a pixel. The function is a user defined PL/pgSQL function taking a matrix containing the neighbour values and returning one value. Code do not exist yet but will be very much similar to 2). Out of bound pixels values are set to NULL. This version requires the user to write a PL/pgSQL function. Many predefined function should be delivered.

4) **ST_MapAlgebraFctNgb(**rasttable text, rastcolumn text, band int, pixeltype text, radius int, funcname text[, funcargs text]**) -** A one raster version taking a **table name and a raster column name** (in order to work on a **tiled coverage**) and a **user defined function** (with optional parameters) of the set of first, second, etc... **neighbours** of a pixel. The passed matrix should include values for out of bound pixels taken from neighbour tiles.
Some versions were done:

Two rasters versions. These versions must take into account different alignment, different extents, nodata and non-existent values.

5) **ST_MapAlgebraExpr(**rast1 raster, band1 integer, rast2 raster, band2 integer, pixeltype text, extentexpr text, expression text, nodata1expr text, nodata2expr text, nodatanodatadaexpr text**) -** A two rasters version taking an expression of two pixels at a time, one from each rasters.

6) **ST_MapAlgebraExpr(**rast1table text, rast1column text, band1 integer, rast2table text, rast2column text, band2 integer, pixeltype text, extentexpr text, expression text, nodata1expr text, nodata2expr text, nodatanodatadaexpr text**) -** A two rasters version taking **two table names and two raster column name** (in order to work on a **tiled coverage**) and an expression of two pixels at a time, one from each rasters.

7) **ST_MapAlgebraFct(**rast1 raster, band1 integer, rast2 raster, band2 integer, pixeltype text, extentexpr text, funcname text[, funcargs text]**) -** A two rasters version taking a **user defined function** (with optional parameters) of two pixels at a time, one from each rasters. The function is a user defined PL/pgSQL function taking two float8 values and returning one value.

8) **ST_MapAlgebraFct(**rast1table text, rast1column text, band1 integer, rast2table text, rast2column text, band2 integer, pixeltype text, extentexpr text, funcname text[, funcargs text]**) -** A two rasters version taking a **table name and a raster column name** (in order to work on a **tiled coverage**) and a **user defined function** (with optional parameters) of two pixels at a time, one from each rasters. The function is a user defined PL/pgSQL function taking two float8 values and returning one value. Non-existent values are found in the neighbour tiles when possible.

Parameters and details for each variant follow...

**Details for 1) ST_MapAlgebraExpr(raster rast, integer band, text expression, text nodatavalueexpr, text pixeltype) -> raster**

This function is already implemented. See the specifications of Objective 2.0.02 and the documentation.

**Details for 3) ST_MapAlgebraFctNgb()**

For now ST_MapAlgebra expressions refer only to the pixel being computed. e.g. "rast * 100". The original plan was to allow refering to neighbour pixels using two coordinated relative to the pixel being computed. e.g. "rast[-1,0] * 100" where rast[-1,0] refer to the value of the pixel one pixel to the left of the pixel being computed. However this syntax might prove to be hard to use when many neighbours are to be used.

An alternative syntax would involve another function name (e.g. ST_MapAlgebraNgb or ST_MovingWindow) and a way to define a neighbour rectangular region around the computed pixel (e.g.: "2,2" meaning a rectangle encompassing the two neighbour pixels in each direction) and a function to call with this matrix of pixel values. A complete example might look like:

SELECT ST_MapAlgebraFctNgb(rast, band, pixeltype, "ST_Mean", 2, 2, "ignore")

So this would mean "for each pixel, compute the average of all the 1 + 8 + 16 = 25 pixels surrounding the current pixel and "ignore" pixels with nodata values."

The "ST_Mean" summarizing function should accept three parameters: an array of float8 values, a X and a Y dimension, and optionnally a "what to do with nodata values". The possible value for this last parameter could be:

-"NULL": If any value is a nodata value, return NULL.

-"ignore": Ignore any nodata value so that if 4 pixels on 25 are nodata values, do the computation with the 21 remaining. This is the default.

-"value": Replace any nodata value with the value of the pixel being computed.

-a value: Replace any nodata value with this value and compute.

Open questions

DZ: Since this accepts a user function, the user function should determine how to handle NODATA values inside of the neighborhood. These different modes of operating should be arguments to the userfunctions, and not to ST_MapAlgebraFctNgb.

Pierre: They ARE arguments to the user function as are 2 and 2 in this example. But all user function MUST accept at least these three parameters.

DZ: The user function can determine the dimensions of the neighborhood from the incoming 2-dimensional array, so the user function must accept: neighborhood float[][], nodataflag text, variadic args text[]. It is not necessary to pass the neighborhood dimensions to the user function. They are still required in the main ST_MapAlgebraFctNgb function, as it must construct the neighborhood array.

Any remaining parameters to ST_MapAlgebraNgb could be passed to the summarizing functions for its own need (e.g. "round" to specify that only the pixel forming a circle should be used in the computing).

A number of predefined summarizing function could be delivered: ST_Max, ST_Min, ST_Sum, ST_Distinct, ST_Mean, ST_STD, ST_Range, ST_Quantile, ST_Median, ST_Majority, ST_Minority, ST_Slope, ST_Aspect, and more...

Users could write their own map algebra summarizing functions.

A more sophisticated version would pass a georeferenced raster instead of just a value matrix so that summarizing function could use this geoereference (e.g. to determine a value from the whole coverage (with ST_Value) when the neighbours are out of the bound of the raster). Passing a raster would allow existing raster functions (like the summarizing function which are to come). Only the optional "what to do with nodata values" could be needed and some additional parameters. In this case the example become:

SELECT ST_MapAlgebraNgb(rast, band, pixeltype, 2, 2, "ST_Mean", "ignore")

and the dimensions do not have to be passed to the summarizing functions since it could deduce them from ST_Width & ST_Height.

An even more sophisticated version should get a raster table and a raster column as parameters and try to search for neighbour in the whole raster coverage when out of bound pixels are part of the neighbourhood. e.g.:

SELECT ST_MapAlgebraNgb("mycoveragetable", "myrastercolumn", band, pixeltype, 2, 2, "ST_Mean", "ignore")

Three difficulties must be solved to implement this function:

-The construction of the matrix must to be passed to the summarizing functions must be optimized when passing from one pixel to the other.

-We must see how it is possible to call a PL/pgSQL function from a C function

-We must see how to pass a variable number of parameter to a PL/pgSQL function

See also: Notes taken by David Zwarg during the Montreal Code Sprint 2011 and http://trac.osgeo.org/postgis/ticket/860

**Details for 3) ST_MapAlgebraFctNgb()**

This variant works on a tiled coverage. When computing values on the edge of a tile, it has the responsibility to provide out of range value contained in neighbour tiles to the user function.

Open question: What to do when there are more than one pixel (because they are overlapping) value neighbouring the current pixel? Could be another parameter saying take the 'MIN', the 'MAX', the 'FIRST', the 'LAST', the 'MEAN'...

**Details for 5) to 8) Two rasters versions**

- A simple PL/pgSQL prototype of the two raster version of ST_MapAlgebra() exists in http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_mapalgebra.sql. This version iterates over every pixel of the unionized extent of two rasters even if there are large areas where both rasters are absent and hence are interpreted as nodata values.

- The prototype of an optimized version, trying to set those large areas of nodata values as well as areas where only one raster is present (this is the case when unioning two contiguous non-overlapping rasters) as a block (not pixel by pixel) is still in development. See http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_mapalgebra_optimized.sql The idea is to set blocks of the resulting raster using ST_SetValues() (described in Objective 2.0.05) instead of processing one pixel at a time. This is somewhat similar to setting a large block of memory with memcpy() or memset() rather than setting a buffer one value at a time. The resulting raster is divided into rectangular block with the _MapAlgebraParts() function.

- Both rasters must have the same SRID. If not, ST_MapAlgebra() should return an error: "ERROR: Operation on two geometries with different SRIDs".

- An optional option could be added to allow specifying which raster, between the first and the second, is the MASTER raster. The MASTER raster is used to determine the pixeltype, the alignment and the nodatavalue of the resulting raster. If the MASTER raster do not have a nodata value defined and it is necessary to set one in the resulting raster then the minimal possible value for the pixeltype should be used as nodata value. In case this option would not be added or in case it is not passed to the function, the first raster should be used to determine those informations.

- Both raster must be aligned. This is determined using the ST_SameAlignment() function described below. If a resampling is necessary they use the planned ST_Resample() function to resample the second raster to the first one before processing (or the first to the second if a MASTER parameter is provided). If ST_Resample() is not yet implemented when these functions are implemented, just return an error message: "ERROR: MapAlgebra on rasters with different alignment not yet implemented."

- The computation extent (extentexpr in the functions below) and hence the extent of the resulting raster can be:

-'FIRST' (the extent of the first raster) (default),

-'SECOND' (the extent of the first raster),

-'INTERSECTION' (the extent of the intersection of both rasters) (could also be default),

-'UNION' (the extent of the union of both rasters).

- In certain cases, for example when the computation extent is set to 'INTERSECTION', it is better to reduce the execution of ST_Mapalgebra(rast1, rast2) to overlapping rasters. This reduction is performed by adding a 'WHERE ST_Intersects(raster, raster)' clause to the query (to be implemented). If such a clause is not used and the rasters don't overlap an empty raster is returned. This behaviour is similar to the one of ST_Intersection(). Hence:

SELECT ST_MapAlgebra(rast1, rast2, mathExpr) FROM mytable WHERE ST_Intersects(rast1, rast2)

should be much faster and return many less empty rasters than:

SELECT ST_MapAlgebra(rast1, rast2, mathExpr) FROM mytable

- The expression are evaluated by calling the equivalent or an EXECUTE SQL statement. “EXECUTE” in pl/pgsql or SPI_execute in C. This mechanism ensures that users can use any PostgreSQL function and operators in the expression as well as their own custom pl/pgsql functions. Raster values in the expressions are refered by “rast1” and “rast2”. Ex.: ST_MapAlgebra(raster1, band1, raster2, band2, “rast1 + cos(rast2) + 4”)

- Alternative expressions, evaluated in place of the main expression, can be provided as parameter to deal with nodata values:

-nodata1expr is evaluated when the first raster pixel value is nodata or when the first raster is absent from the pixel area being evaluated

-nodata2expr is evaluated when the second raster pixel value is nodata or when the second raster is absent from the pixel area being evaluated

-nodatanodataexpr is evaluated when both rasters pixel values are nodata or when the both rastera are absent from the pixel area being evaluated

-expression is evaluated when both raster pixel values are withdata.

All expressions default to NULL. When an expression is NULL every pixel filling the condition of this expression is set to NULL (nodata).

- Alternate expressions like nodata1expr and nodata2expr are used to simplify complex decision expression trying to deal with the presence of nodata value pixels. Having three short expressions like this:

'rast2', 'rast2', 'rast1'

is simpler to understand than a single complex expression dealing with nodata like this:

‘CASE WHEN rast2 IS NULL THEN rast1 ELSE rast2 END’

This is a simple case. In more complex cases, expressions can quickly get incomprehensible and alternate expressions greatly simplify the task of writing expressions, even if having three extra parameters may seams cumbersome.

- Open Question 1 (DZ): Should ST_MapAlgebra resample the resulting rasters internally, or should we let the users resample it afterward with ST_Resample: ST_Resample(ST_MapAlgebra(), originx, originy, pixelsizex, pixelsizey)

PR: I think this would greatly contribute to simplify the API.

- List of functions implementable based on the two raster version of map algebra:

-ST_Intersection(raster, raster) See at the end of plpgsql/st_mapalgebra.sql

-ST_Union (raster, raster) not the aggregate one. See at the end of plpgsql/st_mapalgebra.sql

-ST_Difference() and ST_SymDifference() See at the end of plpgsql/st_mapalgebra.sql

-ST_Intersection(raster, raster) See at the end of plpgsql/st_mapalgebra.sql

-ST_Clip(raster, geometry) as a wrapper around ST_Intersection(raster, ST_AsRaster(geometry))

-ST_BurnToRaster(): specifications below.

**Details for 5) ST_MapAlgebraExpr(rast1 raster, band1 integer, rast2 raster, band2 integer, expression text, pixeltype text, extentexpr text, nodata1expr text, nodata2expr text, nodatanodataexpr text) -> raster**

See discussion in http://trac.osgeo.org/postgis/ticket/590

**ST_SameAlignment(raster, raster) -> boolean - done see below**

**Objective FV.03 - ST_MapAlgebra (1 and 2 raster variants)**

**Two raster ST_MapAlgebra**

These set of functions take two input rasters and create a 1-band output raster with the extent defined by extenttype.

- ST_MapAlgebraExpr(

rast1 raster, band1 integer,

rast2 raster, band2 integer,

expression text,

pixeltype text DEFAULT NULL, extenttype text DEFAULT 'INTERSECTION',

nodata1expr text DEFAULT NULL, nodata2expr text DEFAULT NULL,

nodatanodataval double precision DEFAULT NULL

) -> raster

rast1: the FIRST raster upon which a map algebra operation is to be done

band1: the band index (1-based) of the FIRST raster upon whose pixels a map algebra operation is to be done

rast2: the SECOND raster upon which a map algebra operation is to be done

band2: the band index (1-based) of the SECOND raster upon whose pixels a map algebra operation is to be done

expression: valid SQL expression resulting in a double precision value or NULL. called when band 1 pixel AND band 2 pixel are NOT NODATA

pixeltype: the datatype of the output raster's one band

extenttype: one of the following (INTERSECTION, UNION, FIRST, SECOND)

nodata1expr: valid SQL expression resulting in a double precision value or NULL. called when band 1 pixel IS NODATA and band 2 pixel IS NOT NODATA

nodata2expr: valid SQL expression resulting in a double precision value or NULL. called when band 1 pixel IS NOT NODATA and band 2 pixel IS NODATA

nodatanodataval: double precision value used when band1 pixel and band 2 pixel are NODATA

ST_MapAlgebraExpr(r1.rast, 1, r2.rast, 2, 'rast1', '32BF', 'INTERSECTION') ST_MapAlgebraExpr(r1.rast, 1, r2.rast, 2, '((rast1 + rast2)/2.)::numeric', '32BF', 'UNION', 'rast2', 'rast1', NULL) ST_MapAlgebraExpr(r1.rast, 1, r2.rast, 2, 'CASE WHEN rast2 IS NOT NULL THEN NULL ELSE rast1 END', '32BF', 'FIRST', NULL, 'rast1', NULL) ST_MapAlgebraExpr(r1.rast, 1, r2.rast, 2, 'CASE WHEN rast1 IS NOT NULL THEN NULL ELSE rast2 END', '32BF', 'SECOND', 'rast2', NULL, NULL)

- ST_MapAlgebraExpr(

rast1 raster,

rast2 raster,

expression text,

pixeltype text DEFAULT NULL, extenttype text DEFAULT 'INTERSECTION',

nodata1expr text DEFAULT NULL, nodata2expr text DEFAULT NULL,

nodatanodataval double precision DEFAULT NULL

) -> raster

ST_MapAlgebraExpr(r1.rast, r2.rast, 'rast1', '32BF', 'INTERSECTION') ST_MapAlgebraExpr(r1.rast, r2.rast, '((rast1 + rast2)/2.)::numeric', '32BF', 'UNION', 'rast2', 'rast1', NULL) ST_MapAlgebraExpr(r1.rast, r2.rast, 'CASE WHEN rast2 IS NOT NULL THEN NULL ELSE rast1 END', '32BF', 'FIRST', NULL, 'rast1', NULL) ST_MapAlgebraExpr(r1.rast, r2.rast, 'CASE WHEN rast1 IS NOT NULL THEN NULL ELSE rast2 END', '32BF', 'SECOND', 'rast2', NULL, NULL)

This set of 2-raster ST_MapAlgebra functions require a user-provided function instead of expressions. The user-provided function must receive three parameters (two double precision and one VARIADIC text array) and returns one double precision value. A template for this user-provided function would be:

CREATE OR REPLACE FUNCTION userfunction(rast1 double precision, rast2 double precision, VARIADIC arg text[]) RETURNS double precision AS $$ DECLARE BEGIN -- your code here END; $$ LANGUAGE 'plpgsql';

The function should be able to support a NULL input parameter. The function may also be STRICT.

- ST_MapAlgebraFct(

rast1 raster, band1 integer,

rast2 raster, band2 integer,

userfunction regprocedure,

pixeltype text DEFAULT NULL, extenttype text DEFAULT 'INTERSECTION',

VARIADIC userargs text[] DEFAULT NULL

)

ST_MapAlgebraFct(r1.rast, 1, r2.rast, 1, 'raster_mapalgebra_intersection(double precision, double precision, text[])'::regprocedure, '32BF', 'INTERSECTION')

- ST_MapAlgebraFct(

rast1 raster,

rast2 raster,

userfunction regprocedure,

pixeltype text DEFAULT NULL, extenttype text DEFAULT 'INTERSECTION',

VARIADIC userargs text[] DEFAULT NULL

)

ST_MapAlgebraFct(r1.rast, r2.rast, 'raster_mapalgebra_intersection(double precision, double precision, text[])'::regprocedure, '32BF', 'INTERSECTION')

**Objective FV.14 - Being able to intersect two rasters to get a raster.**

**ST_Intersection(raster, integer, raster, integer) -> raster** - Returns a two bands raster with values only in the intersecting areas of both rasters. Integer parameters are the band number of the raster.

Please look at ST_Intersection(raster, geometry) in FV.01 for complete specs.

~~ ~~**Variants**

~~ 1) ST_Intersection(raster, integer, raster, integer) -> raster -- the integer parameters are the band number of the rasters~~ 2) ST_Intersection(raster, raster, integer) -> raster -- default first raster to band # 1

~~ 3) ST_Intersection(raster, integer, raster) -> raster -- default second raster to band # 1~~ 4) ST_Intersection(raster, raster) -> raster -- default both rasters to band # 1

**Objective FV.16 - Being able to quickly get raster statistics. - ***Done*

*Done*

**Add cached basic raster statistic to the base raster WKB format.
**

Statistics to be cached should include:

min/max

mean

standard deviation

histogram

build parameters of stats (sample rate, method used to determine # of bins in histogram?)

How are the statistics to be kept fresh? Automatically using some method to see how much of the raster has changed since the last stats calculation? Or let the user decide?

**ST_SummaryStats(raster, nband) -> record**

This is the core function that gets the summary statistics (# of values, mean, standard deviation, minimum value, maximum value) of a specified raster's band. It is this function that ST_Mean, ST_StdDev and ST_MinMax calls for their appropriate values.

- ST_SummaryStats(rast raster, nband int, exclude_nodata_value boolean) -> record

returns one record of five columns (count, mean, stddev, min, max)

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included in the stats. if TRUE, nodata values are not included

ST_SummaryStats(rast, 1, FALSE)

- ST_SummaryStats(rast raster, nband int) -> record

assumes exclude_nodata_value = TRUE

ST_SummaryStats(rast, 2)

- ST_SummaryStats(rast raster, exclude_nodata_value boolean) -> record

assumes nband = 1

ST_SummaryStats(rast, TRUE)

- ST_SummaryStats(rast raster) -> record

assumes nband = 1 and exclude_nodata_value = TRUE

ST_SummaryStats(rast)

Due to the time it may take to do on-the-fly calculation of summary stats for large rasters (say 10000 x 10000), an alternative that sacrifices accuracy for speed is required. The following functions sample a percentage of the raster in a methodical randomized manner. The algorithm used for sampling is...

- select the larger dimension of the width and height. compute the number of pixels to sample in each "row" of the larger dimension

- pick pixels from each "row" of the larger dimension in an incremental rolling manner where each increment is randomly determined.

The set of ST_ApproxSummaryStats functions are:

- ST_ApproxSummaryStats(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> record

sample_percent: a value between 0 and 1 indicating the percentage of the raster band's pixels to consider

ST_ApproxSummaryStats(rast, 3, FALSE, 0.1) ST_ApproxSummaryStats(rast, 1, TRUE, 0.5)

- ST_ApproxSummaryStats(rast raster, nband int, sample_percent double precision) -> record

assumes that nband = 1

ST_ApproxSummaryStats(rast, 2 0.01) ST_ApproxSummaryStats(rast, 4, 0.025)

- ST_ApproxSummaryStats(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> record

assumes that nband = 1

ST_ApproxSummaryStats(rast, FALSE, 0.01) ST_ApproxSummaryStats(rast, TRUE, 0.025)

- ST_ApproxSummaryStats(rast raster, sample_percent double precision) -> record

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxSummaryStats(rast, 0.25)

- ST_ApproxSummaryStats(rast raster) -> record

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxSummaryStats(rast)

The situation arises where the summary statistics of a coverage table is required. As the coverage may be large (tens of gigabytes of memory or larger), the following functions are provided to permit an incremental computation of the summary statistics.

- ST_SummaryStats(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> record

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_SummaryStats('tmax_2010', 'rast', 1, FALSE) ST_SummaryStats('precip_2011', 'rast', 1, TRUE)

- ST_SummaryStats(rastertable text, rastercolumn text, nband int) -> record

exclude_nodata_value = TRUE

ST_SummaryStats('tmax_2010', 'rast', 1)

- ST_SummaryStats(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> record

nband = 1

ST_SummaryStats('precip_2011', 'rast', TRUE)

- ST_SummaryStats(rastertable text, rastercolumn text) -> record

nband = 1 and exclude_nodata_value = TRUE

ST_SummaryStats('tmin_2009', 'rast')

Variations for ST_ApproxSummaryStats are:

- ST_ApproxSummaryStats(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> record

ST_ApproxSummaryStats('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxSummaryStats('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxSummaryStats(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> record

exclude_nodata_value = TRUE

ST_ApproxSummaryStats('tmax_2010', 'rast', 1, 0.5) ST_ApproxSummaryStats('precip_2011', 'rast', 1, 0.2)

- ST_ApproxSummaryStats(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> record

nband = 1

ST_ApproxSummaryStats('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxSummaryStats('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxSummaryStats(rastertable text, rastercolumn text, sample_percent double precision) -> record

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxSummaryStats('tmax_2010', 'rast', 0.5) ST_ApproxSummaryStats('precip_2011', 'rast', 0.2)

- ST_ApproxSummaryStats(rastertable text, rastercolumn text) -> record

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxSummaryStats('tmax_2010', 'rast') ST_ApproxSummaryStats('precip_2011', 'rast')

The mean returned in the coverage functions (has rastertable and rastercolumn arguments) is the true mean of the raster tiles. The standard deviation returned is the standard deviation of all raster tiles.

**ST_Count(raster, nband) -> bigint**

This function calls ST_SummaryStats and only returns the count from that function.

- ST_Count(rast raster, nband int, exclude_nodata_value boolean) -> bigint

returns the count as an integer

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included in the stats. if TRUE, nodata values are not included

ST_Count(rast, 1, FALSE)

- ST_Count(rast raster, nband int) -> bigint

assumes exclude_nodata_value = TRUE

ST_Count(rast, 2)

- ST_Count(rast raster, exclude_nodata_value boolean) -> bigint

assumes nband = 1

ST_Count(rast, TRUE)

- ST_Count(rast raster) -> bigint

assumes nband = 1 and exclude_nodata_value = TRUE

ST_Count(rast)

The set of ST_ApproxCount functions are:

- ST_ApproxCount(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> bigint

sample_percent: a value between 0 and 1 indicating the percentage of the raster band's pixels to consider

ST_ApproxCount(rast, 3, FALSE, 0.1) ST_ApproxCount(rast, 1, TRUE, 0.5)

- ST_ApproxCount(rast raster, nband int, sample_percent double precision) -> bigint

assumes that nband = 1

ST_ApproxCount(rast, 2 0.01) ST_ApproxCount(rast, 4, 0.025)

- ST_ApproxCount(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> bigint

assumes that nband = 1

ST_ApproxCount(rast, FALSE, 0.01) ST_ApproxCount(rast, TRUE, 0.025)

- ST_ApproxCount(rast raster, sample_percent double precision) -> bigint

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxCount(rast, 0.25)

- ST_ApproxCount(rast raster) -> bigint

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxCount(rast)

The following functions are provided for coverage tables.

- ST_Count(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> bigint

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_Count('tmax_2010', 'rast', 1, FALSE) ST_Count('precip_2011', 'rast', 1, TRUE)

- ST_Count(rastertable text, rastercolumn text, nband int) -> bigint

exclude_nodata_value = TRUE

ST_Count('tmax_2010', 'rast', 1)

- ST_Count(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> bigint

nband = 1

ST_Count('precip_2011', 'rast', TRUE)

- ST_Count(rastertable text, rastercolumn text) -> bigint

nband = 1 and exclude_nodata_value = TRUE

ST_Count('tmin_2009', 'rast')

Variations for ST_ApproxCount are:

- ST_ApproxCount(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> bigint

ST_ApproxCount('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxCount('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxCount(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> bigint

exclude_nodata_value = TRUE

ST_ApproxCount('tmax_2010', 'rast', 1, 0.5) ST_ApproxCount('precip_2011', 'rast', 1, 0.2)

- ST_ApproxCount(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> bigint

nband = 1

ST_ApproxCount('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxCount('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxCount(rastertable text, rastercolumn text, sample_percent double precision) -> bigint

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxCount('tmax_2010', 'rast', 0.5) ST_ApproxCount('precip_2011', 'rast', 0.2)

- ST_ApproxCount(rastertable text, rastercolumn text) -> bigint

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxCount('tmax_2010', 'rast') ST_ApproxCount('precip_2011', 'rast')

**ST_Sum(raster, nband) -> double precision**

This function calls ST_SummaryStats and only returns the sum from that function.

- ST_Sum(rast raster, nband int, exclude_nodata_value boolean) -> double precision

returns the sum as an integer

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included in the stats. if TRUE, nodata values are not included

ST_Sum(rast, 1, FALSE)

- ST_Sum(rast raster, nband int) -> double precision

assumes exclude_nodata_value = TRUE

ST_Sum(rast, 2)

- ST_Sum(rast raster, exclude_nodata_value boolean) -> double precision

assumes nband = 1

ST_Sum(rast, TRUE)

- ST_Sum(rast raster) -> double precision

assumes nband = 1 and exclude_nodata_value = TRUE

ST_Sum(rast)

The set of ST_ApproxSum functions are:

- ST_ApproxSum(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

sample_percent: a value between 0 and 1 indicating the percentage of the raster band's pixels to consider

ST_ApproxSum(rast, 3, FALSE, 0.1) ST_ApproxSum(rast, 1, TRUE, 0.5)

- ST_ApproxSum(rast raster, nband int, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxSum(rast, 2 0.01) ST_ApproxSum(rast, 4, 0.025)

- ST_ApproxSum(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxSum(rast, FALSE, 0.01) ST_ApproxSum(rast, TRUE, 0.025)

- ST_ApproxSum(rast raster, sample_percent double precision) -> double precision

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxSum(rast, 0.25)

- ST_ApproxSum(rast raster) -> double precision

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxSum(rast)

The following functions are provided for coverage tables.

- ST_Sum(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> double precision

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_Sum('tmax_2010', 'rast', 1, FALSE) ST_Sum('precip_2011', 'rast', 1, TRUE)

- ST_Sum(rastertable text, rastercolumn text, nband int) -> double precision

exclude_nodata_value = TRUE

ST_Sum('tmax_2010', 'rast', 1)

- ST_Sum(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> double precision

nband = 1

ST_Sum('precip_2011', 'rast', TRUE)

- ST_Sum(rastertable text, rastercolumn text) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_Sum('tmin_2009', 'rast')

Variations for ST_ApproxSum are:

- ST_ApproxSum(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

ST_ApproxSum('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxSum('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxSum(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> double precision

exclude_nodata_value = TRUE

ST_ApproxSum('tmax_2010', 'rast', 1, 0.5) ST_ApproxSum('precip_2011', 'rast', 1, 0.2)

- ST_ApproxSum(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> double precision

nband = 1

ST_ApproxSum('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxSum('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxSum(rastertable text, rastercolumn text, sample_percent double precision) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxSum('tmax_2010', 'rast', 0.5) ST_ApproxSum('precip_2011', 'rast', 0.2)

- ST_ApproxSum(rastertable text, rastercolumn text) -> double precision

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxSum('tmax_2010', 'rast') ST_ApproxSum('precip_2011', 'rast')

**ST_Mean(raster, nband) -> double precision**

This function calls ST_SummaryStats and only returns the mean from that function.

- ST_Mean(rast raster, nband int, exclude_nodata_value boolean) -> double precision

returns the mean as a double precision

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included. if TRUE, nodata values are not included.

ST_Mean(rast, 1, FALSE)

- ST_Mean(rast raster, nband int) -> double precision

assumes exclude_nodata_value = TRUE

ST_Mean(rast, 2)

- ST_Mean(rast raster, exclude_nodata_value boolean) -> double precision

assumes nband = 1

ST_Mean(rast, TRUE)

- ST_Mean(rast raster) -> double precision

assumes nband = 1 and exclude_nodata_value = TRUE

ST_Mean(rast)

The set of ST_ApproxMean functions are:

- ST_ApproxMean(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

ST_ApproxMean(rast, 3, FALSE, 0.1) ST_ApproxMean(rast, 1, TRUE, 0.5)

- ST_ApproxMean(rast raster, nband int, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxMean(rast, 2 0.01) ST_ApproxMean(rast, 4, 0.025)

- ST_ApproxMean(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxMean(rast, FALSE, 0.01) ST_ApproxMean(rast, TRUE, 0.025)

- ST_ApproxMean(rast raster, sample_percent double precision) -> double precision

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxMean(rast, 0.25)

- ST_ApproxMean(rast raster) -> double precision

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxMean(rast)

The following functions are provided for coverage tables.

- ST_Mean(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> double precision

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_Mean('tmax_2010', 'rast', 1, FALSE) ST_Mean('precip_2011', 'rast', 1, TRUE)

- ST_Mean(rastertable text, rastercolumn text, nband int) -> double precision

exclude_nodata_value = TRUE

ST_Mean('tmax_2010', 'rast', 1)

- ST_Mean(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> double precision

nband = 1

ST_Mean('precip_2011', 'rast', TRUE)

- ST_Mean(rastertable text, rastercolumn text) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_Mean('tmin_2009', 'rast')

Variations for ST_ApproxMean are:

- ST_ApproxMean(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

ST_ApproxMean('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxMean('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxMean(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> double precision

exclude_nodata_value = TRUE

ST_ApproxMean('tmax_2010', 'rast', 1, 0.5) ST_ApproxMean('precip_2011', 'rast', 1, 0.2)

- ST_ApproxMean(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> double precision

nband = 1

ST_ApproxMean('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxMean('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxMean(rastertable text, rastercolumn text, sample_percent double precision) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxMean('tmax_2010', 'rast', 0.5) ST_ApproxMean('precip_2011', 'rast', 0.2)

- ST_ApproxMean(rastertable text, rastercolumn text) -> double precision

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxMean('tmax_2010', 'rast') ST_ApproxMean('precip_2011', 'rast')

The mean returned in the coverage functions (has rastertable and rastercolumn arguments) is the true mean of the raster tiles.

**ST_StdDev(raster, nband) -> double precision**

This function calls ST_SummaryStats and only returns the standard deviation from that function.

- ST_StdDev(rast raster, nband int, exclude_nodata_value boolean) -> double precision

returns the standard deviation as a double precision

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included. if TRUE, nodata values are not included.

ST_StdDev(rast, 1, FALSE)

- ST_StdDev(rast raster, nband int) -> double precision

assumes exclude_nodata_value = TRUE

ST_StdDev(rast, 2)

- ST_StdDev(rast raster, exclude_nodata_value boolean) -> double precision

assumes nband = 1

ST_StdDev(rast, TRUE)

- ST_StdDev(rast raster) -> double precision

assumes nband = 1 and exclude_nodata_value = TRUE

ST_StdDev(rast)

The set of ST_ApproxStdDev functions are:

- ST_ApproxStdDev(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

ST_ApproxStdDev(rast, 3, FALSE, 0.1) ST_ApproxStdDev(rast, 1, TRUE, 0.5)

- ST_ApproxStdDev(rast raster, nband int, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxStdDev(rast, 2 0.01) ST_ApproxStdDev(rast, 4, 0.025)

- ST_ApproxStdDev(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> double precision

assumes that nband = 1

ST_ApproxStdDev(rast, FALSE, 0.01) ST_ApproxStdDev(rast, TRUE, 0.025)

- ST_ApproxStdDev(rast raster, sample_percent double precision) -> double precision

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxStdDev(rast, 0.25)

- ST_ApproxStdDev(rast raster) -> double precision

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxStdDev(rast)

The following functions are provided for coverage tables.

- ST_StdDev(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> double precision

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_StdDev('tmax_2010', 'rast', 1, FALSE) ST_StdDev('precip_2011', 'rast', 1, TRUE)

- ST_StdDev(rastertable text, rastercolumn text, nband int) -> double precision

exclude_nodata_value = TRUE

ST_StdDev('tmax_2010', 'rast', 1)

- ST_StdDev(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> double precision

nband = 1

ST_StdDev('precip_2011', 'rast', TRUE)

- ST_StdDev(rastertable text, rastercolumn text) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_StdDev('tmin_2009', 'rast')

Variations for ST_ApproxStdDev are:

- ST_ApproxStdDev(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> double precision

ST_ApproxStdDev('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxStdDev('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxStdDev(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> double precision

exclude_nodata_value = TRUE

ST_ApproxStdDev('tmax_2010', 'rast', 1, 0.5) ST_ApproxStdDev('precip_2011', 'rast', 1, 0.2)

- ST_ApproxStdDev(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> double precision

nband = 1

ST_ApproxStdDev('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxStdDev('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxStdDev(rastertable text, rastercolumn text, sample_percent double precision) -> double precision

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxStdDev('tmax_2010', 'rast', 0.5) ST_ApproxStdDev('precip_2011', 'rast', 0.2)

- ST_ApproxStdDev(rastertable text, rastercolumn text) -> double precision

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxStdDev('tmax_2010', 'rast') ST_ApproxStdDev('precip_2011', 'rast')

The standard deviation returned in the coverage functions (has rastertable and rastercolumn arguments) is the standard deviation of all raster tiles.

**ST_MinMax(raster, nband) -> record**

This function calls ST_SummaryStats and only returns the min and max values from that function.

- ST_MinMax(rast raster, nband int, exclude_nodata_value boolean) -> record

returns the record (min double precision, max double precision)

nband: index of band

exclude_nodata_value: if FALSE, nodata values in band are included. if TRUE, nodata values are not included.

ST_MinMax(rast, 1, FALSE)

- ST_MinMax(rast raster, nband int) -> record

assumes exclude_nodata_value = TRUE

ST_MinMax(rast, 2)

- ST_MinMax(rast raster, exclude_nodata_value boolean) -> record

assumes nband = 1

ST_MinMax(rast, TRUE)

- ST_MinMax(rast raster) -> record

assumes nband = 1 and exclude_nodata_value = TRUE

ST_MinMax(rast)

The set of ST_ApproxMinMax functions are:

- ST_ApproxMinMax(rast raster, nband int, exclude_nodata_value boolean, sample_percent record) -> record

ST_ApproxMinMax(rast, 3, FALSE, 0.1) ST_ApproxMinMax(rast, 1, TRUE, 0.5)

- ST_ApproxMinMax(rast raster, nband int, sample_percent double precision) -> record

assumes that nband = 1

ST_ApproxMinMax(rast, 2 0.01) ST_ApproxMinMax(rast, 4, 0.025)

- ST_ApproxMinMax(rast raster, exclude_nodata_value boolean, sample_percent double precision) -> record

assumes that nband = 1

ST_ApproxMinMax(rast, FALSE, 0.01) ST_ApproxMinMax(rast, TRUE, 0.025)

- ST_ApproxMinMax(rast raster, sample_percent double precision) -> record

assumes that nband = 1 and exclude_nodata_value = TRUE

ST_ApproxMinMax(rast, 0.25)

- ST_ApproxMinMax(rast raster) -> record

assumes that nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxMinMax(rast)

The following functions are provided for coverage tables.

- ST_MinMax(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean) -> record

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_MinMax('tmax_2010', 'rast', 1, FALSE) ST_MinMax('precip_2011', 'rast', 1, TRUE)

- ST_MinMax(rastertable text, rastercolumn text, nband int) -> record

exclude_nodata_value = TRUE

ST_MinMax('tmax_2010', 'rast', 1)

- ST_MinMax(rastertable text, rastercolumn text, exclude_nodata_value boolean) -> record

nband = 1

ST_MinMax('precip_2011', 'rast', TRUE)

- ST_MinMax(rastertable text, rastercolumn text) -> record

nband = 1 and exclude_nodata_value = TRUE

ST_MinMax('tmin_2009', 'rast')

Variations for ST_ApproxMinMax are:

- ST_ApproxMinMax(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision) -> record

ST_ApproxMinMax('tmax_2010', 'rast', 1, FALSE, 0.5) ST_ApproxMinMax('precip_2011', 'rast', 1, TRUE, 0.2)

- ST_ApproxMinMax(rastertable text, rastercolumn text, nband int, sample_percent double precision) -> record

exclude_nodata_value = TRUE

ST_ApproxMinMax('tmax_2010', 'rast', 1, 0.5) ST_ApproxMinMax('precip_2011', 'rast', 1, 0.2)

- ST_ApproxMinMax(rastertable text, rastercolumn text, exclude_nodata_value boolean, sample_percent double precision) -> record

nband = 1

ST_ApproxMinMax('tmax_2010', 'rast', FALSE, 0.5) ST_ApproxMinMax('precip_2011', 'rast', TRUE, 0.2)

- ST_ApproxMinMax(rastertable text, rastercolumn text, sample_percent double precision) -> record

nband = 1 and exclude_nodata_value = TRUE

ST_ApproxMinMax('tmax_2010', 'rast', 0.5) ST_ApproxMinMax('precip_2011', 'rast', 0.2)

- ST_ApproxMinMax(rastertable text, rastercolumn text) -> record

nband = 1, exclude_nodata_value = TRUE and sample_percent = 0.1

ST_ApproxMinMax('tmax_2010', 'rast') ST_ApproxMinMax('precip_2011', 'rast')

**ST_Histogram(raster, nband) -> set of records**

ST_Histogram and ST_ApproxHistogram provide methods to determine a raster's data distribution.

The return of ST_Histogram and ST_ApproxHistogram is a set of records where each record is (min, max, count, percent).

ST_Histogram has the following variations.

- ST_Histogram(rast raster, nband int, exclude_nodata_value boolean, bins int, width double precision[], right boolean) -> set of records

returns set of records of four columns (min, max, count, percent)

nband: index of band to process on

bins: the number of categories/bins to have in the histogram. If NULL or value less than one, the number of categories will be auto-computed using Sturges' formula if the number of values >= 30 or Square-root choice if number of values < 30.

http://en.wikipedia.org/wiki/Histogram#Mathematical_definition

width: an array indicating the width of each category/bin. If the number of bins is greater than the number of widths, the widths are repeated. Example: 9 bins, widths are [a, b, c] will have the output be [a, b, c, a, b, c, a, b, c].

right: compute the histogram from the right rather than from the left (default). This changes the criteria for evaluating a value x from [a, b) to (a, b].

ST_Histogram(rast, 2, FALSE, NULL, NULL, FALSE) ST_Histogram(rast, 1, TRUE, 100, NULL, FALSE) ST_Histogram(rast, 2, FALSE, NULL, ARRAY[100, 50], FALSE) ST_Histogram(rast, 2, FALSE, 9, ARRAY[1000], TRUE) ST_Histogram(rast, 2, FALSE, 20, ARRAY[100, 200, 300], TRUE)

- ST_Histogram(rast raster, nband int, exclude_nodata_value boolean, bins int, right boolean) -> set of records

parameter "width" is not specified thus resulting in all bins having the same widths

ST_Histogram(rast, 2, FALSE, 5, FALSE)

- ST_Histogram(rast raster, nband int, exclude_nodata_value boolean, bins int) -> set of records

the parameter "right" is removed and assumed to be FALSE

ST_Histogram(rast, 2, FALSE, 5)

- ST_Histogram(rast raster, nband int, exclude_nodata_value boolean) -> set of records

the parameter "bins" is removed and set to NULL. The function will compute the number of bins to use

- ST_Histogram(rast raster, nband int) -> set of records

exclude_nodata_value is assumed to be TRUE

- ST_Histogram(rast raster) -> set of records

assumes that nband is 1.

- ST_Histogram(rast raster, nband int, bins int, width double precision[], right boolean) -> set of records

exclude_nodata_value is assumed to be TRUE

- ST_Histogram(rast raster, nband int, bins int, right boolean) -> set of records

all bins will have equal widths

- ST_Histogram(rast raster, nband int, bins int) -> set of records

right is assumed to be FALSE

ST_ApproxHistogram should have the following variations.

- ST_ApproxHistogram(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision, bins int, width double precision[], right boolean) -> set of record

sample_percent: a value between 0 and 1 indicating the percentage of the raster band's pixels to consider when generating the histogram.

ST_Histogram(rast, 2, FALSE, 0.1, NULL, NULL, FALSE) ST_Histogram(rast, 1, TRUE, 1, 100, NULL, FALSE) ST_Histogram(rast, 2, FALSE, 0.2, NULL, ARRAY[100, 50], FALSE) ST_Histogram(rast, 2, FALSE, 0.25, 9, ARRAY[1000], TRUE) ST_Histogram(rast, 2, FALSE, 0.05, 20, ARRAY[100, 200, 300], TRUE)

- ST_ApproxHistogram(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision, bins int, right boolean) -> set of records

parameter "width" is not specified thus resulting in all bins having the same widths

- ST_ApproxHistogram(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision, bins int) -> set of records

the parameter "right" is removed and assumed to be FALSE

ST_ApproxHistogram(rast, 2, FALSE, 5)

- ST_ApproxHistogram(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision) -> set of records

the parameter "bins" is removed and set to NULL so that function can compute the number of bins to use

- ST_ApproxHistogram(rast raster, nband int, sample_percent double precision) -> set of records

exclude_nodata_value is assumed to be TRUE

- ST_ApproxHistogram(rast raster, nband int) -> set of records

assumes that sample_percent is 0.1

- ST_ApproxHistogram(rast raster, sample_percent double_precision) -> set of records

assumes that nband is 1

- ST_ApproxHistogram(rast raster) -> set of records

assumes that nband is 1 and sample_percent is 0.1

- ST_ApproxHistogram(rast raster, nband int, sample_percent double precision, bins int, width double precision[], right boolean) -> set of records

exclude_nodata_value is assumed to be TRUE

- ST_ApproxHistogram(rast raster, nband int, sample_percent double precision, bins int, right boolean) -> set of records

all bins will have equal widths

- ST_ApproxHistogram(rast raster, nband int, sample_percent double precision, bins int) -> set of records

right is assumed to be FALSE

The following set of function are for coverages.

- ST_Histogram(rastertable text, rastercolumn text, nband int DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, bins int default 0, width double precision[] DEFAULT NULL, right boolean DEFAULT FALSE) -> set of records

rastertable: name of table with raster column

rastercolumn: name of column of data type raster

ST_Histogram('tmax_2010', 'rast') ST_Histogram('precip_2011', 'rast', 1) ST_Histogram('precip_2011', 'rast', 1, FALSE) ST_Histogram('precip_2011', 'rast', 1, FALSE, 5) ST_Histogram('precip_2011', 'rast', 1, FALSE, 10, NULL) ST_Histogram('precip_2011', 'rast', 1, FALSE, 10, NULL, TRUE)

- ST_Histogram(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, bins int, right boolean) -> set of records

ST_Histogram('tmin_2010', 'rast', 2, FALSE, 5, FALSE)

- ST_Histogram(rastertable text, rastercolumn text, nband int, bins int, width double precision[] DEFAULT NULL, right boolean DEFAULT FALSE) -> set of records

ST_Histogram('ndvi_2010', 'rast', 1, 5) ST_Histogram('ndvi_2010', 'rast', 1, 0, ARRAY[0.5]::double precision[]) ST_Histogram('ndvi_2010', 'rast', 1, 5, NULL, TRUE)

- ST_Histogram(rastertable text, rastercolumn text, nband int, bins int, right boolean) -> set of records

ST_Histogram('veg_2009', 'rast', 2, 3, FALSE) ST_Histogram('veg_2009', 'rast', 2, 3, TRUE)

A set of functions of ST_ApproxHistogram for coverage tables:

- ST_ApproxHistogram(rastertable text, rastercolumn text, nband int DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, sample_percent double precision DEFAULT 0.1, bins int DEFAULT 0, width double precision[] DEFAULT NULL, right boolean DEFAULT FALSE) -> set of records

ST_ApproxHistogram('precip_2010', 'rast') ST_ApproxHistogram('precip_2010', 'rast', 1) ST_ApproxHistogram('precip_2010', 'rast', 2, FALSE) ST_ApproxHistogram('precip_2010', 'rast', 1, TRUE, 0.25) ST_ApproxHistogram('precip_2010', 'rast', 3, FALSE, 0.2, 10) ST_ApproxHistogram('precip_2010', 'rast', 1, TRUE, 0.1, 0, ARRAY[1]::double precision[]) ST_ApproxHistogram('precip_2010', 'rast', 1, TRUE, 0.1, 0, ARRAY[1]::double precision[], FALSE)

- ST_ApproxHistogram(rastertable text, rastercolumn text, nband int, exclude_nodata_value boolean, sample_percent double precision, bins int, right boolean)

- ST_ApproxHistogram(rastertable text, rastercolumn text, nband int, sample_percent double precision)

- ST_ApproxHistogram(rastertable text, rastercolumn text, sample_percent double precision)

- ST_ApproxHistogram(rastertable text, rastercolumn text, nband int, sample_percent double precision, bins int, width double precision[] DEFAULT NULL, right boolean DEFAULT FALSE)

- ST_ApproxHistogram(rastertable text, rastercolumn text, nband int, sample_percent double precision, bins int, right boolean)

**ST_Quantile(raster, nband) -> set of records**

In addition to determining the histogram of a raster, providing the ability to compute quantiles permits a user to reference a value in the context of the sample or population. Thus, a value could be examined to be at the raster's 25%, 50%, 75% percentile.

http://en.wikipedia.org/wiki/Quantile

ST_Quantile variations:

- ST_Quantile(rast raster, nband int, exclude_nodata_value boolean, quantiles double precision[]) -> set of records

each row returned is of (quantile double precision, value double precision)

nband: index of band to process on

quantiles: array of percentages to compute values for

ST_Quantile(rast, 1, FALSE, ARRAY[0.1, 0.3, 0.7]) ST_Quantile(rast, 1, TRUE, ARRAY[0.2]) ST_Quantile(rast, 1, FALSE, ARRAY[0, 1])

- ST_Quantile(rast raster, nband int, quantiles double precision[]) -> set of records

"exclude_nodata_value" is assumed to be TRUE

ST_Quantile(rast, 1, ARRAY[0.1, 0.3, 0.7])

- ST_Quantile(rast raster, nband int, exclude_nodata_value boolean) -> set of records

"quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1]

- ST_Quantile(rast raster, nband int) -> set of records

"exclude_nodata_value" is assumed to be TRUE and "quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1]

- ST_Quantile(rast raster, quantiles double precision[]) -> set of records

"nband" is assumed to be 1 and "exclude_nodata_value" is TRUE

- ST_Quantile(rast raster) -> set of records

"nband" is assumed to be 1 and "quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1]

- ST_Quantile(rast raster, nband int, exclude_nodata_value boolean, quantile double precision) -> record

quantile: the single percentile to compute

- ST_Quantile(rast raster, nband int, quantile double precision) -> record

"exclude_nodata_value" is assumed to be TRUE

- ST_Quantile(rast raster, exclude_nodata_value boolean, quantile double precision) -> record

"nband" is assumed to be 1

- ST_Quantile(rast raster, quantile double precision) -> record

"nband" is assumed to be 1 and "exclude_nodata_value" is assumed to be TRUE

ST_ApproxQuantile adds a "sample_percent" indicating the percentage of the raster to sample

- ST_ApproxQuantile(rast raster, nband int, exclude_nodata_value boolean, sample_percent double precision, quantiles double precision[]) -> set of records

nband: index of band to process on

sample_percent: a value between 0 and 1 indicating the percentage of the raster band's pixels to consider when computing the quantiles

quantiles: array of percentages to compute values for

ST_ApproxQuantile(rast, 1, FALSE, 0.1, ARRAY[0.1, 0.3, 0.7]) ST_ApproxQuantile(rast, 1, TRUE, .2, ARRAY[0.2]) ST_ApproxQuantile(rast, 1, FALSE, 0.3, ARRAY[0, 1])

- ST_ApproxQuantile(rast raster, nband int, sample_percent double precision, quantiles double precision[]) -> set of records

"exclude_nodata_value" is assumed to be TRUE

ST_ApproxQuantile(rast, 1, .05, ARRAY[0.1, 0.3, 0.7])

- ST_ApproxQuantile(rast raster, nband int, sample_percent double precision) -> set of records

"exclude_nodata_value" is assumed to be TRUE and "quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1]

- ST_ApproxQuantile(rast raster, sample_percent double precision, quantiles double precision[]) -> set of records

"nband" is assumed to be 1

- ST_ApproxQuantile(rast raster, nband int, sample_percent double precision, quantile double precision) -> record

quantile: the single percentile to compute

- ST_ApproxQuantile(rast raster, sample_percent double precision, quantile double precision) -> record

"nband" is assumed to be 2

- ST_ApproxQuantile(rast raster, sample_percent double precision) -> set of records

"nband" is assumed to be 1 and "quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1]

- ST_ApproxQuantile(rast raster, nband int, quantile double precision) -> record

"sample_percent" assumed to be 0.1

- ST_ApproxQuantile(rast raster, quantiles double precision[]) -> set of records

"nband" is assumed to be 1 and "sample_percent" assumed to be 0.1

- ST_ApproxQuantile(rast raster, quantile double precision) -> record

"nband" assumed to be 1 and "sample_percent" assumed to be 0.1

- ST_ApproxQuantile(rast raster, nband int) -> set of records

"quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1] and "sample_percent" assumed to be 0.1

- ST_ApproxQuantile(rast raster) -> set of records

"nband" is assumed to be 1, "quantiles" assumed to be ARRAY[0, 0.25, 0.5, 0.75, 1] and "sample_percent" assumed to be 0.1

**Objective FV.03 - Implement all the necessary versions of ST_MapAlgebra**

**ST_SameAlignment(raster, raster) -> boolean**

This function returns true if both rasters' grids are aligned.

Two rasters grid are aligned if:

- They share the same pixel scales and skews

- At least one of any of the four corner of any pixel of one raster fall on any corner of the grid of the other raster.

Alignment is not the same concept as georeference. Two rasters can be aligned but not have the same georeference.

Rotation is important here since two rasters grid might look perfectly aligned but are not if their rotated are different.

Knowing if two rasters share the same alignment is useful when it is time to decide if one of them must be resampled before doing other operations (like ST_MapAlgebra on two rasters).

The variants of ST_SameAlignment are:

- ST_SameAlignment(rast1 raster, rast2 raster)

- ST_SameAlignment(ulx1, uly1, scalex1, scaley1, skewx1, skewy1, ulx2, uly2, scalex2, scaley2, skewx2, skewy2)

~~The first variant is useful to PL/pgSQL function which have already get the values of the parameters.
~~

**Implementation Details**

~~Only the first variant should be implemented in C. The second one is a PL/pgSQL variants. The C implementation should follow the PL/pgSQL version implemented in http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_mapalgebra.sql
~~

~~It is not clear if this PL/pgSQL implementation works when raster are rotated. To verify.
~~

~~See discussion in http://trac.osgeo.org/postgis/ticket/589
~~

~~Bborie:
~~

~~The variant that should be developed in C is (2) as it is more likely that someone will have two rasters instead of that large set of raster elements. Instead, (1) should be a pl/pgsql function calling (2).
~~

**Objective FV.05 - Being able to reproject a raster. - ***Done*

*Done*

**ST_Transform(raster|geometry, SRID) -> same type as input**

ST_Transform enables the end-user to reproject a raster to a new projection. Unlike the ST_Transform function for geometries, ST_Transform for rasters depends upon the specificiation of a resampling algorithm and an error tolerance. In addition, reprojecting a raster will probably change the scale of the pixels. Therefore, ST_Transform for rasters can be more involved than the ST_Transform for geometries.

*If a skewed raster is provided to ST_Transform, the output raster will be deskewed (zero skew in X and Y axes thus "north up"). To preserve the skew, use ST_Resample.*

- ST_Transform(rast raster, srid integer, algorithm text DEFAULT 'NearestNeighbour', maxerr double precision DEFAULT 0.125, scalex double precision DEFAULT 0, scaley double precision DEFAULT 0)

returns a new raster in the projection specified by "srid"

srid: the SRID of the projection to use when reprojecting the raster

algorithm: the resampling algorithm to use when reprojecting the raster. default is 'NearestNeighbour'. possible algorithms are:

NearestNeighbour (default. fastest performance but worst interpolation) NearestNeighbor (for those wanting to use the American spelling) Bilinear Cubic CubicSpline Lanczos

maxerr: the threshold for transformation approximation by the resampling algorithm (in pixel units). default is 0.125, which is the same value used in GDAL gdalwarp utility. if set to zero, no approximation takes place.

scalex: the reprojected raster's scale in the X axis. default is 0 indicating that the user isn't specifying the reprojected raster's scale

scaley: the reprojected raster's scale in the Y axis. default is 0 indicating that the user isn't specifying the reprojected raster's scale

ST_Transform(rast, 3310) ST_Transform(rast, 3310, 'Bilinear') ST_Transform(rast, 3310, 'Lanczos', 0) ST_Transform(rast, 3310, 'Lanczos', 0.5) ST_Transform(rast, 3310, 'Lanczos', 0.125, 1000) ST_Transform(rast, 3310, 'Lanczos', 0.125, 1000, 1000)

- ST_Transform(rast raster, srid integer, scalex double precision, scaley double precision, algorithm text DEFAULT 'NearestNeighbour', maxerr double precision DEFAULT 0.125)

ST_Transform(rast, 4326, 500, 500) ST_Transform(rast, 4326, 500, 500, 'Cubic') ST_Transform(rast, 4326, 500, 500, 'CubicSpline', 0)

- ST_Transform(rast raster, srid integer, scalexy double precision, algorithm text DEFAULT 'NearestNeighbour', maxerr double precision DEFAULT 0.125)

scalexy: the reprojected raster's scale in the X and Y axes. default is 0 indicating that the user isn't specifying the reprojected raster's scale

ST_Transform(rast, 4326, 250) ST_Transform(rast, 4326, 250, 'Cubic') ST_Transform(rast, 4326, 100, 'CubicSpline', 0)

**Objective FV.06 - Being able to do some base raster operations. - ***Done Partially*

*Done Partially*

**ST_ValueCount(raster, value) -> integer**

ST_ValueCount provides the ability to count the number of times that a user-provided value is present in a raster. To handle floating point values, a rounding argument is provided.

A set of functions for one or more search values:

*If NULL is passed for "searchvalues" to any of the ST_ValueCount variations with "searchvalues", the function returns the counts for all unique values*

- ST_ValueCount(rast raster, nband integer, exclude_nodata_value boolean, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

returns the number of times that each value in searchvalues is present in the raster

exclude_nodata_value: if FALSE, nodata values in band are considered in the count. if TRUE, nodata values are not considered

searchvalues: the set of values to count in the raster

roundto: the decimal position to round a pixel value to. Originally intended for use with 32BF and 64BF pixel types, it can also be used with integers when round to the tens, hundreds or higher place.

examples are...

roundto < 0: no rounding 0: no rounding 0.1: round to the tenths place 0.01: round to the hundredths place 0.001: round to the thousandths place 1: round to the ones place 10: round to the tens place 100: round to the hundreds place

ST_ValueCount(rast, 1, TRUE, ARRAY[23], 0) ST_ValueCount(rast, 5, FALSE, ARRAY[3.14], 0.01) ST_ValueCount(rast, 2, TRUE, ARRAY[100], 100) ST_ValueCount(rast, 1, FALSE, ARRAY[-9999, 0], 1) ST_ValueCount(rast, 1, FALSE, NULL::double precision[], 1)

- ST_ValueCount(rast raster, nband integer, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

exclude_nodata_value is assumed to be TRUE

ST_ValueCount(rast, 5, ARRAY[3.14], 0.01) ST_ValueCount(rast, 2, NULL::double precision[], 100)

- ST_ValueCount(rast raster, nband integer, searchvalues double precision[]) -> setof record (searchvalue, count)

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValueCount(rast, 1, ARRAY[-9999]) ST_ValueCount(rast, 1, NULL::double precision[])

- ST_ValueCount(rast raster, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValueCount(rast raster, searchvalues double precision[]) -> setof record (searchvalue, count)

nband is assumed to be 1. roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE.

A set of functions for a single search value:

- ST_ValueCount(rast raster, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision) -> integer

returns the number of times that searchvalue is present in the raster

searchvalue: the value to count in the raster

ST_ValueCount(rast, 1, TRUE, 23, 0) ST_ValueCount(rast, 5, FALSE, 3.14, 0.01) ST_ValueCount(rast, 2, TRUE, 100, 100) ST_ValueCount(rast, 1, FALSE, -9999, 1)

- ST_ValueCount(rast raster, nband integer, searchvalue double precision, roundto double precision) -> integer

exclude_nodata_value is assumed to be TRUE

ST_ValueCount(rast, 5, 3.14, 0.01) ST_ValueCount(rast, 2, 100, 100)

- ST_ValueCount(rast raster, nband integer, searchvalue double precision) -> integer

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValueCount(rast, 1, -9999)

- ST_ValueCount(rast raster, searchvalue double precision, roundto double precision) -> integer

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValueCount(rast raster, searchvalue double precision) -> integer

nband is assumed to be 1. roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE.

The set of functions for processing coverages return "bigint" instead of "integer".

A set of functions for one or more search values:

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

rastertable: name of the table with a raster column

rastercolumn: name of the raster column

ST_ValueCount('test', 'rast', 1, TRUE, ARRAY[23], 0) ST_ValueCount('test', 'rast', 5, FALSE, ARRAY[3.14], 0.01) ST_ValueCount('test', 'rast', 2, TRUE, ARRAY[100], 100) ST_ValueCount('test', 'rast', 1, FALSE, ARRAY[-9999, 0], 1) ST_ValueCount('test', 'rast', 1, FALSE, NULL::double precision[], 1)

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

exclude_nodata_value is assumed to be TRUE

ST_ValueCount('test', 'rast', 5, ARRAY[3.14], 0.01) ST_ValueCount('test', 'rast', 2, NULL::double precision[], 100)

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, searchvalues double precision[]) -> setof record (searchvalue, count)

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValueCount('test', 'rast', 1, ARRAY[-9999]) ST_ValueCount('test', 'rast', 1, NULL::double precision[])

- ST_ValueCount(rastertable text, rastercolumn text, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, count)

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValueCount(rastertable text, rastercolumn text, searchvalues double precision[]) -> setof record (searchvalue, count)

nband is assumed to be 1. roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE.

A set of functions for a single search value:

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision) -> bigint

searchvalue: the value to count in the raster

ST_ValueCount('test', 'rast', 1, TRUE, 23, 0) ST_ValueCount('test', 'rast', 5, FALSE, 3.14, 0.01) ST_ValueCount('test', 'rast', 2, TRUE, 100, 100) ST_ValueCount('test', 'rast', 1, FALSE, -9999, 1)

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, searchvalue double precision, roundto double precision) -> bigint

exclude_nodata_value is assumed to be TRUE

ST_ValueCount('test', 'rast', 5, 3.14, 0.01) ST_ValueCount('test', 'rast', 2, 100, 100)

- ST_ValueCount(rastertable text, rastercolumn text, nband integer, searchvalue double precision) -> bigint

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValueCount('test', 'rast', 1, -9999)

- ST_ValueCount(rastertable text, rastercolumn text, searchvalue double precision, roundto double precision) -> bigint

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValueCount(rastertable text, rastercolumn text, searchvalue double precision) -> bigint

**ST_ValuePercent(raster, value) -> double precision**

ST_ValuePercent is the sibling of ST_ValueCount and returns the percentage of a raster's band that is of a specified value. To handle floating point values, a rounding argument is provided.

A set of functions for one or more search values:

*If NULL is passed for "searchvalues" to any of the ST_ValuePercent variations with "searchvalues", the function returns the percents for all unique values*

- ST_ValuePercent(rast raster, nband integer, exclude_nodata_value boolean, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

returns the percentage of a raster's band that each value in searchvalues is found

exclude_nodata_value: if FALSE, nodata values in band are considered in the percents. if TRUE, nodata values are not considered

searchvalues: the set of values to get percents for in the raster

roundto: the decimal position to round a pixel value to. Originally intended for use with 32BF and 64BF pixel types, it can also be used with integers when round to the tens, hundreds or higher place.

examples are...

roundto < 0: no rounding 0: no rounding 0.1: round to the tenths place 0.01: round to the hundredths place 0.001: round to the thousandths place 1: round to the ones place 10: round to the tens place 100: round to the hundreds place

ST_ValuePercent(rast, 1, TRUE, ARRAY[23], 0) ST_ValuePercent(rast, 5, FALSE, ARRAY[3.14], 0.01) ST_ValuePercent(rast, 2, TRUE, ARRAY[100], 100) ST_ValuePercent(rast, 1, FALSE, ARRAY[-9999, 0], 1) ST_ValuePercent(rast, 1, FALSE, NULL::double precision[], 1)

- ST_ValuePercent(rast raster, nband integer, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

exclude_nodata_value is assumed to be TRUE

ST_ValuePercent(rast, 5, ARRAY[3.14], 0.01) ST_ValuePercent(rast, 2, NULL::double precision[], 100)

- ST_ValuePercent(rast raster, nband integer, searchvalues double precision[]) -> setof record (searchvalue, percent)

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValuePercent(rast, 1, ARRAY[-9999]) ST_ValuePercent(rast, 1, NULL::double precision[])

- ST_ValuePercent(rast raster, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValuePercent(rast raster, searchvalues double precision[]) -> setof record (searchvalue, percent)

A set of functions for a single search value:

- ST_ValuePercent(rast raster, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision) -> integer

searchvalue: the value to get a percent for in the raster

ST_ValuePercent(rast, 1, TRUE, 23, 0) ST_ValuePercent(rast, 5, FALSE, 3.14, 0.01) ST_ValuePercent(rast, 2, TRUE, 100, 100) ST_ValuePercent(rast, 1, FALSE, -9999, 1)

- ST_ValuePercent(rast raster, nband integer, searchvalue double precision, roundto double precision) -> integer

exclude_nodata_value is assumed to be TRUE

ST_ValuePercent(rast, 5, 3.14, 0.01) ST_ValuePercent(rast, 2, 100, 100)

- ST_ValuePercent(rast raster, nband integer, searchvalue double precision) -> integer

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValuePercent(rast, 1, -9999)

- ST_ValuePercent(rast raster, searchvalue double precision, roundto double precision) -> integer

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValuePercent(rast raster, searchvalue double precision) -> integer

The set of functions for processing coverages return "bigint" instead of "integer".

A set of functions for one or more search values:

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

rastertable: name of the table with a raster column

rastercolumn: name of the raster column

ST_ValuePercent('test', 'rast', 1, TRUE, ARRAY[23], 0) ST_ValuePercent('test', 'rast', 5, FALSE, ARRAY[3.14], 0.01) ST_ValuePercent('test', 'rast', 2, TRUE, ARRAY[100], 100) ST_ValuePercent('test', 'rast', 1, FALSE, ARRAY[-9999, 0], 1) ST_ValuePercent('test', 'rast', 1, FALSE, NULL::double precision[], 1)

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

exclude_nodata_value is assumed to be TRUE

ST_ValuePercent('test', 'rast', 5, ARRAY[3.14], 0.01) ST_ValuePercent('test', 'rast', 2, NULL::double precision[], 100)

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, searchvalues double precision[]) -> setof record (searchvalue, percent)

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValuePercent('test', 'rast', 1, ARRAY[-9999]) ST_ValuePercent('test', 'rast', 1, NULL::double precision[])

- ST_ValuePercent(rastertable text, rastercolumn text, searchvalues double precision[], roundto double precision) -> setof record (searchvalue, percent)

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValuePercent(rastertable text, rastercolumn text, searchvalues double precision[]) -> setof record (searchvalue, percent)

A set of functions for a single search value:

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision) -> bigint

searchvalue: the value to get a percent for in the raster

ST_ValuePercent('test', 'rast', 1, TRUE, 23, 0) ST_ValuePercent('test', 'rast', 5, FALSE, 3.14, 0.01) ST_ValuePercent('test', 'rast', 2, TRUE, 100, 100) ST_ValuePercent('test', 'rast', 1, FALSE, -9999, 1)

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, searchvalue double precision, roundto double precision) -> bigint

exclude_nodata_value is assumed to be TRUE

ST_ValuePercent('test', 'rast', 5, 3.14, 0.01) ST_ValuePercent('test', 'rast', 2, 100, 100)

- ST_ValuePercent(rastertable text, rastercolumn text, nband integer, searchvalue double precision) -> bigint

roundto is assumed to be 0, so no rounding takes place. exclude_nodata_value is assumed to be TRUE

ST_ValuePercent('test', 'rast', 1, -9999)

- ST_ValuePercent(rastertable text, rastercolumn text, searchvalue double precision, roundto double precision) -> bigint

nband is assumed to be 1. exclude_nodata_value is assumed to be TRUE.

- ST_ValuePercent(rastertable text, rastercolumn text, searchvalue double precision) -> bigint

**ST_Resample(raster, method, originx, originy, pixelsizex, pixelsizey) -> raster**

ST_Resample is the main function underlying the ST_Transform, ST_Rescale, ST_Reskew and ST_SnapToGrid functions. ST_Resample provides the ability to change a raster's projection, adjust the scale of the raster within its extent, change the skew (or deskew) and "dissolve" the raster to a grid.

*If a skewed raster is provided to ST_Resample and skewx and/or skewy are not provided, the output raster will be deskewed (zero skew in X and Y axes thus "north up")*

- ST_Resample(

rast raster, srid integer DEFAULT NULL, scalex double precision DEFAULT 0, scaley double precision DEFAULT 0, gridx double precision DEFAULT NULL, gridy double precision DEFAULT NULL, skewx double precision DEFAULT 0, skewy double precision DEFAULT 0, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125

) -> raster

srid: the SRID that the function will use to reproject the raster to. If NULL, the raster's SRID will be used.

scalex: the scale/pixel size in the X axis of the resampled raster. If scalex and scaley are zero (0), the resampled raster's scale will be autocomputed. If provided, scaley must also be provided.

scaley: the scale/pixel size in the Y axis of the resampled raster. If scalex and scaley are zero (0), the resampled raster's scale will be autocomputed. If provided, scalex must also be provided.

gridx: the X coordinate of a point on the grid to which the raster will be aligned. Value is in the resampled raster's world coordinates. If provided, gridy must also be provided.

gridy: the Y coordinate of a point on the grid to which the raster will be aligned. Value is in the resampled raster's world coordinates. If provided, gridx must also be provided.

skewx: the skew along the X axis of the resampled raster. If skewx and skewy are zero (0), the resampled raster will be "north up".

skewy: the skew along the Y axis of the resampled raster. If skewx and skewy are zero (0), the resampled raster will be "north up".

algorithm: the algorithm to use when resampling the raster. default is 'NearestNeighbour?'. possible algorithms are:

NearestNeighbour (default. fastest performance but worst interpolation) NearestNeighbor (for those wanting to use the American spelling) Bilinear Cubic CubicSpline Lanczos

maxerr: the threshold for approximation by the resampling algorithm (in pixel units). default is 0.125, which is the same value used in GDAL gdalwarp utility. if set to zero, no approximation takes place.

- ST_Resample(

rast raster, ref raster, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125

) -> raster

ref: the reference raster to which rast will copy the scalex, scaley, skewx, skewy and srid. In addition, the upperleft corner of ref will be used as a grid point to align rast.

**ST_Rescale(raster, scalex, scaley) -> raster**

ST_Rescale is a focused function of ST_Resample for changing the X and Y scale without affecting the extent of the raster.

*If a skewed raster is provided to ST_Rescale, the output raster will be deskewed (zero skew in X and Y axes thus "north up"). To preserve the skew, use ST_Resample.*

- ST_Rescale(rast raster, scalex double precision, scaley double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125) -> raster

scalex: the scale/pixel size in the X axis of the resampled raster

scaley: the scale/pixel size in the Y axis of the resampled raster

- ST_Rescale(rast raster, scalexy double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125) -> raster

scalexy: the scale/pixel size in the X and Y axes of the resampled raster.

**ST_Reskew(raster, skewx, skewy) -> raster**

ST_Reskew is a focused function of ST_Resample for changing the skew along the X and Y axes.

- ST_Reskew(rast raster, skewx double precision, skewy double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125) -> raster

skewx: the skew along the X axis of the resampled raster. If skewx and skewy are zero (0), the resampled raster will be "north up".

skewy: the skew along the Y axis of the resampled raster. If skewx and skewy are zero (0), the resampled raster will be "north up".

- ST_Reskew(rast raster, skewxy double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125) -> raster

skewxy: the skew along both X and Y axes of the resampled raster. If skewxy is zero (0), the resampled raster will be "north up".

**ST_SnapToGrid(raster, gridx, gridy)**

ST_SnapToGrid provides the ability to align a raster to grid (possibly that of a coverage). The returned raster has its upper-left corner placed at the closest valid grid point to the source raster's upper-left corner without losing a data. The same is done for the lower-right corner.

*If a skewed raster is provided to ST_SnapToGrid, the output raster will be deskewed (zero skew in X and Y axes thus "north up"). To preserve the skew, use ST_Resample.*

- ST_SnapToGrid(

rast raster, gridx double precision, gridy double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125, scalex double precision DEFAULT 0, scaley double precision DEFAULT 0

) -> raster

gridx: the X coordinate of a point on the grid to which the raster will be aligned. Value is in the raster's world coordinates.

gridy: the Y coordinate of a point on the grid to which the raster will be aligned. Value is in the raster's world coordinates.

scalex: the scale/pixel size in the X axis of the resampled raster. This is also the scale of the grid to which the raster is being "snapped". If provided, scaley must be provided. If scalex and scaley are zero, the input raster's scale will be used.

scaley: the scale/pixel size in the Y axis of the resampled raster. This is also the scale of the grid to which the raster is being "snapped" If provided, scalex must be provided. If scalex and scaley are zero, the input raster's scale will be used.

- ST_SnapToGrid(

rast raster, gridx double precision, gridy double precision, scalex double precision, scaley double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125

) -> raster

- ST_SnapToGrid(

rast raster, gridx double precision, gridy double precision, scalexy double precision, algorithm text DEFAULT 'NearestNeighbour?', maxerr double precision DEFAULT 0.125

) -> raster

scalexy: the scale/pixel size in the X and Y axes of the resampled raster.

~~ * Implemented as a wrapper around GDAL like done for ST_DumpAsPolygons()~~
~~ * Resampling methods are the same as the one priovided by GDAL~~

~~ ~~
**Variants**

~~ * The ~~
**first series of variants** only change the pixelsize of the raster.

~~ 1) ST_Resample(raster, pixelsize) - modify pixelsize only and resample using the default method~~

~~ 2) ST_Resample(raster, method, pixelsize) - modify pixelsize and resample using the specified method~~

~~ 3) ST_Resample(raster, pixelsizex, pixelsizey) - modify pixelsize with different values for x and y using the default method~~

~~ 4) ST_Resample(raster, method, pixelsizex, pixelsizey) - modify pixelsize with different values for x and y using the specified method~~

~~ * The ~~
**second series of variants** realign the raster and change the pixelsize

~~ 5) ST_Resample(raster, x, y, pixelsize) - realign and modify pixelsize using the default method~~

~~ 6) ST_Resample(raster, method, x, y, pixelsize) - realign and modify pixelsize using the specified method~~

~~ 7) ST_Resample(raster, x, y, pixelsizex, pixelsizey) - realign and modify pixelsize with different values for x and y using the default method~~

~~ 8) ST_Resample(raster, method, x, y, pixelsizex, pixelsizey) - realign and modify pixelsize with different values for x and y using the specified method~~

~~ 9) ST_Resample(raster, x, y, pixelsizex, pixelsizey, skewx, skewy) - realign and modify pixelsize and skew with different values for x and y using the default method~~

~~ 10) ST_Resample(raster, method, x, y, pixelsizex, pixelsizey, skewx, skewy) - realign and modify pixelsize and skew with different values for x and y using the specified method~~

~~ * The ~~
**third series of variants** align and resample the raster to match the alignment, pixelsize and skew of an existing raster.~~ 11) ST_Resample(destraster, sourceraster)~~

~~ ~~
**Questions**

~~ * Should there be a band parameter?~~

~~ * How does GDAL allow resampling?~~

~~ * Which methods GDAL supports? ArcGIS supports NEAREST | BILINEAR | CUBIC | MAJORITY~~

~~ * Does GDAL support change in skew?~~

**Objective FV.22 - Making raster_columns and raster_overview as views**

raster_columns and raster_overviews are now constraint-based views. The structure of the raster_columns views is below.

Column | PostgreSQL Type | Description |

r_table_catalog | name | Name of the database containing the table with the raster column. |

r_table_schema | name | Name of the schema containing the table with the raster column. |

r_table_name | name | Name of the table containing a column of type raster. |

r_raster_column | name | Name of the raster column in the table. All attribute columns following this column apply to the rasters in this column. This column was previously known as r_column. |

srid | integer | ID of the spatial reference system of the rasters in this column. Value is extracted from the ST_SRID constraint on this raster column. The constraint is typically named enforce_srid_<r_raster_column>. |

scale_x | double precision | The scale on the X-axis of the rasters in this column. Value is extracted from the ST_ScaleX constraint on this raster column. The constraint is typically named enforce_scalex_<r_raster_column>. |

scale_y | double precision | The scale on the Y-axis of the rasters in this column. Value is extracted from the ST_ScaleY constraint on this raster column. The constraint is typically named enforce_scaley_<r_raster_column>. |

blocksize_x | integer | The width of the rasters in this column. Value is extracted from the ST_Width constraint on this raster column. The constraint is typically named enforce_width_<r_raster_column>. |

blocksize_y | integer | The height of the rasters in this column. Value is extracted from the ST_Height constraint on this raster column. The constraint is typically named enforce_height_<r_raster_column>. |

same_alignment | boolean | If TRUE, all rasters in this column are aligned. State is extracted from the ST_SameAlignment constraint comparing the rasters in this column against coordinates known to be on the grid. The constraint is typically named enforce_same_alignment_<r_raster_column>. |

regular_blocking | boolean | If TRUE, all rasters in this column are regularly blocked. Though this column is technically based upon a constraint, the constraint is purely information and does NO actual constraining. Therefore if this column is TRUE, a user explicitly specified that the rasters in this column are regularly blocked. The constraint is typically named enforce_regular_blocking_<r_raster_column>. |

num_bands | integer | The number bands within each raster of this column. Value is extracted from the ST_NumBands constraint on this raster column. The constraint is typically named enforce_num_bands_< r_raster_column>. |

pixel_types | text[] | Text array of the pixel types of the bands within each raster of this column. Value is extracted from a special function that creates an array of the band pixel types of the rasters in this column. The constraint is typically named enforce_pixel_types_<r_raster_column>. |

nodata_values | double precision[] | Double precision array of the NODATA values of the bands within each raster of this column. Value is extracted from a special function that creates an array of the band NODATA values of the rasters in this column. The constraint is typically named enforce_nodata_values_<r_raster_column>. |

out_db | boolean[] | Boolean array of the out-of-database flag of the bands within each raster of this column. Value is extracted from a special function that creates an array of the band out-of-database flags of the rasters in this column. The constraint is typically named enforce_out_db_<r_raster_column>. |

extent | geometry | The maximum extent within which all rasters of this column must be covered by. The maximum extent is computed by ST_ConvexHull(ST_Collect(ST_ConvexHull(raster))) of all rasters in this column at the time that the extent constraint was added.. Value is extracted from the ST_CoveredBy(ST_ConvexHull(raster)) constraint on this raster column. The constraint is typically named enforce_max_extent_<r_raster_column>. |

Raster constraints can be set using one of the AddRasterConstraints functions.

- AddRasterConstraints (

rastschema name,

rasttable name,

rastcolumn name,

VARIADIC constraints text[]

)

constraints: keywords indicating constraint to attempt to add. possible keywords are:

srid scale_x or scalex scale_y or scaley scale blocksize_x or blocksizex or width blocksize_y or blocksizey or height blocksize same_alignment or samealignment or alignment regular_blocking or regularblocking num_bands or numbands pixel_types or pixeltypes nodata_values or nodatavalues or nodata out_db or outdb extent

SELECT AddRasterConstraints('schema', 'table', 'rast', 'srid', 'extent')

- AddRasterConstraints (

rasttable name,

rastcolumn name,

VARIADIC constraints text[]

)

- AddRasterConstraints (

rastschema name,

rasttable name,

rastcolumn name,

srid boolean DEFAULT TRUE,

scale_x boolean DEFAULT TRUE,

scale_y boolean DEFAULT TRUE,

blocksize_x boolean DEFAULT TRUE,

blocksize_y boolean DEFAULT TRUE,

same_alignment boolean DEFAULT TRUE,

regular_blocking boolean DEFAULT FALSE,

num_bands boolean DEFAULT TRUE,

pixel_types boolean DEFAULT TRUE,

nodata_values boolean DEFAULT TRUE,

out_db boolean DEFAULT TRUE,

extent boolean DEFAULT TRUE

)

- AddRasterConstraints (

rasttable name,

rastcolumn name,

srid boolean DEFAULT TRUE,

scale_x boolean DEFAULT TRUE,

scale_y boolean DEFAULT TRUE,

blocksize_x boolean DEFAULT TRUE,

blocksize_y boolean DEFAULT TRUE,

same_alignment boolean DEFAULT TRUE,

regular_blocking boolean DEFAULT FALSE,

num_bands boolean DEFAULT TRUE,

pixel_types boolean DEFAULT TRUE,

nodata_values boolean DEFAULT TRUE,

out_db boolean DEFAULT TRUE,

extent boolean DEFAULT TRUE

)

Though users can manually remove raster constraints, it is easier to use one of the DropRasterConstraints functions

- DropRasterConstraints (

rastschema name,

rasttable name,

rastcolumn name,

VARIADIC constraints text[]

)

constraints: the same keywords as function #1 for AddRasterConstraints

- DropRasterConstraints (

rasttable name,

rastcolumn name,

VARIADIC constraints text[]

)

- DropRasterConstraints (

rastschema name,

rasttable name,

rastcolumn name,

srid boolean DEFAULT TRUE,

scale_x boolean DEFAULT TRUE,

scale_y boolean DEFAULT TRUE,

blocksize_x boolean DEFAULT TRUE,

blocksize_y boolean DEFAULT TRUE,

same_alignment boolean DEFAULT TRUE,

regular_blocking boolean DEFAULT TRUE,

num_bands boolean DEFAULT TRUE,

pixel_types boolean DEFAULT TRUE,

nodata_values boolean DEFAULT TRUE,

out_db boolean DEFAULT TRUE,

extent boolean DEFAULT TRUE

)

- DropRasterConstraints (

rasttable name,

rastcolumn name,

srid boolean DEFAULT TRUE,

scale_x boolean DEFAULT TRUE,

scale_y boolean DEFAULT TRUE,

blocksize_x boolean DEFAULT TRUE,

blocksize_y boolean DEFAULT TRUE,

same_alignment boolean DEFAULT TRUE,

regular_blocking boolean DEFAULT TRUE,

num_bands boolean DEFAULT TRUE,

pixel_types boolean DEFAULT TRUE,

nodata_values boolean DEFAULT TRUE,

out_db boolean DEFAULT TRUE,

extent boolean DEFAULT TRUE

)

The raster_overviews view is structured as the following:

Column | PostgreSQL Type | Description |

o_table_catalog | name | Name of the database containing the table with the overview column. |

o_table_schema | name | Name of the schema containing the table with the overview column. |

o_table_name | name | Name of the table containing a column of type raster considered an overview. |

o_raster_column | name | Name of the overview column in the table. All attribute columns following this column apply to the rasters in this column. This column was previously known as o_column. |

r_table_catalog | name | Name of the database containing the table with the raster column from which this overview column was derived. |

r_table_schema | name | Name of the schema containing the table with the raster column from which this overview column was derived. |

r_table_name | name | Name of the table containing a column of type raster from which this overview column was derived. |

r_raster_column | name | Name of the raster column in the table from which this overview column was derived. This column was previously known as r_column. |

overview_factor | integer | The factor used to compute the overviews in this column. If the factor is 2, the overviews' scale is 2x of the reference rasters. |

**One column has been removed from the raster_overviews views: out_db.**

As all overviews are rasters, overviews will have have a record in raster_columns and another record in raster_overviews.

The distinguishing factor that makes a raster be treated as an overview is the *enforce_overview_<o_raster_column>* constraint. The constraint can be applied using one of the AddOverviewConstraints functions. **The overview constraint checks that the reference raster column and table exists. If the reference raster column does not exist, the constraint is violated.**

- AddOverviewConstraints (

ovschema name, ovtable name, ovcolumn name,

refschema name, reftable name, refcolumn name,

ovfactor int

)

- AddOverviewConstraints (

ovtable name, ovcolumn name,

reftable name, refcolumn name,

ovfactor int

)

To drop an overview constraint, use DropOverviewConstraints.

- DropOverviewConstraints (

ovschema name,

ovtable name,

ovcolumn name

)

- DropOverviewConstraints (

ovtable name,

ovcolumn name

)

~~Following the conversion of geometry_columns to a view in PostGIS, it is planned to do the same with raster_column and raster_overview for the raster part. Converting to a view has a number of advantages:
~~

~~ * The raster_column view rows will always be in synch with the existing list raster columns.
~~

~~ * All the information will be trustworthy because it will be enforced by constraints, so people can't change the band types etc... without changing the constraints. The raster_columns information will always be correct or null (if the constraint can not be applied).
~~

~~ * Idem for the raster_overview table.
~~

~~PostGIS is using the typmod feature of PostgreSQL to 'store' metadata about a table and then display them in the geometry_column view. Unfortunately the typmod is limited to XXX bytes and there is too much information to 'store' about a raster table to use typmod. PostGIS raster will therefore use another approach consisting of 'storing' metadata about a raster table as constraints on the table. A set of constraints will be applied to a raster table and those constraints will be read and displayed by the raster_column view.
~~

~~The ~~**major changes concerning the raster_column table** are as follow:

~~ * There will be a flexible AddRasterConstraint() function trying to add a set of constraint on a table. As for any constraint, each constraint will be successfully applied only when all the rows of the table fulfill this constraint. The list of potential constraint applied by the C loader (using the AddRasterConstraint function) and hence the list of column available in the raster_column view will be: srid int, samealignment boolean, scalex float8, scaley float8, width int, height int, numberofbands int, pixeltypes text[], nodatavalues float[] in addition to the 'r_table_catalog', 'r_table_schema', 'r_table_name' and 'r_column' columns. There is still discussion about if global extent as a geometry should be added to this list. Comments are welcome.
~~

~~ * The raster_column view rows will be determined by querying the PostgreSQL catalog. A typical query exists for this.
~~

~~ * The raster_column view will derive columns of metadata from the constraints applied to each table listed. When a constraint does not exist (because it could not be applied successfully to the table by AddRasterconstraint()), the column corresponding to this constraint for this table will be null.
~~

~~The ~~**major changes concerning the raster_overview table** are as follow:

~~ * The raster_overview table will also be replaced by a raster_overview view.
~~

~~ * We will provide a new function to support the creation of the raster_overview view and application of three additional constraints specific to overviews (AddOverviewConstraints). These three additional constraints are: 'reference raster table oid', 'overview factor' and 'global extent'. Those constraints will be displayed by the raster_overview view as 'r_table_catalog', 'r_table_schema', 'r_table_name', 'r_column', 'overview_factor' and 'global_extent' in addition to the 'o_table_catalog', 'o_table_schema', 'o_table_name' and 'o_column' columns.
~~

~~ * We will also provide a SQL function for creating overviews (ST_CreateOverview(schemaname text, tablename teat, columnname, factor int)).
~~

~~ * The raster_overview view will NOT be created by default by the rtpostgis.sql script. It will be the responsibility of applications to use the AddOverviewConstraints and ST_CreateOverview() function to create the raster_overviews view and create overviews.
~~

~~The ~~**major changes concerning the raster2pgsql loader** are as follow:

~~ * We are working on a new C importer to get rid of any Python, Numpy and Python GDAL Binding dependency which is a major obstacle to successful installation of PostGIS 2.0.
~~

~~ * This new loader will attempt to add the constraints on the loaded table using the AddRasterConstraint() function. There will be an option to NOT try to add the constraint for applications preferring to have no constraint applied to the tables over having a raster_column view filled with the proper information.
~~

~~ * This new importer will NOT have any option to create the raster_overview view, nor to create overview tables as we leave this to the application. We also think that it is more important to be able to create overviews in SQL as a post loading process query so overviews can be updated/recreated when rasters tiles are edited, added or deleted. Application wishing to create overview can implement their own overview creation code or just use the future ST_CreateOverview() SQL function.
~~

~~ * We will stop supporting the old raster2pgsql.py which was creating overview tables and filling the raster_overview table. It will be available in the script/python folder for people wishing to create overviews after a slight addaptation (to the new AddRasterConstraints() and AddOverviewConstraints() functions).
~~

### Attachments (2)

- st_intersects_triangle.png (2.7 KB) - added by 5 years ago.
- snaptogrid.png (4.9 KB) - added by 5 years ago.

Download all attachments as: .zip