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.

[BACK TO TOP]

Region: GeoDataLayers

Table: layernStructure 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.

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNinternal id
geometryGEOMETRY   NNgeo object of some geometrical type - should have spatial index GIST in PostGIS!!!
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
xln_geometryINDEX`geometry`


Table: layernattribTable with attributes for layern.

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNinternal id
layeridINTEGER FK NNid of the geometric object in layern table
layerattribINTEGER FK NNlayer attribute id
valueVARCHAR(254)    value of the parameter
dtDATETIME    date and time
systemuseridINTEGER    system user, who inserted the data value (links to core database)
deviceidVARCHAR(100)    device id used to measure this data point - one of the devices registered in the system
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
xlayernattrib_dtINDEX`dt`
xlayernattrib_sysuidINDEX`systemuserid`
xlayernattrib_layerattribINDEX`layerattrib`
xlayernattrib_layeridINDEX`layerid`
xlayernattrib_deviceidINDEX`deviceid`


Table: layer2dnStructure 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.

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNnumeric id of the geometric object
geometryGEOMETRY   NNgeometric object
attributexINTEGER    list of attributes defined in layerattrib table for this layer
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
xl2d_geometryINDEX`geometry`


[BACK TO TOP]

Region: GeoObjectLayers

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

ColNameDatatypePKFKAINNDefaultValueComments
sitelocidINTEGERPK AINNinternal id of the site location
siteidINTEGER   NN(FK) site id from main database
sitelocationMULTIPOLYGON   NNpolygon geometry for site - should have spatial index GIST in PostGIS!!!
sitelocdtDATETIME   NNcreation date of the site location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`sitelocid`
xsl_sitelocationINDEX`sitelocation`
xsl_siteidINDEX`siteid`
xsl_sitelocdtINDEX`sitelocdt`
xsl_currentlocINDEX`currentloc`


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

ColNameDatatypePKFKAINNDefaultValueComments
triallocidINTEGERPK  NNinternal id of the site location
trialidINTEGER   NN(FK) trial id from the main database
triallocationMULTIPOLYGON   NNgeometry object for the trial - should have spatial index GIST in PostGIS!!!
triallocdtDATETIME   NNcreation date of the trial location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`triallocid`
xtl_triallocationINDEX`triallocation`
xtl_trialidINDEX`trialid`
xtl_triallocdtINDEX`triallocdt`
xtl_currentlocINDEX`currentloc`


Table: trialunitlocLocations of trial units (plots) from core database

ColNameDatatypePKFKAINNDefaultValueComments
trialunitlocidINTEGERPK  NNinternal id of the trial unit location
trialunitidINTEGER   NN(FK) trial unit id from the main database
trialunitlocationGEOMETRY   NNgeometry of the trial unit - should have spatial index GIST in PostGIS!!!
trialunitlocdtDATETIME   NNcreation date of the trial unit location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`trialunitlocid`
xtul_trialunitlocationINDEX`trialunitlocation`
xtul_trialunitidINDEX`trialunitid`
xtul_trialunitlocdtINDEX`trialunitlocdt`
xtul_currentlocINDEX`currentloc`


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

ColNameDatatypePKFKAINNDefaultValueComments
contactlocidINTEGERPK AINNinternal id of contact location
contactidINTEGER   NN(FK) contact id from the main database
contactlocationMULTIPOINT   NNcontact location - should have spatial index GIST in PostGIS!!!
contactlocdtDATETIME   NNcreation date of the contact location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`contactlocid`
xcl_contactlocationINDEX`contactlocation`
xcl_contactidINDEX`contactid`
xcl_contactlocdtINDEX`contactlocdt`
xcl_currentlocINDEX`currentloc`


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

ColNameDatatypePKFKAINNDefaultValueComments
specimenlocidINTEGERPK AINNinternal id of the specimen location
specimenidINTEGER   NN(FK) specimen id from main database
specimenlocationGEOMETRY   NNgeometry for the specimen - should have spatial index GIST in PostGIS!!!
specimenlocdtDATETIME   NNcreation date of the specimen location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`specimenlocid`
xspl_specimenidINDEX`specimenid`
xspl_specimenlocationINDEX`specimenlocation`
xspl_specimenlocdtINDEX`specimenlocdt`
xspl_currentlocINDEX`currentloc`


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

ColNameDatatypePKFKAINNDefaultValueComments
surveylocidINTEGERPK AINNinternal id of the survery location
surveyidINTEGER   NN(FK) survey id from main database
surverylocationGEOMETRY   NNgeometry for survey - should have spatial index GIST in PostGIS!!!
surverylocdtDATETIME   NNcreation date of the survey location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`surveylocid`
xsul_surveyidINDEX`surveyid`
xsul_surveylocationINDEX`surverylocation`
xsul_surveylocdtINDEX`surverylocdt`
xsul_currentlocINDEX`currentloc`


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

ColNameDatatypePKFKAINNDefaultValueComments
storagelocidINTEGERPK AINNinternal id of the storage location
storageidINTEGER   NN(FK) storage id from main database
storagelocationGEOMETRY   NNgeometry for storage - should have spatial index GIST in PostGIS!!!
storagelocdtDATETIME   NNcreation date of the storage location
currentlocTINYINT   NNflag indicating if the location is current
descriptionVARCHAR(254)    location description - optional
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`storagelocid`
xstl_storageidINDEX`storageid`
xstl_storagelocationINDEX`storagelocation`
xstl_storagelocdtINDEX`storagelocdt`
xstl_currentlocINDEX`currentloc`


[BACK TO TOP]

Region: LayerDefinitionThis is metadata definition of the layers in the database

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

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNinternal id
parentINTEGER FK  id of the parent layer
nameVARCHAR(100)   NNlayer name
aliasVARCHAR(100)    layer alias
layertypeVARCHAR(30)   NNlayerlayer, layer2d, layerimg - these three values refer to the base name of the real layer
layermetadataTEXT    metadata for a layer. Can be a piece of xml or some sort of other agreed convention to store info about data
iseditableTINYINT   NN10|1 flag defining if layer can be edited. Set to 0 to disable edits.
createuserINTEGER   NN0id of the system user, who created the layer
createtimeDATETIME   NNdate and time of the layer creation
lastupdateuserINTEGER   NNid of the system user, who last updated the layer info or field definition (not a data in the layer!)
lastupdatetimeDATETIME   NNdate and time of layer info or definition update
sridINTEGER   NN4326spatial reference id - refer to http://en.wikipedia.org/wiki/SRID
geometrytypeVARCHAR(30)   NNfor internal layers this is the type of the geometry column in LayerN table, have to match OGC standards (POINT, MULTIPOINT, POLYGON .. etc)
descriptionVARCHAR(254)    layer description
owngroupidINTEGER   NNgroup id which owns the record
accessgroupidINTEGER   NN0group id which can access the record (different than owngroup)
owngrouppermTINYINT   NNpermission for the own group members
accessgrouppermTINYINT   NN0permission for the other group members
otherpermTINYINT   NN0permission for all the other system users
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
xlayer_nameUNIQUE`name`
xlayer_parentINDEX`parent`
xlayer_owngroupidINDEX`owngroupid`
xlayer_accessgroupidINDEX`accessgroupid`
xlayer_owngrouppermINDEX`owngroupperm`
xlayer_accessgrouppermINDEX`accessgroupperm`
xlayer_otherpermINDEX`otherperm`


Table: layerattribContains 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.

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNinternal id
unitidINTEGER   NNunitid (FK) to generalunit table in core structure
layerINTEGER FK NNlayer id
colnameVARCHAR(100)   NNname of the attribute column
coltypeVARCHAR(30)   NNtype of the column
colsizeINTEGER   NNsize of the column
validationVARCHAR(254)    rules for value validation
colunitsVARCHAR(100)   NNwhat units the value is in (e.g. deg Celsius, km, percent)
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
xlayerattrib_layerINDEX`layer`
xlayerattrib_unitidINDEX`unitid`


Region: Links to a core schemaLink to devices to log environmental data

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

ColNameDatatypePKFKAINNDefaultValueComments
layerattribINTEGER FK NNid of the attribute column of the layer where this device will be logging into
deviceidVARCHAR(100)   NN(FK) Device Id from the core database - DeviceRegister table
deviceparamVARCHAR   NNName of the parameter from the device for that attribute
activeTINYINT   NN1[0,1] flag to indicate if this definition is active. Definitions should not be removed
IndexNameIndexTypeColumns
xdatadevice_uniqueUNIQUE`deviceid`, `deviceparam`, `layerattrib`
xdatadevice_layerattribINDEX`layerattrib`


[BACK TO TOP]

Region: TileSets

Table: tilesetSet of tiles - layer composed of raster images.

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPKFK NNinternal id
geometryGEOMETRY    bounding box of the entire tile set
resolutionINTEGER   NNprimary resolution of the source image
minzoomINTEGER   NNminimum zoom level for a tileset
maxzoomINTEGER   NNmaximum zoom level for the tileset
tilepathVARCHAR(254)   NNpath where the images are stored
spectrumVARCHAR(254)    bandwidth spectrum that source image was taken
tilestatusVARCHAR(254)   NNinternal status of the tile set like pending or completed
imagetypeVARCHAR(245)   NNpng or jpg or other
descriptionTEXT    short description of the tile set - from user who imported the set into kddart
metadataTEXT    json object with relvant metadata information for the tileset like cloud coverage, vegetation etc
sourceTEXT    tile set source as json object
IndexNameIndexTypeColumns
PRIMARYPRIMARY KEY`id`
ts_resolutionINDEX`resolution`
ts_minzoomINDEX`minzoom`
ts_maxzoomINDEX`maxzoom`
ts_tilestatusINDEX`tilestatus`
ts_geometryINDEX`geometry`


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

ColNameDatatypePKFKAINNDefaultValueComments
idINTEGERPK AINNinternal tile set id
tilesetINTEGER FK NNtileset that this tile belongs to
geometryGEOMETRY   NNgeometry object - vector boundary of the raster image
xcoordINTEGER   NNx coordinate of the tile
ycoordINTEGER   NNy coordinate of the tile
zoomlevelINTEGER   NNzoom level of this tile
IndexNameIndexTypeColumns
PRIMARYINDEX`id`
tiles_tilesetINDEX`tileset`
tiles_geometryINDEX`geometry`
tiles_xcoordINDEX`xcoord`
tiles_ycoordINDEX`ycoord`
tiles_zoomlevelINDEX`zoomlevel`


[BACK TO TOP]

RELATIONSlist of model relations

RelNameRelTypeFromTableToTableCreateRefDef
Rel_071..n (NA)layer layerattrib1
LayerAtrib+DataDevice1..n (NA)layerattrib datadevice1
LayerAtrib+LNA1..n (NA)layerattrib layernattrib1
LN+LNA1..n (NA)layern layernattrib1
LayerParent1..n (NA)layer layer1
Layer+TileSet1..1layer tileset1
Rel_081..n (NA)tileset tiles1