[postgis-users] Re: Re: Proposed SQL interface for PGRaster

[Patrick]

"Patrick" <pvanlaake at users.sourceforge.net> wrote in message 
news:el6efg$lc$1 at sea.gmane.org...
>
> I would like to suggest that the community puts together some real 
> designs, develop some use-cases, and then work out how the design affects 
> the implementation of some basic operations.
>
Hi all,

Here is my proposal for a raster - raster band - data tile hierarchy. Note 
that this is only meant to demonstrate the principle and by no means 
complete, let alone definitive. Please shoot.


-- The RASTER object
CREATE TABLE PGRaster(
  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_x_dim float8 NOT NULL CHECK (pixel_x_dim > 0),
  pixel_y_dim float8 NOT NULL CHECK (pixel_y_dim > 0),
  ulx float8 NOT NULL,
  uly float8 NOT NULL

  :
  : -- other properties
  :
) WITH OIDS;

-- The RASTER BAND object
CREATE TABLE PGRasterBand(
  bid serial PRIMARY KEY,
  rid int4 REFERENCES PGRaster(rid) ON DELETE CASCADE,
  band_order int4 DEFAULT 0,
  name varchar(64),
  data_type int4 NOT NULL,
  :
  : -- other properties
  :
  tile_dim_x int4 NOT NULL CHECK (tile_dim_x > 0),  -- # pixels/tile
  tile_dim_y int4 NOT NULL CHECK (tile_dim_y > 0)
) WITH OIDS;

-- The DATA TILE object
CREATE TABLE PGRasterDataTile(
  bid int4 REFERENCES PGRasterband(bid) ON DELETE CASCADE,
  tile_index_x int4 NOT NULL CHECK (tile_index_x >= 0),
  tile_index_y int4 NOT NULL CHECK (tile_index_y >= 0),
  data bytea, -- or whatever we choose

  :
  : -- other properties
  :
  PRIMARY KEY (bid, tile_index_x, tile_index_y)
) WITH OIDS;

Cheers,
Patrick