Central San is a water resources agency that serves nearly 450,000 customers in our 145-square-mile service area. Founded in 1946, Central San collects and treats 30-50 million gallons of wastewater every day, and produces about 600 million gallons of recycled water for industrial uses and irrigation. We operate and maintain a large wastewater treatment plant, 19 pumping stations, and 1,500 miles of sewer collection pipelines.

Central San began using Geographic Information System (GIS) technology in the mid-1990’s to replace the manual method of producing maps of our infrastructure. However, GIS tools were only available to the mapping department –other departments in our organization relied on hardcopy maps produced from our GIS. Operations data was scattered across numerous departmental and enterprise databases and required key staff to generate summary reports on request. In 2000, the GIS department began planning a system to give staff access to GIS and operations data from a web browser.

When Central San first deployed a pilot web-GIS application in 2002, it was built on two software platforms: AutoDesk MapGuide and Allaire ColdFusion.  Autodesk MapGuide itself had two components: a server component (which converted GIS data into a binary format which was transmitted to the web browser), and browser plugins (which rendered the binary data to the browser screen via a Java applet or ActiveX control).  Other than configurable tooltips, MapGuide didn’t have a mechanism for displaying detailed attribute information about the various GIS map elements.  Because of this, several dynamic ColdFusion web pages were developed to present the GIS attribute information, mostly in tabular format.  For the purposes of the pilot application, the ColdFusion server was connected to three databases: the GIS asset registry, the Computerized Maintenance Management System (CMMS) for tracking maintenance on our sewer collection infrastructure, and the Enterprise Resource Planning (ERP) database that contained parcel ownership and permit history.

In 2005, we replaced the GIS software used to maintain our GIS data and redesigned the GIS database schema.  The quality of our data drastically improved, and we automated processes to generate the key identifiers that would allow connecting GIS infrastructure assets to related departmental databases.  Once the new GIS system was in place, we upgraded to the latest version of ColdFusion (at that time Macromedia ColdFusion MX 6.1) and fairly quickly rewrote the application to accommodate the new GIS schema and provide robust search tools.We connected the ColdFusion server to additional departmental databases: CCTV pipeline inspection history, pipeline hydraulic capacity, infrastructure improvement project data, environmental compliance inspection history, and a document management system.  These databases ran on a variety of platforms: Microsoft SQL Server, Microsoft Access, and IBM AS/400.

The new web-GIS application was quickly adopted by several of our departments.  Because the new GIS software allowed us to update our map data more regularly, we were able to provide up-to-date information in the web-GIS application long before updated paper maps were released.  However, the biggest factor in the success of the web-GIS was the real-time access to the departmental databases related to our assets through the ColdFusion-driven web pages.  These tools saved hundreds of hours of staff research time.  When our Permit Counter staff recognized the efficiency this application offered to them, they installed dedicated computers at the counter so they could access the application while they were assisting customers.

This web-GIS application remained a vital tool for Central San staff until 2015.  Along the way, we added new functionality using ColdFusion and JavaScript.  Over the years, we went through several upgrades of ColdFusion (Macromedia ColdFusion MX7, Adobe ColdFusion 9 and 10).

In 2015, we completed a 3-year effort to modernize our entire GIS ecosystem.  We replaced both the GIS editing environment and the web-GIS software with the Esri ArcGIS platform, and again redesigned the GIS database schema to incorporate an industry-standard data model for public agencies.  We also deployed Geocortex by Latitude Geographics, which consumes Esri ArcGIS map services and provides a robust web-based user interface to our GIS data.  Geocortex allowed us to rapidly deploy a new web-GIS application with even more functionality than our older web-GIS application.

Geocortex provides detailed GIS asset information in a panel on the left-hand side of the map interface.  However, the Geocortex tools for creating more detailed reports of GIS asset and related information (such as from departmental or enterprise databases) were not powerful enough to meet our needs.  Once again, we turned to ColdFusion to create custom report pages.

Since this presented another opportunity to start from a clean slate, we decided it was time to build the new reporting application using modern development principles.  The previous ColdFusion was written entirely in procedural style, with small pieces written using ColdFusion components (though not fully object-oriented-programming or OOP).  For this application, we chose to embrace OOP and use an MVC framework to make the application code easier to maintain and understand.  We chose Framework One (FW/1) because of its small footprint and because it was easy to learn quickly.  We also used the powerful open-source Bootstrap and Datatables JavaScript/CSS libraries and to provide a consistent look and feel to the application.  We also deployed a brand new dedicated web server running Adobe ColdFusion 11.

Within 6 weeks, we had the initial set of report screens completed.  We configured our Geocortex application to create hyperlinks in the asset information panel.  Those hyperlinks launch the relevant detailed ColdFusion report pages in separate browser tabs.

The Permit Counter Report is a good example of the complex data that we are able to handle with ColdFusion.  This report draws from several databases and communicates a tremendous amount of information related to a single land parcel we provide service to (data sources shown in parentheses):

1. Basic recording information about the parcel, including the County assessor’s identification number (Sungard ERP on AS/400),
2. Notes for intra-department coordination and information sharing (Sungard ERP on AS/400),
3. If the parcel is a commercial property that our Environmental Compliance group inspects and monitors, display a link to the ColdFusion-based Environmental Compliance report (custom Environmental Compliance database on Microsoft SQL Server),
4. If Central San has easement or access agreement documents on file for any infrastructure that crosses the parcel, display a link to the Laserfiche web application to view those documents (Laserfiche document system on Microsoft SQL Server),
5. If Central San has documents related to future development or re-development of the parcel, display a link to a ColdFusion-based Development report containing details and links to those documents in the Laserfiche web application (Laserfiche document system on Microsoft SQL Server),
6. Any historic sewer-related permits issued for the parcel that pre-date our ERP system, along with links to view scanned copies of those permits in the Laserfiche web application (Laserfiche document system on Microsoft SQL Server), and
7. A detailed listing of sewer-related application, permit, fee, and inspection information from our ERP system (Sungard ERP on AS/400).

The data from the Sungard ERP (for items 1, 2 and 7) above is pulled from 20 different tables.  Each of the different Laserfiche document types has a unique query to de-normalize the data for consumption.  All of this complexity is hidden from the user – they see just the information they need presented in a clear and logical fashion.  Permit Counter staff can quickly see the entire history of a parcel and quickly process new permit applications for our customers.

ColdFusion can be used to fill in may of the functionality gaps of commercial off-the-shelf (COTS) or open-source GIS web applications.  Since it runs on the Java Virtual Machine (JVM), ColdFusion applications can communicate with any database that has JDBC drivers available (even if those drivers aren’t included in the ColdFusion installation).  They can also easily consume other web-based Application Programming Interfaces (APIs) using protocols such as SOAP or REST.  In the GIS world, this means a ColdFusion application can access GIS data provided by ArcGIS Server/ArcGIS Online REST map services or any other GIS platform that provides web services.