Enterprise Application Integration Utilizing GIS & Spatial Databases

1. Enterprise Application Integration Utilizing GIS & Spatial Databases IDS Emergency Management 2004 June 28, 2004 Stuart Rich SGCi SGCi Penobscot Bay Media

2. Welcome The applications of GIS and software application integration in emergency management are myriad. Dispatch centers are usual places for such applications, but it is becoming more common if the world of responders as well. Often the best solution is adapting an application created for one purpose and applying that to another. The following are some examples to show what is possible in the arena of integrated applications that may have some cross-over value for emergency management as well.

3. Introduction At SGCi Penobscot Bay Media, one of our specialties is developing custom applications that integrate business databases with Geographic Information Systems (GIS). Our development staff has experience with Oracle, MS SQL Server, and IBM DB2 and we are developer partners with Oracle, Microsoft and IBM. We are also ESRI Business Partners with specific expertise with ArcSDE, ArcInfo 8, ArcObjects, ArcIMS, and ArcPad. This blend of database and GIS capabilities makes us uniquely qualified to create custom applications that blend the two technologies to help our clients solve their business problems.

4. Almost all of the business information that we deal with on a day to day basis has some sort of a geographic dimension to it. Customers have business addresses, invoices have shipping addresses, tenants have physical building space requirements. Often, the geographic relationships between these entities have significant operational implications. Customer addresses affect shipping charges and the routing of delivery vehicles. The location of available space makes it more or less desirable to a potential tenant. The position of a parcel in a particular planning zone may impact its current use or its development potential. There is a clear need to analyze the geographic relationships that exist within our business data.

5. Unfortunately, many of our business data structures are relatively complex. A Customer may have many Invoices which have many Line Items. A Well may have many Samples which have many Contaminants for each Sample. These complex data structures do not translate well to a geographic data model which is inherently flat. In order to blend the two data models into a single application, custom programming is required. To demonstrate these hybrid applications, we will show some of the work that we have done for the Maine Department of Environmental Protection.

6. SGCi Penobscot Bay Media Marine Oil Spill Information System Demo

7. Marine Oil Spill Information System (MOSIS) MOSIS consists of a pre-assembled set of environmental data store in ArcSDE on the mobile data server and a set of cartographic automation tools. These tools allow MDEP GIS analysts to start creating response logistics maps with consistent cartography and symbology within minutes of arriving at the command center. By utilizing the versioning capabilities in the ArcGIS Geodatabase, the analysts are able to save historic versions of the spill event for review and operational analysis after the event is over.

8. Because the system is deployed on the Citrix application server, additional analysts (up to 12 concurrent users) can be added to the system immediately without any software installation requirements other than the Citrix client. This capability allows the GIS response team to scale up very quickly with very little systems administration overhead.

9. The Solution Because of the rural nature of Maine, there is no wireless or cellular coverage in much of rural Maine for which MDEP is responsible. Our solution had to be a self-contained deployment that allowed the responders to have a complete copy of the relevant MDEP databases and their Enterprise GIS that traveled with them in their response vehicles. Our custom application utilized Oracle advanced replication, ArcSDE, ArcObjects, and Borland Delphi to create a fully independent mobile deployment for response personnel in four regional offices around the state of Maine.

10. MOSIS starts out with a template of pre-symbolized base map layers.

11. 1 2 When a new spill event is created (1), the user is prompted for some limited metadata about the spill event.(2)

12. A number of new custom feature classes are then automatically created in the Enterprise Geodatabase (1) and versioned for editing. All of these feature classes are specific to a particular spill event so that they can be analyzed separately. 1

13. The analyst can then use ArcGIS and a few other custom tools to add new features to the map, import NOAA trajectory models, model plume movements, save historic versions and print map atlases.

14. Distinctive Features • The following features represent leading edge technology for the MOSIS application: • Automatic creation of versioned custom feature classes • Citrix deployment of the mobile application server for scalabilitya • Automation of ArcGIS versioning

15. Conclusion Geography is often a determining factor in many of our daily business decisions. In this particular case, response logistics maps are a mission critical component of marine oil spill response. The software foundations provided by ArcGIS and Oracle and Citrix have allowed us to create a custom application for MDEP that not only integrates GIS with other business database information, but also deploys that integrated information into the field in a way that all parties have access to the most up-to-date information and applications currently available.

16. SGCi Penobscot Bay Media Query Builder Demo

17. Query Builder Usually, applications that integrate geographic and business data are focused on a particular business problem and work flow. In these situations, a work flow specific data form such as the one we built for the Quarterly Monitoring Program are the most appropriate solution. In other instances, however, where the users are quite sophisticated and the nature of their complex and unpredictable, a more flexible solution is required. For these situations we created a generic query builder for the Maine Department of Environmental Protection (MDEP).

18. This application was created as a custom extension to ESRI ArcGIS 8.2 A custom query builder has been created to allow users to link their geographic and business data with ad hoc queries.

19. 3 1 The user is presented with an ad hoc query builder tool. Any field from the data model (1) can be dragged onto the query (2). Filters, calculations, etc can be added to each field. When the user is finished, she executes the query. (3) 2

20. 2 The results from this query are then represented both on the map (1) and in an associated data form (2). In this case, several attributes have been reported for each well point. 1

21. To support the symbolization of multiple values for each point, we have created a 5-part compound user-defined symbol. Each segment of the pie (1) represents one value. By clicking on the segment, the user can define its symbology. 1

22. Here the user can define the color ramp, value breaks, and any patterns that she wishes to be applied to this segment of the compound symbol.

23. The resulting compound symbol represents up to five separate values for the same point in a single symbol.

24. Conclusion By extending ArcGIS with a generic query builder, sophisticated users can create ad hoc queries to explore the geographic relationships that exist within their business data. In this example, the business data and the geographic data are stored in separate Oracle instances. This same architecture could easily be applied to any of the major database platforms including MS SQL Server, IBM DB2, or Informix. The more interesting challenge is adapting thistechnology to solve your specific businessproblems.

25. SGCi Penobscot Bay Media Quarterly Monitoring Demo

26. Quarterly Monitoring Program One of the programs of the Maine Department of Environmental Protection (MDEP) monitors wells on a regular basis for possible groundwater contamination. The Groundwater database that stores the information about wells, samples, contaminants, owners, etc. is a large complex database consisting of 70 inter-related tables. Geologists and Hydrologists from MDEP needed a tool to investigate the spatial relationships between the data in their groundwater database. This tool would help them better understand contamination trends and estimate plume movement over time.

27. This application was created as a custom extension to ESRI ArcGIS 8.2 Each green dot that you see is a well being monitored for groundwater contamination.

28. 3 1 2 The hydrologist will use the selection tool (1) to select the wells that he is interested in. (2) He then clicks the Data Form button to see the groundwater data for the selected wells. (3)

29. A data form is then displayed that presents all of the groundwater data that is available for the selected wells.

30. The user can then select the individual well that he is interested in viewing more detailed information on and view all of the related groundwater data….

31. Including a graph of user selected contaminants over time.

32. Conclusion By extending ArcGIS with custom, database specific data forms, we can create integrated applications that allow users to explore the geographic relationships that exist within their problem specific business databases. In this example, the business data and the geographic data are stored in separate Oracle instances. This same architecture could easily be applied to any of the major database platforms including MS SQL Server, IBM DB2, or Informix. The more interesting challenge is adapting thistechnology to solve your specific businessproblems.

33. SGCi Penobscot Bay Media Skill Search Application Demo

34. Case Study The geographic location of a given asset has a direct impact on its value to an organization. This premise underlies an application we developed for a large construction firm specializing in heavy industrial projects across a dozen states; employing a staff of nearly 2,500. The geographic extent of operations provide many staffing challenges. Old methods resulted in increased travel costs as personnel might be assigned to projects many hours from home even when qualified workers were available nearby.

35. Case Study (continued) No tools existed to make fast, reliable evaluations about the proximity of skilled workers to a given job site. Staffing personnel needed an application that could assist them in finding qualified workers for jobs that were within a reasonable drive of the job site. Furthermore, this application needed to be available to staffing personnel at job sites around the country, not just in the home office. Skill Search

36. Skill Search Skill Search is a Web-based application that empowers staffing decision makers to make optimal use of any organizations’ geographically diverse workforce.

37. Skill Search begins by prompting the user for the skills, training, education, and geographic proximity required for an assignment.

38. Skill Search then creates a list of qualified candidates (1); locates each candidate on the map (2); locates the job site on the map (3). The user can interact with the map to explore associated business data about each worker or job site. 2 3 1

39. A worker information page displays the associated business data to assist in that staffing decision. A map illustrates the best route from the employee’s home to the job site.

40. To reduce confusion, tardiness and worker frustration, the map also includes turn by turn directions from their home to the job site with mileage and estimated travel time.

41. Distinctive Features • Skill Search showcases leading-edge technology, advanced integration and sophisticated data queries: • ArcGIS 8.3 – the gold-standard in GIS software • Integration of GIS with business/personnel data • ArcIMS deployment of interactive mapping and custom routing capabilities • Map hotlinks to the business/personnel databases

42. Conclusion Geography plays an important role in many business decisions. In our example, assigning the appropriate employee to a job site can help the company realize tangible, bottom-line benefits by decreasing travel costs. In an EMS context, the same application can be adapted to provide near-real-time resource reallocation throughout an event timeline. • Who is the nearest radiation specialist? • Where is the closest medical facility? • Where can I get four HazMat suits in the shortest possible time? These are questions that cannot be answered on the fly or with any precision using any traditional means. It is the combination of real-time geographic data (including incident reports, blocked roadways, chemical plumes, etc) and resource data that makes such a system so powerful in the hands of emergency managers.

43. Questions? For more information about this paper or other customized emergency response solutions, please contact SGCi Penobscot Bay Media, LLC. Stuart Rich President and GIS Division Director SGCi SGCi Penobscot Bay Media 16 School St., Rockland, ME 04841 (877) 594-0118 x 22 sturich@penbaymedia.com Michael Hardy General Manager SGCi SGCi Penobscot Bay Media SGCi SGCi Penobscot Bay Media 16 School St., Rockland, ME 04841 (877) 594-0118 x 27 mhardy@penbaymedia.com Web: http://www.penbaymedia.com/ems ** SGCi Penobscot Bay Media is a Woman-Owned, Service-Disabled Veteran-Owned (SDVO) Small Business with GSA contracts on Schedules 70, 899 and 541 (pending).