Version 89 (modified by 15 years ago) ( diff ) | ,
---|
PostGIS WKT Raster Beta 0.1 Working Specifications
Quick Links
- Objective 0.1.6c - Being able to load rasters in the database
- Objective 0.1.6d - Being able to get all the properties of a raster (all the “Get” functions).
- Objective 0.1.6e - Being able to intersect vector and raster to produce vector.
- Objective 0.1.6f - Being able to return a JPEG, a TIFF or a PNG.
- RASTER_COLUMNS Metadata Table
- RASTER_OVERVIEWS Metadata Table
- Why avoid to link with PostGIS?
- Specification Comments following the Code Sprint
Objective 0.1.6c - Being able to load rasters in the database
gdal2wktraster
A prototype of the translation utility implemented in Python and with use of GDAL and its bindings to Python. Create an SQL commands output to create a table of raster. As input raster file, all GDAL formats are accepted. The script is available as gdal2wktraster.py script.
Open Questions:
- Should we change names of options to strictly follow those used by raster2pgsql and shp2pgsql? Pierre: Yes! We should follow PostGIS tracks as much as possible in everything we do in this project.
- How can I import all the bands from a multiband TIFF?
USAGE:
gdal2wktraster.py [<options>]
OPTIONS:
Source and Destination options: MANDATORY PARAMETERS, ALWAYS REQUIRED
-r <RASTERFILE> Specifies input raster file. Multiple -r options can be specified for a number of input files or wildcards can be used (? and *).
-t [<SCHEMA>].<TABLE> Name of destination table in with or without target schema specified.
Raster processing: Optional parameters used to manipulate input raster dataset
-s <srid> Set the SRID field of output raster. Default is -1.
-b <nbband> Specify the number of band to be extracted from input raster file.
-k <BLOCK_SIZE> Specify the size of the block of the input raster, assuming regular blocking mode. Must be specified as WIDTHxHEIGHT.
-R Register the raster as a filesystem (out-db) raster. Absolute path to the raster and georeferencing informations are stored instead of the raster actual data. This storage system is only allowed in SVN trunk version right now.
-l <OVERVIEW_LEVEL> Create overview tables named as o_<LEVEL>_<RASTER_TABLE> and populate it with GDAL-provided overviews. Regular blocking only.
Database processing: Optional parameters used to manipulate database objects
-c Creates a new table and populates it with raster input file(s), this is the default if you do not specify any options.
-d Drops the table, then recreates it and populates it with current raster file(s) data.
-f <COLUMN> Name of target column for raster data. Default column name is rast.
-F Add a "filename" column containing the original name of the loaded raster file.
-I Create a GiST index on the raster column.
-M Issue VACUUM command against all generated tables.
-V Create RASTER_OVERVIEWS table used to store overviews metadata.
Miscellanous: Other optional parameters
-e <ENDIAN> Control endianness of generated binary output of raster; specify 0 for XDR (big-endian) and 1 for NDR (little-endian). Only NDR output is supported right now.
-w <VERSION> Specify version of WKT Raster protocol. Default is 0; only this value is supported right now.
-o <file> Output file for generated SQL commands. If not specified, stdout is assumed.
-v Switch on excessively verbose mode, useful for debugging.
-h Display this help screen.
raster2pgsql
Should be as much similar to shp2pgsql as it is possible.
- For Beta 0.1: TIFF only, only the first band, options for tiling.
Open Questions:
- Pierre: Should there be an option to be able to write directly to the database instead of writing a .sql file by default?
- Pierre: If we use GDAL, should we allow more file format than only JPEG and TIFF?
Pierre:I guess this comes for free. We might encounter some problems with some format however. I don't know which one yet.
USAGE:
raster2pgsql [<options>] rasterfile [rasterfile…] [<schema>.]<table>
Create an SQL commands file to create a table of raster. If rasterfile is multiband and –b is not specified, every band are inserted. Multiple band can also be specified using multiple filenames (rasterfile1 is the first band, rasterfile2 the second, etc…). Can process multiple file from a folder.
georeference (and pixel size) must exist directly in the files or in a companion World File.
OPTIONS:
-s <srid> Set the SRID field. Default is -1.
-b <nbband> Specify the number of band. The number of rasterfile must correspond to this number.
-P <pixeltypes> Specify the pixels types in which to store each band. Ex. ‘8-bit unsigned integer,16-bit float’. conversion may happens.
-n <nodata values> Specify the nodata value for each bands. Ex. ‘0,0.0’. Default to ‘none’ for each band.
-t <pixels> Divide rasters into <pixels>x<pixels> tiles, one tile per row. Default is to store whole rasters as one row.
(-d|a|b|c|p) Mutually exclusive inserting options:
-d Drops the table, then recreates it and populates it with current raster file data.
-a Appends raster file into current table, must be exactly the same pixel size, number of band, NODATA value and pixel type.
-B Appends raster files as a new bands. When tiled with the –t option, the new band is inserted tiled in the same way as the original band.
-c Creates a new table and populates it, this is the default if you do not specify any options.
-p Prepare mode, only creates the table.
-r <raster_column> Specify the name of the raster column (mostly useful in append mode).
-D Use PostgreSQL dump format (defaults to sql insert statements).
-I Create a GiST index on the bounding box of the raster column.
-? Display this help screen
Objective 0.1.6d - Being able to get all the properties of a raster (all the “Get” functions).
ST_SRID(raster|geometry) → integer
Return the SRID associated with the raster.
ST_Width(raster) → integer
Return the width of the raster.
ST_Height(raster) → integer
Return the height of the raster.
ST_PixelSizeX(raster) → float64
Return the georeference's X pixel size of the raster. See.
ST_PixelSizeY(raster) → float64
Return the georeference's Y pixel size of the raster. See.
ST_RotationX(raster) → float64
Return the georeference's X rotation.
ST_RotationY(raster) → float64
Return the georeference's Y rotation. See.
ST_UpperLeftX(raster) → float64
Return the georeference's X-coordinate of the center of the upper left pixel. See.
ST_UpperLeftY(raster) → float64
Return the georeference's Y-coordinate of the center of the upper left pixel. See.
ST_GeoReference(raster) → string
Return the georeference of the raster as a string representing the 6 doubles of an equivalent world file (including the carriage return). See.
ST_NumBands(raster) → integer
Return the number of band included in the raster.
ST_BandPixelType(raster, integer) → string
Return the pixel type of the specified 1-based Nth band of raster. Band index is 1-based. The function returns one of the following values:
- 1BB - 1-bit boolean
- 2BUI - 2-bit unsigned integer
- 4BUI - 4-bit unsigned integer
- 8BSI - 8-bit signed integer
- 8BUI - 8-bit unsigned integer
- 16BSI - 16-bit signed integer
- 16BUI - 16-bit unsigned integer
- 32BSI - 32-bit signed integer
- 32BUI - 32-bit unsigned integer
- 16BF - 16-bit float
- 32BF - 32-bit float
- 64BF - 64-bit float
ST_BandNoDataValue(raster, integer) → float32
Return the NoDataValue of the specified 1-based Nth band of raster. Band index is 1-based. The value is always returned as a float32 even if the pixel type is integer.
Objective 0.1.6e - Being able to intersect vector and raster to produce vector.
List of PostGIS functions similar or related to ST_GetBBox(), ST_Envelope() and ST_Shape():
ST_Boundary(geometry) (not really - always return a geometry a dimension lower - i.e. the boundary of a polygon is a polyline.)
ST_box(geometry) (return a PostgreSQL box object)
ST_box2d(geometry) (return a box2d object)
ST_box3d(geometry) (return a box3d object)
getBBOX(geometry) (Deprecation in 1.2.3)
ST_Envelope(geometry) Returns the minimum bounding box for the supplied geometry, as a geometry. The polygon is defined by the corner points of the bounding box ((MINX, MINY), (MINX, MAXY), (MAXX, MAXY), (MAXX, MINY), (MINX, MINY)). (PostGIS will add a ZMIN/ZMAX coordinate as well). In PostGIS, the bounding box of a geometry is represented internally using float4s instead of float8s that are used to store geometries. The bounding box coordinates are floored, guarenteeing that the geometry is contained entirely within its bounds. This has the advantage that a geometry's bounding box is half the size as the minimum bounding rectangle, which means significantly faster indexes and general performance. But it also means that the bounding box is NOT the same as the minimum bounding rectangle that bounds the geometry.
envelope(geometry) (Deprecation in 1.2.3)
Function definitions for WKT Raster 0.1.6
ST_GetBBox(raster) (not yet implemented but planned) is replaced with ST_Envelope() since there is no more an equivalent ST_GetBBox function in PostGIS.
ST_Raster_to_box2d(raster) should be renamed ST_box2d(raster).
ST_Box2D(raster) → BOX2D - Returns a BOX2D representing the extent of the raster.
If the raster is rotated, the result is a box2d enclosing the rotated raster.
The only difference with ST_Envelope(raster) is that ST_Box2D(raster) returns a BOX2D, not a geometry.
Implementation details
This function replaces ST_Raster_to_box2d() and should be used for the "raster AS box2d" cast. It should also be used instead of ST_raster_envelope() when creating indexes in gdal2wktraster.py (to be tested).
ST_Envelope(raster) → polygon geometry - Returns the minimum bounding box for the supplied raster, as a geometry.
If the raster is rotated, the envelope is a non-rotated box enclosing the rotated raster.
The only difference with ST_Box2D(raster) is that ST_Envelope(raster) returns a geometry, not a BOX2D.
Implementation details
This function should be implemented as a SQL or a PL/pgSQL function looking like: 'SELECT ST_geometry(ST_Box2D(rast))'
There is already a ST_raster_envelope() function but this has actually the behavior of the ST_ConvexHull(raster, 'WITHNODATA') described below. There is also a ticket (#348) related to this function: http://trac.osgeo.org/postgis/ticket/348
ST_ConvexHull(raster) → polygon geometry - Returns the minimum convex geometry that encloses every significant pixel from the raster.
By default the resulting polygon TAKES the NODATA values into account. The resulting polygon enclose only pixels having a significant value (different than the NODATA value).
Variant 1: ST_ConvexHull(raster, 'WITHNODATA') SHOULD NOT TAKE the NODATA value into account. It generally returns a square or rectangle polygon that can be rotated or not depending on the rotation of the raster. If the raster is not rotated ST_ConvexHull(geometry, 'WITHNODATA') is (almost) equivalent to ST_Envelope(raster).
Implementation details
The first variant (taking NODATA values into account) ST_ConvexHull(raster) could be roughly implemented as a SQL or PL/pgSQL function looking like 'SELECT ST_ConvexHull(ST_Shape(raster))'. For sure computing only the external edge (without considering eventual holes like ST_Shape() does) should be faster. This imply writing the equivalent of a more simple version of ST_Shape.
The second variant (not taking NODATA values into account) is actually already implemented by the ST_raster_envelope() function which is wrongly named. There is also a ticket (#348) related to this function: http://trac.osgeo.org/postgis/ticket/348
ST_Shape(raster) → polygon geometry - Returns a geometry encomparsing every pixel having a significant value (different than the NODATA value).
This polygon geometry might contain holes if some internal areas of the raster contain pixels with NODATA values. By opposition ST_ConvexHull(raster) never contains holes.
Implementation details
This function could be roughly implemented as a SQL function looking like 'SELECT ST_Collect((ST_AsPolygon(raster)).geom)'. For sure a more specialised version could be faster than ST_AsPolygon.
ST_AsPolygon(raster) → geomval set - Returns a set of "geomval" value, one for each contiguous group of pixel sharing the same value.
This is a set-returning function (SRF). A "geomval" value is a complex type composed of a polygon geometry (one for each contiguous group of pixel sharing the same value) and the value associated with this geometry. These values are always returned as a value of type double precision. The shape of each polygon follow pixels edges.
This function should be used with precaution on raster with float pixel type as it could return one "geomval" (or polygon) per pixel. This kind of raster should be reclassified (using the planned ST_Reclassify(raster,…) function) before using ST_AsPolygon().
Implementation details
A ST_AsPolygon(raster, 'REGROUP') variant should regroup every resulting polygon having the same value into complex (or multipart) polygons.
This function is at the base of the first version of ST_Intersection which compute the intersection between a raster and a geometry.
To avoid linking directly with PostGIS (see "Why avoid to link with PostGIS?" below) It should be implemented as a SQL wrapper around ST_AsWKTPolygon() looking something like this:
CREATE TYPE geomval AS (geom geometry, val float8); CREATE TYPE wktgeomval AS (wktgeom text, val float8);
CREATE OR REPLACE FUNCTION ST_AsPolygon(rast raster) RETURNS SETOF geomval AS $$
SELECT ST_GeomFromText(wktgeomval.wktgeom), wktgeomval.val FROM ST_AsWKTPolygon(%1) AS wktgeomval;
$$ LANGUAGE SQL;
So it can then be used like this:
SELECT (ST_AsPolygon(rast)).val, (ST_AsPolygon(rast)).geom FROM sometable
or like this (maybe faster, to verify):
SELECT (gv).val, (gv).geom FROM (SELECT ST_AsPolygon(rast) gv FROM sometable) foo
ST_AsWKTPolygon(raster) → wktgeomval set - Returns a set of "geomval" value, one for each contiguous group of pixel sharing the same value.
This is a set-returning function (SRF). A "wktgeomval " value is a complex type composed of a the wkt representation of a geometry (one for each contiguous group of pixel sharing the same value) and the value associated with this geometry. These values are always returned as a value of type double precision. The shape of each polygon follow pixels edges.
This function should be used with precaution on raster with float pixel type as it could return one "geomval" (or polygon) per pixel. This kind of raster should be reclassified (using the planned ST_Reclassify(raster,…) function) before using ST_AsPolygon().
ST_AsWKTRaster() should not be used directly. Use ST_AsPolygon() instead.
Implementation details
This function construct the WKT strings representing the geometries grouping pixels of a raster having the same value. It does not directly construct geometries and therefore prevent WKT Raster from having to link with liblwgeom.a (see "Why avoid to link with PostGIS?" below) It should also return the value associated with this WKT polygon.
It should be implemented as a C function passing the raster to GDAL and converting each polygon produced by the GDALPolygonize function to a WKT string accompagned by its corresponding value in a wktgeomval type. See http://www.postgresql.org/docs/8.4/interactive/xfunc-c.html#AEN44970 on how to return rows and sets in a C function.
ST_Intersects(raster, geometry) → boolean - Returns TRUE if the geometry and the raster "spatially intersect" - (share any portion of space) and FALSE if they don't (they are Disjoint).
Variant 1: ST_Intersects(geometry, raster) → boolean
Variant 2: ST_Intersects(raster, raster) → boolean
This function permform a full intersection test and proceed in three steps to determine if the raster intersects with the geometry:
1) It first checks if the bounding box of the raster intersects with the bounding box of the geometry using the && operator. This test if very quick.
2) If the first test returns TRUE, it checks if the geometry returned by ST_ConvexHull(raster, 'WITHNODATA') intersects with the geometry. This test is slower since it might involve the geometry shape.
3) If the second test returns TRUE, it checks if the geometry returned by ST_Shape(raster) intersects with the geometry. This test is slower since it involve the computation of the raster shape and it might involve the geometry shape.
Variant 2 proceeds in a very similar way except that convex hulls of both rasters are computed and compared in step 2) and both shape of raster are computed and compared in step 3).
If you want to limit the intersection test to the first condition, simply use the && operator. The raster and the geometry will be casted to their respective bounding box (box2d objects):
raster && geometry
If you want to limit the intersection test to the second condition you can write:
ST_Intersects(ST_ConvexHull(raster, 'WITHNODATA'), geometry)
Implementation details
This function should be implemented as a pl/pgSQL function performing the three test described one after and conditionally to the other.
It might be faster to skip test 2) if this test is not signicantly faster than test 3).
ST_Intersection(raster, geometry) → geometry - Returns a geometry that represents the shared portion of the geometry and the raster.
Variant 1:
ST_Intersection(geometry, raster) → geometry
Variant 2 & 3:
ST_Intersection(raster, geometry, 'raster') → raster
ST_Intersection(geometry, raster, 'raster') → raster
Variant 4 & 5:
ST_Intersection(raster, raster) → raster
ST_Intersection(raster, raster, 'geometry') → geometry
Returns a geometry that represents the point set intersection of the geometry and the raster. The raster is first polygonised using ST_AsPolygon(raster) and ST_Intersection(geometry, geometry) is then performed between the provided geometry and all the geometries resulting from the polygonisation of the raster.
If the geometries do not share any space (are disjoint), then an empty geometry collection is returned.
ST_Intersection in conjunction with ST_Intersects is very useful for clipping geometries such as in bounding box, buffer, region queries where you only want to return that portion of a geometry that sits in a country or region of interest.
If you only want to compute the intersection between the convex hull of the raster without polygonising it to group of contiguous pixels sharing a common value, do:
ST_Intersection(ST_ConvexHull(raster, 'WITHNODATA'), geometry)
If you only want to compute the intersection between the shape of the raster without polygonising it to group of contiguous pixels sharing a common value, do:
ST_Intersection(ST_Shape(raster), geometry)
Implementation details
This function should be implemented as a pl/pgSQL function (possibly only a SQL function) performing the intersection between the provided geometry and the table generated by ST_AsPolygon(raster).
Objective 0.1.6f - Being able to return a JPEG, a TIFF or a PNG.
ST_bytea(raster, band) → 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.
- 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?
- 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, band) → raster
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))
ST_AsJPEG(raster, quality) → JPEG as "bytea"
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 JPEG 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)
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"
RASTER_COLUMNS Metadata Table
The following metadata table provides external applications and possibly internal tools the ability to discover tables with raster data, and to learn various information about those datasets.
Column Name | Type | Constraints | Comments |
r_table_catalog | character varying(256) | NOT NULL | |
r_table_schema | character varying(256) | NOT NULL | |
r_table_name | character varying(256) | NOT NULL | |
r_column | character varying(256) | NOT NULL | |
srid | integer | NOT NULL | Use 0 for unknown SRID |
pixel_types | ARRAY=VARCHAR= | NOT NULL | an array of pixeltypes, one per band. The band count is implicit in the size of this array. |
out_db | boolean | NOT NULL | false if internal tiles, true if tiles are references to files outside the database |
regular_blocking | boolean | NOT NULL | false by default, true if all blocks are equal sized, abutted and non-overlapping, started at top left origin (see below) |
nodata_values | ARRAY=DOUBLE= | an array of nodata values, one per band. The entry may be NULL to indicate no NODATA values. | |
pixelsize_x | double | width of a pixel in geounits (per SRID) | |
pixelsize_y | double | height of a pixel in geounits (per SRID) | |
blocksize_x | integer | the width of a block in pixels (NULL if irregular) | |
blocksize_y | integer | the height of a block in pixels (NULL if irregular) | |
extent | GEOMETRY | a polygon geometry containing all raster tiles, or NULL if predefined bounds are not known. For "regular_blocking" cases this geometry will be a simple rectangle. In other cases it might be an irregular polygon. |
If the regular_blocking field is true a number of restrictions are placed on the raster column that is defined:
- All tiles must have the same size (blocksize_x and blocksize_y).
- All tiles must be non-overlapping, and appear on regular block grid.
- The top left block must start at the top left corner of the extent.
- The right most column, and bottom row of blocks may have portions that extend beyond the raster extent. These areas will be assumed to be NODATA and not part of the described raster.
- The extent field must be a simple rectangle (non-rotated).
It is permissible to for regular_blocking rasters to omit some blocks/tiles (sparse rasters) in which case the missing tiles should be assumed to be all NODATA or zero valued.
RASTER_OVERVIEWS Metadata Table
If the RASTER_COLUMNS "regular_blocking" value is true then "all blocks are equal sized, abutted and non-overlapping, started at top left origin", plus additional constraints. This regular blocking capability raises the possibility of having very large contiguous raster coverages (made up of many individual WKTRaster-s) which, in turn, raises potential performance problems. Other raster formats counter this by having overviews; a concept that is already supported by GDAL.
The overviews support was discussed on Martin Daly's (Cadcorp) initiative and is available in the postgis-devel archives under thread WKTRaster & overviews.
The following metadata table provides external applications and possibly internal tools the ability to discover tables that contain overview raster data, and to learn various information about those datasets.
Column Name | Type | Constraints | Comments |
o_table_catalog | character varying(256) | NOT NULL | |
o_table_schema | character varying(256) | NOT NULL | |
o_table_name | character varying(256) | NOT NULL | |
o_column | character varying(256) | NOT NULL | Together define the column containing overview data |
r_table_catalog | character varying(256) | NOT NULL | |
r_table_schema | character varying(256) | NOT NULL | |
r_table_name | character varying(256) | NOT NULL | |
r_column | character varying(256) | NOT NULL | Together define the base raster coverage |
out_db | boolean | NOT NULL | false if internal tiles, true if tiles are references to files outside the database |
overview_factor | integer | NOT NULL | The integral factor of the overview, e.g. 2 means an overview is ½ resolution of the base raster coverage |
The following restrictions apply:
- blocksize_x and blocksize_y for the overview are the same as that of the original, base raster coverage.
- pixelsize_x and pixelsize_y for the overview are calculated from the original, base raster coverage pixelsize_x and pixelsize_y (using overview_factor).
- Overviews should not be registered in the raster_columns metadata table.
- The raster_overviews metadata table can have more than one entry for any given base raster coverage, but they must all have different overview_factor values, e.g. 2, 4, 8, 16, etc.
No provision is provided, or suggested, for creating, updating, or deleting overviews.
Pros
- Overviews and base raster data tables are independent of each other, so overviews do not muddy the water for casual queries ("SELECT * FROM RASTER_COLUMNS").
- Overview and base raster data metadata will always match.
Cons
- Two metadata tables instead of one.
- Overviews have to share blocksizes with the base raster column.
Alternative Overviews Metadata
An alternative design is to extend the RASTER_COLUMNS metadata table with the following columns:
Column Name | Type | Constraints | Comments |
or_table_catalog | character varying(256) | NOT NULL | |
or_table_schema | character varying(256) | NOT NULL | |
or_table_name | character varying(256) | NOT NULL | |
or_column | character varying(256) | NOT NULL | Together define the original ("or_") raster coverage |
overview_factor | integer | NOT NULL | The integral factor of the overview, e.g. 2 means an overview is ½ resolution of the base raster coverage |
The overview_factor column could be removed by enforcing a divide by two rule, each overview being half the resolution of its parent.
Original rasters would be queried like this:
SELECT * FROM raster_columns WHERE overview_factor = 0
Overview rasters would be queried like this:
SELECT * FROM raster_columns WHERE or_table_name = 'myraster'
Pros
- Only one metadata table.
- Overviews can have blocksizes independent of their base raster data.
Cons
- A casual metadata query ("SELECT * FROM RASTER_COLUMNS") will return overviews and base raster data.
- Metadata for base data and overviews could conflict, e.g. different SRID-s, mis-matched pixelsizes, etc.
Why avoid to link with PostGIS?
The general idea is that when we need PostGIS services to manipulate geometries, we should always use PL/pgSQL functions instead of implementing them in C. This prevent us from having to link with any PostGIS lib (mostly liblwgeom.a). This has some advantages:
- We facilitate backward compatibility with older version of PostGIS - The SQL API is much more stable than the internal C API.
- This makes WKT Raster releases as much as possible independent from PostGIS releases. We don't have to make sure WKT Raster is compatible (compile) with the very last PostGIS C function version.
- This prevent us from constantly having to adjust our code to changes in the PostGIS C API.
Specification Comments following the Code Sprint
This section is dedicated to discussion that took place during the Toronto Code Sprint 2009.
- Bug tracking - Launch separate Google project or join PostGIS bug tracker, as a module? Answer: We will track our bug in the PostGIS Google Code project. PostGIS plan on moving (back) to Trac soon.
- Wiki: is there a chance to have a better wiki for documentation, schedule, specs, road map, users inputs? Actual PostGIS wiki limitations: security, no images, no tables (or sophisticated page structure) (For this Leo and Regina are drafting up a PSC and then once we have a PSC we'll vote to locate on OSGEO. Main issue is Refractions server is kind of old)
- Is Google Code Wiki well working?
(remember export issues with Google code - see gvSIG - Cuba)
- Is Google Code Wiki well working?
- Windows build. What is the plan?
- First step is to review Windows/Visual C++ native build configuration for PostGIS. ML: I will try to investigate and solve outstanding issues like PGXS makefiles generation, soon.
- ML: Prefer native build toolsets on supported platform (Windows/Visual C++, GCC/Linux, etc.). On Windows, avoid Cygwin, MinGW, wherever possible. Issue directly related to point a).
- Is cmake a possibility?
- First step is to review Windows/Visual C++ native build configuration for PostGIS. ML: I will try to investigate and solve outstanding issues like PGXS makefiles generation, soon.
- GiST indexing: is wrapping PostGIS Gist function in PL/PgSQL sufficient? From Paul comments it seems actually that cast are sufficients.
- API: Any other SQL raster function you would like to see?
- ML: ST_AsJPEG - What JPEG implementation to use?
- ML: ST_AsTIFF - Do we aim to depend on libgeotiff?
- ML: Is there any function that would require/fit GDAL dependency?
- Pierre: We should maybe investigate the cost of using GDAL and have a general ST_AsImage(format, params) instead of using the individual tiff, jpeg and png libraries. Those will anyway also have a cost I guess… And we will probably have to link with GDAL for AsPolygon(rast) and AsRaster(geom) anyway.
- ML: ST_AsJPEG - What JPEG implementation to use?
- Implementing raster → vector conversion for intersections(). How to do it? Possible using only plpgsql? So we don't have to link with liblwgeos and we can have our own release schedule.
- polygonize with GDAL or GRASS, that generates some internal structure for those libraries, and you turn that into a WKT form of polygon. That can be passed to PostGIS, and you don't need to link to liblwgeom.
- polygonize with GDAL or GRASS, that generates some internal structure for those libraries, and you turn that into a WKT form of polygon. That can be passed to PostGIS, and you don't need to link to liblwgeom.
- Best practice for band referencing: having a band parameter (index?) or always use ST_Band()
-e.g. SELECT ST_AsTIFF(ST_Band(raster,2),60) FROM coverandtemp
SELECT ST_AsTIFF(ST_Band(raster,2),ST_Band(raster,1),60) FROM coverandtemp
or
SELECT ST_AsTIFF(raster,2,60) FROM coverandtemp
Pierre: Having a band parameter (or a list of band) for each function trying to access a band would be preferable to avoid useless memcopy of band data.
- Architecture & design issues:
- How to handle no NODATA? Pierre from Frank input: We'll need a HasNoDataValue flag for each band.
- How to handle no NODATA? Pierre from Frank input: We'll need a HasNoDataValue flag for each band.
- Documentation
- Setup a structure (likely on a Wiki), sections for developers and for users
- Update existing documents like RFCs - they are out of date.
- Describe key terms: canonical format, serialized format, transfer format (WKB), in-db raster, out-db raster, etc. This should help people to grasp the WKT Raster key elements quickly, and help them to decide which document/RFC to read in details.
- Setup a structure (likely on a Wiki), sections for developers and for users
- Development Roadmap
- Review current schedule.
- Add note on a website where users should (or should not) expect 1 release - clear statement.
Attachments (2)
- WKTRasterAsPolygon.gif (20.0 KB ) - added by 14 years ago.
- WKTRasterEnvelopeConvexHullAndPolygon.gif (21.8 KB ) - added by 14 years ago.
Download all attachments as: .zip