SQL for Branch Versioned LRS Data

[AyanPalit]

Data leads and DBA's rely on SQL to query data. Data analysts unversed with LRS and/or versioning may struggle with query methods. 

Note that there are 2 technical concepts in play:

• Branch Versioning (more fundamental geodatabase concept)
• ArcGIS Roads and Highways
  • Time Awareness: FROMDATE/ TODATE fields (R&H specific and applies to Routes and Events only)
  • Location Error: LOCATIONERROR field (R&H specific and applies to Events only)

 The design considerations are discussed in the blog SQL Views and Branch Versioning

The following SQL code logic may be used to build queries off branch-versioned LRS datasets:

SELECT Name, Type                       -- Attributes
FROM GIS.ROAD_XX t                      -- Table
WHERE t.gdb_is_delete = 0               -- Start branch versioning logic
AND t.gdb_branch_id = 0
AND t.gdb_archive_oid = ( SELECT MAX(gdb_archive_oid)
                          FROM  <table> bt
                          WHERE bt.globalID = t.globalID
                          AND   bt.gdb_branch_id = 0 )	
                                        -- End branch versioning logic
-- Optional LRS filtering
AND TODATE IS NULL			-- Current LRS records   
AND LOCATIONERROR = ‘NO ERROR’	        -- Event records with no LRS errors

[GraemeBrowning_Aurizon]

I'm far from fluent in SQL but today I saw the importance of the code you provided above. 

I went looking for it because I noticed that my LRS network feature class had 3,541 features when I opened it in ArcGIS Pro and it also had gaps in the OBJECTID sequence.  When I looked at the same table in SSMS it had 3,560 rows and some of the OBJECTID values were duplicated.

By applying your code in SSMS, and also a definition query of ToDate IS NULL in ArcGIS Pro, a sanity restoring 3,523 current features/rows were seen in both places.

[AyanPalit]

@GraemeBrowning_Aurizon  Glad you were able to put this SQL snippet to good use!