Integrate your business systems with ArcGIS using Spatial Views

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

TSQL Query

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.

Update Privileges

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.

This entry was posted in Local Government, Water Utilities and tagged , , , . Bookmark the permalink.

Leave a Reply

7 Comments

  1. bleroux says:

    Spatial Views are great but as a service the ability to query for summary statistics is not available. Very unfortunate for me.

  2. slavey7 says:

    Could the author clarify what is published as a service in this example (step 4)? I would expect to use the new view in my map service that shows hydrants, and would only need to publish/update that map service. I wouldn’t expect to publish the view as its own service, so just want to make sure.
    This is definitely a powerful method of exposing non-GIS data. Thanks for the post.

    • jmmoro00 says:

      Hi slavey7, Great question, I’ll try and explain with a little bit more detail…

      When a spatial view is created inside of the database and permissions are granted for access it can be treated just as any other feature class added to a map document / published in a map service. In your example, you would be able to add the hydrants, any / all of the water network elements that are important to the map service, in addition you can add the spatial view which includes other RDBMS based tabular business system data as well. All of the layers are included in the map service and participate in the functionality of the ArcGIS Server Service and are exposed via the REST and SOAP interfaces. A spatial view does not have to be published as an isolated service alone.

      Hope this clears it up, let me know if there are more questions!

      Thanks, Joe

  3. steve_li says:

    Can I achieve the same thing using Query Layer in ArcMap? What the pro/con using spatial view vs. Query Layer?

    • jmmoro00 says:

      Hi Steve,

      You are absolutely correct that the end result of a spatial view is very similar to a query layer in ArcMap.

      There are a couple of significant benefits to spatial views:
      - Spatial views persist in the database, users with access to the geodatabase using ArcGIS tools will have persistent access to the up to date information in the view. If the view is important to multiple users or users without the skills to build their own query layer inside of ArcGIS, the experience and interaction is much easier.

      -The permissions for spatial views are controlled via the standard GUI dialog from within ArcGIS. In contrast, permissions to access the non spatial tables in a query layer are most commonly provisioned outside of the ArcGIS Environment.

      - This is maybe a more personal perspective, but I like the syntax checking that SQL Server Management Studio , SQL Developer Studio, and SQL TOAD provide to catch my syntax errors and to help view and test the results of the view, particularly as they get more complex and include multiple tables and query statements. This certainly can be accomplished with query layers as well, my TSQL is usually just rusty enough that I appreciate the validation.

      There are benefits to query layers as well
      - Query layers are more flexible for an ArcGIS user who is creating them. If you want to change a query, alter an if statement, or modify the statement on the fly it is much easier. A spatial view, created at the RDBMS level, is a more permanent view of the data and persists. The query layers are referenced inside of the map document (and inside the map services if the map document is published as a ArcGIS Server service). Both methods can accomplish the same thing, how the products is being used really drive which will serve you best.

      - One other real benefit of the query layer is access and permissions. It’s pretty common for GIS to lack access to the RDBMS management tools needed to create spatial views. As long as a user has access to the needed data, within ArcMap a user can create the same view and experience quickly if they lack to access to the database.

      This isn’t an exhaustive list, if additional things come to mind I’ll be sure to post and if you have further questions please let us know!

      Thanks, Joe

  4. jhodge says:

    How are multiple records handled? usually a ‘join’ indicates a one-to-one relationship. What about where there are multiple work orders to a hydrant or manhole, how is this handled? Is there one feature and many related records or multiple features created?

    • jmmoro00 says:

      This is a great question as well! A spatial join does in fact support many to one / multiple records. The end result is a little different than the results of a many to one relationship class however. All records will be shown, one geometry per record in the tabular data table. The geometries will be stacked on top of each other however and unless different symbology or representations are used. With a little planning however it is still a great way to expose the data and bring databases to life on a map.

      There are a couple of considerations for implementing the above situation however…

      1) When scripting the spatial view the “From” statement needs to be created in a specific order.
      FROM JOIN

      Example:
      FROM workorders.dbo.WorkOrders (Many potential work orders) AS W JOIN dbo.Hydrants (One single fire hydrant) AS H
      ON W.WorkOrderAssetNumber = H.HydrantAssetNumber

      2) There needs to be a unique identifier for each record within the view as well. The ObjectID of the geometries will not work in the case as it would in a 1:1 join scenario. When a spatial view is added to the ArcGIS Desktop, the user is prompted to identify the unique identifier, the Primary Key / unique identifier from the table containing the “many” records needs to be identified. If a unique identifier is specified from the “one” table then only a single geometry / value from the many table will be shown in the map.

      Hope this helps, great questions!!!

      Thanks and keep them coming! – Joe

      Joe Morocco
      Solutions Engineer
      ESRI Water Practice