User:Mkennard/HydroGeoSphere Data Model/Temp: Difference between revisions

From XMS Wiki
Jump to navigationJump to search
No edit summary
m (Jmonson moved page Talk:HydroGeoSphere Data Model/Temp to User:Unjedai/HydroGeoSphere Data Model/Temp without leaving a redirect: moving out of talk namespace)
(No difference)

Revision as of 22:40, 11 July 2016

HydroGeoSphere Data Model
Created using MySQL Workbench

The HydroGeosphere Data Model documents a relational database method of storing data used by the numerical model HydroGeoSphere. The data model is being developed to be compatible with the Arc Hydro Groundwater Data Model.

The wiki tables below document the tables in the relational database. The table name and the field (column) names and types are given along with descriptions.

OBJECTID fields with type OID are integer fields that are automatically numbered by the database system and serve as the primary key in the table. The primary key field is marked by the Primary key.png key icon.

A sample database can be found here. This database is in SQLite format. There are a number of free SQLite readers you can use to examine the database. SQLiteSpy is one and can be found via a Google search.

A version of HydroGeoSphere that reads and writes the database can be found here.

Node, face and edge sets

A node set is a list of node ids, an edge set is a list of edges (node id pairs), and a face set is a list of element face ids. A node/edge/face set represents a location at which a boundary condition can be defined.

The NodeSet, FaceSet and EdgeSet tables are used to track the nodes, faces and edges in the sets. Although a particular instance of a SetID and NodeID should be unique and not repeated in the NodeID table and could therefore serve as a primary key, an OBJECTID column is used instead for convenience. The same applies to the FaceSet and EdgeSet tables.

NodeSet
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier.
SetID Integer Unique numerical identifier for the set.
NodeID Integer ID of a node in the set.
FaceSet
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier.
SetID Integer Unique numerical identifier for the set.
ElementID Integer ID of the element associated with the face in the set.
FaceID Integer Element based ID of the face in the set. Refer to Element types.
EdgeSet
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier.
SetID Integer Unique numerical identifier for the set.
NodeID1 Integer NodeID of the first node on the edge in the set.
NodeID2 Integer NodeID of the second node on the edge in the set.

The SetDefinition table stores the node, face or edge set name and its type.

SetDefinition
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier. This is not needed due to SetID and will be removed in the future.
SetID Integer Unique numerical identifier for the node, edge or face set.
SetType Integer The type of set (node, edge or face). Uses SetType coded value domain.
SetName Text The name of the set.

Boundary conditions

Boundary conditions are stored in the BC table. The TsID is used to associate a time series with a BC. A boundary condition is associated with a node set, face set or edge set via the BcHasSet table.

BC
Field Type Description
Primary key.png BcID Integer Unique numerical identifier for the BC.
BcType Integer The type of the BC. BcType coded value domain.
BcDomain Integer The domain. DomainType coded value domain.
BcInterpolate Logical


Interpolate flag. Interpolate between time-series values if TRUE. This field would be an INTEGER in SQLite.
BcInputType Integer Type of input for the BC. InputType coded value domain.
BcNPanelTime Integer Number of time panels.
TsID Integer ID of the time series (stored in the TimeSeries table) associated with this BC.

The BcHasSet table associates BCs with sets. The table is necessary for a many-to-many relationship between BCs and sets. That is, a BC could be used on more than one set, and a set could use more than one BC.

If it is decided that a BC can be associated with only one set, then an alternative is to eliminate the BcHasSet table and put a SetID field in the BC table. Then there would be a one-to-many relationship between sets and BCs, meaning one set could be used by many BCs.

If it is decided that a set can be associated with only one BC, then an alternative is to elminate the BcHasSet table and put a BcID field in the SetDefinition table. Then there would be a one-to-many relationship between BCs and sets, meaning one BC could be used by many sets.

Another alternative is to have a one-to-one relationship between BCs and sets, in which case the BcHasSet table can be eliminated and the BC and SetDefinition tables could be merged into one table. However, if it is desirable to create one without the other - for example to create the sets first and the BCs later - then we don't want to use this approach and they should be kept separate.

BcHasSet
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier. The combination of BcID and SetID could serve as the primary key but OBJECTID is used for the convenience of having just one column as the primary key.
BcID Integer Unique numerical identifier for the BC.
SetID Integer Unique numerical identifier for the Node, Face or Edge Set.

Time

Time is patterned after the AHGW data model which is currently in draft version. Some draft documentation on it is available here.

TimeSeries
Field Type Description
Primary key.png OBJECTID OID Unique numerical identifier.
TsID Integer Unique numerical identifier for the Time Series in the BC table. This is called "VarID" in Arc Hydro Groundwater.
TsTime Double The time stamp specifying the date and time associated with the time series value.
UTCOffset Double The number of hours the time coordinate system used to define TsTime is displaced from UTC.
TsValue Double The numerical value itself.
TsValueS Text Similar to TsValue but a string. To accommodate rasters.

The VariableDefinition table is taken from AHGW but we only need a couple of the standard columns. This table stores the names of time series. The BC, TimeSeries and VariableDefinition tables can all be linked via TsID.

VariableDefinition
Field Type Description
Primary key.png TsID Integer Unique numerical identifier for the Time Series in the BC table. This is called "VarID" in Arc Hydro Groundwater.
TsName Text The time series name. This is called "VarName" in Arc Hydro Groundwater.
NoDataVal Double If TsValue = NoDataValue in the Time Series table, then this time panel is inactive.
NoDataValS Text Similar to NoDataVal but a string. To accommodate rasters.

Coded Value Domains

Coded value domains are like enumerations in C++. They define a set of values a variable can have.

BcType
Field type: Integer


Code Name
0 undefined
1 head
2 flux
InputType
Field type: Integer


Code Name
0 undefined
1 value
2 rasterxy
3 rasterxz
4 rasteryz
5 eq_elev
6 eq_init
SetType
Field type: Integer


Code Name
0 Node
1 Edge
2 Face
DomainType
Field type: Integer


Code Name
1 Porous_media
2 Fracture_media
3 Dual_media
4 Overland_media
5 Channel_media
6 Et_media