A Trigger-Happy Solution

An interesting question came up the other day about managing data so that a feature is automatically re-symbolized when its condition changes.  This case involved manholes in an urban street system but the principle is be the same for any feature class with a status attribute.  Let's say the condition of these manholes can be classified as "planned," "new," "good," "poor," "damaged" or "repaired."  Their location on a map is symbolized by these values to illustrate the overall condition of the city's infrastructure.  Status can change, though.  How do you capture all of the change event details and also update the feature status?

Inspection and repair are the two events that can influence manhole status.  Each needs its own table in which the time, observations, actions taken and result of every event is recorded.  These are classic many-to-one relationships* that might occur in any database schema.  One manhole may have multiple inspections and may experience multiple repairs.  Both of those tables would therefore include the manhole ID as a foreign key.

In a less demanding environment, the current status of a manhole could then be retrieved by joining these tables on the manhole ID with a query that returned the condition of the most recent inspection or repair record.  This could be done with either an ad hoc SQL query or by defining a view in the database.  A database view is just a stored query.  It acts like a table but it actually provides a composite record created on-the-fly from a set of related tables.

Good enough for GIS?  Not really.  If your feature is being symbolized by status, then status needs to be an attribute in the feature table -- or the feature has to be rendered from the view described above.  GIS applications read from the database frequently.  They exercise a query for feature data whenever the map is being panned, zoomed or otherwise redrawn.  Using a view means that the status relationship between place (the one record of position) and time (the multiple records of inspection and possible repair) has to be re-evaluated each time the feature is requested.

A much better design would allow the database to perform that task just once.  This could occur whenever a new inspection or repair record is written by invoking a database trigger.  Triggers are special action that can be defined to occur whenever a record in a specific table is inserted, updated or deleted.  It can occur before or after that record is modified, or even instead of such a modification.  This is powerful stuff and the manhole example shows why:

Whenever an inspection occurs, a new record is written to the inspection table with all of the relevant details including the ID of the manhole in question and the result of the inspection.  A trigger on that table has access to this new information and it can automatically act upon the the feature table to update its status.  It's as simple as defining an instruction like:
UPDATE manhole SET condition = insert.condition WHERE fid = insert.fid"
(...the "insert" reference in this expression is the new record being written.) 

The same technique is used with the repair table.  When a new record is written there, its trigger can also act upon the feature table with:
UPDATE manhole SET condition = 'repaired' WHERE fid = insert.fid
The beauty of this arrangement is that the feature symbol will be immediately updated the next time it is drawn.  There is also no need to determine which inspection or repair record is most recent because the most recent record actually triggered the change.  So, there you have it: accuracy and performance all at once.

This is a nice example because it is a common situation and it illustrates the important role that databases play in GIS.  Good relational design and SQL skills can't be ignored.  They are an essential part of being a good GIS technician!

{% put styles %}

{% endput %}

Posted in Database, Techniques on Sep 27, 2019.