Brief Overview of State's Outdoor Advertising Program
Missouri's Outdoor Advertising (ODA) program is centralized and administered under the Right of Way Division, Missouri Department of Transportation (MoDOT) located in Jefferson City. The state is split into seven "Areas" and their is one field inspector for each Area. The ODA program permits by sign location, as opposed to sign face, and permits are renewed every two years on the anniversary date of the permit. There are approximately 14,000 permits currently in their system.
MoDOT's automated data gathering system is called the Transportation Management System (TMS). It is a Department-wide, all inclusive data base of various transportation data and information. One part of TMS is the Travelway Features Management System (TFM). TFM is a collection of applications that maintains data about travelway features. Travelway features are points or areas of interest that exists along a travelway such as billboards, junkyards, bridge culverts, sign controllers, etc. Therefore, the Outdoor Advertising program data is a part of the TFM system. Within TFM, one has access to Travelway Feature Maintenance, Feature Inspection Maintenance, Feature Comment Maintenance, Feature Permit Maintenance and Involved Party Maintenance.
Motivation Behind Selection of System (Technical and Administrative Drivers)
What prompted need for system?
The ODA program originally utilized a mainframe data base storage system. At some point, the mainframe system was separated into seven Area mainframe files. The system was difficult to monitor and to manage, and data seemed to disappear. It was also very slow to print. The ODA program had access to enhancement funds, and looked into designing their own stand alone database system. It was decided by MoDOT that the ODA program not build a stand alone system, but rather integrate with the department-wide system (TMS) that was already under development and in use for many functions of MoDOT.
What were your system requirements?
ODA's previous system was very slow and it required significant programming time and effort in order to extract useable data. The system was difficult to monitor and to manage, and data seemed to disappear. It was also very slow to print. Consequently, ODA's primary objectives were to create a system that was user friendly, especially for generating queries and reports, easy to update and to maintain, and reliable.
Process of system selection
ODA program staff knew that they wanted to move away from a mainframe system and to a client-sever type of application. They looked at Florida's system and liked many aspects of it including the choice of database software (Oracle), and its use of Global Positioning System (GPS) for locating signs. Since TMS had these two essential attributes and was already under design and even in use for some MoDOT functions, it was decided that the ODA program integrate with TMS rather than design a stand alone system. A contractor was already operating on-site at MoDOT on the design of TMS. Therefore, there was no need to go to competitive bid for the design of the ODA portion of the system (the ODA module).
System Design and Development Process
Contractor versus in-house development?
ODA used the same contractor who was already working on-site at MoDOT under a long-term contract on the design of TMS. Enhancement funds were used for the development of the ODA module. The consultant's fees were billed separately for his work on the ODA module versus the rest of TMS.
Who and what process was involved in technical planning and system design?
The Outdoor Advertising Manager, the Right of Way Liaison, and some of the District permit specialists engaged in what were called joint application development (JAD) meetings with the consultant. The JADs took place over several days and resulted in a flow diagram of the data that needed to be stored and collected for ODA, and how those elements would fit in with TMS as a whole.
How long to develop system?
TMS as a whole took about three years to develop. TMS was implemented in phases starting about 18 months prior to the site visit. The first phase of the ODA module was rolled-out in November 1999, and has been in operation for approximately 11 months. Design of the ODA module and implementation of the first phase took approximately 10 months. The JAD meetings took place over several days almost a year before design work on the ODA module actually began. The second phase of implementation includes a correspondence component. This component provides the ability to generate appropriate letters regarding permit renewal, violations and other correspondence. The correspondence piece was implemented in early 2000, about 4 or 5 months after phase I. The field data collection system will be the final phase of the system and will likely be implemented in early 2001.
Was the system designed to integrate with other systems?
The ODA module is a fully integrated piece of MoDOT's Transportation Management System. TMS houses all transportation related data collected by the state including data on: billboards, accident statistics, pavement conditions, bridges, roads, etc. TMS also integrates with the state's financial management system (FMS).
Hardware, including data collection tools
The system's hardware components include a server and desktop PCs. Field data collection equipment has not yet been purchased. System will eventually include some type of GPS unit (TMS system uses voice activated GPS units for other applications, but ODA will probably use some other kind of GPS data collection tool), laser measuring system and digital cameras.
The system uses Oracle database software running on a Windows NT operating system. Reports and queries are generated in tabular and/or chart form using Impromptu. ArcView is used for mapping or other geographical display of queried data.
The TMS system is a client-server PC based system using Oracle database software. The ODA module is one aspect of the Travelway Features Management system within the MoDOT Transportation Management System. (See Appendix A) The screens are menu-driven, object-oriented, windows type screens. Several commonly used queries and reports are included in the design of the system based on requirements identified by ODA staff. Customized queries and reports can also be generated. Imbedded reports include: Biennial Permits Due by Area by Reference Number, Biennial Permits Due by Area by Name, Biennial Permits Due by Area by County, Customers without FMS Number, Outdoor Advertising Sign Inventory Details by Area by Travelway, Outdoor Advertising Sign Inventory Details for Permits Due by Area by Reference Number, Outdoor Advertising Sign Inventory Details for Permits Due by Area by Name, Outdoor Advertising Sign Inventory Details for Permits Due by Area by County, TMS FMS Permit Number Cross Reference, and Financial Transactions Logged Not Sent to FMS. (See Appendix B)
The system includes various error checking features including a user-ID and password log-on requirement, and drop-down menus associated with most data fields. The user's ID defines the level of access he or she has to certain data in the system. For example, District inspectors can view all data in the system, but can only change/add/delete data in their own District. Various internal data checks are also built into the system. For instance, the system will not allow a user to generate a new permit number if the permit application information includes illegal sign parameters, e.g. too large, etc.
Data Collection/Data Input - Procedure and Mechanics
ODA program procedures and practices
District offices each process their own permit applications and perform their own biennial sign inspections. Permits are renewed every two years on the anniversary date of the permit approval. The field data collection portion of the ODA module has not yet been designed. Currently, field inspectors download the information that they need from TFM onto a zip disk, print it out, do their inspections, make any changes/corrections/additions, etc. to the data at their district office, then send the information back to the Headquarters office in Jefferson City to be uploaded permanently into the main system.
How was existing data transferred to the new system?
ODA data was stored on seven Area mainframe files. Data files from these mainframe systems were transferred to the new system, but significant data "cleansing" was required before the data could be transferred. Extra spaces, commas, quotation marks, etc. had to be removed from the old data files so that the relevant data could be recognized and transferred to the new system. Data conversion was a very difficult and time consuming process. The new system includes data fields that were not a part of the old system. ODA decided to convert the data they had, and to gradually update missing pieces of information after the new system was implemented and in use.
Hardware/software data collection tools used?
The field data collection system has not yet been designed. It will eventually include use of digital cameras, GPS units of some kind, and a laser measuring device of some kind. Most likely, field inspectors will use laptops equipt with a networked version of the ODA portion of TFM. But the system may end up being configured to have field inspectors download portions of the data base to be used in the field and then upload the corrected information to the main system.
System Strengths and Weaknesses
A large part of the ODA module functionality will be the field data collection system which is not yet in use. Despite this, several strengths and benefits of the system are evident. These points include:
The system is extremely flexible in terms of adding/changing data to be collected, e.g. no modification of code necessary to change screens.
There is complete locational integration, through use of GPS coordinates, between all travelway features.
The system integrates with the state's accounting system. This allows invoices to be generated automatically based on renewal date information in system. Integration with the accounting also means that permits are automatically denied if a TFM user attempts to give a new permit number to an existing account that is delinquent.
The only weakness identified by ODA was that, because the ODA module of TFM is a part of the overall MoDOT system, it is not as customized to the Outdoor Advertising program procedures and needs as a stand alone systems might have been.
What would you do differently/advice?
To date, ODA has not identified anything about the system that they would change. They felt their planning and design process was sufficient and that the right people were involved. ODA staff are happy with the selection of the Oracle software and the type of system in general. Because their contractor was on-site working with MoDOT long-term, ODA felt their use of a consultant went very well. Missouri felt that integration with the ODA program's accounting system was critical to a data collection and management system. The correspondence component has also proved a very useful and time-saving feature.
Design, implementation and training
Missouri spent approximately $574,000 for Phase I design, development and implementation. Phase II, which is composed of the correspondence piece that generates appropriate renewal letters, violation letters and other correspondence, cost an additional $57,671.
Hardware and equipment
The field data collection of Missouri's system is not yet complete. Equipment costs are unknown. The server and PCs used to run the system are the same as those that were already in use at MoDOT and are not specific to this use.
Sample System Screens
Textual descriptions of database screen images in this appendix are provided immediately following each image. These descriptions are enclosed in brackets. Within each image description, any fields and buttons in the screen are identified in bold, italicized text. Other words appearing in the screen images are in quotation marks.
[The Billboard Permit Maintenance screen is split into four sections. The top section is called "Travelway Location" and includes the following fields: Designation, Name, Direction, County, County Log and Continuous Log. The section below that is called "Feature". This section contains the following fields: Type, Status, and Reason. The third section of the screen is called "Feature Traits". The fields in this section are: Permit (number), Message, Land use, Inside city (yes/no). The bottom section of the screen is a data output section. It contains a spreadsheet displaying various information about the travelway feature described in the sections above. The spreadsheet has six columns. The columns are: Permit, Application Date, Type, Status, Reason, and Involved Party. Each permit number would be another row in the spreadsheet.
Menu options at the top of the screen include File, Edit, View and Help. There is also a toolbar below the menu bar that contains various shortcut icons such as to print and to add information. It appears as though some of the other icons may be shortcuts for printing a report of the information in the screen.
[The TWF (stands for Travelway Feature) Billboard Maintenance Screen is similar to the previous screen in that the top portion of the screen contains various fields and the bottom portion of the screen is a data output spreadsheet displaying various information about the selections made above. At the top of the screen is an area to designate the "Selected Travelway". The travelway is identified by the following fields: Designation, Name and Direction. The middle portion of the screen has a box labeled "From" and beside it, a box called "To". The "From box contains the following fields: District, County, County Log, and Continuous Log. The "To" box contains the same fields. Just above the data output spreadsheet area is the field Feature Type. It has a pull down menu next to it with "billboard" selected. Next to that is the field As of Date. There is space to enter a date and a button that calls up a calendar. The next field over is Status Code. This field also has an associated pull down menu, but nothing is currently selected. The spreadsheet area has five columns - Begin Log, End Log Activation Date, Status and Reason.
The menu options at the top of the screen include File, Edit, View and Help. There is also a toolbar below the menu bar that contains various shortcut icons. It appears as though these icons may be shortcuts for printing a report of the information in the screen.
[The TWF Correspondence Generation screen has two main portions. The top of the screen contains a box called "Templates". The box has two columns and a scroll bar on the side and contains a long list of possible types of correspondence. The two columns are Name and Description. For example, the first row in the box says "BIENNIALPERMITCOVER" under Name, and "BIENNEAL PERMIT COVER". Next to the box is the Feature Type field, which has a pull down menu associated with it. The feature type "Billboard" is currently selected. Also next to the box is a message that tells the user that "Templates are stored in Y:\TMS\Templates". The template name currently highlighted in the box is also displayed in a field called Template: next to the box. Below the "Templates" box is the "Fields" box. This box contains two columns - Name and Data. This box displays the fields that are included in the template highlighted in the "Templates" box. Below the "Fields" box is a check box that says, "Clear User Specified fields after document generation." It is currently selected.
At the top of the screen are the following menu options: File, Edit, View and Help. Below the menu bar is a toolbar with various shortcut icons such as a print button.]
[The Query Selection screen is simply a list of various possible reports. The hierarchy of the list is "Reports - Travelway Features Maintenance - Outdoor Advertising". Below that is a list of 19 different reports. The possible reports are: Biennial Permits Due by Area by County, Biennial Permits Due by Area by Name, Biennial Permits Due by Area by Reference Number, Customers without FMS (financial management system), Number, Financial Transactions Logged Not Sent to FMS, ODA Billboard Permits by Area by Landowner, ODA Billboard Permits by Landowner by Area, ODA Cash Receipts, ODA Delinquent Receivable, ODA Financial Transactions Overriden by UserID, ODA Notice to Remove Billboard greater than 30 days, ODA Renewals No Permits Sent, ODA Vegetation Permits Active greater 30 Days, Outdoor Advertising Sign Inventory Details by Area by Travelway, Outdoor Advertising Sign Inventory Details for Permits Due by Area by County, Outdoor Advertising Sign Inventory Details for Permits Due by Area by Name, Outdoor Advertising Sign Inventory Details for Permits Due by Area by Reference Number, Outdoor Advertising Sign Inventory Details for Permits Due by Area by Travelway, TMS FMS Permit Number Cross Reference.
At the bottom of the screen are two buttons - OK and Cancel. Menu options at the top of the screen include File and Help.]
[The Owner Selection screen has a field at the top called Feature Type. This field has a pull-down menu with "Billboard" selected. Below that is a field called Start Position. Next to the Start Position field is a button that says Show All. Below these three fields is a box labeled Owner Name. It contains a list of billboard permit owners and has a scroll bar to the side. Next to this box are two fields Transfer From Owner and Transfer to Owner. Next to these fields are buttons - From and To which transfer the owner name highlighted in the box into the Transfer From and Transfer To fields. At the bottom of the screen are three buttons - OK, Cancel and Help.]
[The Involved Party Maintenance screen consists of three fields across the top of the screen and a data display spreadsheet area taking up the rest of the screen. The three fields are: Feature Type, Involved Party Type and Get Involved Parties starting with:. The Feature Type field has a drop-down menu with Billboard selected. The Involved Party Type field also has an associated drop-down menu, which is activated revealing the options "owner, land owner, inspector, remover, builder or XFR Owner". The Get Involved Parties starting with field has a button next to it labeled Involved Party List.
The data display area is a spreadsheet with four columns and scroll bars along the top and side. The four columns are: Feature Type, Involved Party Type, Bus, and Name.
The menu options at the top of the screen are File, Edit, View and Help. Below the menu bar is a toolbar with several shortcut icons such as print, add, stop and find.]
[The TWF Permit Transfer screen has three fields at the top - Feature Type, Transfer from Owner and Transfer to Owner. None of these fields has associated pull-down menus. Below these fields is a boxed off area with two radio buttons that can be selected. The boxed off area is called "Transfer Method" and the two radio buttons that can be selected are "Multiple Transfers on a Single Form" or "Single Transfer on a Single Form". Below this are two data display boxes situated side-by-side. On the left is the "Transferable Permits" box and on the right is the "Permits to be Transferred" box. The Transferable Permits box has two columns - Permit Number and Permit (name). The Permits to be Transferred box has the same two columns. Between the two boxes are four buttons with different direction arrows on them allowing the user to transfer permit number entries between the two boxes. At the bottom of the screen is a button labeled Transfer.
Menu options at the top of the screen include File, Edit, View and Help. Below the menu bar is a toolbar with two shortcut icons that appear to allow the user to move forward and/or backward to another permit.]
APPENDIX B: Billboard Feature Reports Menu Screen and Sample Reports
* Case study based on site visit conducted on December 9, 1999 and telephone update conducted in October, 2000. Those present at the site visit included:
Marci Horton, Outdoor Advertising Manager, Right of Way Division, Missouri Department of Transportation (MoDOT)
Joyce Musick, District Permit Inspector, Outdoor Advertising, MoDOT
Dewayn Gaddy, Right of Way Liason, MoDOT
Mark Schmidt, consultant, database designer
Madalynn Bell, financial information
Diana Johnson, Right-of-Way Technician (responsible for training)