DArTdb database model description

GIS enviro ER diagram Version: 2.7.0

Environmental module of KDDart. GIS data can be defined and stored here. This module is implemented in PostgreSQL with PostGIS, so relevant SQL for database creation needs to be generated.

Region: GeoDataLayers

Table: layern	Structure for spatio-temporal layer table which stores geometry objects for the layer. The suffix n in the table name refers to the id from the layer table.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		internal id
geometry	GEOMETRY				NN		geo object of some geometrical type - should have spatial index GIST in PostGIS!!!

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
xln_geometry	INDEX	`geometry`

Table: layernattrib	Table with attributes for layern.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		internal id
layerid	INTEGER		FK		NN		id of the geometric object in layern table
layerattrib	INTEGER		FK		NN		layer attribute id
value	VARCHAR(254)						value of the parameter
dt	DATETIME						date and time
systemuserid	INTEGER						system user, who inserted the data value (links to core database)
deviceid	VARCHAR(100)						device id used to measure this data point - one of the devices registered in the system

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
xlayernattrib_dt	INDEX	`dt`
xlayernattrib_sysuid	INDEX	`systemuserid`
xlayernattrib_layerattrib	INDEX	`layerattrib`
xlayernattrib_layerid	INDEX	`layerid`
xlayernattrib_deviceid	INDEX	`deviceid`

Table: layer2dn	Structure for a flat 2D table, where a single geometric object has a single set of attributes attached to it. The suffix n in the layer name is the id from the layer table. The column attributex is a conceptual representation. In fact it is a list of columns defined for this layer in the layerattrib table.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		numeric id of the geometric object
geometry	GEOMETRY				NN		geometric object
attributex	INTEGER						list of attributes defined in layerattrib table for this layer

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
xl2d_geometry	INDEX	`geometry`

Region: GeoObjectLayers

Table: siteloc	Contains the geographical location for sites defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
sitelocid	INTEGER	PK		AI	NN		internal id of the site location
siteid	INTEGER				NN		(FK) site id from main database
sitelocation	MULTIPOLYGON				NN		polygon geometry for site - should have spatial index GIST in PostGIS!!!
sitelocdt	DATETIME				NN		creation date of the site location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`sitelocid`
xsl_sitelocation	INDEX	`sitelocation`
xsl_siteid	INDEX	`siteid`
xsl_sitelocdt	INDEX	`sitelocdt`
xsl_currentloc	INDEX	`currentloc`

Table: trialloc	Contains the geographical location of trials (experiments) defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
triallocid	INTEGER	PK			NN		internal id of the site location
trialid	INTEGER				NN		(FK) trial id from the main database
triallocation	MULTIPOLYGON				NN		geometry object for the trial - should have spatial index GIST in PostGIS!!!
triallocdt	DATETIME				NN		creation date of the trial location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`triallocid`
xtl_triallocation	INDEX	`triallocation`
xtl_trialid	INDEX	`trialid`
xtl_triallocdt	INDEX	`triallocdt`
xtl_currentloc	INDEX	`currentloc`

Table: trialunitloc	Locations of trial units (plots) from core database

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
trialunitlocid	INTEGER	PK			NN		internal id of the trial unit location
trialunitid	INTEGER				NN		(FK) trial unit id from the main database
trialunitlocation	GEOMETRY				NN		geometry of the trial unit - should have spatial index GIST in PostGIS!!!
trialunitlocdt	DATETIME				NN		creation date of the trial unit location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`trialunitlocid`
xtul_trialunitlocation	INDEX	`trialunitlocation`
xtul_trialunitid	INDEX	`trialunitid`
xtul_trialunitlocdt	INDEX	`trialunitlocdt`
xtul_currentloc	INDEX	`currentloc`

Table: contactloc	Contains the geographical location of contacts defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
contactlocid	INTEGER	PK		AI	NN		internal id of contact location
contactid	INTEGER				NN		(FK) contact id from the main database
contactlocation	MULTIPOINT				NN		contact location - should have spatial index GIST in PostGIS!!!
contactlocdt	DATETIME				NN		creation date of the contact location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`contactlocid`
xcl_contactlocation	INDEX	`contactlocation`
xcl_contactid	INDEX	`contactid`
xcl_contactlocdt	INDEX	`contactlocdt`
xcl_currentloc	INDEX	`currentloc`

Table: specimenloc	Contains the geographical location for specimens defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
specimenlocid	INTEGER	PK		AI	NN		internal id of the specimen location
specimenid	INTEGER				NN		(FK) specimen id from main database
specimenlocation	GEOMETRY				NN		geometry for the specimen - should have spatial index GIST in PostGIS!!!
specimenlocdt	DATETIME				NN		creation date of the specimen location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`specimenlocid`
xspl_specimenid	INDEX	`specimenid`
xspl_specimenlocation	INDEX	`specimenlocation`
xspl_specimenlocdt	INDEX	`specimenlocdt`
xspl_currentloc	INDEX	`currentloc`

Table: surveyloc	Contains the geographical location for survey defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
surveylocid	INTEGER	PK		AI	NN		internal id of the survery location
surveyid	INTEGER				NN		(FK) survey id from main database
surverylocation	GEOMETRY				NN		geometry for survey - should have spatial index GIST in PostGIS!!!
surverylocdt	DATETIME				NN		creation date of the survey location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`surveylocid`
xsul_surveyid	INDEX	`surveyid`
xsul_surveylocation	INDEX	`surverylocation`
xsul_surveylocdt	INDEX	`surverylocdt`
xsul_currentloc	INDEX	`currentloc`

Table: storageloc	Contains the geographical location for strorage defined in the core database.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
storagelocid	INTEGER	PK		AI	NN		internal id of the storage location
storageid	INTEGER				NN		(FK) storage id from main database
storagelocation	GEOMETRY				NN		geometry for storage - should have spatial index GIST in PostGIS!!!
storagelocdt	DATETIME				NN		creation date of the storage location
currentloc	TINYINT				NN		flag indicating if the location is current
description	VARCHAR(254)						location description - optional

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`storagelocid`
xstl_storageid	INDEX	`storageid`
xstl_storagelocation	INDEX	`storagelocation`
xstl_storagelocdt	INDEX	`storagelocdt`
xstl_currentloc	INDEX	`currentloc`

Region: LayerDefinition	This is metadata definition of the layers in the database

Table: layer	List of data layers. These can be top (row) value or may be derived from other layers, e.g. through features selections or data aggregation.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		internal id
parent	INTEGER		FK				id of the parent layer
name	VARCHAR(100)				NN		layer name
alias	VARCHAR(100)						layer alias
layertype	VARCHAR(30)				NN	layer	layer, layer2d, layerimg - these three values refer to the base name of the real layer
layermetadata	TEXT						metadata for a layer. Can be a piece of xml or some sort of other agreed convention to store info about data
iseditable	TINYINT				NN	1	0\|1 flag defining if layer can be edited. Set to 0 to disable edits.
createuser	INTEGER				NN	0	id of the system user, who created the layer
createtime	DATETIME				NN		date and time of the layer creation
lastupdateuser	INTEGER				NN		id of the system user, who last updated the layer info or field definition (not a data in the layer!)
lastupdatetime	DATETIME				NN		date and time of layer info or definition update
srid	INTEGER				NN	4326	spatial reference id - refer to http://en.wikipedia.org/wiki/SRID
geometrytype	VARCHAR(30)				NN		for internal layers this is the type of the geometry column in LayerN table, have to match OGC standards (POINT, MULTIPOINT, POLYGON .. etc)
description	VARCHAR(254)						layer description
owngroupid	INTEGER				NN		group id which owns the record
accessgroupid	INTEGER				NN	0	group id which can access the record (different than owngroup)
owngroupperm	TINYINT				NN		permission for the own group members
accessgroupperm	TINYINT				NN	0	permission for the other group members
otherperm	TINYINT				NN	0	permission for all the other system users

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
xlayer_name	UNIQUE	`name`
xlayer_parent	INDEX	`parent`
xlayer_owngroupid	INDEX	`owngroupid`
xlayer_accessgroupid	INDEX	`accessgroupid`
xlayer_owngroupperm	INDEX	`owngroupperm`
xlayer_accessgroupperm	INDEX	`accessgroupperm`
xlayer_otherperm	INDEX	`otherperm`

Table: layerattrib	Contains layer attributes definitions or columns to define geometric objects. Each spatial object, i.e. a meteorological station, can be defined using a number of attributes (e.g. temperature, humidity, air pressure, time of value recording). This table serves 2 types of layers: spatio-temporal, 2d flat tables. layerimgn does not require additional parameters descriptors.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		internal id
unitid	INTEGER				NN		unitid (FK) to generalunit table in core structure
layer	INTEGER		FK		NN		layer id
colname	VARCHAR(100)				NN		name of the attribute column
coltype	VARCHAR(30)				NN		type of the column
colsize	INTEGER				NN		size of the column
validation	VARCHAR(254)						rules for value validation
colunits	VARCHAR(100)				NN		what units the value is in (e.g. deg Celsius, km, percent)

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
xlayerattrib_layer	INDEX	`layer`
xlayerattrib_unitid	INDEX	`unitid`

Region: Links to a core schema	Link to devices to log environmental data

Table: datadevice	Maintains the many to many relationship between device register and layer attributes and defines how the data is inserted into database from automated logging systems.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
layerattrib	INTEGER		FK		NN		id of the attribute column of the layer where this device will be logging into
deviceid	VARCHAR(100)				NN		(FK) Device Id from the core database - DeviceRegister table
deviceparam	VARCHAR				NN		Name of the parameter from the device for that attribute
active	TINYINT				NN	1	[0,1] flag to indicate if this definition is active. Definitions should not be removed

IndexName	IndexType	Columns
xdatadevice_unique	UNIQUE	`deviceid`, `deviceparam`, `layerattrib`
xdatadevice_layerattrib	INDEX	`layerattrib`

Region: TileSets

Table: tileset	Set of tiles - layer composed of raster images.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK	FK		NN		internal id
geometry	GEOMETRY						bounding box of the entire tile set
resolution	INTEGER				NN		primary resolution of the source image
minzoom	INTEGER				NN		minimum zoom level for a tileset
maxzoom	INTEGER				NN		maximum zoom level for the tileset
tilepath	VARCHAR(254)				NN		path where the images are stored
spectrum	VARCHAR(254)						bandwidth spectrum that source image was taken
tilestatus	VARCHAR(254)				NN		internal status of the tile set like pending or completed
imagetype	VARCHAR(245)				NN		png or jpg or other
description	TEXT						short description of the tile set - from user who imported the set into kddart
metadata	TEXT						json object with relvant metadata information for the tileset like cloud coverage, vegetation etc
source	TEXT						tile set source as json object

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`id`
ts_resolution	INDEX	`resolution`
ts_minzoom	INDEX	`minzoom`
ts_maxzoom	INDEX	`maxzoom`
ts_tilestatus	INDEX	`tilestatus`
ts_geometry	INDEX	`geometry`

Table: tiles	Each records represents sinle tile image with its geometry and attributes.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
id	INTEGER	PK		AI	NN		internal tile set id
tileset	INTEGER		FK		NN		tileset that this tile belongs to
geometry	GEOMETRY				NN		geometry object - vector boundary of the raster image
xcoord	INTEGER				NN		x coordinate of the tile
ycoord	INTEGER				NN		y coordinate of the tile
zoomlevel	INTEGER				NN		zoom level of this tile

IndexName	IndexType	Columns
PRIMARY	INDEX	`id`
tiles_tileset	INDEX	`tileset`
tiles_geometry	INDEX	`geometry`
tiles_xcoord	INDEX	`xcoord`
tiles_ycoord	INDEX	`ycoord`
tiles_zoomlevel	INDEX	`zoomlevel`

RELATIONS	list of model relations

RelName	RelType	FromTable	ToTable	CreateRefDef
Rel_07	1..n (NA)	layer	layerattrib	1
LayerAtrib+DataDevice	1..n (NA)	layerattrib	datadevice	1
LayerAtrib+LNA	1..n (NA)	layerattrib	layernattrib	1
LN+LNA	1..n (NA)	layern	layernattrib	1
LayerParent	1..n (NA)	layer	layer	1
Layer+TileSet	1..1	layer	tileset	1
Rel_08	1..n (NA)	tileset	tiles	1