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 |