Difference between revisions of "GPKG Time Series Format Specification"

From Tuflow
Jump to navigation Jump to search
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
The below section lists the TUFLOW GPKG time series format specification. The specification sits ontop of the <b><u>[https://www.geopackage.org/spec120/index.html GPKG v1.2 Specification]</u></b> with two additional required tables. The below lists the details of the additional tables with examples. The examples are based a the 1D/2D linked TUFLOW-SWMM example model based on TUFLOW's module 6 tutorial model - <b>M06_5m_003.tcf</b>.
+
The below section describe the TUFLOW GPKG time series format specification. The specification is build ontop of the <b><u>[https://www.geopackage.org/spec120/index.html GPKG v1.2 Specification]</u></b> with two additional required tables. The additional tables and details of the GIS layers are descibed below with examples. The examples are based a the 1D/2D linked TUFLOW-SWMM example model based on TUFLOW's module 6 tutorial model - <b>M06_5m_003.tcf</b>.
  
 
=== TUFLOW Time Series Version ===
 
=== TUFLOW Time Series Version ===
−
An additional table called <Code>TUFLOW_timeseries_version</code>
+
An additional table called <Code>TUFLOW_timeseries_version</code> should be present in the GPKG. This table contains the version number of the TUFLOW GPKG time series format and should be set to <code>1.0.0</code>. The table should have the following columns:<br>
 +
{| class="wikitable" style="width:40%"
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Column Name
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Type
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=60%| Description
 +
|-
 +
|Version||TEXT||Major.Minor.Patch version number
 +
|-
 +
|}
  
 
=== Time Series Info ===
 
=== Time Series Info ===
−
...
+
An additional table called <code>Timeseries_info</code> should be present in the GPKG. The purpose of this table is to list the available result types and relevant information for each type. The table should have the following columns:<br>
 +
{| class="wikitable" style="width:50%"
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| Column Name
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| Type
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=70%| Description
 +
|-
 +
|row||INTEGER||Primary key
 +
|-
 +
|Table_name||TEXT||Reference to the GIS table that the result sits within
 +
|-
 +
|Count||INTEGER||Number of unique elements
 +
|-
 +
|Reference_time||TEXT||Reference time in the form of: <code>[UNITS] since YYYY-MM-DD hh:mm:ss</code>
 +
|-
 +
|dt||FLOAT||Output timestep (units defined in <code>Reference_time</code>)
 +
|-
 +
|Column_name||TEXT||Name of the column in the GIS table containing the result
 +
|-
 +
|Series_name||TEXT||Name of the result type
 +
|-
 +
|Series_units||TEXT||Result type units
 +
|-
 +
|}
 +
<br>
 +
<b>Example <code>Timeseries_info</code></b><br>
 +
{| class="wikitable" style="width:70%"
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=5%| Row
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Table_name
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=5%| Count
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=25%| Reference_time
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| dt
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| Column_name
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| Series_name
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=5%| Series_units
 +
|-
 +
|1||M06_5m_003_swmm_ts_P||22||hours since 2020-01-01 00:00:00||1.0||Depth||Depth||m
 +
|-
 +
|2||M06_5m_003_swmm_ts_P||22||hours since 2020-01-01 00:00:00||1.0||Water Level||Water Level||m
 +
|-
 +
|3||M06_5m_003_swmm_ts_P||22||hours since 2020-01-01 00:00:00||1.0||Storage Volume||Storage Volume||m3
 +
|-
 +
|4||M06_5m_003_swmm_ts_L||18||hours since 2020-01-01 00:00:00||1.0||Flow||Flow||cms
 +
|-
 +
|5||M06_5m_003_swmm_ts_L||18||hours since 2020-01-01 00:00:00||1.0||Velocity||Velocity||m/s
 +
|-
 +
|}
  
 
===GIS Layers===
 
===GIS Layers===
Line 11: Line 64:
 
<br>
 
<br>
 
The column names of the GPKG layers should follow the convention below:<br>
 
The column names of the GPKG layers should follow the convention below:<br>
−
{| class="wikitable" style="width:40%"
+
{| class="wikitable" style="width:50%"
 
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Column Name
 
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Column Name
 
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Type
 
! style="background-color:#005581; font-weight:bold; color:white;" width=20%| Type
Line 28: Line 81:
 
|TimeId||INTEGER||Unique id for each timestep
 
|TimeId||INTEGER||Unique id for each timestep
 
|-
 
|-
−
|Time_hours||FLOAT||Output time in hours
+
|Time_relative||FLOAT||Output relative time (reference time and units in <code>Timeseries_info</code>)
 
|-
 
|-
 
|Datetime||DATETIME||Output time in datetime
 
|Datetime||DATETIME||Output time in datetime
 
|-
 
|-
−
|colspan="3"|...[column for each result type for that given geometry type - examples below]
+
|colspan="3"|...[column for each result type for that given geometry type as listed in <code>Timeseries_info</code>- examples below]
 
|-
 
|-
 
|Flow||FLOAT||Result flow
 
|Flow||FLOAT||Result flow
Line 39: Line 92:
 
|-
 
|-
 
|}
 
|}
 +
<Br>
 +
The GIS layer should contain a feature for each applicable element and timestep i.e. the same pipe may be referenced 10 times if there are 10 output timesteps. The order should be ascending based on the output time.<Br>
 +
<b>Example <code>M06_5m_003_swmm_ts_L</code> with three output timesteps and three culverts</b><Br>
 +
{| class="wikitable" style="width:70%"
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=5%| fid
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| geometry
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| ID
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| Type
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| Source
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=5%| TimeId
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| Time_relative
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=15%| Datetime
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| Flow
 +
! style="background-color:#005581; font-weight:bold; color:white;" width=10%| Velocity
 +
|-
 +
|1||LINESTRING||FC01.1_R||CONDUITS||SWMM||1||1.0||2020-01-01T01:00:00Z||52.313579||5.3814558
 +
|-
 +
|2||LINESTRING||FC01.2_R||CONDUITS||SWMM||1||1.0||2020-01-01T01:00:00Z||66.050522||4.5863313
 +
|-
 +
|3||LINESTRING||FC04.1_C||CONDUITS||SWMM||1||1.0||2020-01-01T01:00:00Z||0.5314214||2.1729025
 +
|-
 +
|1||LINESTRING||FC01.1_R||CONDUITS||SWMM||2||2.0||2020-01-01T02:00:00Z||19.547256||2.2898435
 +
|-
 +
|2||LINESTRING||FC01.2_R||CONDUITS||SWMM||2||2.0||2020-01-01T02:00:00Z||50.088657||3.4779915
 +
|-
 +
|3||LINESTRING||FC04.1_C||CONDUITS||SWMM||2||2.0||2020-01-01T02:00:00Z||0.6140663||2.2505249
 +
|-
 +
|1||LINESTRING||FC01.1_R||CONDUITS||SWMM||3||3.0||2020-01-01T03:00:00Z||3.1847720||1.4646966
 +
|-
 +
|2||LINESTRING||FC01.2_R||CONDUITS||SWMM||3||3.0||2020-01-01T03:00:00Z||7.6079978||0.5282743
 +
|-
 +
|3||LINESTRING||FC04.1_C||CONDUITS||SWMM||3||3.0||2020-01-01T03:00:00Z||0.1079226||1.4705778
 +
|-
 +
|}
 +
 +
<br><br>
 +
{{Tips Navigation
 +
|uplink=[[TUFLOW_Viewer#GPKG_Time_Series_Format| Back to TUFLOW Viewer Main Page]]
 +
}}

Latest revision as of 20:49, 10 November 2023

The below section describe the TUFLOW GPKG time series format specification. The specification is build ontop of the GPKG v1.2 Specification with two additional required tables. The additional tables and details of the GIS layers are descibed below with examples. The examples are based a the 1D/2D linked TUFLOW-SWMM example model based on TUFLOW's module 6 tutorial model - M06_5m_003.tcf.

TUFLOW Time Series Version

An additional table called TUFLOW_timeseries_version should be present in the GPKG. This table contains the version number of the TUFLOW GPKG time series format and should be set to 1.0.0. The table should have the following columns:

Column Name Type Description
Version TEXT Major.Minor.Patch version number

Time Series Info

An additional table called Timeseries_info should be present in the GPKG. The purpose of this table is to list the available result types and relevant information for each type. The table should have the following columns:

Column Name Type Description
row INTEGER Primary key
Table_name TEXT Reference to the GIS table that the result sits within
Count INTEGER Number of unique elements
Reference_time TEXT Reference time in the form of: [UNITS] since YYYY-MM-DD hh:mm:ss
dt FLOAT Output timestep (units defined in Reference_time)
Column_name TEXT Name of the column in the GIS table containing the result
Series_name TEXT Name of the result type
Series_units TEXT Result type units


Example Timeseries_info

Row Table_name Count Reference_time dt Column_name Series_name Series_units
1 M06_5m_003_swmm_ts_P 22 hours since 2020-01-01 00:00:00 1.0 Depth Depth m
2 M06_5m_003_swmm_ts_P 22 hours since 2020-01-01 00:00:00 1.0 Water Level Water Level m
3 M06_5m_003_swmm_ts_P 22 hours since 2020-01-01 00:00:00 1.0 Storage Volume Storage Volume m3
4 M06_5m_003_swmm_ts_L 18 hours since 2020-01-01 00:00:00 1.0 Flow Flow cms
5 M06_5m_003_swmm_ts_L 18 hours since 2020-01-01 00:00:00 1.0 Velocity Velocity m/s

GIS Layers

The results are stored in GPKG layers, typically between 1 - 3 layers are stored in the result file depending on the output (one for point results, one for line results, one for polygon results). There is no requirement against storing results from multiple model runs in a single GPKG, this is just not typically how outputs are written.

The column names of the GPKG layers should follow the convention below:

Column Name Type Description
fid INTEGER Primary key
geometry GEOMETRY GeoPackage Geometry
ID TEXT Element ID (e.g. Pipe1)
Type TEXT Element Type (e.g. CONDUIT)
Source TEXT Result Source (e.g. SWMM)
TimeId INTEGER Unique id for each timestep
Time_relative FLOAT Output relative time (reference time and units in Timeseries_info)
Datetime DATETIME Output time in datetime
...[column for each result type for that given geometry type as listed in Timeseries_info- examples below]
Flow FLOAT Result flow
Velocity FLOAT Result velocity


The GIS layer should contain a feature for each applicable element and timestep i.e. the same pipe may be referenced 10 times if there are 10 output timesteps. The order should be ascending based on the output time.
Example M06_5m_003_swmm_ts_L with three output timesteps and three culverts

fid geometry ID Type Source TimeId Time_relative Datetime Flow Velocity
1 LINESTRING FC01.1_R CONDUITS SWMM 1 1.0 2020-01-01T01:00:00Z 52.313579 5.3814558
2 LINESTRING FC01.2_R CONDUITS SWMM 1 1.0 2020-01-01T01:00:00Z 66.050522 4.5863313
3 LINESTRING FC04.1_C CONDUITS SWMM 1 1.0 2020-01-01T01:00:00Z 0.5314214 2.1729025
1 LINESTRING FC01.1_R CONDUITS SWMM 2 2.0 2020-01-01T02:00:00Z 19.547256 2.2898435
2 LINESTRING FC01.2_R CONDUITS SWMM 2 2.0 2020-01-01T02:00:00Z 50.088657 3.4779915
3 LINESTRING FC04.1_C CONDUITS SWMM 2 2.0 2020-01-01T02:00:00Z 0.6140663 2.2505249
1 LINESTRING FC01.1_R CONDUITS SWMM 3 3.0 2020-01-01T03:00:00Z 3.1847720 1.4646966
2 LINESTRING FC01.2_R CONDUITS SWMM 3 3.0 2020-01-01T03:00:00Z 7.6079978 0.5282743
3 LINESTRING FC04.1_C CONDUITS SWMM 3 3.0 2020-01-01T03:00:00Z 0.1079226 1.4705778



Up
Go-up.png Back to TUFLOW Viewer Main Page
Retrieved from "https://wiki.tuflow.com/w/index.php?title=GPKG_Time_Series_Format_Specification&oldid=34873"