This presentation will discuss how we have leveraged the capabilities of SQL database views to create streamlined, dynamic reporting workflows for enterprise geodatabases. Oftentimes, as the technical needs of a project evolves, the geodatabase schema created at the outset may not be suited to best report on and track the data via dashboards or other web applications. SQL database views offer a method through which GIS professionals can transform, join, or aggregate datasets dynamically to fit their needs without overhauling their geodatabase model or affecting the underlying datasets. In addition, SQL database views can offer an alternative to other automatic field calculation methodologies such as attribute rules or scripts. In this presentation, we will dive into three different real-world project examples, ranging in complexity from beginner to more advanced, to showcase how database views allowed us create maps, apps, and dashboards best suited for our clients’ needs. The first use case will show how database views can be used symbolize stormwater assets based on their related inspection and maintenance data as well as create dynamic, calculated fields to track asset inspection statuses. The next will demonstrate how views can aggregate multiple different datasets to optimize stormwater illicit discharge inspection reporting efficiency and create more streamlined user interfaces for web applications. The third example will delve into the process of using database views to temporally manipulate stormwater best management practice inspection data, granting the end user the ability to examine overall inspection statistics from not only the current year, but also any previous year over the inspection program’s duration. To wrap up the presentation, we will give a high-level overview of how to get started with database views, including which tools and software are required, to empower attendees to implement database views for their own project needs.
Chapter III, Section 4 (g) of the Niagara County Sanitary code (Sanitary Code of the Niagara County Health District, 2022) charges the Niagara County Health Department with informing property owners who utilize a septic system that there is a requirement to connect to a sanitary sewer if one is available at the time of a property sale (or transfer). If sanitary sewer is not available at the time of sale, then the county health department must perform a test (tracer dye testing) of the existing septic system to ensure that the system is in good working order. Also, at times when an aging septic system is replaced, county public health engineers may be called upon to inspect and approve a new septic construction. Historically, GIS has played no role in this process.
The Niagara County Department of Public health has embarked on an ambitious multi-year GIS project; the goals include:
--> To map existing sanitary sewer pipeline GIS data (covering the entirety of the county’s 12 towns and three cities).
--> Append to the GIS data the layout and components of newly installed septic systems (using a high-accuracy GNSS receiver with RTK augmentation)
--> Provide a document management tool via a web map application/interface that will easily link the user to the project file of each septic system inspection or new installation.
The purpose of this session is to share our experiences thus far in in this process. We hope that attendees of this session will share their own in-house processes to fulfil county health code requirements. We aim to use GIS to improve access to, as well as the accuracy of our project records. While the process will take time, our end deliverable of this project will be a modernized record management tool and high precision mapping of the county’s approximately 18,000+ septic systems.
