[postgis-users] Re: Rasters (once more)

[Patrick]

On 15-Oct-2005, Frank Warmerdam <warmerdam at pobox.com> ever so gracefully
noted:

> On 10/15/05, Patrick <pvanlaake at users.sourceforge.net> wrote:
> > * How to integrate palettes? Externally using a reference field or
> > internally in the raster band record as a binary field?
>
> I would expect palettes to be optionally associated with bands.
> There are many models for palettes, have you thought about
> how you want to model them?
>
> Simple: 256 entries, an RGB color for each entry.
>
> Options:
>  o Transparentency: add an alpha for each entry.
>  o Variable Size: allow any number of entries.
>  o Ranges: allow a color to be applied to ranges of values.
>  o Non-integer values: Input values are doubles.
>  o Color models other than RGB.
>  o Specify a start and end color over range.
>
There are very indeed many different types of palettes and that makes it so
hard to come up with a solution that fits all scenarios. I suppose that all
the options you mention should be covered, with the possible exception of
non-integer values. In addition, there should be a label for every palette
entry. I would favour a palette external to the raster, in a separate table,
because that would enable the sharing of palettes across rasters.

> > * How to integrate attributes for ordinal data types?
>
> Wouldn't the attributes just be another (non-spatial) table?
>
Yes, but the question is one of how to. I suppose that a table oid reference
and a field name to link to would be sufficient for a 1:1 link, but other
options may exist including not providing a mechanism at all and leaving it
up to the user or application to construct the appropriate SQL statements.

> > * Should tiles have a fixed size globally, be fixed system-wide, for a
> > the
> > raster, or establish it at the level of the band? How does the answer to
> > this question bear on implementing OGC GC evaluate* methods?
>
> Is there a particular reason not to allow setting the tile size on
> a per-band basis?  In many cases, for small images, it will be
> desirable to treat an entire band as one tile.
>
I favour a solution on a per-band basis (see SQL DDL below), but there may
be other solutions. For instance, returning a profile for a pixel (what the
OGC evaluate* functions do) is much easier if the tiling of all the bands is
the same.

> Rather than (or in addition to) the Pascal code, can you post
> the schemas for supporting tables and examples of how an
> image would be stored expressed as SQL table definitions?

Here goes:

-- Create PostGIS raster objects
-- =============================

-- For management simplicity, create a postgis group.
-- Users must be group members to use these objects.
-- CREATE GROUP postgis;

-- DROP TABLE pgis_raster_tile;
-- DROP TABLE pgis_raster_bands;
-- DROP TABLE pgis_rasters;
-- DROP TABLE pgis_raster_band_types;
-- DROP TABLE pgis_raster_band_data_types;

-- The RASTER BAND TYPE object
-- This table and its data are supposed to be IMMUTABLE.
CREATE TABLE pgis_raster_band_types (
  btid        int4 PRIMARY KEY,
  type_name   varchar(20),
  is_discrete boolean
) WITHOUT OIDS;

ALTER TABLE pgis_raster_band_types OWNER TO postgres;
GRANT SELECT ON pgis_raster_band_types TO GROUP postgis;

INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (1,
'unknown', true);
INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (2,
'ordinal', true);
INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (3,
'continuous', false);
INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (4,
'surface', false);
INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (5,
'angular', false);
INSERT INTO pgis_raster_band_types (btid, type_name, is_discrete) VALUES (6,
'image', true);


-- The RASTER BAND DATA TYPE object
CREATE TABLE pgis_raster_band_data_types (
  dtid        int4 PRIMARY KEY,
  type_name   varchar(10),
  bits        int4
) WITHOUT OIDS;

ALTER TABLE pgis_raster_band_data_types OWNER TO postgres;
GRANT SELECT ON pgis_raster_band_data_types TO GROUP postgis;

INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (0,
'unassigned', 0);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (1,
'bit', 1);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (2,
'bit2', 2);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (3,
'bit4', 4);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (4,
'uint8', 8);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (5,
'uint16', 16);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (6,
'uint32', 32);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (7,
'int8', 8);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (8,
'int16', 16);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (9,
'int32', 32);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (10,
'int64', 64);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (11,
'float32', 32);
INSERT INTO pgis_raster_band_data_types (dtid, type_name, bits) VALUES (12,
'float64', 64);

-- The RASTER object
CREATE TABLE pgis_rasters (
  rid         serial PRIMARY KEY,
  name        varchar(64) UNIQUE,
  srid        int4 DEFAULT -1,
  num_rows    int4 NOT NULL CHECK (num_rows > 0),
  num_cols    int4 NOT NULL CHECK (num_cols > 0),
  pixel_size  float8 NOT NULL CHECK (pixel_size > 0),
  ulx         float8 NOT NULL,
  uly         float8 NOT NULL
) WITH OIDS;

ALTER TABLE pgis_rasters OWNER TO postgres;
GRANT SELECT, INSERT, UPDATE, DELETE ON pgis_rasters TO GROUP postgis;


-- The RASTER BAND object
CREATE TABLE pgis_raster_bands (
  bid               serial PRIMARY KEY,
  rid               int4 REFERENCES pgis_rasters (rid) ON DELETE CASCADE,
  name              varchar(64),
  band_type         int4 REFERENCES pgis_raster_band_types (btid) DEFAULT 0,
  data_type         int4 NOT NULL REFERENCES pgis_raster_band_data_types
  (dtid),
  pixel_is_point    boolean DEFAULT FALSE,
  null_value        float8,
  min_value         float8,
  max_value         float8,
  tiledim_x         int4 NOT NULL CHECK (tiledim_x > 0),
  tiledim_y         int4 NOT NULL CHECK (tiledim_y > 0)
) WITH OIDS;

ALTER TABLE pgis_raster_bands OWNER TO postgres;
GRANT SELECT, INSERT, UPDATE, DELETE ON pgis_raster_bands TO GROUP postgis;


-- The DATA TILE object
CREATE TABLE pgis_raster_tile (
  bid         int4 REFERENCES pgis_raster_bands (bid) ON DELETE CASCADE,
  tilex       int4 NOT NULL CHECK (tilex >= 0),
  tiley       int4 NOT NULL CHECK (tiley >= 0),
  data        bytea,
  PRIMARY KEY (bid, tilex, tiley)
) WITH OIDS;

ALTER TABLE pgis_raster_tile OWNER TO postgres;
GRANT SELECT, INSERT, UPDATE, DELETE ON pgis_raster_tile TO GROUP postgis;


A few remarks to accompany the above:

* Grid coordinate system starts at [0,0] in the upper-left corner. This may
be extended by allowing grids coordinate systems to start in any corner and
progress in any direction. Makes internal functionality quite a bit harder
to write.
* Rows and columns are supplied at the raster level, so all bands have those
dimensions. This is one of my personal favourites, but I am open to
discussion. Some logical combinations of bands would then be disallowed,
such as Landsat thermal band, PAN band in several image formats, or ASTER
band 3B vs VIS bands.
* Pixel is square. This could be changed without much fuss.
* Bands are 2-dimensional. Could be changed, but I would like to hear some
good arguments to do so.
* Many features still need to be added, such as palette information for
integer types, and possibly a mechanism to link attributes.

Please try the code and I will be glad to discuss it further.

Best,
Patrick