RDBMS modelling for EPANET projects – Part 1

Class name

Parent class

Table name

Comments

User

N/A

auth_user

Django default
users.  Users own networks and can belong to groups and have security settings.

HydraulicOptions

–

– (abstract)

Network analysis options which are used in more than one place in EPANET: networks and program defaults.

Hydraulic options:
FlowUnits,
HeadlossFormula,
SpecificGravity,
RelativeViscosity,
MaximumTrials,
Accuracy,
IfUnbalanced,
DefaultPattern,
DemandMultiplier,
EmitterExponent, and
StatusReport.

TOptionsData

HydraulicOptions

wsproject_toptionsdata

Network analysis options.

Quality options:
QualityParameter,
QualityMassUnits,
RelativeDiffusivity,
TraceNode,
QualityTolerance,
(and MaximumSegments is not used).

Reactions options:
BulkReactionOrder,
WallReactionOrder,
GlobalBulkCoefficient,
GlobalWallCoefficient,
LimitingConcentration, and
WallCoefficientCorrelation.

Time options:
Duration,
HydraulicTimeStep,
QualityTimeStep,
PatternTimeStep,
PatternStartTime,
ReportTimeStep,
ReportStartTime,
ClockStartTime, and
ReportStatistic.

Energy options:
PumpEfficiency,
EnergyPrice,
PricePattern,
DemandCharge,
CHECKFREQ,
MAXCHECK, and
DAMPLIMIT.

TMapOptions

–

wsproject_tmapoptions

Network map options:
DispNodeIDs,
DispNodeValues,
DispNodesBySize,
DispNodeBorder,
DispLinkIDs,
DispLinkValues,
DispLinksBySize,
DispJuncs,
DispTanks,
DispPumps,
DispValves,
DispEmitters,
DispSources,
DispLabels,
LabelsTranspar,
NotationTranspar,
NodeSize,
LinkSize,
ArrowStyle,
ArrowSize,
ColorIndex,
NotationZoom,
LabelZoom,
SymbolZoom,
ArrowZoom, and
NotationSize.

CalibDataArrays

–

wsproject_calibdataarrays

Network calibration data for nodes
(demand, head, pressure, and quality) and links
(flow, velocity and head loss) can be stored in formatted files.
When EPANET networks are written in .net files, the names of any
specified files will be written but not the content of the files which must be
distributed with the .net file if others are to use it. To store the same
data in a multi-user RDBMS, the files are imported and stored in
the database in Base64Fields. The original file names are also
stored in the database to help with tracing data.

Calibration files for nodes:
NodeCalibDemandFname and NodeCalibDemand,
NodeCalibHeadFname and NodeCalibHead,
NodeCalibPressureFname and NodeCalibPressure,
NodeCalibNodeQualFname and NodeCalibNodeQual.

Calibration files for links:
LinkCalibFlowFname and LinkCalibFlow,
LinkCalibVelocityFname and LinkCalibVelocity,
LinkCalibHeadLossFname and LinkCalibHeadLoss,

DefProps

–

wsnetwork_defprops

Network property defaults for different
types of network objects.

Junction defaults:
DefJuncComment,
DefJuncTag,
DefJuncElevation,
DefJuncPrimaryDemand,
DefJuncPrimaryDemandPattern,
DefJuncDemandCategories,
DefJuncEmitterCoeff,
DefJuncInitialQuality,
DefJuncSourceQuality,
DefJuncSourcePattern, and
DefJuncSourceType.

Reservoir defaults:
DefReservComment,
DefReservTag,
DefReservHead,
DefReservHeadPattern,
DefReservInitialQuality,
DefReservSourceQuality,
DefReservSourcePattern, and
DefReservSourceType.

Tank defaults:
DefTankComment,
DefTankTag,
DefTankElevation,
DefTankInitialLevel,
DefTankMinLevel,
DefTankMaxLevel,
DefTankDiameter,
DefTankMinVolume,
DefTankVolumeCurve,
DefTankMixingModel,
DefTankMixingFraction,
DefTankBulkReactCoeff,
DefTankInitialQuality,
DefTankSourceQuality,
DefTankSourcePattern, and
DefTankSourceType.

Pipe defaults:
DefPipeComment,
DefPipeTag,
DefPipeLength,
DefPipeDiameter,
DefPipeRoughnessCoeff,
DefPipeMinorLossCoeff,
DefPipeStatus,
DefPipeBulkReactCoeff, and
DefPipeWallReactCoeff.

Pump defaults:
DefPumpComment,
DefPumpTag,
DefPumpHeadCurve,
DefPumpPower,
DefPumpSpeed,
DefPumpSpeedPattern,
DefPumpStatus,
DefPumpEfficiencyCurve,
DefPumpEnergyPrice, and
DefPumpPricePattern.

Valve defaults:
DefValveComment,
DefValveTag,
DefValveDiameter,
DefValveValveType,
DefValveSetting,
DefValveMinorLossCoeff, and
DefValveFixedStatus.

AnalysisDisplayColours

–

– (abstract)

Colours used for the colour-coded display of network analysis results.

5 colours for the 5 value
bands used when colouring nodes or links based on the results of
analysis in EPANET. 5 colours for nodes (MapNodeColour0-4)
and 5 colours for links (MapLinkColour0-4).
In EPANET, these colours are edited with the Color Ramp Selector
(View > Legends > Modify > Node/Link to display the Legend Editor and
then press the Color Ramp button).

AnalysisDisplayConfiguration

AnalysisDisplayColours

wsnetwork_analysisdisplayconfiguration

Values used to separate value bands in the colour-coded display of network analysis results. These values are also displayed on the node/link legend.

4 Map legend values for each
of several different node and link parameters.

Node parameters
(28 columns with NodeLegend prefix):
Elevation (1-4), BaseDemand (1-4), InitQual (1-4),
Demand (1-4), Head (1-4), Pressure (1-4) and NodeViews (1-4).

Link parameters
(40 columns with LinkLegend prefix):
Diameter (1-4), Roughness (1-4), BulkCoeff (1-4),
WallCoeff (1-4), Flow (1-4), Velocity (1-4), HeadLoss (1-4),
Friction (1-4), ReactRate (1-4) and LinkViews (1-4).

In EPANET, these values are edited with the Legend Editor
(View > Legends > Modify > Node/Link).

Note that in EPANET code, there is an
allowance for 4 more Link parameters (LinkLegendLength (1-4)), but these are not written
to the .net file and so are ignored here also.

Network

–

wsnetwork_network

A Network must belong to a User.

Some data fields stored in the database are not stored in EPANET .net files:

Fname (not essential, but can be helpful in identifying where the
network came from) and AutoBackup flag.

From here on, this class is defined (as closely as possible) to
include the fields in the order in which EPANET 2.00.12 writes them
to the .net file so that anyone familiar with the EPANET code will
find it easier to follow or verify (in the EPANET 2.00.12 source code
[download from EPA web site]
the writing operation is in file Ufileio.pas
function SaveProject() starting at line 137).

Ver,
nJUNCS (redundant),
nRESERVS (redundant),
nTANKS (redundant),
nPIPES (redundant),
nPUMPS (redundant),
nVALVES (redundant),
nLABELS (redundant),
nPATTERNS (redundant),
nCURVES (redundant),
nCNTRLS (redundant),
Title,
Notes,

AnalysisOptions (mandatory one to one link to TOptionsData object),

AutoLength.

(SaveProject() then writes Patterns, Curves, Junctions, Reservoirs, Tanks,
Pipes, Pumps, Valves, Controls and Labels)

MapLowerLeft (as PointField),
MapUpperRight (as PointField),
MapUnits,

MapBackDropFilename and
MapBackDropImage (store the entire image file as a Base64Field),
MapBackDropOffset (as PointField),
MapBackDropVisible,

MapOptions (mandatory one to one link to TMapOptions object).

SaveProject() then writes the colours used in displaying analysis
results (class AnalysisDisplayConfiguration, however, includes both
colours and legend intervals),

AnalysisDisplayConfig (mandatory one to one link to AnalysisDisplayConfiguration object),

CalibData (optional one to one link to CalibDataArrays object),

IDIncrement,
JunctionIDPrefix,
ReservoirIDPrefix,
TankIDPrefix,
PipeIDPrefix,
PumpIDPrefix,
ValveIDPrefix,
LabelIDPrefix,
PatternIDPrefix,
CurveIDPrefix,
ControlIDPrefix,

DefaultProperties (mandatory one to one link to DefProps object).

SaveProject() then saves the legend intervals included in
AnalysisDisplayConfiguration.

LMargin,
TMargin,
RMargin,
BMargin,
HeaderText,
HeaderAlignment,
HeaderEnabled,
FooterText,
FooterAlignment,
FooterEnabled,
PageNumbers,
TitleAsHeader,
Orientation.

NetworkObjectMixin

–

– (abstract)

A NetworkObject must belong to a Network,
so all classes inheriting from this class will belong to a network (NetworkID).

PhysicalNetworkObject

NetworkObjectMixin

– (abstract)

Adds Name (EPANET ID), Description and Tag.

TNode

PhysicalNetworkObject

wsnetwork_tnode

Adds Location (as a PointField),
InitialQuality, SourceQuality and SourceType (but not quality time pattern).

Junction

TNode

wsnetwork_junction

Adds Elevation,
EmitterCoefficient, and QualityTimePatternID (quality time pattern).

Demands are somewhat mixed up in EPANET.
The first demand, its pattern and the number of demand list entries are stored in a
Data array in the .net file, and this information is duplicated in a demand list.
However, the demand list also includes an optional demand category name for the first demand
which is only stored in that list. Such storing of duplicated information like this makes errors
more likely, so this modelling leaves out any demand information from this Junction class:
it is all stored in ordered Demand objects which point to this Junction. We do not even store the
count of demands at all since this is available from counting the number of Demand rows.
Another quick way to check would be to add 1 to the maximum Index number of Demands belonging
to the Junction.

A more exact mapping of the modelling in EPANET
would add BaseDemand, DemandPatternID and DemandCount to the Junction class, but these have
been omitted because they add unnecessary confusion.

Demand

NetworkObjectMixin

wsnetwork_demand

Demand(s) for a given Junction.
Adds JunctionID, Index (order), BaseDemand, DemandPatternID and CategoryName.

Note that since this class inherits from
NetworkObjectMixin, these rows store the network ID which is really redundant given
that the Junction it points to also has that information stored. Sometimes purity of
modelling is best sacrificed for performance. Storing this information makes it easy
to quickly fetch all the demands for a given network.

Reservoir

TNode

wsnetwork_reservoir

Adds Head, HeadPatternID,
QualityTimePatternID.

Tank

TNode

wsnetwork_tank

Adds BaseElevation, MinimumLevel, InitialLevel and MaximumLevel,
Diameter, MinimumVolume, VolumeCurveID, MixingModel, MixingFraction, ReactionCoefficient and QualityTimePatternID.

TLink

PhysicalNetworkObject

wsnetwork_tlink

Adds StartNode, EndNode and Path (as a LineStringField).

Pipe

TLink

wsnetwork_pipe

Having
all links storing their start and end points in a LineStringField helps with
doing spatial scans for links, a useful feature for later developments.

Adds Length,
Diameter, RoughnessCoefficient, MinorLossCoefficient, InitialStatus, BulkCoefficient
and WallCoefficient.

Pump

TLink

wsnetwork_pump

Adds Power,
HeadCurveID, RelativeSpeed, SpeedTimePatternID, InitialStatus, EfficiencyCurveID,
EnergyPrice and PricePatternID.

Valve

TLink

wsnetwork_valve

Adds Diameter,
ValveType, ValveSetting, MinorLossCoefficient and FixedStatus.

Curve

NetworkObjectMixin

wsnetwork_curve

Adds Name,
CurveType, Description and Coordinates (as MultiPointField).

Pattern

NetworkObjectMixin

wsnetwork_pattern

Adds Name, Description and Coordinates (as MultiPointField).

Control

NetworkObjectMixin

wsnetwork_control

Simple controls. EPANET does
not give these any identification at all and saves them simply as lines of text in order.
However this class inherits from NetworkObjectMixin, so it has a unique ID for each identified
rule or comment (or even empty line). One modelling option could be to
separate comments and empty lines from genuine rules, but this has not been done here.
Adds LinkID and ControlText.

Rule

NetworkObjectMixin

wsnetwork_rule

Rule-based
controls which have a name and can include comments.
Adds Name and RuleText.

MapLabel

NetworkObjectMixin

wsnetwork_maplabel

Adds Name, Text,
Location (as a PointField), AnchorNodeID, MeterType, MeterNodeID, MeterLinkID,
Font, FontSize, FontBold flag and FontItalic flag.

MeterNodeID and MeterLinkID
can both be null and one of them at most will be set.

Classname

Parent class

Tablename

Comments

User

N/A

auth_user

Django default
users.  Users own projects and can belong to groups and have security settings.

Project

–

wsnetwork_project

A Project
belongs to a user so that projects from other users can be filtered out.  Many Networks can belong
to one Project.  Defines
ID, User ID, Title, filename (not essential, but helpful in identifying
where the project came from and must be unique for projects owned by a
user), name, comments, version, date created, date modified, date last
saved, backup flag, open flag, open user and lots of other
WaterSums-specific information (map config etc.).

Network

–

wsnetwork_network

A Network must
belong to a Project so a Project ID field is added to the existing fields defined in the table above.