In addition to the authoritative asset information stored in the enterprise GIS, Water Utilities have a wealth of information stored in relational databases (RDBMS) like Oracle or Microsoft SQL Server. This data includes information such as billing and customer information, asset and work information from a maintenance management system, and other types business system data that have a relationship with assets stored in an enterprise geodatabase.
Did you know that you could use spatial views to provide secure access to these often critical and dynamic databases through all of your GIS clients? A spatial view is essentially a query of multiple tables that is stored in the database (instead of in a map which is how joins are stored) that is presented to users as a single database object like a table or feature class.
- Spatial views can represent a subset of data because they are based on a query; they are a great way to filter a large table to map only the important items.
- Spatial views can join and aggregate multiple non-spatial and spatial tables, providing a single point of secure access.
- Spatial views are a great way to hide the complexity of the underlying databases.
- Spatial views are read only.
- Spatial views are dynamic; when data or geometry is updated in any of the parent tables the information is instantly available to the GIS clients. They reduce the need to process and manage copies of data.
- Spatial views are built in the native TSQL development tools for your database, such as SQL Toad or SQL Developer for Oracle and Microsoft SQL Management Studio for Microsoft SQL Server.
The following example describes how to create a spatial view using hydrant work order data.
Step 1 - Create Privileges
As a user with “create” privileges within the database, navigate to the database / geodatabase where you want to the new spatial view to reside.
Step 2 - Create TSQL Query
Create a new TSQL Query. The syntax for creating a new spatial view is as follows:
Create View <view name> AS
Select <Attributes to be included in the view, separated by a comma>
From <database #1> Join <database #2>
On <Join Field #1> = <Join Field #2>
For the hydrant example, the syntax is:
Create View dbo.WHydrant_Work_Order AS
Select W.WorkOrder, W.FacilityID, W.Catergory, W.DateCreated, W.Priority, W.AssignedTo, W.DueDate, W.Source, W.Task, W.Comments, W.Status, H.MANUFACTURER, H.FLOW, H.Shape
From Workorders.dbo.HydrantsWorkOrders W Join geodata.dbo.WHYDRANT H
On W.FacilityID = H.FACILITYID
Execute the SQL query to create the view.
Step 3 - Update Database Privileges
Update the privileges so desktop users can access the Spatial View or add the data to a map and share it as a map service.
Step 4 - Publish the Service
Using ArcGIS Desktop, publish the service. After publishing the service share it with your organization. Add the spatial view to a web map in ArcGIS Online and save it in the Basic Viewer Template to see how it works. As changes are made to the geometry or tabular data they will be instantly viewable within the client applications.
Think about all the other uses for spatial views at a Water Utility
In addition to the work order history example described in this post there are many other spatial view use cases. These include viewing the statuses of valves or hydrants that are managed in other enterprise systems, access to customer information or shut off lists stored in a customer information system, or providing secure access to information regarding gate security codes. Spatial views allow you to use maps to provide access to virtually any data related to location.