Correction, it looks like a database view would be more appropriate if I wanted a persistant query. http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-sql-server/views-in-geodatabase.htm
Assets with no inspections in GIS
I'm looking for the best way to determine which stormwater outfall structures have never had a sampling or inspection performed.
In ArcMap, the Lucity GIS toolbar provides a handy way to create a database view of the lucity samplings under Lucity Views> View Storm Sampling Results. Tool here: http://help.lucity.com/webhelp/latest/gis/#21980.htm
This performs an INNER JOIN between structures and the sample table, retrieving the most current inspection. This result returns only those features which have a successful join. Instead, I would like to either perform a FULL OUTER JOIN, or at least a LEFT OUTER JOIN to return all structures, not just those with an inspection, and see which do not have inspections (null values).
Default Lucity View - Storm Samplings:
"Creating view sql = CREATE VIEW LUCITY_USER.GIS_SMSSAMPL AS SELECT SMSSAMPL.*, SMVSTRUC.SN_ST_NO FROM (SMVSTRUC INNER JOIN SMSSAMPL ON SMSSAMPL.SS_SN_ID = SMVSTRUC.SN_ID) INNER JOIN (SELECT SS_SN_ID, MAX(SS_SAMP_DT) AS MAXDATE FROM SMSSAMPL GROUP BY SS_SN_ID) AS MHMAX ON MHMAX.SS_SN_ID = SMSSAMPL.SS_SN_ID AND SMSSAMPL.SS_SAMP_DT = MHMAX.MAXDATE"
I would like to stand up a simple web map to show which features still need to be inspected. A nightly routine could perform this query and recreate the feature but ideally I'd like to have it update on a more on-demand basis. As inspections are performed, users would see those structures change in appearance on the web map.
1) I can take this initial result, export it, and join it back to my full list of structures in GIS, however, it would be useful to skip this step.
2) I'm also considering making my own table view in our SDE database that mirrors this query but to my own specifications. http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/make-table-view.htm
3) Is there a better way?
Please sign in to leave a comment.
I was also going to recommend a database view rather than a table view if storing something in the geodatabase was your goal. However, another option to explore is to create a Query Layer in ArcMap with your specifications, and then publish that as a map service. At that point, there's no need for a nightly routine because it acts similarly to a view but it's stored in your mxd rather than in your database. Just a possibility. Good luck!
Within the Lucity Storm Structures module there is actually a 'Last Inspection' date field. This field is automatically updated when inspection records are created within our system. This is true for most of our inventory modules.
You can join that field from the Lucity database to your feature class and then symbolize it so that it will display a different color for structures that have an inspection date and for those that don't. This would also allow you to possibly graduate the symbology based on how long ago it was inspected.
An easy way to join in our lucity data would be to use the Lucity Links tool. This is found in ArcMap on the Feature Class properties.
Let me know if you have any further questions about that.
@Jonathan Semones, Thanks for the suggestion with Lucity Field links, this looks promising and I'll keep it mind. Unfortunately it looks like only 101 out of our 852 outfalls (Structures where Monitoring Site = 1) have a value for "last inspection date" (SN_INSP_DT). According to my stormwater workers, we use the Samplings table for MS4 outfall inspections, which I show as a dashboard query "SMSSAMPL LEFT JOIN SMVSTRUC ON SMSSAMPL.SS_SN_ID = SMVSTRUC.SN_ID." Should a Sampling on a Structure also update the "Last Inspection Date" field or is this a workflow issue within our organization? Thank you.