User:Mkennard/HydroGeoSphere Data Model/Temp

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