Cornell Culverts Instructions
CornellCulvertsInstructions
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 34
| Download | |
| Open PDF In Browser | View PDF |
The Cornell Culverts Model
Last Updated: 11/12/2018
Created by David Gold, edits: Allison Truhlar, Jo Archibald
Instructions for the CulvertStartKitAndFileTemplate download (rename the folder with your project
name)
Soil and Water Lab
Cornell University
Objective: To identify undersized culverts for both current and future precipitation estimates to
facilitate investment in climate resilient communities.
Model Description: The Cornell culvert model uses culvert data collected in the field in conjunction
with publicly available topography, precipitation, land use and soil data to delineate the watershed of
individual culverts, compute the peak discharge generated over each culvert watershed for a range of
storm events, and evaluate the hydraulic capacity of each culvert.
This model consists of four main components: 1. watershed delineation, 2. peak discharge calculation, 3.
capacity calculation, and 4. return period assignment. The watershed component of the model is
conducted using ArcGIS, while the peak discharge calculation, capacity calculation and return period
assignment are executed using Python scripts.
1. The watershed delineation component of the model is conducted on ArcGIS using custom tools
created by Rebecca Marjerison for her PhD dissertation. The tools first delineate the watershed
of each culvert. Next, all culvert watersheds being evaluated are aggregated into a single
shapefile. Finally, the area, weighted Curve Number (CN) and Time of Concentration (Tc) are
computed for each watershed.
2. The second component of the model is the peak discharge calculation. The watershed data
compiled in the initial phase of the model is used as the input for this component. The
procedure set in the USDA Natural Resources Conservation Service (NRCS) Technical Release 55
(TR-55) graphical method is used to determine peak discharge for various return period storms
for each delineated watershed.
3. The third component of the model is the calculation of culvert capacity. Using field data, the
capacity of each culvert is modeled using the inlet control equation set forth by the Federal
Highway Administration Hydraulic Design Series 5. In this model, the headwater ponding height
was assumed to be the height of the road surface above the culvert invert.
4. In the final component of the model, the assigned capacity of each culvert is compared against
the peak discharges calculated for the culvert in order to determine the maximum return period
storm that the culvert can safely pass.
1
Model Inputs:
1. Culvert data collected by field data teams using either the NAACC protocol or the Tompkins
county storm water data collection protocol.
2. The precipitation depths from the 1, 2, 5, 10, 25, 50, 100, 200 and 500 year 24-hour return
period storm events in the watershed of interest.
3. DEM tiles covering the entire watershed of interest (accuracy of the model output is dependent
on accuracy of the DEM used, therefore the highest resolution DEM available for the watershed
of interest should be used).
4. New York state curve number raster constructed by Rebecca Marjerison.
Model Outputs:
1. current_runoff.csv: Contains the runoff from the 1, 2, 5, 10, 25, 50, 100, 200 and 500 year return
period 24-hour storm events under current precipitation.
2. future_runoff.csv: Contains the runoff from the 1, 2, 5, 10, 25, 50, 100, 200 and 500 year return
period 24-hour storm events under projected 2050 precipitation.
3. culv_geom.csv: Contains calculated geometry values of each culvert including area and
necessary constants used in the capacity equations.
4. capacity_output.csv: Contains the capacity of each culvert in m3/s.
5. return_periods.csv: Contains the maximum return period storm event that each culvert can
safely pass under current and projected 2050 precipitation estimates.
6. model_output.csv: Contains a summary of the model output including the maximum return
period that each culvert can pass for current and future precipitation, the capacity of each
culvert, the culvert GPS coordinates, the cross sectional area of each culvert, and the computed
watershed area, time of concentration and curve number for each culvert.
Required Software:
This model was developed and tested using:
1. ESRI ArcMap 10.3.1
2
2. Python 2.7 (comes with the ArcMap 10.3.1 installation)
a. NumPy 1.7.1 package (comes with the ArcMap 10.3.1 installation, also available at
https://pypi.python.org/pypi/numpy)
3. IDLE, a Python editor (comes with the ArcMap 10.3.1 installation)
4. Microsoft Excel
5. (Optional) R version 3.4.3
3
Flow Charts of Model:
4
5
Procedure:
Part 0: Set up your file structure
The suggested file structure is shown below. Each rectangle represents a folder (or in the case of). Blue
rectangles correspond to the folders i the CulvertStartKitAndFileTemplate folder that do not need to be
renamed. This file structure can be downloaded from the Cornell Soil & Water Github repository
(https://github.com/SoilWaterLab/CulvertStartKit). The red rectangle corresponds to the
CulvertTools.tbx, an Arc Toolbox Orange rectangles are folders that should be renamed by the user, the
original file name is indicated in square brackets [], with an example name below:
All_Culverts_shapefile
CulvertTools.tbx
CulvertStartKit
[CulvertStartKitAndFileTemplate]
CulvertEvaluation
Working directory
[AAA_Data_folder]
(example: ALB)
(example: Albany_2017)
DEMs
GIS_files
Temp
WS_Poly
To download the CulvertStartKitAndFileTemplatefrom Github, first navigate to the links above. Then,
click on the green button on the right side of the webpage that reads “Clone or Download.” A dropdown menu will appear, select “Download ZIP.” (see image on next page).
6
The current naming convention for the data folder is a three-letter acronym. For example, if analyzing
culverts in Albany county, the data folder would be named ALB. The working directory can have any
descriptive name.
7
Part 1: Download data and prepare GIS file
Step 1: Export Data
If using NAACC data:
I.
II.
III.
Download the NAACC spreadsheet.
Save the data in .csv format into your “data” folder within CulvertEvaluation . For the file
name, use the same three-letter acronym as the folder name (e.g., ALB.csv).
See note below about the column order required for NAACC data.
Note: To input data using the NAACC formatting, even if not sourced from NAACC, ensure these data are
in the correct column for proper interpretation by the model. Only the bolded columns are required.
All other columns can be empty. See Sample_NAACC_data.csv in CulvertModel_StartKit.
A. Survey_Id
L. Crossing Type (using the same word descriptors as is used in NAACC protocol, i.e. “Bridge”,
“culvert”)
T. GPS_X_Coordinate (if corrected GPS coordinates are available, ensure they are in these columns)
U. GPS_Y_Coordinate (if corrected GPS coordinates are available, ensure they are in these columns)
W. Inlet_Type (using the same word descriptors as is used in NAACC protocol, i.e. “projecting”,
“wingwall”)
Y. Number_Of_Culverts
AB. Road_Fill_Height
AJ. NAACC_CulvertID
AN. Crossing_Structure_Length
AR. Inlet_Height
AS. Inlet_Structure
AT. Inlet_Substrate_Water_Width
AU. Inlet_Water_Depth
AV. Inlet_Width
AX. Material [“Dual-Walled HDPE”, “Plastic”, “Corrugated HDPE”, “Smooth Metal”, “Corrugated
Metal”, “Metal”, “Concrete”, “Stone”]
BC. Outlet_Height (not currently used because of Inlet Control assumption)
BD. Outlet_Structure (not currently used because of Inlet Control assumption)
BE. Outlet_Substrate_Water_Width (not currently used because of Inlet Control assumption)
BF. Outlet_Water_Depth (not currently used because of Inlet Control assumption)
BG. Outlet_Width (not currently used because of Inlet Control assumption)
BJ. Slope_Percent
8
If using Fulcrum data:
I.
II.
III.
IV.
V.
VI.
VII.
VIII.
IX.
X.
Log onto fulcrum
Click on the “Stormwater culvert data collection” app
Click on “export data” (lower left hand side of page)
Ensure file type is CSV
Choose “Mobile Device Created Time ”, “Eastern Time”
Check “Include GPS data”
Set the date range that you’d like data from
Hit next then hit finish on the next page
Wait for the data to finish exporting, then click download and save the zip file to your
project folder.
Extract the zip file to the “data” folder within the subfolder you’ve created for your county
or watershed.
Explanation: This model has been constructed to accept data in two different formats, the method of
data collection will dictate which format the data has been collected in. If the data was collected
following the North Atlantic Aquatic Connectivity Collaborative (NAACC) protocol, follow the NAACC
data procedure. If the data was collected for the Tompkins county stormwater project, follow the
Fulcrum procedure.
I.
II.
Step 2: Data extraction Ensure the downloaded .csv field data file is in the data folder within
“CulvertEvaluation ” folder
Run the “extract” python script
a. Right click on “Extract” Python file in the CulvertEvaluation folder
b. Select “Edit” or “Edit with IDLE”
c. F5 on your keyboard will activate the user interface. Alternatively, from the “Run”
dropdown menu, select “Run Module”.
d. Type the full path or relative path from the CulvertEvaluation Folder (e.g. ALB/ALB.csv)
of the file containing your culvert field measurements
e. When asked for Watershed abbreviation, type the three letter abbreviation you used to
name your data folder within CulvertEvaluation.
i. For example: If modeling all culverts in Albany county, enter “ALB.”
f. If data is from fulcrum, type “F” when prompted, if the data is from NAACC, type “N”.
g. Your data will output to the data folder as two csv files, starting with the three-letter
watershed abbreviation.
i. For example: The extract script outputs ALB_field_data.csv and
ALB_not_extracted.csv to the CulvertEvaluation folder. Move both of these files
to the data folder “ALB.”
ii. See sample_field_data.csv and sample_not_extracted.csv files in the
CulvertModel_StartKit
9
What is in the field_data.csv and not_extracted.csv files?
Field_data.csv is your culvert crossing data, ready to be used in the ArcGIS part of the model.
Not_extracted.csv contains culvert crossing data that was rejected for modeling. Reasons for
rejection, in the order evaluated within the extract.py script, are:
o Negative measurement values for inlet width, inlet height, HW, or culvert length.
o If the crossing type is marked as bridge, and the inlet shape is not Box/Bridge with
Abutments or Open Bottom Arch Bridge/Culvert.
o If the crossing type is marked as bridge, the inlet shape is Box/Bridge with Abutments or
Open Bottom Arch Bridge/Culvert, and the inlet width is more than 20 ft.
Explanation: The raw field data spreadsheet, whether from Fulcrum or NAACC, contains extraneous
information that crowds the data sheet and makes it difficult to find the relevant culvert information.
The extract script creates a new spreadsheet that contains only the information relevant to culvert
evaluation. A second new spreadsheet is also created that captures all of the data points that did not
meet the modeling criteria because of missing information or dimensions unacceptable to the current
model.
Step 3: Create and save the GIS file
I.
Open a new GIS file and save it to your working directory (see green rectangle below).
All_Culverts_shapefile
CulvertEvaluation
Data folder
(example: ALB)
CulverStartKit
CulvertTools.tbx
Working directory
(example: Albany_2017)
DEMs
GIS_files
Temp
ArcMap file
(example: ALB.mxd)
II.
WS_Poly
Set the coordinate system to: “Projected Coordinate System: UTM-NAD 83 Zone 18N” for
culverts located in the Eastern majority of New York State.
a. In the table of contents, right click on “Layers” and select “Properties”
10
III.
b. Select the “Coordinate System” tab
i. Navigate to Projected Coordinate System: “UTM-NAD 83 Zone 18N”
Load the Culverts Toolbox into ArcToolbox
a. Right click within the ArcToolbox window and select “Add Toolbox.” Then navigate to
the location of the CulvertTools file.
Step 4: Load culvert and elevation data into GIS map
I.
II.
In ArcCatalog, locate extracted culvert field data file in your data folder, right click and select
“Create Feature Class” and then “From XY Table”. In the dialog box:
a. Ensure that the X field is set to “Long” and the Y field is set to “Lat”
b. Select “Coordinate System of Input Coordinates”
c. Select: Geographic Coordinate system-> World-> WGS 1984
d. Specify your working directory as the location for new shapefile to be saved
Project new shapefile to UTM coordinates
a. Arctoolbox Data Management ToolsProjections and TransformationsProject
(or just search “project”)
b. Choose the newly created shape file as your target file
c. Select Projected Coordinate System: “UTM-NAD83 Zone18N” (or whatever coordinate
system is appropriate for your culverts’ location. This should be the same as what you
set in Step 3) as new coordinate system
d. Remove unprojected shapefile from the GIS file
Explanation: This step converts the field data from a .csv file to a shapefile that can be used in ArcGIS.
The GPS points collected are in the WGS84 coordinate system, so we need to first make sure that GIS
imports the points in the appropriate location as WGS84, then projects the newly created shapefile to
UTM coordinates.
11
Step 5: Download DEMs
Note: If at Cornell, do not follow the below steps. Instead, use the stitched 10m DEM of NYS
available on the WRI server (WRI Interns > Culvert Project > GIS_Data >
NYS_10m_DEM_mosaic). You can optionally clip this DEM to the extent of your county of
interest. County boundaries are available from the NYS GIS Clearinghouse or CUGIR. If
necessary, first merge together counties. To clip a raster (DEM) with a polygon (the county
boundaries), you can use Data Management > Raster > Raster Processing > Clip.
I.
II.
III.
IV.
Add a basemap to better visualize culvert locations:
a. Select the add layer icon,
, then choose “Add Basemap” and select the one titled
“streets” (or another of your choice that includes street locations)
Download the highest resolution DEM available (often 1m, 2m or 10m resolution)
For culverts located in New York State:
a. Go to the NYS GIS clearinghouse (gis.ny.gov)
b. Click on the “Data” tab on the left of the gray bar (as of April 2016)
c. Click on “Elevation” in the Data tab dropdown
d. From the top of the list of options on the left-hand side of the window, select
“Statewide Interactive DEM Download”
e. Selecting an option from the Download button,
, from the right-hand menu of
symbols, encircle the area of your desired DEM coverage, referencing the locations of
culvert points on your ArcMap to determine which LiDAR tiles are needed.
f. From the download results menu that appears on the left-hand side of the window,
select available LiDAR tiles with the highest resolution from the “DEM Results”
dropdown. Click on each tile name to download.
Create a new folder in your working directory called “DEMs” and copy the downloaded DEMs to
that folder.
Add LiDAR tiles to the GIS file
Explanation: The NY state GIS clearinghouse is a repository for publically available GIS data in the state.
This includes 10-m data statewide, 2-m LiDAR DEMs in Tompkins County and 1-m and 2-m LiDAR DEMs
in the Hudson Valley. The LiDAR data can be downloaded in tiles, which can be found on the NYS
Orthoimagery app. Each LiDAR tile has a unique number. The basemap may be helpful in identifying the
rough location of the DEM tiles.
12
Part 2: Watershed delineation, Tc and aggregated CN calculation using ArcGIS
Step 6: Mosaic DEMs
I.
II.
Note: This step is not required if you have county-wide LiDAR data available, or if you are
using the state-wide 10m DEM file from Cornell.
Use the mosaic tool to combine all DEMs
a. Arctoolbox Data Management Raster Raster Dataset Mosaic to New
Raster (or search “mosaic to new raster”)
i.
(If working with two sets of DEM tiles at different resolutions, first
mosaic tiles of the same resolution. Then, resample the lower resolution
mosaic to the cell size of the higher resolution mosaic before
proceeding).
ii.
Choose all DEM tiles as input rasters
iii.
Select the DEM folder as output location
iv.
Name your new raster as “yourwatershedname_DEM.img”
v.
Check if the Coordinate System of the DEMs are UTM-NAD 83 Zone 18N
(or whatever you set in Step 3).
1. If not, first mosaic to their current coordinate system using the
“mosaic to new raster” tool, then using the “project raster”
tool, project the mosaiced DEM to “UTM-NAD 83 Zone 18N” (or
whatever you set in Step 3).
2. If so, choose “Projected Coordinate System: UTM-NAD 83 Zone
18N” (or whatever you set in Step 3) as special reference for
raster
vi.
Pixel Type: 32 bit float (or check the property of your DEM file)
vii.
Set Number of bands equal to 1
viii.
Hit “ok”
Explanation: This tool aggregates all DEM Lidar tiles into a single tile. For a tutorial on the procedure,
see this video: https://www.youtube.com/watch?v=MxoaylTyNKg
Step 7: Burn Streams
I.
II.
III.
Note: This step is not necessary if working with 1m DEM data.
Ensure that the Spatial Analyst extension is activated.
Add a layer of national hydrography dataset (NHD) flowlines specific to your state or region,
projected to UTM coordinates as assigned in Step 3. Hereafter called NHDFlowline_p.shp
a. The NHD Flowlines can be downloaded from the USGS and then clipped to the extent of
your county of interest.
https://viewer.nationalmap.gov/basic/?basemap=b1&category=nhd&title=NHD%20Vie
w
13
IV.
V.
VI.
VII.
VIII.
Create a new shapefile for clipping that encompass all the culvert points as well as sufficient
additional area to include contributing flowlines. Alternatively, use the county boundaries.
Using the Clip (analysis) tool, clip NHDFlowline_p.shp by the clip shapefile (Step 7, III.) to create
“flow_clip.shp”
Conversion Tools To Raster Feature to Raster to convert the clipped flowline
“flow_clip.shp” to raster “flow_ras”
a. Use the PERMANENT_ field (?)
b. Output cell size should be the same as the DEM. To do this, click on Environments >
Output Coordinates. Under the Output Coordinate System drop-down menu, select
“Same as Layer ‘yourDEM’”. Then go to Environments > Processing Extent. Under the
Extent drop-down menu, select “Same as layer yourDEM.” Under the Snap Raster dropdown menu, also select your DEM file. Save these changes by clicking okay. Your
Output cell size should now be set to the same size as your DEM cell size (you can check
the DEM cell size by right clicking on the DEM, navigating to Properties > Source, and
looking at Cell Size under Raster Information.
Spatial Analyst Reclass Reclassify
a. Set the input raster to flow_ras.
b. Reclassify the old values of NODATA as new values of 0.
c. Reclassify all other values as new values of 1.
d. Save the output raster flow_burn.
Spatial Analyst Map Algebra Raster Calculator
a. Enter the following expression:
CON(“flow_burn” == 1, “yourwatershedname_DEM”, “yourwatershedname_DEM” +
maximum elevation of yourwatershedname_DEM).
b. Save as yourwatershedname_DEMburn
Explanation: “The first step in creating a hydrologically correct DEM is to ‘burn streams’. Stream burning
ensures that flow is forced to those cells that correspond to the true locations of streams. Stream
burning is somewhat of a misnomer since it actually involves raising the elevation of all cells in the DEM
that do not fall along the stream network. The general rule for stream burning is that all non-stream
cells in the DEM should be raised by an amount greater than the DEM’s highest elevation. You will do
this by converting the stream vector data to raster then building an equation that raises all cells in the
DEM that do not fall along streams by the max elevation of the DEM (e.g. 1300).”
14
Step 8: Run the DEM_prep tool
I.
II.
III.
Be aware that this tool and others may be small and hard to notice in a corner of your screen
when it is first opened.
In the Culvert_Tools toolbox, right click on DEM_prep and select “edit”.
Double click on the blue circle that says “Projected DEM.” In the drop-down dialogue that
appears, select yourwatershedname_DEMburn
15
IV.
Double click on the Blue Circle that says “Workspace”. In the dialogue that appears,
navigate to your DEM folder.
V.
Hit the check mark to validate that all inputs are in properly
VI.
Run the model by clicking the blue arrow:
Explanation: This tool will create two new rasters from your mosaiced DEM. First, it will create a flow
direction1 raster, then it will create a flow accumulation2 raster. These rasters will be used in the
delineation of culvert watersheds.
Note: As a quick check for whether the tool is working correctly, the flow direction file it creates should
have 8 distinct values (1,2, 4, 8, 16, 32, 64, and 128), which indicate the relative elevation of a given cell
to its eight adjacent neighbor cells. Flow travels towards lower cell values.3
1
2
http://help.arcgis.com/EN/arcgisdesktop/10.0/help/index.html#//009z00000063000000.htm
http://help.arcgis.com/EN/arcgisdesktop/10.0/help/index.html#/How_Flow_Accumulation_works/009z00000062000000/
3
http://desktop.arcgis.com/en/arcmap/10.3/tools/spatial-analyst-toolbox/flow-direction.htm
16
------------------------------------------------------------------------------------------------------------------------------------------
Optional steps: Step 9 and 10
Step 9: Create flow paths from flow accumulation raster (Optional)
I.
II.
Use Raster calculator to extract largest flow paths from layer
a.
Arctoolboxspatial analyst map algebra raster calculator (or just search “raster
calculator”)
b.
Enter the equation SetNull("watershedabbreviation_DEMfacc.tif"<5000,1)
i.
Double click on “SetNull” in the conditional box
ii.
Double click on your flow accumulation layer in the layers and variables box to
add it into the set_null calculation before the comma
iii.
Set all values of the flow accumulation less than 5000 to be equal to 1
c.
Set your output location and name the new raster
d.
Hit ok
Convert your new raster layer into a shapefile using the “raster to polyline” tool
a. Arctoolboxconversion tools from raster raster to polyline (or just search “raster
to polyline”)
b. Choose your newly created raster as the input raster
c. Name the shapefile you will be creating “watershedabbreviation_flowpaths” and save
it to a desired location
d. Hit “ok”
Explanation: This step will create a flow network from the flow accumulation layer. This will give you a
rough idea of where the areas of highest flow accumulation are, as predicted by the DEM. This is a
useful step for visualizing where GIS will likely delineate the culvert watersheds and assessing the
accuracy of culvert GPS points.
Step 10: Asses point locations, move points that seem to be inaccurate to the closest flow
paths (Optional)
I.
II.
Search through each point to ensure that it accurately reflects the true location of the
culvert.
To move a culvert (see note), right click on the culvert point layer, and select “Edit Layer”
a. Document which culverts have been moved manually
Note: The Satellite Imagery basemap is helpful for this step. Be very careful here, unless the proper
location is obvious and a clear GPS error is apparent, do not manually manipulate the culvert’s
location. Many culverts are not on major waterways and the resolution of the DEM can influence
flow accumulation lines (visualized in Step 8).
------------------------------------------------------------------------------------------------------------------------------------------
17
Step 11: Culvert Watershed Delineation
I.
II.
In the Culvert Toolbox, right click on the Culvert_ws_delineation tool and select “edit”
Double click on the blue circle furthest to the left, and select your projected culverts point
layer
flow l
III.
Double click on the Iterate Feature selection. Input Features = Points Layer UTM. Field =
BarrierID.
18
IV.
Double click on the flow accumulation bubble. Select your flow accumulation raster.
19
V.
Double click on the snap pour point rectangle.
a. Input raster or feature pour point data = Selected_point
b. Ensure that the pour point field exists for the layer (e.g. FID or NAACC_ID)
c. Input accumulation raster = flow accumulation
d. Ensure the “output raster” file path exists in your system. Keep Snap%code% as the file
name.
e. Ensure that the snap distance at the bottom is set to 20 m (or desired snap distance).
20
VI.
Double click on the flow direction bubble and select your flow direction raster
VII.
Double click on the Watershed square.
a. Input flow direction raster = flow direction
b. Input raster or feature pour point data = Snap%code%
c. Pour point field (optional) = VALUE
d. Ensure the output raster path exists in your system, keeping the default file name
(Snap%code%ws.tif)
21
VIII.
IX.
Ensure you have an empty folder named WS_Poly in your working directory (see Step 3.I.)
Double click on the Raster to Polygon yellow rectangle
a. Input raster = ws raster
b. Field (optional) = Value
c. Output polygon features = Navigate to your WS_Poly folder and then add
%code%ws.shp
X.
Hit the check mark to validate that all inputs are entered properly:
XI.
XII.
Run the model by clicking the blue arrow:
Your map should looking something like the below picture after the tool finishes. Note that
it is common for 1/3 to ½ of culverts to have a very small delineated watershed area (< 1
ha). This is something that we are actively working on fixing in the model. These small
watersheds will not be processed in later steps of the model.
22
Explanation: This tool will delineate the watershed4 of each culvert using the flow direction and flow
accumulation rasters. Each culvert’s watershed will save to the WS_Poly folder and will take the same
name as its culvert point.
4
https://developers.arcgis.com/rest/elevation/api-reference/watershed.htm
23
Step 11: Append WS
I.
II.
III.
IV.
Ensure the All_Culverts shapefile in your WD has an empty attribute table by right clicking
on the layer in the Table of Contents and selecting “open attribute table”
In the Culvert Toolbox, right click on the append_ws tool and select “edit”
Double click on the blue ellipse that says “watershed folder” and select your WS_Poly folder
Double click on the yellow rectangle that says “Dissolve.”
a. Input features = selected watershed
b. For the Output Feature Class, verify that the Temp path is correct for your system. Keep
the file name %name%dissolve.shp
c. Keep all other defaults.
24
V.
Double click on the blue ellipse on the right hand side of the model that says “All_Culverts.”
Select the All_Culverts shapefile.
VI.
VII.
Validate and run append ws.
Your map should look just like your delineated culvert watershed map, but all the
watersheds will be the same color.
Explanation: The append_ws tool amalgamates all the culvert watersheds into a single shapefile. Each
individual watershed then becomes an attribute in this new “All_Culverts” shapefile.
Step 12: Prepare the All_Culverts shapefile for export
I.
Add empty columns to the All_Culverts file for Area, Time of Concentration and Curve
Number
a. In the table of contents, right click on the All_Culverts and select “open attribute table”
b. Click on the drop down in the top left corner of the attribute table and select “Add
Field”
i.
Title the new field “Area_sqkm” and select “float” as the type. Leave the
defaults for precision and scale.
c. Add another field titled “Tc_hr” with type “float”
d. Add a third field titled “CN” with type “float”
e. Right click on the heading of the Area_sqkm field and select “Calculate Geometry” and
hit “yes” when the dialogue box pops up
i.
Ensure that “area” is listed in the property field
ii.
Select square kilometers for the units
iii.
Hit “ok”
25
Explanation: In this step we are setting up our watershed data file to be exported. In order to calculate
the peak discharge, we will need the area, time of concentration (Tc) and Curve Number (CN) for each
culvert watershed. The area is calculated in this step. Tc and CN will be calculated in the next two steps.
The python code to be run later on relies on column order to track inputs, so following the order above
when you add fields is important.
Step 13: Calculate the Tc for each culvert watershed
I.
II.
III.
Calculate the watershed slope using the Slope tool
a. Create a filled version of the NYS DEM (or clipped county DEM). Use the Fill tool under
Spatial Analyst.
b. Arctoolbox Spacial AnalystSurfaceSlope
c. Select the filled DEM you just created. NOT the filled burned DEM created in Step 7.
d. Select percent rise for type
e. Save the new raster to your DEM folder
Calculate the watershed flow lengths (For a county, on the new computer, this step takes
about 5 min)
a. Arctoolbox Spacial AnalystHydrologyFlow length
b. Select the flow direction. This can be the burned flow direction raster from Step 7.
c. Ensure that method is set to “UPSTREAM”
d. Save the new raster to your DEM folder
In the Culvert Toolbox, right click on the Tc tool and select “edit”
a. Double click on the blue ellipse furthest to the left and select the “All_Culverts” file as
model input
26
b.
Double click on the flow length ellipse and select the newly created flow length raster
c.
Double click on the slope ellipse and select the newly created slope raster
27
d.
IV.
Double click on both yellow Clip squares and ensure the path to your Temp folder is
correct. Keep the default file name (flen_temp.tif for the flow length clip, and
slope_temp.tif for the slope clip).
Validate and run the model
All_Culverts will be filled in.
. No new files will be created. The Tc field in
Explanation: This tool uses the Kripitch equation to calculate time of concentration for each culvert
watershed. The slope of each watershed is taken by clipping the slope raster for the entire watershed to
the boundaries of each culvert watershed, then calculating the average slope for each watershed. The
flow length is taken by clipping the flow length raster of the entire watershed to each culvert watershed
and selecting the longest flow path inside each culvert watershed.
Step 14: Calculate the weighted CN for each culvert watershed
I.
II.
Add a raster of curve numbers for your area of interest to the table of contents
a. A curve number raster has been created for NY by Rebecca Marjerison for use in this
model. It is based on 2006 landuse and 2010 soils data from New York State. It is
available from the Cornell Soil and Water Lab.
Project Curve Number raster (Project Raster (Data Management)) to your UTM coordinate
system from Step 3 (NAD_1983_UTM_Zone_18N for Eastern NY)
a. Save as “CN_UTM.tif”
28
III.
IV.
V.
Clip(Data Management) “CN_UTM.tif” by “All_culverts” to get “CN_UTMclip”
a. Check “Use Input Features for Clipping Geometry”
Resample (Tools/Data Management Tools/Raster/Raster Processing/Resample)
“CN_UTMclip” to the size of the DEM, call it CN_UTMclipRes
In the Culvert Toolbox, right click on the CN tool and select “edit”
a. Double click on the blue ellipse farthest to the left and select the “All_culverts” layer
b. Double click on the blue ellipse that has an arrow going to the yellow “clip” ellipse and
select the resampled CN_UTMclip.tif as the input file
29
c. Double click on the yellow rectangle labeled “Clip”
a. Input raster = CN_UTMclipRes (the resampled CN raster)
b. Output extent = I_fc_2m_merge_BarrierID
c. Ensure the Output Raster Dataset path exists in your system. Keep the default
file name (cn_temp)
d. Verify and run the model (If error results, see note below)
e. Ensure that the temporary files are being saved to a proper location on your machine
Explanation: The NY_Curve_Number.tif file is a raster image created by Rebecca Marjerison that
combines 2006 landuse and 2010 soils data from New York State into a single Curve Number raster file.
Each pixel value in the raster represents a curve number, the CN tool clips the raster to each individual
culvert watershed and then finds a weighted average of the curve number of that watershed and adds it
to the attribute table.
Note: This step has trouble on small watersheds. Occasionally, in the middle of a run, the script will stop,
with an error message. If this happens, exit the model, open the attribute table of the All_Culverts layer
and, skipping the entry with the area that was too small, select all of the remaining culverts that haven’t
been analyzed. Do this each time the script has an error. If possible, next, go back through the layer at
the end, zoom to each culvert that wasn’t analyzed and use the “identify” cursor to determine the value
of the CN raster beneath the culvert watershed:
30
1. Example culvert watershed that could not be analyzed using the CN tool:
2. Turn back on the statewide CN raster:
3. Use the identify cursor
to determine the CN value(s) under the watershed, and estimate an
average.
4. Right click on the All_Culverts shapefile and open the attribute table.
5. Start an editing session, and change the value of the CN field for the watershed to the estimated
average.
31
Step 15: Export watershed data from ArcGIS
I.
II.
III.
Export data as text file from ArcGIS
a.
Right click on the All_culverts shapefile and select “open attribute table”
b.
In the Table Options drop down in the top left corner of the attribute table, selected
“Export”
c.
Ensure that the Export is set to “all data”
d.
Save the script as a .txt file into the “CulvertEvaluation” folder as All_culverts.txt
Open the text file in Excel
a.
First open excel, then load the file, ensuring that “all files” is selected in the “open”
window
b.
Once opened, a Text Import Wizard should open automatically in Excel.
i.
In step one of the Text Import Wizard dialogue box, ensure that the bubble for
“Delimited” is selected as the file type
ii.
Hit “Next”
iii.
In step two of the Text Import Wizard dialogue box, select “Comma” as the
delimiter and ensure that no other boxes are selected.
iv.
Hit “finish”
Save the file in yourwatershedname subfolder within the “CulvertEvaluation” folder as a .csv
Explanation: Once all of the relevant data has been added to the All_Culverts attribute table, it is ready
to be exported for use in the Cornell Culverts Evaluation Python script. This script will use this data in
conjunction with NRCC precipitation data (downloaded in the next step) to determine the peak
discharge from various storm events for each culvert. In order for the data to be accepted by Python, it
must be saved in CSV format in the same folder as the Python script.
Step 16: Download NRCC precipitation data
I.
II.
III.
IV.
V.
VI.
Go to: http://precip.eas.cornell.edu/
Select the “Data and Products” tab
In the “Select Product” column on the left hand side, select “Extreme Precipitation TablesText/CSV”
Type the name of the county that contains the study watershed.
Click the “Submit” Box in the center of the screen below the map, a text file called “output”
will then download automatically
Follow the procedure in step 15.II to open and save the output text file in excel as a CSV file.
Explanation: The North Eastern Regional Climate Center (NRCC) provides estimates of the precipitation
values for a range of return period storms of varying durations. This step locates the data for the study
watershed and exports it to a .csv file that will be used in the peak discharge calculations in the next
step.
32
Part 3: Use the python script to compute the peak discharge, capacity and max
return periods
Step 17: Run Cornell_Culverts_Evaluation python script
I.
II.
III.
IV.
Ensure that the “your-watershed-name” data folder contains the exported data from the
GIS in step 15, the precipitation CSV file exported from NRCC in step 16 and the exacted
culvert data file from step 2.
Open “county_list.csv”, located within the “CulvertEvaluation” folder, and include the
names of all input files located in your data folder, ensuring “.csv” follows each file name
you enter. Put this file in your data folder
a. Example input is below:
county_abbrevi watershed_data_fil watershed_precipitation_f field_data_file
ation
ename
ilename
name
Your threeletter abr. (e.g.
ALB_field_data
ALB)
All_culverts.csv
ALB_precip.csv
.csv
Right click on the Cornell_Culverts_Evaluation.py file in your “CulvertEvaluation” folder
i.
Select “Edit with IDLE”
ii.
Once the script is open, hit F5 (This will open the python shell)
Follow the script’s instructions.
This script will perform the following operations:
1. Calculate peak discharge for both current and future storm events. Events evaluated include the
1, 2, 5, 10, 25, 50, 200 and 500 year storms.
2. Calculate the cross sectional area of each culvert and assign each culvert minor loss coefficients
based on culvert material and geometry.
3. Calculate the capacity of each culvert
4. Compare the capacity and peak discharges for each culvert and assign each culvert a maximum
return period storm that it can safely pass
5. Summarize the data output in a single .csv file titled “model_output”
Script inputs:
1. Watershed data CSV file (final output from steps 2-15)
2. Field data collection file (product of the extraction in step 2)
3. Precipitation file (product of step 16)
Script Outputs (same as model outputs!):
1. culv_geometry: a csv file containing the areas of each culvert and assigned coefficients used in
the capacity calculations
33
2. capacity_output: a CSV file containing the maximum capacity of each culvert before the
headwater overtops the road surface
3. current_runoff: a CSV file containing the calculated runoff of each culvert watershed under the
1, 2, 5, 10, 25, 50, 100, 200 and 500 year storm events with current precipitation data
4. future_runoff: a CSV file containing the calculated runoff of each culvert watershed under the 1,
2, 5, 10, 25, 50, 100, 200 and 500 year storm events with projected 2050 precipitation data
5. return_periods: a CSV file containing the maximum return period storm that each culvert can
safely pass for the current and projected 2050 precipitation data.
6. model_output: a CSV file containing a summary of the above 5 outputs. The file contains the
maximum return period that each culvert can safely pass, the capacity of each culvert and
information about culvert geometry. This file can be viewed as the final output of this model.
Explanation: The Cornell_Culvert_Evaluation script does not actually perform any calculations by
itself; instead, it serves as a command center to call on various functions that each performs a
certain task.
------------------------------------------------------------------------------------------------------------------------------------------
Optional Step 18: Use R script to append Survey_ID numbers
1. This step was created to append Survey_ID numbers onto the model_output file created by the
Cornell_Culvert_Evaluation python script. It requires the R script survey_id_v2.R, available from
Allison Truhlar.
2. The R script requires the yourwatershedname.csv file, yourwatershedname_field_data.csv,
yourwatershedname_not_extracted.csv, yourwatershedname_export.csv, and
yourwatershedname_model_output.csv.
------------------------------------------------------------------------------------------------------------------------------------------
34
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.5 Linearized : No Page Count : 34 Language : en-US Tagged PDF : Yes Author : Dave Creator : Microsoft® Word 2016 Create Date : 2018:11:12 15:26:20-05:00 Modify Date : 2018:11:12 15:26:20-05:00 Producer : Microsoft® Word 2016EXIF Metadata provided by EXIF.tools