Database for EPANET RDBMS Modelling

EPANET RDBMS Modelling

WaterSums models EPANET networks in the same way as EPANET does and can read/write EPANET network (.net) and input (.inp) files. In WaterSums 2, we extend this modelling to include EPANET RDBMS modelling.  To do this, we store networks in a set of PostgreSQL database tables using the Django object to relational mapper to map the EPANET properties to RDBMS columns. Since we need to store geographic information, PostGIS and GeoDjango are used to provide spatial support.

This page has been updated from time to time – see the bottom of the page for the details of updates (last technical update was 9 September 2014).

One fundamental modelling choice is to decide the expected usage: single user or multiple users.  We have chosen to assume multiple users since this supports a single user with very little added complexity for that user, but makes it easy for workgroups to share network data between several, or even many, users.

The mapping of data fields from the EPANET network file format to PostgreSQL database columns has mostly been very simple and direct – except where changes need to be made to support multiple users or where some structural improvements have be made to remove confusion.  Multiple users need to be able to access their own networks easily without being distracted by the networks owned by other users. WaterSums uses the User class provided by the Django authentication system  to provide this ownership and access support.

WaterSums projects can contain multiple networks, but EPANET does not provide this support, so for most of this article we will ignore the Project class which allows multiple networks to belong to one project. Whereas WaterSums has a user owning projects which in turn contain networks, an EPANET-only model has users owning networks directly.  This is the modelling initially presented here. Note that EPANET uses the words ‘network’ and ‘project’ interchangeably. WaterSums, however, uses ‘network’ as the equivalent of an EPANET network or project, while also providing a project concept which represents a collection of networks.

EPANET uses object IDs (names) with certain uniqueness rules:

  • The ID for any kind of EPANET node (Junction, Reservoir or Tank) must be unique across all IDs for all types of nodes. For example, a Junction and a Reservoir cannot have the same ID/name.
  • The ID for any kind of EPANET link (Pipe, Pump or Valve) must be unique across all IDs for all types of links.

In the WaterSums  RDBMS data model, each class is modelled with a unique ID – including labels and controls – and all network classes also store a foreign key reference pointing to the network they belong to. WaterSums  also adds extra attributes to some classes (for example a ‘deleted’ flag) but these are ignored here, as is all undo/redo functionality.

To help match the ID restrictions of EPANET, all EPANET network classes are modelled with a class Meta parameter “unique_together” based on the network ID and the object name of the class of object for which EPANET requires the name to be unique (eg. all nodes must have a unique name so the TNode “Name” value must be unique).

In summary, this is a modelling of an EPANET project file which is as close to the EPANET storage format as possible whilst providing for multiple users who can own multiple networks but do not normally wish to see the networks of other users.

Note that the storing of program settings and defaults is not included in this modelling, only the storage of network information such as is saved by EPANET in .net files (including default values which are set for a project).

The table below allows Django models to be developed. If there is enough response to this article by comments below or through the Contact page, we will release a simplified version of the models file(s) after the final release of WaterSums 2. In fact, if there is enough interest in helping to make sure that a Django-based RDBMS schema for EPANET is made available and maintained as an open source project, WaterSums would also be happy to contribute all of this information to the task.  Please contact us if you are in a position to help with this.

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.

 

For completeness, the description change for the User class, the modelling changes necessary to the Network class and the WaterSums Project class itself are shown in the table below.

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.

 

Update history

  • 13 June 2018 edited to fix various links that broke when the site was migrated
  • 9 September 2014 to remove invalid Length attribute from Valve and change name of Tank attribute BottomElevation to BaseElevation
  • 6 September 2014 to change point storage for Curves and Patterns from LineStringFields to MultiPointFields.
  • 4-5 September 2014 to remove LinkLegendLength (1-4) and LinkLegendLinkQual (1-4) from
    AnalysisDisplayConfiguration class and add many links.
  • 25 August 2014 to add NotationSize to TMapOptions class.