Taking IT to the street: Using a street network and parcel base to integrate facility, maintenance, and customer data

Taking IT to the street: Using a street network and parcel base to integrate facility, maintenance, and customer data

Nancy B. Lerner
EMA Services Inc., 4319 Medical Drive
Suite 131-343, San Antonio, Texas 78229

Abstract
Utilities and public works departments use a number of different software packages for customer service and facilities management. These systems include AM/FM/GIS, work management systems (WMS), customer information systems (CIS), hydraulic models, and fixed asset financial systems. Database integration is essential to achieving the full benefits of these software tools.

Street locations are a critical piece of the integration puzzle. This paper explores technical issues in the design of an integrated database that associates a street network and parcel base with street names, address ranges, situs addresses, parcel IDs, and the locations of facilities, customers, capital projects, and maintenance work.

Assumptions and methods
This paper is designed to raise database design issues that are of special interest to utilities and public works organizations. The issues are important to anyone who ultimately needs to use the database, but the paper is geared toward technical data modelers and database programmers. The appropriate resolution of the issues in this paper will depend on an organization’s business practices; there is no single “right way.” Possible approaches are suggested as a means to clari~ issues and provide a starting point for consideration of an organization’s unique requirements. Figure 1 defines the conventions this paper uses to convey information about entity relationships. The definitions describe the relationship of a left-hand entity to a right-hand entity.

Figure 1. Conventions for Describing Entity Relationships in this Paper

Refreshing locations on a street network
A street network is a database of physical attributes and addresses for a topologically connected set of street segments. Each segment is identified in part by two endpoint nodes. The nodes usually represent physical street intersections, but some may represent points at which there is a change in the street’s physical composition, jurisdiction, or addressing rules. Figure 2 shows endpoint nodes defining street segments. Typically, the “from” node represents the end of the street with the lower address numbers. If this rule is not applied consistently, then the street segment entity needs a separate attribute to indicate which end bears the lower number. Figure 2 illustrates the difference between a conceptual street network and its location in space; a street network can have multiple locations in space depending upon which map or survey is being considered.

Figure 2. Street Segments, Street Network Nodes, and Coordinate Locations

People generally refer to a street segment by citing a street name and address block range. Street naming and addressing rules can be complex. In order to accurately represent street network locations, database designs should be flexible enough to accommodate the following phenomena:

Multiple graphical components representing curved street segments
Different street segments with identical endpoint nodes
Duplicate street names and non-contiguous streets
Alias street names
Name and address discontinuities on a street segment

Multi~le Gra~hical Components Rem-esentin~ Curved Street Se~ments When using AM/FM/GIS or CAD tools to create street centerlines, drafters may break curved streets into multiple pieces to better represent the physical curves. These extra segments are useful for visual representation. However, it is not necessary to create separate attribute records for each small piece of street. In order to maximize data management efficiency, the segments in a street network should define meaningful breaks in physical structure, jurisdiction, or addressing rules. Figure 3 illustrates a logical distinction between street segments and the small pieces that comprise their visual representations. This database design provides the option of linking street segments to one or more graphic components.

Figure 3. Street Segments May Have One or More Graphic Components

Different Street Segments with Identical Endpoint Nodes
When all streets are aligned in regular grids, the individual segments maybe uniquely identified by their endpoint nodes. However, most communities have at least a handful of irregularly patterned streets and circular drives that have the same endpoint nodes as other segments. If this is the case, then the street segment key needs an additional component to augment the identi&ing nodes. In Figure 1, the street segment entity is identifies by a three-part key: the “from” Node ID, the “to” Node ID, and the Segment ID.

Street Name Normalization
In a normalized database, a street name is an entity, not an attribute of street segment. Figure 4 illustrates the basic concept. In most communities, the set of street segments associated with a given name will occasionally be non-contiguous due to breaks in street alignments and duplication of street names. In these situations, communities may want the ability to re-name groups of street segments at some point in the future. (There area number of public safety arguments and other reasons for eliminating the redundant street names.) By using a random ID as the key and modeling the name itself as an attribute, an organization can create separate street name instances to link to non-contiguous sets of street segments. The multiple instances may carry the same name attribute at first, but the structure allows a local government to change the name of one contiguous set of segments without creating a new entity instance and modi~ing the associated street segment records. This structure also facilitates retention of a street name history.

Figure 4. Street Name Entity Names a Set of Street Segments

Street name nomalization has a significant impact on data maintenmce productivity. Rather than maintaining (and validating) street name text in myriad address records, an organization can manage the data in a table or tables representing a separate street name entity. This concept works when addresses are defined as the combination of structure numbers and fronting street segments. (See Figure 5.)

As Figure 4 illustrates, a street name generally consists of a main street name, a type suffix (e.g., Avenue, Boulevard), a pre-directional (e.g., N, S, or NE before the main name) and a post directional (e.g., N, S, or NE after the type suffix).

Alias Street Names
In most communities, streets can have alternative names. These alternatives maybe historical street names, or they may be numerical designators that appear on signage along with the official names. By designing these “alias street names” into its database, an organization enables system users to search and select data using common aliases. Street name aliases can be modeled in many ways. If the alternative names generally pertain to the entire collection of segments bearing a particular official name, then a one to many relationship can be modeled between a street name entity and an alias entity. If aliases pertain frequently to subsets of the segments associated with an official name, an organization may wish to create an associative entity that links street segments with either an alias entity or the street name entity. In the latter case, the street name entity would include instances of official street names and unofficial (alias) names. Whichever approach is selected, the model should reflect the almost universal reality of streets bearing multiple alternative names.

Name and Address Discontinuities on a Street Segment
If a street name or numbering scheme changes mid-block, it maybe useful to divide the street into different network segments so that the name and number ranges can be properly represented. Sometimes, street segments carry different names on opposite sides of the street. This may occur when a street serves as a boundary between two local governments. Even if the street name is the same, different numbering schemes may apply. One way to deal with this situation is to create an entity for the segment’s “second side.” The second side entity would carry its own address number range and its own relationship to street name (and potentially to street name alias associations). This approach allows the organization to maintain a single logical segment while preserving the flexibility needed to represent boundary streets. Alternatively, the two sides of the street could be represented as separate street segment instances sharing common endpoint nodes and a common set of street segment graphic components. (See Figure 3.) This approach requires the use of a unique Segment ID to distinguish between the opposite sides of the street. (See Figure 2.) When selecting an approach to this issue, it is usefi..d to consider pavement management and dispatch processes. Even if the organization uses different networks for these purposes, it maybe worthwhile to design consistent approaches to jurisdictional boundary issues.

Linking structure addresses and the street network
When high and low potential addresses (i.e., address number ranges) are designed as attributes of a street segment entity (See Figure 5), and when a consistent rule (or a separate attribute) designates the “low number” end of the segment, a street network can be used to approximate locations of street addresses. In addition, the street segment entity plays a role in the addressing of individual structures. Depending on the rules of the particular community, street addresses may be assigned to buildings, signs, benches, utilities, and even potential building sites on vacant plots of land. In Figure 5, the structure entity is a generalized address-bearing object that represents all address-bearing entities.

Street Segment

Figure 5. Street Segments Front Address Bearing Structures

Figure 5 illustrates the use of a fronting street segment to associate a structure with a street name. Some structures occupy large areas that are bounded by multiple street segments. However, for addressing purposes, a structure that bears a single address has only one “fronting” segment. If a single physical structure bears multiple main address numbers, an organization might consider an associative entity that links a structure and a segment. Alternatively, the organization could define each portion of the building or structure that bears its own address as a separate instance of the structure entity. Under this approach, a single instance of a physical building entity could be related to multiple instances of an address-bearing structure entity.

Substructures are portions of an address-bearing structure that carry unique unit numbers. Apartments and office suites are examples of substructures. By linking the substructure entity to the structure entity as in Figure 5, an organization captures both the street name and the main street number in a normalized design.

Some communities have structures or substructures that use non-standard addresses. For example, some structures use the names of small private driveways that are not part of the street network. Figure 5 offers an approach to modeling the non-standard addresses. It preserves the relationship to the fronting street and can even support an “ideal” main address number that would be assigned if the structure were addressed in the standard way. At the same time, it allows a non-standard address to supersede the one implied by the association with the fronting street. If an organization needs to support a large quantity of non-standard addresses, it may wish to normalize this design by creating “non-standard addressing” relationships between the structure entity and the street name and alias street name entities.

Customers and addresses
There are at least three different types of addresses associated with customer accounts:

The general mailing address, which need not bear any relationship to the street network.
The billing address, which need not bear any relationship to the street network.
The service address, which is a situs address that can be defined using the street network.

The physical location of an account is closely approximated by the service address. However, the street segment that fronts the home or building that carries the service address may not be the segment that is closest to the services and meters on the property. For this reason, utilities may wish to store more detailed information about service location.

Parcels and Service Locations
Utilities frequently use parcels as a means of locating customer accounts and their associated services. A parcel link is a handy way to tie account data to AM/FM/GIS graphics, and parcel polygons are useful for thematically mapping consumption and other account data. However, there is rarely a one-to-one relationship between parcels and accounts or services. For example, one parcel can be associated with many accounts and/or many services for a single account. This may happen when parcels are re-platted and joined after utilities are in place. Also, one account may include many parcels, particularly if a parcel is re-platted and split after utilities are in place. Because of these limitations, it is important to distinguish between the location of a parcel and the location of services and meters.

Treating Services and Meters as Facilities
If services and meters are modeled as any other utility facility, an organization can pinpoint their locations and still retain the relationship to parcels through the customer account entity. (See Figure 6.)

Figure 6. Parcels, Accounts, Meter Sites, and Meters Are Related Entities.

Locations of facilities and work
As illustrated by the Meter at Meter Site entity in Figure 6, there is a difference between a physical facility and the site it occupies. Separate entities are required in order to maintain comprehensive maintenance records for pieces of equipment that may be moved and for sites that may hold different pieces of equipment over time. Work orders (i.e., maintenance records) relate to the association of facilities and facility sites. This association may bear date attributes to show the period of time during which a particular facility occupies a particular site. The date information may be part of the associative entity’s key, or a separate key component may be needed, particularly if a piece of equipment has the potential for being re-built and re-installed at the same site at a later date. (See Figure 7.)

Figure 7. Customers, Complaints, Work Events, and Facilities Are Related Entities.

When defining pending work (i.e., entering a new work request or work order in a computerized maintenance rnanagement system), it may not be possible to identify the specific instances of the facilities at sites that are to receive the work. This information may not be known until after an initial investigation. For this reason, the relationship between a work event entity and a facility at a site cannot be mandatory all the time, but an organization may wish to require entry of at least one work event facility as part of the work order close-out process. This ensures that the utility can analyze historical maintenance locations.

Although some organizations assign official situs addresses to out-plant facilities, most rely on maps to show the locations of facilities in the street. A database can provide this information if it includes map coordinates for facility sites and if those map coordinates have been derived using the same base map that has determined the map coordinates for street segments or associated rights-of-way. It is also possible to build a more explicit relationship by clustering facility site instances with street segment and node instances. An optional one-to-many relationship between the street network segment or node and the facility site can prove useful in searching and selecting groups of out-plant infrastructure sites.

When the “facility” undergoing work is actually a segment of street, the public works organization may choose to use the addressing street network segments and nodes to define physical streets and intersections. Alternatively, the organization may wish to use a separate pavement management network. In either case, the work event entity may incorporate attributes for distances or estimated address ranges to further pinpoint the work location.

Linking customers complaints and maintenance
Organizations receive complaints from customers and non-customers. The data model should accommodate either. (See Figure 6, where Customer is a sub-entity of Party.) Parcel numbers are poor substitutes for Party IDs because complainants may not be property owners.

An important issue in designing a complaints database is the management of redundant complaints. If three people call to complain about water in an intersection, an organization may wish to track each individual complaint. One way to do this is to create a separate complaint instance for each party, in which case the complainant contact information can be captured with a direct relationship between party and complaint. (See Figure 7.) The alternative is to create an associative entity that permits multiple parties to be added to a single complaint.

A robust database design would augment the relationships depicted in Figure 7 to show optional associative entities that link street network segments or nodes with complaints and work requests; this provides spatial information when the affected facilities are unknown. Figure 7 shows complaints inheriting location data by generating work requests, which are then processed and assigned to work orders that affect facilities at facility sites.

Accommodating common legacy data structures
Street network and facility location issues are easiest to address when an organization is implementing a complete logical data model to which all software programs will adhere. When legacy files structures have to be integrated with the logical model, it maybe difficult to accommodate certain structures. Common problems include:

Unformatted Addresses One way to integrate free-format situs address data is to build the street network and street name link, reconcile the contents of the legacy file, and build validation routines around controlled data entry procedures for ongoing use of the legacy system. No Distinction between Facility and Facility Site. Many maintenance systems do not distinguish between pieces of equipment and the sites they occupy. One approach to this problem is to maintain a separate set of site identifiers and a facility site association outside the system database instance. The organization would then need to create system modifications or middleware to support maintenance of the outside tables. Another approach is to treat the facility entity in the system database as the associative entity such that a piece of equipment receives a new ID when it is moved to a new site. Under this approach, separate tables for the facilities and sites are maintained outside the system database instance along with a copy of the associative entity instances (IDs and dates only). The external tables need to be updated when a piece of equipment is installed, moved, or retired.

Conclusion
A street network is an excellent mechanism for integrating public works and utility information. However, the value of the street network depends upon the organization’s logical data model. Some software products over-generalize data concepts in a manner that diminishes the value of the street network. Proper planning can alleviate some of these problems. Also, robust logical data models can help organizations evaluate the fit of potential new products.