Attachment B - SDSFIE Data Model.xlsx

BridgeToAdaptationTable

READ ME

o PS ID – use entry from associated attribute field o PSA ID – use entry from associated attribute field o Function Class – use entry from associated attribute field o Road Sequence – use entry from associated attribute field (three-digit format) o Road Sequence Suffix – if applicable, use entry from associated attribute field roadSegStatus Road Segment Status yes Choose the appropriate option from the drop-down menu according to field observation; options include:

o Abandoned – segment is no longer utilized and/or maintained o Open To Traffic – most common selection for public road segments o Proposed – segment reflects a proposed route, though no infrastructure currently present o TBD – status is unknown o Under Construction – segment reflects a road where construction has begun but is not yet complete

2. Select all features that are missing GUIDs

3. Open the Field Calculator tool for relevant attribute field, then choose the Python parser, and check the option to Show Codeblock

4. Use the following entries:

a. Pre-Logic Script Code:

def ID():

import uuid return '{' + str(uuid.uuid4()) + '}'

b. [Attribute Field Name]= ID().upper()

Choose the appropriate option from the drop-down menu; options include:

o Road – primary type used for inventory o Wayside – an area where the edge of the road has been widened, using the same surface material, to allow for vehicles to temporarily move away from the main lane of traffic o Boat Ramp – the length of a boat ramp, taken along its centerline, from the top to bottom of its surface material (or the water’s edge) o Driveway – most often used for driveways providing access to/from RV dump stations o Camping Spur – driveway or pull-through specifically servicing a campsite o Travel Route – illustrates the primary traffic pattern through a parking area userAccess User Access no Indicates if the segment is open to the public or not.

Choose the appropriate option from the drop-down menu (Figure 13.2); options include:

o Public – Per 23 USC 125, public roads are defined as, “a road that, except during scheduled periods, extreme weather conditions, or emergencies, the road is open to the general public for use with a standard passenger vehicle without restrictive gates or prohibitive signs or regulations, other than for general traffic control or restrictions based on size, weight, or class of registration” o Non-Public – All roads that do not meet the definition stated above Verify with project.

allowPublicNonmotorized Allow Public Non-Motorized no For service roads only (function class 4), indicate if public use is permitted by non-motorized means for recreation Choose the appropriate option from the drop-down menu; options include:

o Yes – a service road on which public, non-motorized recreation (e.g., walking, bicycling) is permitted o No – a service road on which public, non-motorized recreation is not permitted; a restricted area; the dam crest road, for many (but not all) projects, is a suitable example o NA – should be selected for all public roads Consult with project managedBy Managed By no Indicates the agency in which road maintenance and management responsibility is vested Choose the appropriate option from the drop-down menu o Selection should match MANAGING_AGENCY_TYPE in OMBIL Verify with project ownership Ownership no Choose the appropriate option from the drop-down menu o Consult OMBIL for the associated Managing Agency Type, then find the corresponding entry within the Ownership drop-down menu: Private Entity = Concessionaire (OMBIL), US State = State (OMBIL), US County = Local Government (OMBIL), Other Federal Administration = Other Federal Agency (OMBIL), Other = All Others (Quasipublic) (OMBIL) fltp FLTP no Indicates if the road segment is included in the agency’s FLTP network Choose the appropriate option from the drop-down menu; options include:

o Yes – Select for all public roads except when the following is true (one or both):

Managed By is an agency other than Corps of Engineers Asset Type = Boat Ramp, Driveway, Camping Spur, or Travel Route o No – Select for all non-public assets, as well as the for the exceptions stated above o Partial – Less common; consult with project, if suspected

Silvernail, Benjamin J CIV USARMY CEIWR (US): Silvernail, Benjamin J CIV USARMY CEIWR (US):

This gets confusing when collected in addition to assetType. A feature can be a "wayside" as its assetType (not a road assetType), but then be a road in the function class.

I like that this helps to pull out PSA vs PS roads, and extract service roads, boat ramps, and travel routes. But travel routes and boat ramps are also asset types that could help with this.

Since this was originally added to help develop the idpk, having a corresponding number helps achieve this. But we could take another look at the alignment between this and assetType to reduce confusion.

Ex.) we would know if it's a PS or PSA road if there is a PSAID or a 0 for that component. So these could be condensed to one function class, and include in the definition waysides, etc. Category Code no Complete with the single-digit number corresponding with the appropriate function class (Figure 13.2):

o 1 – Project Road (public; outside of any PSA, but still within the USACE project boundary) o 2 – Area Road (public; within a PSA) o 3 – Service Road o 7 – Boat Ramp o 8 – Driveway o 9 – Travel Route through Parking Area

o If a segment has a single spur, then “s” is suitable o If a segment has multiple spurs, then the suffixes should incorporate consecutive numbering (i.e., “s1”, “s2”, “s3”) fromDesc From Description no Coordinates for start point (initial vertex) of segment, formatted as follows:

o Latitude (degrees minutes seconds); Longitude (degree minutes seconds) o Ex. “37° 7' 23.315" N; 119° 53' 21.008" W” Recommend use of Calculate Geometry and Field Calculator tools in ArcMap to complete toDesc To Description no Coordinates for end point (final vertex) of segment, formatted as follows (Figure 13.8):

o Latitude (degrees minutes seconds); Longitude (degree minutes seconds) o Ex. “37° 7' 23.315" N; 119° 53' 21.008" W” Recommend use of Calculate Geometry and Field Calculator tools in ArcMap to complete

Consult with project surfaceType Surface Type no Choose the appropriate option from the drop-down menu o In most cases, this will either be “Asphaltic Concrete” or “Gravel” Consult with project, if uncertain width Width no Average width of the road segment in feet If segment width is variable, measure at width that is judged to be most representative Recommend use of a measuring wheel lanes Lanes no Number of lanes present for the majority of the road segment If the road segment is not striped, determine whether the majority appears capable of supporting two-way traffic for passenger vehicles:

o If so, designate segment as having two lanes o If not, designate segment as having one lane

o No – centerline absent

ArcMap Editor Tracking tool will auto-populate field createDate Create Date no Indicates when a feature in geodatabase was created or imported ArcMap Editor Tracking tool will auto-populate field editBy Edit By no Indicates who most recently edited a feature in geodatabase ArcMap Editor Tracking tool will auto-populate field editDate Edit Date no Indicates when a feature in geodatabase was most recently edited ArcMap Editor Tracking tool will auto-populate field

o PS ID – use entry from associated attribute field o PSA ID – use entry from associated attribute field o Function Class – use entry from associated attribute field o Road Sequence – use entry from associated attribute field (three-digit format) o Road Sequence Suffix – if applicable, use entry from associated attribute field

When an official name exists, it should be used (though, rare for parking areas) Most USACE parking areas do not presently have names, and an unofficial name will need to be constructed o Consult USACE Rules of the Road (Section IV.) for more information isPaved Is Paved yes Choose the appropriate option from the drop-down menu o In Most cases, this will either be “Asphaltic Concrete” or “Gravel” Consult with project, if uncertain

- This will be the sum of Number of Car Spaces and Number of Trailer Spaces fields operationalStatus Operational Status yes The state of usability of the parking area Choose the appropriate option from the drop-down menu; options include:

o “In Service” will be the most common selection o Consult with project if another selection appears to be more appropriate parkingAreaIdpk Primary Key Identifier yes Formula for generating: PS ID - PSA ID - Function Class - Parking Sequence o PS ID – use entry from associated attribute field o PSA ID – use entry from associated attribute field o Function Class – use entry from associated attribute field o Parking Sequence – use entry from associated attribute field (three-digit format) pavementMaterial Pavement Material yes Choose the appropriate option from the drop-down menu o In Most cases, this will either be “Asphaltic Concrete” or “Gravel”

- Consult with project, if uncertain

if GUID is not auto-populated Consult article In appendix for guidance on creating

- Choose the appropriate option from the drop-down menu; options include:

o On-Road – Parking spaces will be accessible from a traffic lane along the roadside, such as parallel parking spaces (can be perpendicular to the road or at another angle) o Off-Road – What would be considered a typical parking lot o Terminal – On-road parking (reference above definition), however, spaces occur along a turnaround userAccess User Access no Indicates if the parking area is open to the public or not

- Choose the appropriate option from the drop-down menu; options include:

o Public – Per 23 USC 125, public roads are defined as, “a road that, except during scheduled periods, extreme weather conditions, or emergencies, the road is open to the general public for use with a standard passenger vehicle without restrictive gates or prohibitive signs or regulations, other than for general traffic control or restrictions based on size, weight, or class of registration” o Non-Public – All roads that do not meet the definition stated above

- Verify with project flmaPartner FLMA Partner no Indicates the Federal Lands Transportation Program participating agency fee boundary or easement the parking lot falls within

- Enter “USACE” for all parking areas; may be configured as the default option managedBy Managed By no Indicates the agency In which parking area maintenance and management responsibility is vested

- Choose the appropriate option from the drop-down menu o Selection should match MANAGING_AGENCY_TYPE In OMBIL

- Verify with project fltp FLTP no Indicates if the parking area is included in the agency’s FLTP network

- Choose the appropriate option from the drop-down menu; options include:

o Yes – Select for all public parking areas except when the following is true:

Managed By is an agency other than Corps of Engineers o No – Select for all non-public assets, as well as for the exception stated above o Partial – Less common; consult with project, if suspected

- Consult with project, if uncertain

o 4 – Project Parking Area (public; outside of any PSA, but still within the USACE project boundary) o 5 – Area Parking Area (public; within a PSA) o 6 – Service Parking Area (non-public)

- Choose the appropriate option from the drop-down menu alias Alias no Alternate name by which the feature is known; for example, a parking area may have an official name (e.g., Howard Parking Area), but project personnel refer to that same parking area by a name with greater local relevance (e.g., Fishing Access Parking Area)

- Consult with project stripedYN StripedYN no Choose the appropriate option from the drop-down menu; options include:

o Yes – striping for spaces present o No – striping for spaces absent numberOfCarSpaces Number of Car Spaces no Record the total number of car parking spaces available including handicapped or reserved spaces

- for parking areas containing only trailer spaces, the total for number of car spaces should read “0”

- for unstriped parking areas:

o Consult with project to determine if parking area is designated for car or trailer usage o Use USACE-provided parking space estimation tool to generate number accordingly numberOfTrailerSpaces Number of Trailer Spaces no Record the total number of trailer parking spaces available including handicapped or reserved spaces

- for parking areas containing only car spaces, the total for number of trailer spaces should read “0”

- for unstriped parking areas:

o Consult with project to determine if parking area is designated for car or trailer usage o Use USACE-provided parking space estimation tool to generate number accordingly squareFeet Square Feet no area of the polygon In square feet

- Once all spatial revisions are complete In the post-processing of data, this field can be easily populated using the Calculate Geometry tool In ArcMap o Before doing so, ensure that data is projected using the state Plane projected Coordinate System (PCS) that corresponds with the project location for which totals are being calculated o for example, When calculating totals for Black Butte Lake (CA), Use:

NAD_1983_2011_StatePlane_California_II_FIPS_0402_Ft_US

- ArcMap Editor Tracking tool will auto-populate field createDate Create Date no Indicates When a feature In geodatabase was created or imported

- ArcMap Editor Tracking tool will auto-populate field editBy Edit By no Indicates who Most recently edited a feature In geodatabase

- ArcMap Editor Tracking tool will auto-populate field editDate Edit Date no Indicates When a feature In geodatabase was Most recently edited

- ArcMap Editor Tracking tool will auto-populate field

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format) elevationUom Elevation Units of Measure yes Pertains to the Maximum & Minimum Pool Elevations included in Boat Ramp attribute table

- Use “Foot” as this should be the unit used by project sites for official elevation records elevationVerticalDatum Elevation Vertical Datum yes Pertains to the Maximum & Minimum Pool Elevations included in Boat Ramp attribute table

- Obtain official record from project

Suggested naming convention: PSA Name & “Boat Ramp” & Descriptor o PSA Name found in OMBIL o Descriptor possibilities include (though not limited to):

Upper, Middle, or Lower (i.e., relative position) 001, 002, 003, etc. (i.e., sequential numbering) isUserFeeRequired Is User Fee Required yes Answer “Yes” if:

o Fee directly associated with Use of Boat Ramp o Fee indirectly associated with Use of Boat Ramp, for example:

Day-Use Fee charged for entrance to PSA including Boat Ramp Campsite reservation Fee required to Use Boat Ramp in (or near) to campground

- if uncertain, Consult with project maxPoolElevation Maximum Pool Elevation yes Highest lake level at which the Boat Ramp is still usable

- Obtain official record from project

- Obtain official record from project numberOfLaunchLanes Number of Launch Lanes yes Count number of lanes present for simultaneous boat launches o Figure 1.1 – Boat ramp pictured includes four launch lanes, each easily distinguishable when viewed on site

- Verify with project

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o In most instances, this will be “Concrete”

- Consult with project, if uncertain courtesyDockPresent Courtesy Dock Present no Note the presence or absence – “Yes” or “No” – of a courtesy dock

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o Facility numbers are project-specific identifiers, most often assigned to waterborne or vault restrooms; may be visible on the exterior of a building – if so, include and verify with project

- Consult with project regarding the possibility of missed or unmarked facility numbers

- Suggested naming convention: PSA Name & Building Type & Facility Number o PSA Name found in OMBIL o Building Type – type chosen for associated attribute field o Facility Number – if applicable, use number from associated attribute field

o Biological – rare, consult with project if suspected o Chemical – portable toilet (e.g., Porta-Potty) o Vault – pit toilet o Water – flush toilets with running water

- Suggested naming convention: PSA Name & “Campground” o PSA Name found in OMBIL – may already include “Campground”; if so, do not repeat word

- Importantly, this number may differ from the highest numbered campsite within the campground o For example, a campground may only have 130 actual sites, despite having a campsite bearing the number “132” (i.e., sites numbers are not always sequential)

o Attendant – The site is for the use of a host or attendant o Equestrian – The site is for equestrian use o Group – The site is for the use of groups o Individual – The site is for the use of individual campers; most common selection o Other – Value that is not otherwise represented o Primitive – The site is for the use as a primitive campsite o Recreational Vehicle – The site is for the use as recreational vehicle parking o Screened Shelter – The site is occupied by a screen shelter o Tent-Only – The site is for the use of tent-only campers o To Be Determined – Undetermined at date of record population; should be revisited at a later date o Volunteer – The site is for the use of volunteers

- Verify with project featureDescription Feature Description yes Field designated for additional information that is not clearly represented through other attributes

- Use field to denote ADA sites (i.e., handicap); verify with project featureName Feature Name yes Consult with project, as they will be the final authority on names for assets at their site

- Suggested naming convention: PSA Name & “Campsite” & Campsite Number/Letter o PSA Name found in OMBIL o “Campsite” is suitable for individual sites; however, it is appropriate to modify for other campsite types (e.g., “Group Campsite”, “Equestrian Campsite”, “Host Campsite”) o Use campsite number posted at campsite (three-digit format); if no number present, verify absence with project isReservableViaNrrs Is Reservable Via NRRS yes Obtain from USACE-provided NRRS database; indicate “Yes” or “No”

- Verify with project, as some sites may actually be walk-in only

- Note – some sites, while not reservable through NRRS, are reservable by directly contacting the project; consult with project about this possibility, and if it is the case, make note of it in Feature Description field

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format) grill Grill no If grill is observed at campsite, indicate "yes," if no grill is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" shadeShelter Shade Shelter no If shade shelter is observed at campsite, indicate "yes," if no shade shelter is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" firepit Firepit no Verify accuracy of USACE-provided NRRS database according to field observation o If NRRS database is correct, choose “Yes” (i.e., NRRS data indicates the presence of a firepit and a firepit is observed at the campsite) o If NRRS database is incorrect, choose “No” (i.e., NRRS data indicates the presence of a firepit and a firepit is not observed at the campsite) picnicTable Picnic Table no Verify accuracy of USACE-provided NRRS database according to field observation o If NRRS database is correct, choose “Yes” (i.e., NRRS data indicates the presence of a picnic table and a picnic table is observed at the campsite) o If NRRS database is incorrect, choose “No” (i.e., NRRS data indicates the presence of a picnic table and a picnic table is not observed at the campsite) drivewayType Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

o In most cases, this will either be “Asphaltic Concrete” or “Gravel”

- Consult with project, if uncertain water Water no If shade shelter is observed at campsite, indicate "yes," if no shade shelter is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" electricAmperage Electric Amperage no Choose the appropriate option from the drop-down menu o Select “0” when electric hookups are not provided o When electric hookups are present, observe the unit for an indication of amperage If multiple amperages are listed, select the highest available Consult with project, if uncertain

- Verify with project, as some hookup units may be incorrectly labeled

- Suggested naming convention: PSA Name & Fishing Feature Type o PSA Name found in OMBIL o Fishing Feature Type – type chosen for associated attribute field (e.g., Fishing Pier, Fish Cleaning Station) fishingFeatureIdpk Primary Key Identifier yes Formula for generating: PS ID - PSA ID – Consecutive Number o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

- Obtain official record from project or operating organization o If no official record exists, make a note in the Feature Description attribute field minimumUsableStageUom Minimum Usable Stage Units Of Measure yes Unit of measure for the water surface elevation at which the marina becomes unusable; in most cases, this should be “Foot”

- Obtain official record from project or operating organization ownership Ownership yes Choose the appropriate option from the drop-down menu o Consult OMBIL for the associated Managing Agency Type, then find the corresponding entry within the Ownership drop-down menu

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

Suggested naming convention: PSA Name & “Picnic Site” & Picnic Site Number o PSA Name found in OMBIL o “Picnic Site” is suitable for individual sites; however, it is appropriate to modify for other picnic site types (e.g., “Group Picnic Site”) o If applicable, use picnic site number posted at picnic site (three-digit format)

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

o A single grill could be positively associated with multiple picnic sites (i.e., “Yes” is selected for multiple picnic sites that all share access to the same grill)

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

See note from Sacramento pilot shadeShelter Shade Shelter no If shade shelter is observed at picnic site, indicate "yes," if no shade shelter is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" firepit Firepit no If firepit is observed at picnic site, indicate "yes," if no shade shelter is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" picnicTable Picnic Table no If picnic table is observed at picnic site, indicate "yes," if no picnic table is observed, indicate "no."

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" water Water no If picnic table is observed at picnic site, indicate "yes," if no picnic table is observed, indicate "no."

o A single water source (e.g., spigot) could not be positively associated with multiple picnic sites (i.e., if a spigot was considered within the bounds of one picnic site, then it could not be considered so for another, adjacent picnic site)

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

Same as above for referencing Sacramento pilot problems Silvernail, Benjamin J CIV USARMY CEIWR (US): Silvernail, Benjamin J CIV USARMY CEIWR (US):

This gets confusing when collected in addition to assetType. A feature can be a "wayside" as its assetType (not a road assetType), but then be a road in the function class.

I like that this helps to pull out PSA vs PS roads, and extract service roads, boat ramps, and travel routes. But travel routes and boat ramps are also asset types that could help with this.

Since this was originally added to help develop the idpk, having a corresponding number helps achieve this. But we could take another look at the alignment between this and assetType to reduce confusion.

Ex.) we would know if it's a PS or PSA road if there is a PSAID or a 0 for that component. So these could be condensed to one function class, and include in the definition waysides, etc.

Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

What if no grill is indicated but one is found? Note from Sacramento case study says to mark "yes" Goldeen, Daniel N CONTRACTOR @ IWR: Goldeen, Daniel N CONTRACTOR @ IWR:

o Select “0” when electric hookups are not providedo when electric hookups are present, observe the unit for an indication of amperage if multiple amperages are listed, Select the Highest available Consult with project, if uncertain

- verify with project, as some hookup units may be incorrectly labeled adaSite ADA Site no Indicators would be the presence of a paved walkway to the site, special picnic table to accommodate wheelchair, etc.Verify with project, as some sights may have some but not all necessary characteristics of an ADA site recreationFeatureIdpk Primary Key Identifier yes Formula for generating: PS ID - PSA ID – Consecutive Number o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

- Suggested naming convention: PSA Name & Recreation Feature Type o PSA Name found in OMBIL o Recreation Feature Type – type chosen for associated attribute field

- Suggested naming convention: Trail Name & PSA Name o If no trail name exists, use “Trail” o PSA Name found in OMBIL

o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format) recreationTrailFeatureType Recreation Trail Feature Type yes Choose the appropriate option from the drop-down menu:

o Generally, if a trail is open to two or more forms of recreation (e.g., hiking and bicycling), it is considered multipurpose o Water trails are along waterways For example, a section of a river used for rafting, floating, or paddling may be considered a water trail; a common entry point for watercraft would be a trailhead in this case

- Verify with project

o Generally, if a trail is open to two or more forms of recreation (e.g., hiking and bicycling), it is considered multipurpose o Water trails are along waterways For example, a section of a river used for rafting, floating, or paddling may be considered a water trail; a common entry point for watercraft would be a trailhead in this case

- Verify with project recreationTrailFeatureIdpk Primary Key Identifier yes Formula for generating: PS ID - PSA ID – Consecutive Number o PS ID found in OMBIL o PSA ID found in OMBIL o Number consecutively within each PSA (use three-digit format)

- Suggested naming convention: PS or PSA Name & “Road Access Point” o PS Name found in OMBIL – use for road points providing access at project boundary o PSA Name found in OMBIL – use for road points providing access at PSA boundary

o PS ID found in OMBIL o PSA ID found in OMBIL – use “0” when road point is associated with project o Number consecutively within each PSA (use three-digit format)