CAMS Data Model Data Dictionary Working Draft Version 1.1
County Asset Management (CAMS)
Physical Inventory Logical Data Model
Working Draft
Version 1.1
January 29, 2007
Table of Contents
1 CAMS Data Model Overview 8
1.1 Status of the Data Model 12
1.2 Status of this Document 13
2 How to Use this Document 14
3 Significant CAMS Attributes 15
3.1 CAMS Asset Life Cycle Tracking Attribute 15
3.1.1 Construction in Progress 15
3.2 CAMS Asset Improvement Status Attribute 15
3.3 CAMS Roadway Identifier Attribute 16
3.4 CAMS Asset Project Identifier Number Attribute 16
3.5 Acquisition Method Attribute 16
3.6 Job Ledger Object Attribute 16
3.7 Feature Identifier Attribute 17
3.8 Pay Item Code Attribute 17
4 CAMS Networks 18
5 List of Tables and Descriptions 19
5.1 Tables 19
5.2 Domains 22
6 CAMS Asset Tables 24
6.1 Administrative Tables 24
6.1.1 ADM_BLDAddress 24
6.1.2 ADM_BLDFloors 24
6.1.3 ADM_BLDInterior 25
6.1.4 ADM_ConstructionProject 26
6.1.5 ADM_ContractPayItem 26
6.1.6 ADM_HCBidItemCodes 26
6.1.7 ADM_LNDStructuresOnParcels 26
6.1.8 ADM_ProjectPayItem 26
6.1.9 ADM_Roadlog 27
6.1.10 ADM_RoadwayNameRef 27
6.1.11 ADM_StakeoutComponent 27
6.2 Construction Project Tables 27
6.2.1 CNS_StakeOutArea 28
6.2.2 CNS_StakeOutLine 28
6.2.3 CNS_StakeOutPoint 29
6.3 Infrastructure Tables 30
6.3.1 INF_FireHydrants 31
6.3.2 INF_Gate 32
6.3.3 INF_Lighting 33
6.3.4 INF_Mailbox 34
6.3.5 INF_Marking 35
6.3.6 INF_SanitarySewerLiftStation 36
6.3.7 INF_SanitarySewerManhole 37
6.3.8 INF_SanitarySewerPipe 39
6.3.9 INF_Section 41
6.4 INF_Sign 43
6.4.1 INF_StormSewerInlet 44
6.4.2 INF_StormSewerPipe 45
6.4.3 INF_StormSewerJunctionBox 46
6.4.4 INF_StormSewerManhole 48
6.4.5 INF_StormSewerOutfall 49
6.4.6 INF_Tunnel 51
6.5 Linear Referencing System 52
6.5.1 INF_LRSRoadways 53
6.5.2 INF_LRSReferenceMarker 53
6.5.3 INF_LRSReferenceElement 53
6.5.4 INF_EventConstInspection 54
6.5.5 INF_EventMaintInspection 54
6.5.6 INF_EventPavementCondition 55
6.5.7 INF_EventStakeOut 55
6.6 Other Supporting Infrastructure Tables 56
6.6.1 INF_OTHCrossSection 56
6.6.2 INF_OTHIntersection 56
6.6.3 INF_OTHLane 57
6.7 Land 58
6.8 LND_Building 58
6.9 LND_Easement 59
6.9.1 LND_FLDCRTLChannel 60
6.9.2 LND_FLDCTRLDetentionFacility 61
6.9.3 LND_FacilityOther 61
6.9.4 LND_Landscape 62
6.9.5 LND_Parcel 63
6.9.6 LND_Park 64
6.9.7 LND_Easement 65
6.9.8 LND_RecreationalFacility 66
6.9.9 LND_SepticSystemDistributionBox 67
6.9.10 LND_SepticSystemLeachField 68
6.9.11 LND_SepticSystemPipe 69
6.9.12 LND_SepticSystemTank 69
6.9.13 LND_SprinklerSystemHead 70
6.9.14 LND_SrinklerSystemPipe 71
6.10 System Management 72
6.10.1 SYS_AssetInstance 72
6.10.2 SYS_NetworkInstance 72
6.11 Metadata 73
6.11.1 MT_Metadata 73
7 Domain Codes 74
7.1 L_AcquisitionMethod 74
7.2 L_AssetImprovementStatus 74
7.3 L_CNSAssetTypeArea 74
7.4 L_CNSAssetTypeLine 75
7.5 L_CNSAssetTypePoint 75
7.6 L_Condition 75
7.7 L_INFApprovalCode 76
7.8 L_INFClassCode 76
7.9 L_INFConditionScore 76
7.10 L_INFDistressScore 76
7.11 L_INFDistressSeverity 76
7.12 L_INFIntersectionType 77
7.12.1 L_INFLaneTaperType 77
7.13 L_INFLaneType 77
7.14 L_INFOffsetDirectionType 77
7.15 L_INFPSR (Pavement Serviceability Rating) 78
7.16 L_INFRideEvaluation 78
7.17 L_INFRouteType 78
7.18 L_INFSanitarySewerPipeType 78
7.19 L_INFSectionCategoryType 79
7.20 L_INFSignType 79
7.21 L_INFStationIncrementType 79
7.22 L_INFTunnelType 80
7.23 L_INFTurnLaneType 80
7.24 L_ImprovementAssetType 80
7.25 L_InspectionStatus 80
7.26 L_Jurisdiction 80
7.27 L_BuildingCities 80
7.27.1 L_LNDBuildingType 81
7.27.2 L_LNDBuildingUse 82
7.27.3 L_LNDChannelType 83
7.27.4 L_LNDDetentionFacilityTypeOther 84
7.27.5 L_LNDFacilityTypeOther 84
7.27.6 L_LNDParcelClass 84
7.27.7 L_LNDParcelStateType 85
7.27.8 L_LNDRecreationalFacilityType 85
7.27.9 L_LNDParcelStateType 85
7.27.10 L_LNDRecreationalFacilityType 86
7.27.11 L_LNDStructureDesc 86
7.27.12 L_LNDUtilityType 86
7.27.13 L_LifeCycleStatus 87
7.27.14 L_MaintJurisdiction 87
8 Other Outstanding Issues 88
9 GLOSSARY 95
10 Appendix A: Bridge Inspections 89
10.1.1 Condition Rating Scale 89
10.1.2 Environmental Rating Scale 89
10.1.3 Multi-Structure Bridge Elements 89
10.1.4 ADM_INFBridgeElmInsp 90
10.1.5 ADM_BridgeInspevnt 90
10.1.6 Elements 90
10.1.7 Bridge Culvert 91
10.1.8 Bridge Deck 91
10.1.9 Bridge Smart Flag 92
11 Bridge Superstructure 92
12 Appendix B: Other Administrative Tables 94
12.1 ADM_CapitalExpenditures 94
12.2 ADM_ConstructionContracts 94
Table of Figures
Figure 11 Harris County CAFR Asset Classes 8
Figure 12 CAMS CAFR Equivalent Asset Classes and Subclasses 9
Figure 13 CAMS Asset Management Modules 9
Figure 14 CAMS Data Sources 10
Figure 15 Importing CAD Files and Bid Item Code information 11
Figure 16 Data Modeling Cycle 13
Figure 31 Legacy Roadlog versus Roadway ID 16
Figure 41Storm Sewer Network 18
Figure 61 Typical Right-of-Way Section 30
Figure 91 Bridge Substructure 90
Figure 92 Typical Bridge Components Courtesy of GlobalSecurity.org 91
List of Tables
Table 31 Life Cycle Status Codes 15
Table 72 Route Types 51
CAMS Data Dictionary
Version 1.1
1 CAMS Data Model Overview
The County Asset Management System (CAMS) Data Model primary objective is to provide the GIS feature structure required to track the Harris County owned and permanently fixed assets that have a capitalization threshold at the component level and relates them to the Comprehensive Annual Financial Report (CAFR). The CAFR is illustrated in Figure 11.
Figure 11 Harris County CAFR Asset Classes
A second CAMS objective is to provide support for all asset types that the county is required to track under the Government Accounting Standards Board Statement 34 (GASB 34) as outlined by the GASB 34 Manual for Texas Cities and Counties.
Prior to the development of the CAMS data model, several existing data models were reviewed and were found lacking in the detail required for a county-level implementation or too specific to address the broader scope of the CAMS project. As a result, Baker chose a hybrid approach by identifying the asset classes required to support the CAFR (See Figure 12) and where appropriate, implemented features as outlined in existing engineering and transportation data models.
The CAMS Data Model is divided into two main asset categories; Infrastructure and Land assets. Infrastructure assets consist of transportation network elements found within a roadway’s right-of-way, such as the road itself, signs, bridges, storm sewers, signaling and all other supporting structures. Land includes buildings, easements and facilities, parcels and parks. Figure 12 illustrates the CAFR equivalent asset classes as they are structured in CAMS.
Figure 12 CAMS CAFR Equivalent Asset Classes and Subclasses
There is also an additional module in CAMS model, titled ConstructionProjects. The ConstructionProject feature dataset is a construction project’s CAD to GIS data import module, that allows for CAD geometry for a project to be imported and processed based on whether the geometry represents a new asset or an asset improvement. Once the geometry is processed, the updates or new features are migrated to the Infrastructure and Land Feature Datasets as illustrated in Figure 13.
Figure 13 CAMS Asset Management Modules
Each feature dataset is comprised of a number of feature classes. The structure of the CAMS Data Model is stored in a UML diagram, in Microsoft Visio Professional 2003.
The data used to populate CAMS comes from three sources; capital improvement projects, donated roads or legacy data. For new Capital Improvement Projects (CIP), as well as donated assets (See Figure 14), where new features are to be added to CAMS, CAD geometry from the plats and as-builts, produced during projects is used to generate new features. The CAMS Data Model provides automated methods for importing CAD geometry, creating project features, calculating their construction cost and adding geometry to the CAMS feature classes and Linear Referencing network. The model utilizes the design and construction bid item codes to capture the asset’s original construction cost.
Figure 14 CAMS Data Sources
As indicated in Figure 14, multiple agencies within the county are to be active participants as either data suppliers and/or data users.
To effectively import and store CAD project data, CAMS uses subtypes as funnels in collecting and processing CAD geometry. Subtypes are used to isolate records into sets that need to be processed based on unique topology and geometry requirements. As an example, CAD lines may represent area features, such as pavement, as well as linear features such as utility networks. Line features, representing area features, are identified by their subtypes and converted to polygons. Line features and point features that are part of utility networks can then be used to build geometric networks.
The ConstructionProject Feature Dataset contains three feature classes, CNS_StakeOutPoints, CNS_StakeOutLines and CNS_StakeOutArea. The feature classes also contain the attributes required to perform a direct CAD import. The attributes include Elevation, Layer, LineType, Text, UIN, and PayItemCode[1]. The feature classes are populated from either CAD plats or as-built drawings during the CAD import using the general process provided in Figure 16 .
Figure 15 Importing CAD Files and Bid Item Code information
The CNS_StakeoutArea feature class contains polygon features created from the CAD polygons or lines found on CAD plats and as-built drawings. The CNS_StakeoutArea feature class includes subtypes[2] defining road sections and features found on the road’s ROW[3]. The section category subtypes include items such as Drainage type, Slope, Overpass, Road, ROW, Shoulder, Surface Type, Sidewalk and other related section details. CAD lines imported for features such as pavement areas and asphalt areas fall into the Surface CNS_StakeoutArea category subtype.
CNS_StakeOutLine include line features participating in network relationships such as storm sewer pipe. Features contained within this dataset may also represent roadway markings and utility lines.
CNS_StakeOutPoint contains point features associated with networks such as storm sewer manholes, storm sewer inlets, sanitary sewer manholes, overhead signs and overhead signals. Other assets included that do not participate in networks but do have a capitalization threshold include gates and road side signs.
CNS_StakeoutArea features may be associated with more than one bid item code. For example, assets such as paved road sections are made-up of more than one layer, such as a stabilized sub-grade base and the actual concrete pavement surface. Such composite features have multiple pay item codes, one for each material used. Each project has its own list of pay item codes with their respective negotiated values, with cost varying with each project.
Note: The features supported within the CNS_StakeOutLine, CNS_StakeOutLine and the CNS_StakeoutArea feature classes needs to be greatly expanded to include all Land feature types supported in the model. This is to be completed once the assets contained within the Land module are approved by Harris County. Additionally, at the time of the delivery of this document, only one infrastructure as-built and five plats have been imported into the model. Additional testing will be required.
To support the one-to-many relationship of area features, pay item codes for polygon features are stored separately, in the ADM_CNSProjectPayItem table. The ADM_CNSProjectPayItem includes the reference pay item, the quantity of the material used and the original estimate. A second table titled the ADM_CNSStakeOutComponent, maps each unique Pay Item Code instance to the CNS_StakeoutArea feature. In a separate table the pay item code name, description and units are stored. The ADM_CNSContractPayItem includes the item number, the item name, and a description and the units used.
To calculate each asset construction cost, the ADM_CNSContractPayItem table and the ADM_CNSStakeOutComponent tables are joined using the A_ReferencePayItem field of the ADM_CNSContractPayItem table and the A_PayItemCode field of the ADM_CNSStakeoutComponent table. The ADM_CNSStakeOutComponent table is then joined to the CNS_StakeoutArea table and using the actual surface area of the feature, the construction cost is calculated.
The cost calculations are performed using the respective units specified by each Pay Item Code and included in the ADM_CNSContractPayItem table. The units can be feet, yards or acres (depends on the units of measure). The respective unit is multiplied times the pay item code unit cost from the ADM_CNSContractPayItem table. The cost of all surface area features can be summed using the G_FeatureID field of the ADM_CNSStakeOutComponents table using a report generator such as Crystal Reports. A description of the pay item code can be obtained from the ADM_CNSProjectPayItem table and added to the report if desired.
CNS_StakeOutLines and CNS_StakeOutAreaPoints features have a one-to-one relationship with pay item codes. The value is calculated by joining each of these feature classes with the ADM_CNSContractPayItem table to derive their construction cost.
When CAMS is fully implemented new projects imported may share the same boundaries of previous projects. Also project boundaries may be subsets of other project boundaries with project boundaries overlapping. To ensure the correct accounting of projects and their expenditures; project boundaries will be preserved. CAMS will account for the true asset value, which fluctuates with improvement projects and maintenance activities.
Although CAMS is to support numerous users from different county organizations, the main users and report creators are the auditors. The current system that supports auditor needs is the Integrated Financial Accounting System (IFAS). CAMS is to be a far more comprehensive asset tracking system than IFAS, but IFAS is to remain the main systems auditors reference.
CAMS is been designed to provide compatibility with emerging standards such as LandXML, ISO 19133 and TransXML.
1.1 Status of the Data Model
The scope of the CAMS data model is very extensive. Hence, the CAMS Data Model development efforts have been subdivided into two phases; the first Phase develops the Infrastructure Module of the CAMS Data model and the second Phase develops the Land Module. Currently we are culminating the first iteration of the Infrastructure and Land Modules development. Additional iterations will follow in order to fine-tune the implementation.
Figure 16 Data Modeling Cycle
(ESRI Data Modeling Standard)
1.2 Status of this Document
With every new meeting or business requirement interview conducted by the CAMS team, new information identified can affect the data model structure. As a result this document subject to change. Readers are encouraged to make recommendations and provide feedback.
2 How to Use this Document
The County Asset Management System (CAMS) Data Model uses table prefixes to identify the Comprehensive Annual Financial Report (CAFR) Asset Classes.
The table prefixes are:
Prefixes / DefinitionADM / Administrative/Accounting
BLD / Building
CNS / Construction
EMT / Easement
INF / Infrastructure
L / Lookup
LND / Land
PRK / Parks
SM / System Management
LRS / Linear Referencing System
OTH / Other Supporting Table
Field prefixes within each table identify the potential users of each field. The list of field prefixes is:
Prefixes / User DomainA / Auditor
C / CAD Group
E / Engineering
F / Flood Control
G / GIS Group
I / Inspectors
M / Maintenance Group
P / Precinct Personnel
S / System Administrator
Some of the tables use more than one prefix in their naming structure. For example, the routes table in CAMS is titled INF_LRSRoadway. This is the case in instances where a special relationship exists within a main asset class such as linear referencing supporting features within the Infrastructure feature dataset. Among the administrative tables, more than one prefix may be used to further indicate when an administrative table belongs to only one asset class such as infrastructure, as is the case of the ADM_INFRoadwaysNameRef table. This table is an administrative table containing all roads for CAMS, but only used within the Infrastructure module.