Easy Work Orders, Permits, and a Self-Maintaining GIS
By Molly Klimas and Joe LaCombe

TAGG-Tidemark workflow

Two new GIS applications at the city of Indianapolis and Marion County, Indiana (known as IndyGov), are automating GIS maintenance tasks and enabling users to tap the information contained in the GIS, perform functions such as permit and work order generation and tracking, and automatically update the GIS with new information.

Fast, Automatic Updates

The two new GIS applications, Accela's Tidemark Automated Geography Generator (TAGG) for permits and Siebel Automated Geography Application (SAGA) for work orders, let IndyGov's 500 employee users initiate, track, and complete all kinds of permits and service requests faster than ever while automatically updating the common GIS.

TAGG-ArcSDE workflow

"As IndyGov's GIS evolved into a centralized function used enterprise-wide throughout the city and county and by thousands of citizens via a Web-based application (ArcIMS), we needed these two applications to streamline processes without having to do double duty updating the common GIS shared by users," said Chuck Carufel, GIS manager for IndyGIS. IndyGov and Esri business partner Woolpert, Inc., developed the two applications using ArcGIS Server and ArcSDE and products by Siebel and Accela.

In the past, there was no automation. IndyGov's GIS staff manually created GIS features corresponding to service requests and permits. The new applications not only automate the feature-creation process for these types of activities but also perform automation behind the scenes and totally within the database. The applications were developed using the Java programming language and Oracle PL/SQL.

Java was used for

• Developing the component of the application necessary for receiving new permit or service request information
• Connecting to ArcSDE
• Spatially locating the coordinate locations of new service requests or permit cases
• Creating the corresponding point features and, in the case of building permits, the corresponding polygon feature

SAGA-Siebel Database Server workflow

Because IndyGov required that the applications be contained within the database in a two-server environment, Woolpert chose Java API for ArcSDE for this functionality. "The ArcSDE Java API allowed us to perform GIS-specific functions and tasks all inside the database and without a visible interface. Java allowed us to develop this functionality and compile the code as Java-stored procedures, which Oracle can store and execute totally within the database," explained Rick Petrecca, GISP, project manager for IndyGIS.

Oracle PL/SQL was used to develop a series of triggers within the Tidemark and Siebel Oracle 9i databases, respectively. The trigger executes when permit cases (Tidemark database) or new service requests (Siebel database) are entered. PL/SQL was also used to create database links from the Siebel/Tidemark database to ArcSDE, which is in Oracle 10g.

The trigger also executes PL/SQL-stored procedures that insert information into a staging table that retains the data if a connection cannot be made to ArcSDE. Finally, once a connection has been made to ArcSDE via the database link, a final PL/SQL-stored procedure executes the Java procedure and passes the required permit or service request information to that application. Any errors that occur are logged to a separate database via a Visual Basic .NET Web service that is called from within the Java code.

Automating Permits and Service Requests

IndyGov's Department of Metropolitan Development logs a significant number of permit and service requests every day. Until recently, corresponding GIS features for these requests were created manually at irregular intervals.TAGG is a server-based solution that now automatically creates these GIS features.

SAGA-ArcSDE Database Server workflow

Customized for IndyGov by Woolpert, TAGG's server-based solutions provide automatic generation of corresponding ArcSDE GIS features based on the new permit data as each new permit is generated. These features are then immediately accessible and viewable for any GIS user who needs to access this data. This application is unique because it integrates Oracle 9i, Oracle 10g, and the ArcSDE 9 Java API functionality.

TAGG was developed as a Java-stored procedure executed via a trigger within Accela's Tidemark Advantage 3.2.1 database. However, this procedure is stored within the Oracle database schema on IndyGov's enterprise ArcSDE Server. When a permit is created, a Tidemark database trigger links to the ArcSDE Server and executes the Java procedure that creates geographic data based on the permit information gathered from the Tidemark database. When the new permit is related to new building construction, additional address information will be inserted in the Master Address Database.

Tidemark users create new permit cases within the Tidemark Advantage software, and the record is saved in the database. The user then moves to the next permit case request and repeats this process. Meanwhile, behind the scenes, an automated process is occurring: As a new permit case is saved to the Tidemark database, a database trigger is executed that creates a connection via a database link to Oracle on the ArcSDE Server.

As a database connection is created on the ArcSDE Server, related information is passed from Tidemark to the Java procedure stored within Oracle. The Java procedure is executed, connects to ArcSDE, and queries a parcel layer for the parcel corresponding to the new permit.

When the parcel is retrieved, the parcel centroid location, or center of the parcel polygon, is returned as an x,y coordinate. The Java procedure then creates a new point feature corresponding to the new permit and assigns the necessary attributes. If the permit is related to new building construction, a polygon feature is created in a separate building permit feature class and the necessary attributes are assigned. Because this is a building permit, the building address information is inserted into the Master Address Database.

Continued on page 2