PostgreSQL (PostGIS)

Long name PostgreSQL (PostGIS)
Short name PG

PostgreSQL is a powerful, open-source object-relational database system. PostGIS, a spatial database extension that adds support for geographic objects, allows the PostgreSQL database to store and query spatial data.

GDB can read image and vector data from PostgreSQL databases, and FEXPORT can be used to load file-based data sets into a PostgreSQL database.

The Remote Data Wizard in CATALYST Professional can be used to access vector and raster layers from a PostGIS database built on PostgreSQL.

When working with a PostgreSQL database in PCI software, the user can set up a number of PCI-defined system tables to store and manage necessary information (indicated below). These system tables are necessary to store raster data in PostgreSQL and recommended, but not required if only working with vectors, that is, tables with geometry information.

Setting up a PostGIS database for raster layers

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)
);
 

All loaded rasters are stored in the same pci_raster table.

Setting up a PostGIS database for vector layers

A GDB vector layer in PostGIS is represented as a PostgreSQL table with a geometry field for the shape geometry and with each field in the layer being a field in the table. The database schema for the vector table will be created by GDB when the layer is loaded.

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

These tables must be created in the /Database/Schemas/public/Tables folder of the PostGIS installation directory.

The pci_layer table includes the layer extents (‘bounds’ field). This field is a 2D polygon geometry object with a WGS84 lat/lon SRID (4326).

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.'

Each vector layer loaded is stored in its own table.

Vectors without PCI-defined system tbles

Using GDB to access a Postgres database without the PCI system tables, only tables with geometry information are supported. It is not required that a user setup PCI system tables. All processes are as described above using a PCI configured database except that there is no metadata associated with a table. When using the Remote Data Wizard, vector tables will be listed with other public tables in pg_catalog.pg_tables.

Loading raster and vector layers into PostGIS using FEXPORT

You can load file-based image channels and vector layers into the PostGIS database by running FEXPORT. You must specify the appropriate values for the parameters in FEXPORT as described in Loading raster and vector layers into PostGIS using FEXPORT.

Note: FEXPORT can load multiple image channels at a time, but only one vector layer at a time.

© PCI Geomatics Enterprises, Inc.®, 2023. All rights reserved.