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.
