View source for User:Mkennard/HydroGeoSphere Data Model/Temp

{{Current development}}

[[Image:HGS_data_model.png|thumb|250px|<div>HydroGeoSphere Data Model<br>Created using MySQL Workbench</div>]]
The HydroGeosphere Data Model documents a relational database method of storing data used by the numerical model [[GMS:HydroGeoSphere|HydroGeoSphere]]. The data model is being developed to be compatible with the [http://www.archydrogw.com/ahgw/Arc_Hydro_Groundwater_Data_Model 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 {{PK}} key icon.

A sample database can be found [ftp://pubftp.aquaveo.com/download/GMS/HGS/Pm_cdo.bc.db 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 [ftp://pubftp.aquaveo.com/download/GMS/HGS/HGS-GMS-2010-Apr-13.zip 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.

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | NodeSet
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} OBJECTID || {{OID}} || Unique numerical identifier.
|-
| SetID || {{Integer}} || Unique numerical identifier for the set.
|-
| NodeID || {{Integer}} || ID of a node in the set.
|}

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | FaceSet
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} 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]].
|}

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | EdgeSet
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} 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.

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | SetDefinition
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} 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|SetType]] coded value domain. 
|-
| SetName || {{String}} || 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.

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | BC
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} BcID || {{Integer}} || Unique numerical identifier for the BC.
|-
| BcType || {{Integer}} || The type of the BC. [[#BcType|BcType]] coded value domain.
|-
| BcDomain || {{Integer}} || The domain. [[#DomainType|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|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.

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | BcHasSet
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} 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 [http://www.archydrogw.com/ahgw/Talk:Arc_Hydro_Groundwater_Data_Model/Temp here].

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | TimeSeries
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} 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 [http://en.wikipedia.org/wiki/Coordinated_Universal_Time UTC].
|-
| TsValue || {{Double}} || The numerical value itself.
|-
| TsValueS || {{String}} || 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.

{| class="wikitable collapsible" style="width: 90%; font-size: 85%;"
! colspan="3" | VariableDefinition
|-
! width="100"|Field !! width="50"|Type !! Description
|-
| {{PK}} TsID || {{Integer}} || Unique numerical identifier for the Time Series in the BC table. This is called "VarID" in Arc Hydro Groundwater.
|-
| TsName || {{String}} || 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 || {{String}} || 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.

{| class="wikitable collapsible" style="width: 20%; font-size: 85%;"
! colspan="2" | <span id="BcType">BcType</span>
|-
! colspan="2" {{IntegerType}}
|-
! width="50"|Code !! Name
|-
| 0 || undefined
|-
| 1 || head
|-
| 2 || flux
|}

{| class="wikitable collapsible" style="width: 20%; font-size: 85%;"
! colspan="2" | <span id="InputType">InputType</span>
|-
! colspan="2" {{IntegerType}}
|-
! width="50"|Code !! Name
|-
| 0 || undefined
|-
| 1 || value
|-
| 2 || rasterxy
|-
| 3 || rasterxz
|-
| 4 || rasteryz
|-
| 5 || eq_elev
|-
| 6 || eq_init
|}

{| class="wikitable collapsible" style="width: 20%; font-size: 85%;"
! colspan="2" | <span id="SetType">SetType</span>
|-
! colspan="2" {{IntegerType}}
|-
! width="50"|Code !! Name
|-
| 0 || Node
|-
| 1 || Edge
|-
| 2 || Face
|}

{| class="wikitable collapsible" style="width: 20%; font-size: 85%;"
! colspan="2" | <span id="DomainType">DomainType</span>
|-
! colspan="2" {{IntegerType}}
|-
! width="50"|Code !! Name
|-
| 1 || Porous_media
|-
| 2 || Fracture_media
|-
| 3 || Dual_media
|-
| 4 || Overland_media
|-
| 5 || Channel_media
|-
| 6 || Et_media
|}

[[Category:Under Development]]