Importing GIS Data

This GIS database contains latitude / longitude and elevation data for the entire geographical area.  In addition to maintaining this database, part of the designer’s responsibility is to perform hydraulic analyses on the areas which are being serviced.  PIPE-FLO® Professional software can perform these detailed network analyses.  So how can these tools be used together to optimize the effectiveness of the water distribution system design?

Let’s look at an example.  We’ll start with the GIS database.  The software that manages your GIS database allows you to trace the path of a particular pipeline from the source to it’s destination on a detailed map of the area.  This path can then be plotted out on a graph as a function of distance vs. elevation as shown in Figure 1.


Figure 1:  Distance vs. Elevation Plot of Selected Area

The software also allows you to extract the distance and elevation information for each data point along that plot.  This information can be exported to a CSV file which can then be pulled into a spreadsheet.  Now you have a spreadsheet with distance and elevation data which looks like Figure 2 (image only includes the first 10 of the 34 total data points).


Figure 2:  GIS Data Points for Distance and Elevation

Note that each Distance data point is a cumulative distance from the zero point at the start.  To consider that in between each data point is a single pipeline, so we will need to do some simple arithmetic to calculate  the length of each pipe segment.  This is easy to do in a spreadsheet, and we must keep in mind that the total number of pipe segments will always be one less than the total number of data points (nodes).  As such, we will drop one cell down from the top for our pipeline lengths so that the first pipe length will be on the same row as the node elevation where it terminates as shown in Figure 3.


Figure 3: GIS Data Points with Calculated Pipe Lengths

Let’s now switch to the PIPE-FLO® Professional program.  You can open the project titled “PIPE-FLO® GIS Test-1.pipe” to follow along.  In PIPE-FLO®, we have drawn 33 pipe segments in series with 34 nodes.  The pipe segments have been partially designed with pipe specifications, fluid zones, and all have been designated with 12 inch nominal sizes.  We have named the nodes “Node 0” through “Node 33” and have named the pipes “Pipe 1” through “Pipe 33”.  The node and pipe names are important for the transfer of data back and forth between the spreadsheet and PIPE-FLO® so we have pulled this information into our spreadsheet using the Datalink function.  Instructions for using Datalink are here.  We have placed the name columns ahead of the data columns by insertion, making sure to line up the names with the data.  The spreadsheet now looks like Figure 4.  You can open the spreadsheet titled “PIPE-FLO® GIS Test-1.xls” to work along from this point.


Figure 4:  Spreadsheet with Node names, Pipe names, and GIS data

Another thing you can do with PIPE-FLO® is take the image of the plotted GIS data and overlay it on the FLO-Sheet.  The GIS Plot image is located here.  By resizing the image, you can line up the data points vertically with the nodes.  Then, you can even move the nodes to line them up with the data points on the image.  Now, the model on the FLO-Sheet looks just like the GIS plot.  In Figure 5, the GIS plot image is shown lined up just below the PIPE-FLO® model.


Figure 5:  PIPE-FLO® Model with GIS Plot Superimposed

Back in our spreadsheet, we have Node and Pipe names from PIPE-FLO®, and we have Distance and Elevation from the GIS.  We have also calculated the pipe lengths from the distance data.  Now we need to generate the assignment scripts so that we can import the pipe length and node elevation data back into the PIPE-FLO® program.  The basic syntax for the importing of design data into PIPE-FLO® uses the following format (Python Language):

pipeflo().doc().get_devicetype(‘Device Name’).set_fieldname(valuetype(value, unit))

In the spreadsheet, we have our node names, pipe names, node elevations and pipe lengths set in separate cells.  So in order to generate a proper text string for the script, we will make use of the CONCATENATE function.  First, we will write an expression to assign an elevation to the first node.  In cell F2 we will type the following:


When you hit enter, the cell should then read as follows:

pipeflo().doc().get_node(‘Node 0’).set_elevation(elevation(636.320007,feet_elevation))

Now highlight the cell and drag the function down to the last row to fill in all the scripts.  Next, we will write an expression to assign a length to the first pipeline.  The first pipeline is in row 3, so in cell F3 we will type the following:

=CONCATENATE(“pipeflo().doc().get_pipe(‘”, B3,”‘).set_length(length (“,E3,”,feet_length))”)

When you hit enter, the cell should then read as follows:

pipeflo().doc().get_pipe(‘Pipe 1’).set_length(length (67.36,feet_length))

Again, drag this function down to the last row to fill in all the scripts.

Now the spreadsheet should look like Figure 6.


Figure 6:  Spreadsheet with Scripts

Now that we have the scripts, we simply need to copy them into a text file.  So highlight cells F2:F35 and copy the Node Elevation assignment scripts.  Then open up Notepad or some other text editor and paste.  Now, highlight cells G3:G35 and copy the Pipe Length assignment scripts.  In the same Notepad file, paste these scripts below the others.  Now save this text file, and rename the “.txt” extension to “.py”.  The text file should look like Figure 7.


Figure 7:  Text File with Scripts in Python Language

If you have the PIPE-FLO® project open, all you need to do to import the data is to find the Python script file you just created, and drag-and-drop the file onto PIPE-FLO®’s FLO-Sheet.  This will automatically write the data from the scripts into the model.  The other way to do this is to click the Data Import button  and browse to find the Python file.  Now if you zoom in on the FLO-Sheet, you can see that the node elevations and the pipe lengths have been specified in the model as shown in Figure 8.


Figure 8:  Node Elevations and Pipe Lengths Specified

Now you can proceed with the PIPE-FLO® model by placing a tank on the inlet to represent the retention pond and another on the outlet to represent the drainage ditch.