PostgreSQL (PostGIS)

To set up a PostGIS database to support raster layers, you must create five custom tables:

• pci_raster
• pci_raster_channels
• pci_raster_levels
• pci_raster_channel_tiles
• pci_layer

These tables must be created in the /Database/Schemas/public/Tables folder of the PostGIS installation directory. Two tables that are created by PostGIS spatial (geometry_columns and spatial_ref_sys) must exist before the custom tables can be created. To create the custom tables, copy and run the following SQL table definitions in the SQL Queries window in PostGIS:

 CREATE TABLE pci_raster (
	layer_name text PRIMARY KEY,
	srid integer NOT NULL REFERENCES SPATIAL_REF_SYS (SRID), -- spatial reference id of the raster
	pixel_x_dim real NOT NULL CHECK (pixel_x_dim > 0.0), -- X-dimension of pixel at pyramid level 1
	pixel_y_dim real NOT NULL CHECK (pixel_y_dim < 0.0), -- Y-dimension of pixel at pyramid level 1
	tlc_x double precision NOT NULL, -- raster top-left corner X-coordinate
	tlc_y double precision NOT NULL  -- raster top-left corner Y-coordinate
);

-- one raster layer to many channels relation
CREATE TABLE pci_raster_channels (
	layer_name text REFERENCES pci_raster(layer_name),
	channel integer NOT NULL CHECK (channel > 0), -- channel number
	pval_type varchar(3) NOT NULL CHECK (pval_type IN ('8U', '16S', '16U', '32R')),
	description text,
	PRIMARY KEY (layer_name, channel)
);

-- one raster to many pyramid levels relation
CREATE TABLE pci_raster_levels (
	layer_name text REFERENCES pci_raster(layer_name),
	level integer NOT NULL CHECK (level > 0), -- pyramid level number
	nrows integer NOT NULL CHECK (nrows > 0), -- number of rows in the level
	ncols integer NOT NULL CHECK (ncols > 0), -- number of columns in the level
	tnrows integer NOT NULL CHECK (tnrows > 0), -- number of rows in a tile of the level
	tncols integer NOT NULL CHECK (tncols > 0), -- number of columns in a tile of the level
	PRIMARY KEY (layer_name, level)
);

-- one raster level and channel to one or more tiles relation
create TABLE pci_raster_level_channel_tiles (
	layer_name text,
	level integer,
	channel integer,
	rorig integer NOT NULL CHECK (rorig > 0), -- row coordinate in raster level of tile top-left pixel
	corig integer NOT NULL CHECK (corig > 0), -- column coordinate in raster level of tile top-left pixel
	tile oid PRIMARY KEY, -- object id of the large object that contains the tile's pixel values
	FOREIGN KEY (layer_name, channel) REFERENCES pci_raster_channels (layer_name, channel),
      FOREIGN KEY (layer_name, level) REFERENCES pci_raster_levels (layer_name, level)
);

-- store generic information about all layers loaded into the database
CREATE TABLE pci_layer (
	name text PRIMARY KEY,
	is_an_index_map boolean NOT NULL,
	type char(6) NOT NULL CHECK (type IN ('raster', 'vector')),
	mrc_time timestamp NOT NULL,
	metadata_table text REFERENCES pci_metadata_table_catalogue(metadata_table)
);
 

GDB uses two custom PostgreSQL tables for storing metadata for the layers:

• pci_metadata_table_catalogue
• pci_layer

To create the custom tables, copy and run the following SQL table definitions in the SQL Queries window in PostGIS:

CREATE TABLE pci_metadata_table_catalogue
(
  metadata_table text NOT NULL,
  CONSTRAINT pci_metadata_table_catalogue_pkey PRIMARY KEY (metadata_table)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE pci_metadata_table_catalogue
  OWNER TO postgres;
COMMENT ON TABLE pci_metadata_catalogue IS 'Lists the names of all layer metadata tables.';


CREATE TABLE pci_layer (
	name text PRIMARY KEY,
	is_an_index_map boolean NOT NULL,
	type char(6) NOT NULL CHECK (type IN ('raster', 'vector')),
	mrc_time timestamp NOT NULL,
	metadata_table text REFERENCES pci_metadata_table_catalogue(metadata_table)
);

SELECT AddGeometryColumn('pci_layer', 'bounds', 4326, 'POLYGON', 2);
COMMENT ON COLUMN pci_layer.mrc_time IS 'Most recently changed time.';
COMMENT ON COLUMN pci_layer.bounds IS '4326 is the SRID for the WGS84 lat/lon system.';
COMMENT ON TABLE pci_layer IS 'Every layer in the database has one row in this table.'