Designing Geodatabases for Transportation. J. Allison Butler

this time is with rows, not columns. There are multiple rows with exactly the same values in the Name, Speed, Lanes, and Pave_Cond columns. There must be only one row for each unique piece of information for the database to be normalized. The values in Speed, Lanes, and Pave_Cond are dependent on street name and address range; i.e., they are nonkey dependencies. Normalizing this database will require creating separate tables for each linear attribute that may span multiple street segments.

       Table 3.2b STREET table

       Table 3.2c SPEED_LIMIT table

       Table 3.2d NUMBER_OF_LANES table

       Table 3.2e PAVEMENT_CONDITION table

      There is a little more work to do. Look at the SegmentID column. Each table has its own SegmentID sequence. Imagine this database actually being composed of shapefiles or feature classes rather than relational tables. Instead of having four nonoverlapping, block-length street segments to define the four blocks of Sesame Street, there are now 10 overlapping street segments. What a maintenance nightmare!

      The whole concept of linear measurement systems was created to provide some degree of normalization for transportation databases. An event table database design using linear measures would have this structure:

       Table 3.3a STREET table

       Table 3.3b LINEAR_EVENT table

      This design is fully normalized because you only need to go to one record to change any piece of information. Of course, some changes may require you to write additional records when you subdivide an existing linear event or create a new one, but to find and change existing data is easy. Turn the SECTION table into a feature class and add dynamic segmentation and you have the traditional route-milelog method of managing data used at many state DOTs.

      The design is fully normalized but the fifth form of normalization has yet to be used. 5NF cannot always be achieved. This step in normalization is actually the elimination of data altogether. You do that by using relationships to reconstruct the data you dropped.

      Suppose you have assigned each customer of your business to one salesman. One customer has one salesman, and all the purchases by that customer are recorded under the name of the salesman. But you also need to know what each customer purchased so you can ship it and keep track of orders when the customer calls. This might lead you to believe that you needed to keep sales records by salesman and customer, but 5NF says that you only need to store purchases one time—the customer—you also store the name of the salesman who serves that customer. (You cannot, by the way, store the sales data only by the salesman, since each salesman has more than one customer.)

       Table 3.4a CUSTOMER table

       Table 3.4b SALESMAN table

       Table 3.4c ORDER table

       Table 3.4d ORDER_ITEM table

      Using this structure, you can find the items sold by a particular salesman or to a specific customer by querying the ORDER table. You can then do operations like calculate salesman commission by multiplying the total value of all sales by the commission rate. You could also track the sales of specific items by salesman for special promotional incentives. This database is in 5NF.

       Tracking events

      One last aspect of geodatabase design needs to be addressed before moving to the specific needs of a transportation dataset. The ArcGIS Tracking Analyst extension provides a data structure called a tracking event. This extension presents a useful way to structure data needed for some transportation applications that do not actually use the Tracking Analyst extension, and it represents an application of the normalization process.

      Tracking events come in two basic types: simple and complex. A simple tracking event is dynamic in that each row in the event table stores the position of the object being tracked. One table stores information about the object and its position. A simple tracking object is one that moves.

      A complex tracking event can be dynamic or stationary. A complex tracking event has one table to describe the object and another table to store its position at each moment of observation. This design allows more information about the tracked object to be stored in a single row, and eliminates the redundancy of the simple tracking object table, where each row has the same descriptive data. A stationary complex tracking event is one where the object is fixed but other objects pass by it or an observed quantity changes over time. A rain gauge is an example of a sensor that could be treated as a stationary complex tracking object.

      Figure 3.15 Tracking events The ArcGIS Tracking Analyst extension works with simple and complex tracking events. A simple event table contains everything needed to map the event’s progress. A complex event consists of a static table that describes the tracked phenomenon and a dynamic table describing temporal observations of each tracked event. Complex events may be stationary or dynamic.

      Simple and complex dynamic tracking events have obvious applications in transit system databases and fleet-management programs, where monitoring a vehicle’s location is useful. For example, you could create a dynamic complex tracking event for each bus in your fleet. The temporal object table would describe buses, with one row for each bus. The temporal observation table would store the GPS-derived coordinates of each bus transmitted by an onboard automated vehicle location (AVL) unit. This function suggests applications where arrival times are forecast for metro trains at stations or online to tell users when the bus is approaching a nearby stop.

      What may be less obvious is using complex stationary tracking events to store observations made over time at a single location. For example, traffic counts and crashes could be viewed as stationary complex tracking events. The temporal objects would be traffic monitoring sites and intersections. The temporal observations would be traffic volumes per unit of time and crashes that occur at random intervals.

       Geodatabase performance

      The number of host database queries that must be executed mainly determines the performance of a geodatabase. Performance is generally inversely proportional to the number of feature classes being used. ESRI provides a free Geodatabase Toolset that you can use when testing the geodatabase design prior to deployment. The functions available in the Toolset include:

      • Editing information

      • DBMS statistics

      •