Latest Contributions by KyleGonterwitz

Good idea.  To do this you will want to track edit changes and ensure your portal group security is working with the change tracking, and yes protect your default and manage LRS Locks in event editor with a lockroot subversion from default and conflict prevention enabled in the ALRS Settings.  I would configure event editor applicatons, secured and delivered to members assigned to portal groups, and customize the cartographic content to facilitate the workflow to the maximum extent possible.  Also configure the event editor with full ability of the editor to create a version, post and reconcile, manage locks, and delete a version all with event editor buttons.  What I do is publish the services with a shared user that is only used for publishing services, not a data owner user or an active directory user but a named user in the database with privileges set for editing event data in versions.  I think in a multi-user environment you would definitely want to have at least a version per user, sometimes more depending on the situation and the number of EE configurations you set up.  It is possible to configure event editor using portal maps that can combine read-only services with the ALRS service, but snapping in the Event Editor might not work on those non-alrs services.  You can also probably add read-only connections when publishing the service to enable the reference layers to support snapping, maybe.   For our installation it was difficult to get the federated portal all set exactly how it had to be for ADFS users and conflict prevention to work but now that we have that figured out I wouldn't want to do it any other way, and it appears this approach is going to work well to achieve your goal, and mine too, of supporting program areas in such a way that they can utilize the event editor and spread the workload horizontally into the agency. ... View more

not quite like that... are you using append events?   ... View more

Shawn recently developed a geocoder based on the route concurrency table, so that concurrency route segments can be geocoded as intersections. We are also ironing out the process of maintianing GIS refposts in a hosted feature dataset (facilitating easy Collector/Quick Capture app access) and locating those features to the refpost control state LRM routes as events for geocoding and offsetting.   here are the ingredients we use ON_ROAD/Route  AT_ROAD/Milepost  OFFSET  DIRECTION here is the formula: 1. Geocode ON/AT roads as intersections using esri geocoder 2.   Obtain Projected XY Coordinate 3.  Buffer the point in by offset distance 4. Intersect the buffer outline to the road centerline segments as points 5.  In SQL, query the matching ON road (standardized)  to the centerline segment road name.  CASE Select coordinate directions from DIRECTION (ie N, E, S, W, NE etc) and RANK the offset coordinates so that you are selecting the most "east" of the intersected points, or keep the intersection if it is the most east. I'll try to find some actual SQL that does the offsetting.  With this method we can locate a few hundred thousand crash records in about a workday. ... View more

You end up with a lot of potential options.  To update everything you could end date everything and start sate  new records  Later than the end date of the last thing.  Some of the Esri feature service and app capabilities let you play data through time using this sort of method, so you can see changes in conditions or measurements over time.  It gets complex when you compare multiple "related" historical things, were they always related?  What if somebody finds a record from the past that should have been a different value?  You can deal with all this but you have to organize virtual history, real history, and reconcile why things changed over time, and how actual changes factor in to predictive analytics vs virtual changes.   An example we deal with a lot is crashes at an intersection, then the intersection is improved, then the factors contributing to crashes can really change significantly because of real changes.  Say we change the name of the highway at the intersection, that doesnt change the actual history, just the relationship of the history to what the name was at the time vs now.     ... View more

start date and end date fields.  For collection and data entry we might interact with those date fields.  For mapping use we might publish a layer with a current date query, most data reviewers want a simple current status.  Playing with date fields let's you know more but adds a lot of complexity.    Also we use a more static ID than the object ID field if possible. ... View more

I just discovered the scale bar does not have an easy option for displaying Nautical Miles like it does for feet, miles, kilometers, etc.  Very disappointing! ... View more

also commented out here is a half baked process to derive a statewide state highway network from the county network.  I think we are working on altering the approach we are taking at that, but the process can be added with just a few additional steps in this process if those steps are done in just the right sequence.  I think we currently are ironing out our expectations and requirements for the data of this derivation process to serve interfaces, and we are thinking of adding an event to maintain the state network history as derived from the county network.   ... View more

I wanted to share an update to our scripted process, now we have included a point event for flagging opposite directions of inventory primary routes on dual carriagway.  By putting the event flag on the real primary noninventory route side, we then adjust the route concurrency table on the segment we flag, overriding the default concurrency rules that would have the inventory direction in both directions across the right of way for two different routes.  An example of this in KAnsas is where US54 intersects US69 and they run opposite on dual carrigway north of Fort Scott Kansas.  For us, US54 is the primary inventory route, so we flag US54 non inventory and alter US69 inventory to turn into US54 noninventory. rather than paste all that code again, here is a link to the code on github: pydev106/GeodbMaintenanceQC.py at master · KDOTGIS/pydev106 · GitHub  another important step I want to point out in our proess is this other script to copy metadata updates beween dev, test, prod, transactional, and publiction environments, pydev106/CopyMetadata.py at master · KDOTGIS/pydev106 · GitHub  ... View more

1.  we see undesired segmentation in the primary routes process when this tool fails.  Amit describes it perfectly in his post.  In our system, we encounter his Example #2 - we can find it when we run route concurrency and inspect that table for overlaps.  For events problems, I suppose you could have trouble ensuring that events are recorded to the primary route. 2. if routes are appended to overwrite existing routes and the geometries are slightly different and not overlapping within tolerance, unexpected results can occur.  It might be more time consuming to clean that up than to take time to do extensive hands-on edits. 3.  I think it involves zooming in to XY tolerance levels and making minor edits to achieve precise geometry overlap. 4.  no time is really any safer than another time.  If you are running it for the first time then a good time is when you have some time to edit routes, troubleshoot, and somehow fix the situations. 5.  the thing we check most often is if the segment route length equals the segment shape length.  We have Data Reviewer so we can run non-monotonic checks I think? Kevin, I will chat with a couple of my editors today and try to clarify some of these responses. ... View more

Interesection gecoding was another beast that we are working hard to tame in Kansas.  Shawn Saving at KU DASC has been working with Esri to resolve many types of intersection geocoding issues and there have been many updates to the geocoding in new releases of pro.  There are unique challenges that the LRS is well suited to handle with geocoding, such as tie duplicate handling, east/west junctions there are many parths forward I am wondering which to take.  We are to the point with segmentation that we are talking about refactoring our approach and leveraging the LRS more for geocoding.  I suppose the approach in a nutshell is management of segments for traditional geocoding and management of intersection points and zones for intersection geocoding. ... View more

now that there are pro environments with geoprocessing tools I've been playing with fire and cross versioning databases and tools to figure things out in pro.  Esri support has let me know the back end architectures are different and there's a good chance some things wont work or be supported doing that, but you could try to use that pro tool on a 10.7.1 database or earlier, it might work.   ... View more

Where it is - it is at the hands of the route editors to run that tool after doing route edits in the version workflow.  There is some aftermath to using it, you just have to absorb that aftermath or clean it up.  The best we can do is to have that run on the lockroot version and post it to default by the route editor after performing the route edits.  We would all much prefer and look forward to a geoprocessing tool. ... View more

This post has been a big benefit to Kansas, we have taken the script and concepts discussed here and incorporated this into a script we run nightly to update a publication database from our transactional editing database.  The entire workflow begins with post reconcile and compress from our transactional envionment, and leverages lists in python of events and network feature classes and tables to act on. I will copy the python here, including the CreatePrimaryNetwork function.  There is one part I am working on that should serve to be able to derive a network, a statewide LRM based on a county LRM shape length, around line 460.  We run this on a server using a task scheduler, its good to test that using PowerShell to make sure Windows will run it, which means it has to be tabbed with spaces instead of actual tabs. WE output primary routes in single part feature as well to facilitate awareness of gapped routes output with this method.      ''' Created on Feb 20, 2019 @author: kyleg ''' ''' KHUB geodatabase maintenance Post Reconcile Analyze and Compress Created on Feb 12, 2019 @author: kyleg ''' #project publication database into webmerc webmerc="""PROJCS[ 'WGS_1984_Web_Mercator_Auxiliary_Sphere', GEOGCS['GCS_WGS_1984', DATUM['D_WGS_1984', SPHEROID['WGS_1984',6378137.0,298.257223563]], PRIMEM['Greenwich',0.0], UNIT['Degree',0.0174532925199433]], PROJECTION['Mercator_Auxiliary_Sphere'], PARAMETER['False_Easting',0.0], PARAMETER['False_Northing',0.0], PARAMETER['Central_Meridian',0.0], PARAMETER['Standard_Parallel_1',0.0], PARAMETER['Auxiliary_Sphere_Type',0.0], UNIT['Meter',1.0]]""" transform_method=r"WGS_1984_(ITRF00)_To_NAD_1983" in_coor_system="""PROJCS[ 'NAD_83_Kansas_Lambert_Conformal_Conic_Feet', GEOGCS['GCS_North_American_1983', DATUM['D_North_American_1983', SPHEROID['GRS_1980',6378137.0,298.257222101]], PRIMEM['Greenwich',0.0], UNIT['Degree',0.0174532925199433]], PROJECTION['Lambert_Conformal_Conic'], PARAMETER['false_easting',1312333.333333333], PARAMETER['false_northing',0.0], PARAMETER['central_meridian',-98.25], PARAMETER['standard_parallel_1',37.5], PARAMETER['standard_parallel_2',39.5], PARAMETER['scale_factor',1.0], PARAMETER['latitude_of_origin',36.0], UNIT['Foot_US',0.3048006096012192]]""" def Analyze(): rh = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' sde = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' from arcpy import AnalyzeDatasets_management print("this function analyzes datasets for "+rh + " and " + sde) print("analyzing RH and SDE in source") AnalyzeDatasets_management(rh, "NO_SYSTEM", "RH.Calibration_Point;RH.Centerline;RH.Centerline_Sequence;RH.Lrs_Edit_Log;RH.Lrs_Event_Behavior;RH.Lrs_Locks;RH.Lrs_Metadata;RH.Redline;", "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE") AnalyzeDatasets_management(sde, "SYSTEM", "", "NO_ANALYZE_BASE", "NO_ANALYZE_DELTA", "NO_ANALYZE_ARCHIVE") def Compress(): sde = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' from arcpy import Compress_management print("compressing "+ sde) Compress_management(sde) def PostAndReconcile(): sde = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' from arcpy import ReconcileVersions_management ReconcileVersions_management(sde, "ALL_VERSIONS", "sde.DEFAULT", "QCADMIN.QC_Lockroot", "LOCK_ACQUIRED", "ABORT_CONFLICTS", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "") print("lockroot reconciled to default without conflicts:") def FullPostAndReconcileDefault(): sde = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' from arcpy import ReconcileVersions_management, Compress_management, DisconnectUser, AcceptConnections print("post and reconcile version with version deletion for " +sde) AcceptConnections(sde, False) DisconnectUser(sde, "ALL") #verListSource = [ver for ver in ListVersions(SDESource) if ver.lower() != 'sde.default' and ver.lower() != 'rh.masterqc'] print("reconciling MasterQC to Default") ReconcileVersions_management(sde, "ALL_VERSIONS", "sde.default", "QCADMIN.QC_Lockroot", "LOCK_ACQUIRED", "ABORT_CONFLICTS", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION") Compress_management(sde) AcceptConnections(sde, True) def FullPostAndReconcileSourcetoDefaultCollapseVersions(): sde = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' from arcpy import ReconcileVersions_management, Compress_management, DisconnectUser, AcceptConnections, DeleteVersion_management print("post and reconcile version with version deletion for " +sde) AcceptConnections(sde, False) DisconnectUser(sde, "ALL") #verListSource = [ver for ver in ListVersions(SDESource) if ver.lower() != 'sde.default' and ver.lower() != 'rh.masterqc'] for SourceVersion in rh: print(SourceVersion) ReconcileVersions_management(sde, "ALL_VERSIONS", "QCADMIN.QC_Lockroot", "RH."+SourceVersion, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION") try: DeleteVersion_management(sde, SourceVersion) except: print(SourceVersion+ " deleted after reconcile/post") print("reconciling MasterQC to Default") ReconcileVersions_management(sde, "ALL_VERSIONS", "sde.default", "RH.MasterQC", "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION") try: DeleteVersion_management(sde, "RH.MasterQC") except: print("MasterQC deleted after reconcile/post - OR - if this is the only message, then nothing was done because versions do not exist") Compress_management(sde) AcceptConnections(sde, True) def RebuildIndexes(): print('not set up yet') def PublicateNetworkProd2Target(target): from arcpy import env from datetime import datetime from KhubFCList import NetworksList, EventList, IntersectionList, TestEvents from arcpy import (da, FeatureClassToFeatureClass_conversion, ChangePrivileges_management, AddMessage, MakeFeatureLayer_management, AddWarning, Project_management, TruncateTable_management, Append_management, ExecuteError, CreateTable_management, AddField_management, Exists, GetMessages, DisconnectUser, Exists, CreateFileGDB_management, Delete_management) pub_event_list = EventList #TestEvents/EventList - use test events for smaller events lists or testing pub_network_list = NetworksList env.overwriteOutput = True #Transactional Database connection to default to copy to publication RH_Database = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' if target == 'Prod': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' elif target == 'Test': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' else: pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' pub_sdedb = 'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' wsruncode = datetime.now() dbname = "Publish"+str(target)+str(wsruncode.year).zfill(4)+str(wsruncode.month).zfill(2)+str(wsruncode.day).zfill(2) ws= env.workspace=r"C:\\temp\\"+dbname+".gdb" if Exists(ws): try: Delete_management(ws) except: raise LicenseError CreateFileGDB_management(r"C:/temp", dbname, "CURRENT") else: CreateFileGDB_management(r"C:/temp", dbname, "CURRENT") print (pub_database) pub_database_path = pub_database env.workspace = pub_database status_table = (pub_database+r"/KHUBPub.GEO.Publication_Status") if not Exists(status_table): CreateTable_management(pub_database, "Publication_Status") AddField_management(status_table, "DATE", "DATE", "", "", "", "Date") AddField_management(status_table, "LAYER_NAME", "TEXT", "", "", "", "Layer Name") AddField_management(status_table, "STATUS", "TEXT", "", "", "", "Status") AddField_management(status_table, "MESSAGE", "TEXT", "", "", "", "Message") status_fields = ["DATE", "LAYER_NAME", "STATUS", "MESSAGE"] def update_status_table(status_table, data, fields, status_value, message_value): now = datetime.now() with da.InsertCursor(status_table, fields) as i_cur: #@UnresolvedImport insert_values = (now, data, status_value, message_value) i_cur.insertRow(insert_values) del i_cur i = 1 for event in pub_event_list: #for testing add the [2] to enumerate the list of events try: event_name = event.split(".")[-1] print(RH_Database+r'/KHUB.RH.'+ event +" " +event_name) DisconnectUser(pub_sdedb, "ALL") FeatureClassToFeatureClass_conversion(RH_Database+r'/KHUB.RH.'+ event, ws, str(event_name)+"lyr", where_clause="""(LRSFromDate is null or LRSFromDate<=CURRENT_TIMESTAMP) and (LRSToDate is null or LRSToDate>CURRENT_TIMESTAMP)""") Project_management((ws+r"/"+str(event_name)+"lyr"), (pub_database_path+r'/KHUBPub.GEO.'+event_name), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") ChangePrivileges_management((pub_database_path+r'/KHUBPub.GEO.'+event_name), "readonly", View="GRANT", Edit="") AddMessage('{0} {1}: {2} {3} {4}'.format(event_name, "copied over publication database", i, "of", len(EventList))) update_status_table(status_table, event_name, status_fields, "Overwrite in Publication Database", None) i += 1 except ExecuteError: AddWarning('{0} {1}: {2} {3} {4}'.format(event_name, "produced an error", i, "of", len(EventList))) message = GetMessages(2) update_status_table(status_table, event_name, status_fields, "Error Occurred", message) i += 1 del event continue for network in NetworksList: try: network_name = network.split(".")[-1] DisconnectUser(pub_sdedb, "ALL") FeatureClassToFeatureClass_conversion(RH_Database+r'/KHUB.RH.'+ network, ws, str(network_name), """(LRSFromDate is null or LRSFromDate<=CURRENT_TIMESTAMP) and (LRSToDate is null or LRSToDate>CURRENT_TIMESTAMP)""") Project_management(ws+r"/"+str(network_name), (pub_database_path+r'/KHUBPub.GEO.'+network_name), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") ChangePrivileges_management((pub_database_path+r'/KHUBPub.GEO.'+network_name), "readonly", View="GRANT", Edit="") AddMessage('{0} {1}'.format(network_name, "overwrote in publication database")) update_status_table(status_table, network_name, status_fields, "Overwrite in Publication Database", None) except ExecuteError: AddWarning('{0} {1}'.format(network_name, "produced an error")) message = GetMessages(2) update_status_table(status_table, network_name, status_fields, "Error Occurred", message) del network continue for intersec in IntersectionList: #intersections have different lrs date fields than the rest of the Roads and Highways LRS, so get them all to pub (for now) try: intersec_name = intersec.split(".")[-1] DisconnectUser(pub_sdedb, "ALL") Project_management(RH_Database+r'/KHUB.RH.'+ intersec, (pub_database_path+r'/KHUBPub.GEO.'+intersec_name), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") AddMessage('{0} {1}'.format(intersec_name, "overwrote in publication database")) update_status_table(status_table, intersec_name, status_fields, "Overwrite in Publication Database", None) except ExecuteError: AddWarning('{0} {1}'.format(intersec_name, "produced an error")) message = GetMessages(2) update_status_table(status_table, intersec_name, status_fields, "Error Occurred", message) continue def RecreatePubGDB(target): from arcpy import CreateEnterpriseGeodatabase_management, CreateDatabaseConnection_management, CreateDatabaseUser_management, DisconnectUser DBName = "KhubPub" if target == 'Prod': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' pubinstance = r"khdbpubprod\khubpub_prod" elif target == 'Test': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' pubinstance = r"khdbpubtest\khubpub_test" else: pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' pubinstance = r"khdbpubdev\khubpub_dev" sapass = raw_input('sysadmin password') sdepass = raw_input('sde password') geopass = raw_input('Geo password') CreateEnterpriseGeodatabase_management("SQL_Server", pubinstance, DBName, "DATABASE_AUTH", "geo_admin", sapass, "SDE_SCHEMA", "sde", sdepass, "", r"C:\Users\planadm\GISDATA\keycodes\License10.6\sysgen\keycodes") CreateDatabaseUser_management(pub_database, "DATABASE_USER", "geo", geopass) CreateDatabaseUser_management(pub_database, "DATABASE_USER", "readonly", "readonly") #geo and readonly users are granted with appropriate privileges in the instance model database, which should therefore be passed to the geodatabse privilege when added to the geodatabase def CreatePrimaryNetwork(target): # Create_Dominant_Routes.py (ArcMap Only) # # Description: Create concurrency table (WITH STATE SYS INV DIR ROUTES ONLY) # and copies Prefixes 1,2,3 Routes to the local geodatabase C:\\\\temp\\CONCURRENT.gdb # https://community.esri.com/thread/227849-export-only-dominant-routes-from-lrs # utilizes workspace C:\\\\temp\\CONCURRENT+YYYYMMDD.gdb - attempts to delete if not existing # --------------------------------------------------------------------------- from datetime import datetime if target == 'Prod': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' pubinstance = r"khdbpubprod\khubpub_prod" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds elif target == 'Test': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' pubinstance = r"khdbpubtest\khubpub_test" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds else: pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' pubinstance = r"khdbpubdev\khubpub_dev" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds target = 'Dev' from arcpy import (env, da, CreateFileGDB_management, AddMessage, AddWarning, Exists, MakeRouteEventLayer_lr, DisconnectUser, CheckExtension, CheckOutExtension, CreateRoutes_lr, ChangePrivileges_management, CopyFeatures_management, Delete_management, MultipartToSinglepart_management, Copy_management, Merge_management, MakeTableView_management, Erase_analysis, ExecuteError, GetMessages, LocateFeaturesAlongRoutes_lr, CheckInExtension, MakeFeatureLayer_management, FeatureClassToFeatureClass_conversion, CreateTable_management, Append_management, AddField_management, CalculateField_management, TableToTable_conversion, Project_management, Dissolve_management, Statistics_analysis, AddJoin_management, RemoveJoin_management, AddIndex_management) from datetime import datetime wsruncode = datetime.now() dbname = "CONCURRENT"+str(target)+str(wsruncode.year).zfill(4)+str(wsruncode.month).zfill(2)+str(wsruncode.day).zfill(2) ws= env.workspace=r"C:\\temp\\"+dbname+".gdb" if Exists(ws): try: Delete_management(ws) except: raise LicenseError CreateFileGDB_management(r"C:/temp", dbname, "CURRENT") else: CreateFileGDB_management(r"C:/temp", dbname, "CURRENT") env.MResolution = 0.000000018939394 env.MTolerance = 0.000000621369949 #arcpy.env.XYTolerance="0.003280833333333 US Survey Foot" #arcpy.env.XYResolution="0.0001 US Survey Foot" db_connection=RH_Database env.overwriteOutput= True; try: if CheckExtension ("Highways")== "Available": #Check out the Network Analyst extension CheckOutExtension("Highways") else: raise LicenseError except LicenseError: print ("Roads & Highways Extension not available") sys.exit() LRSCounty = db_connection+"\\KHUB.RH.LRS_County" KHUBRoutes = "KHUBRoutes" KHUBRoutesLyr="KHUBRoutesLyr" from locref import CalculateRouteConcurrencies whereClauseAll= "Prefix IN ('1','2','3','4','5')" MakeFeatureLayer_management (LRSCounty,KHUBRoutesLyr,whereClauseAll) CalculateRouteConcurrencies(KHUBRoutesLyr, "temp_table", "", "FIND_DOMINANCE") if Exists(ws + "\\concurr_tbl_all"): # Delete it if it does exist Delete_management(ws + "\\concurr_tbl_all") CreateTable_management(ws, "concurr_tbl_all", "temp_table") Append_management("temp_table","concurr_tbl_all") AddField_management("concurr_tbl_all", "Prefix", "TEXT", "", "", "1", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management("concurr_tbl_all", "Prefix", "Mid( [RouteId],4,1 )", "VB", "") print ("concurrent table created") DisconnectUser(pub_sdedb, "ALL") if Exists(pub_database+r'/KHUBPub.GEO.RouteConcurrency'): #make a copy to update in TDSOutput Route Concurrency in Tdsuser schema Delete_management(pub_database+r'/KHUBPub.GEO.RouteConcurrency') else: pass TableToTable_conversion("concurr_tbl_all", pub_database, "RouteConcurrency", "ToDate IS NULL") #make a copy to update in TDSOutput Route Concurrency in Tdsuser schema ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.RouteConcurrency'), "readonly", View="GRANT", Edit="") FeatureClassToFeatureClass_conversion(KHUBRoutesLyr, ws, KHUBRoutes, "LRSToDate IS NULL AND Prefix IN ( '1', '2', '3','4','5') ") CheckInExtension("Highways") print("copy fc completed") # Local variables: TargetRoutesPrefix1235 = ws+"\\KHUBRoutes" concurr_tbl = ws+"\\concurr_tbl_all" DominantFlag1Feat = ws+"\\DominantFlag1Feat" TargetRoutes_Erase = ws+"\\TargetRoutes_Erase" TargetRoutes_Erase_LocateFea = ws+"\\TargetRoutes_Erase_LocateFea" TargetRoutes_Erase_withMP = ws+"\\TargetRoutes_Erase_withMP" SegmentsToCreateRoutes = ws+"\\SegmentsToCreateRoutes" mergeset = ws+"\\mergeset" mergesetCopy = ws+"\\mergesetCopy" #DominantRoutesCreated = ws+"\\DominantRoutesCreated" DominantRoutesAll = ws+"\\DominantRoutesAll" DominantRtesSinglePart=ws+"\\DominantRtesSinglePart" concurr_tbl_View = "concurr_tbl_View" concurr_tbl_View_Events = "concurr_tbl_View Events" Output_Event_Table_Properties = "RID LINE FMEAS TMEAS" TargetRoutes_Erase_LocateFea_Events = "TargetRoutes_Erase_LocateFea_Events" mergesetCopy_Layer = "mergesetCopy_Layer" ##############################create Dominant route segments########################################################### # Process: Make Table View to select subset with query to include only LRS_end_date is null, dominant flag=1 MakeTableView_management(concurr_tbl, concurr_tbl_View, "FromMeasure IS NOT NULL AND ToMeasure IS NOT NULL AND ToDate IS NULL AND DominantFlag = 1 AND RouteId NOT LIKE 'KTA*' AND Prefix IN ('1','2','3','4','5')") MakeRouteEventLayer_lr(TargetRoutesPrefix1235, "RouteId", concurr_tbl_View, "RouteId LINE FromMeasure ToMeasure", concurr_tbl_View_Events, "", "ERROR_FIELD", "NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT") CopyFeatures_management(concurr_tbl_View_Events, DominantFlag1Feat, "", "0", "0", "0") ###############Erase analysis to get route segments that do not carry any route########################################## # Process: Erase, input is all routes, erase feature is dominant routes####################################### Erase_analysis(TargetRoutesPrefix1235, DominantFlag1Feat, TargetRoutes_Erase, "") # Process: The noncarrying route segments are run thr LFAR-Locate Features Along Routes to get LRS milepoints, output is a table LocateFeaturesAlongRoutes_lr(TargetRoutes_Erase, TargetRoutesPrefix1235, "RouteId", "0 Feet", TargetRoutes_Erase_LocateFea, Output_Event_Table_Properties, "FIRST", "DISTANCE", "ZERO", "FIELDS", "M_DIRECTON") MakeRouteEventLayer_lr(TargetRoutesPrefix1235, "RouteId", TargetRoutes_Erase_LocateFea, "rid LINE fmeas tmeas", TargetRoutes_Erase_LocateFea_Events, "", "ERROR_FIELD", "NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT") CopyFeatures_management(TargetRoutes_Erase_LocateFea_Events, TargetRoutes_Erase_withMP, "", "0", "0", "0") print("completed erase fc, dominant fc") Copy_management(TargetRoutes_Erase_withMP, SegmentsToCreateRoutes, "FeatureClass") # Process: Add Field ToMeasure. LFAR GIVES IT AS TMEAS AddField_management(SegmentsToCreateRoutes, "ToMeasure", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management(SegmentsToCreateRoutes, "ToMeasure", "[TMEAS]", "VB", "") AddField_management(SegmentsToCreateRoutes, "FromMeasure", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management(SegmentsToCreateRoutes, "FromMeasure", "[FMEAS]", "VB", "") # Process: Merge output of erase, and the dominant rte seg, COMMON FIELDS WILL BE RouteId, FromMeasure,ToMeasure Merge_management([SegmentsToCreateRoutes,DominantFlag1Feat], mergeset) print ("merge completed") CopyFeatures_management(mergeset, mergesetCopy, "", "0", "0", "0") MakeFeatureLayer_management(mergesetCopy, mergesetCopy_Layer, "\"Shape_Length\" > 0") CreateRoutes_lr(mergesetCopy_Layer, "RouteId", DominantRoutesAll, "TWO_FIELDS", "FromMeasure", "ToMeasure", "UPPER_LEFT", "1", "0", "IGNORE", "INDEX") print ("routes creation completed") #append local routes from transactioal to the mergeset MakeFeatureLayer_management(LRSCounty, "LRS_County_Layer678", "Prefix IN ('6', '7', '8') AND LRSToDate is null") Append_management('LRS_County_Layer678', DominantRoutesAll, "NO_TEST") # Create singlepart features MultipartToSinglepart_management(DominantRoutesAll,DominantRtesSinglePart) AddField_management(DominantRtesSinglePart, "MinMeas", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(DominantRtesSinglePart, "MaxMeas", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management(DominantRtesSinglePart, "MaxMeas", "round(!Shape.extent.MMax!,7) ", "PYTHON_9.3", "") CalculateField_management(DominantRtesSinglePart, "MinMeas", "round(!Shape.extent.MMin!,7) ", "PYTHON_9.3", "") print ("single parts fc with measures created ") #more local variables for just the state route part TargetRoutesPrefix123 = ws+"\\SHSRoutes" whereClauseSHSPrimaryInv= "Substring(RouteId, 4, 1) in ('1', '2', '3') AND Substring(RouteID, 11, 1) = '0'" whereClauseSHS = "LRSToDate IS NULL AND Prefix in ('1', '2', '3')" SHSPI = ws+"\\SHSDomInv" countyDissolve = ws+ "\\LRS_County_Dissolve" StateRoutes=ws+"\\LRS_State" CntyDIOnState = ws+"\\CountyDomOnState" MinStatCntyDIOnState = ws+"\\CountyDomOnState_Statistics" #this above shouldnt really be a variable, see line 460 and the join at 469 and calculalte at 470. The name needs to be # Create State Routes from County Routes - adding @427 on 12/24/2019 FeatureClassToFeatureClass_conversion(KHUBRoutesLyr, ws, "SHSRoutes", whereClauseSHS) FeatureClassToFeatureClass_conversion(DominantRoutesAll, ws, "SHSDomInv", whereClauseSHSPrimaryInv) #FeatureClassToFeatureClass_conversion (TargetRoutesPrefix1235, TargetRoutesPrefix123, whereClauseSHS) #dissolve needs to filter to just SHS, prefix 4 and 5 should not be included Dissolve_management(TargetRoutesPrefix123, countyDissolve, "Prefix;RouteNum;Suffix;UniqueId;InvDir", "", "MULTI_PART", "DISSOLVE_LINES") AddField_management(countyDissolve, "StateRouteID", "TEXT", "", "", "12", "", "NULLABLE", "NON_REQUIRED", "") MakeFeatureLayer_management(countyDissolve, "StateUnique", "UniqueID is not null") MakeFeatureLayer_management(countyDissolve, "StateMainline", "UniqueID is null") CalculateField_management("StateMainline", "StateRouteID", "[Prefix]+ Right([RouteNum],3)+ [Suffix]+ [InvDir]", "VB", "") CalculateField_management("StateUnique", "StateRouteID", "[Prefix]+ Right([RouteNum],3)+ [Suffix]+ [InvDir]+ [UniqueId]", "VB", "") AddField_management(countyDissolve, "FromMeas", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(countyDissolve, "ToMeas", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management(countyDissolve, "FromMeas", "0", "VB", "") CalculateField_management(countyDissolve, "ToMeas", "[Shape_Length]/5280", "VB", "") CreateRoutes_lr(countyDissolve, "StateRouteID", StateRoutes, "TWO_FIELDS", "FromMeas", "ToMeas", "LOWER_LEFT", "1", "0", "IGNORE", "INDEX") LocateFeaturesAlongRoutes_lr(SHSPI, StateRoutes, "StateRouteID", "0 Feet", CntyDIOnState, "RID LINE FMEAS TMEAS", "FIRST", "DISTANCE", "ZERO", "FIELDS", "M_DIRECTON") MakeTableView_management(CntyDIOnState, "CountyDomOnState_Matched", "Substring(RID, 2, 5) = Substring(RouteID, 7, 5) AND Substring(RID, 1, 1) = Substring( RouteId, 4, 1)", "", "#") Statistics_analysis("CountyDomOnState_Matched", MinStatCntyDIOnState, "FMEAS MIN;TMEAS MAX;OBJECTID COUNT", "RouteId;RID") #add index here #join the state measures into Dominant Routes All & SinglePart where they exist as state routes #also output, projected, the State Routes overlapping into the Publication Database as the State Route LRS - consider constructing Fields as used in the LRM System MakeFeatureLayer_management(DominantRtesSinglePart, "StateMeasures", whereClauseSHSPrimaryInv) AddField_management("StateMeasures", "FromState", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management("StateMeasures", "ToState", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management("StateMeasures", "FromCounty", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management("StateMeasures", "ToCounty", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management("StateMeasures", "FromCounty", "[MinMeas]", "VB") CalculateField_management("StateMeasures", "ToCounty", "[MaxMeas]", "VB") AddIndex_management("DominantRtesSinglePart", "RouteID", "RouteID1") AddIndex_management(MinStatCntyDIOnState, "RouteID", "RouteID2") MakeTableView_management(MinStatCntyDIOnState, "JoinStats") AddJoin_management("StateMeasures", "RouteId", "JoinStats", "RouteId", "KEEP_ALL") CalculateField_management("StateMeasures", "DominantRtesSinglePart.FromState", "[DominantRtesSinglePart.MinMeas] + [CountyDomOnState_Statistics.MIN_FMEAS]", "VB") CalculateField_management("StateMeasures", "DominantRtesSinglePart.ToState", "[DominantRtesSinglePart.MaxMeas] + [CountyDomOnState_Statistics.MIN_FMEAS]", "VB") RemoveJoin_management("StateMeasures", "CountyDomOnState_Statistics") #output these results to the publication database DisconnectUser(pub_sdedb, "ALL") Project_management(DominantRoutesAll, (pub_database+r'/KHUBPub.GEO.'+"LRS_County_Primary"), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") FeatureClassToFeatureClass_conversion(DominantRoutesAll, pub_database, "LRS_County_Primary_LCC", "") FeatureClassToFeatureClass_conversion(DominantRtesSinglePart, pub_database, "LRS_County_PrimarySP_LCC", "") Project_management(DominantRtesSinglePart, (pub_database+r'/KHUBPub.GEO.'+"LRS_County_PrimarySP"), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") Project_management(StateRoutes, (pub_database+r'/KHUBPub.GEO.'+"LRS_State"), webmerc, transform_method, in_coor_system, "NO_PRESERVE_SHAPE", "", "NO_VERTICAL") ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.LRS_County_Primary'), "readonly", View="GRANT", Edit="") ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.LRS_County_PrimarySP'), "readonly", View="GRANT", Edit="") ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.LRS_State'), "readonly", View="GRANT", Edit="") ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.LRS_County_PrimarySP_LCC'), "readonly", View="GRANT", Edit="") ChangePrivileges_management((pub_database+r'/KHUBPub.GEO.LRS_County_Primary_LCC'), "readonly", View="GRANT", Edit="") status_table = (pub_database+r"/KHUBPub.GEO.Publication_Status") status_fields = ["DATE", "LAYER_NAME", "STATUS", "MESSAGE"] def update_status_table(status_table, data, fields, status_value, message_value): now = datetime.now() with da.InsertCursor(status_table, fields) as i_cur: #@UnresolvedImport insert_values = (now, data, status_value, message_value) i_cur.insertRow(insert_values) del i_cur i = 1 try: AddMessage('{0} {1}: {2} {3} {4}'.format("Primary Routes", "Primary routes updated in publication database", i, "of", len("Primary Routes"))) update_status_table(status_table, "Primary Routes", status_fields, "Primary routes updated in publication database", None) except ExecuteError: AddWarning('{0} {1}: {2} {3} {4}'.format("Primary Routes", "produced an error", i, "of", len("Primary Routes"))) message = GetMessages(2) update_status_table(status_table, "Primary Routes", status_fields, "Error Occurred", message) i += 1 def LYRS2Pub(target): # this function creates an output to publication of the pavement layers equivalent to LYRS in CANSYS. There are over 96,000 records of pavement layers, and process takes over an hour to write the dataset to publication from datetime import datetime #add datetime reporting/printing to check processing time on server #add logging and error messaging #add Change Privliege Management to add readonly privilege to output if target == 'Prod': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' pubinstance = r"khdbpubprod\khubpub_prod" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds elif target == 'Test': pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' pubinstance = r"khdbpubtest\khubpub_test" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\KHTest\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds else: pub_database = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' pubinstance = r"khdbpubdev\khubpub_dev" pub_sdedb = r'C:\Users\planadm\GISDATA\DbConnections\KHDev\[email protected]' tds = r'C:\Users\planadm\GISDATA\DbConnections\Prod\[email protected]' RH_Database = tds from arcpy import env, MakeRouteEventLayer_lr, FeatureClassToFeatureClass_conversion, DisconnectUser, Delete_management, Exists env.MResolution = 0.000000018939394 env.MTolerance = 0.000000621369949 DisconnectUser(pub_sdedb, "ALL") if Exists(pub_database+r'/KHUBPub.GEO.ev_PavementLayers'): Delete_management(pub_database+r'/KHUBPub.GEO.ev_PavementLayers') else: pass MakeRouteEventLayer_lr(tds+r"/KHUB.RH.LRS_County", "RouteId", tds+r"/KHUB.TDSUser.PS_PLV_Def", "RouteID LINE FromMeasure ToMeasure", r"PavementLayers", "", "ERROR_FIELD", "NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT") FeatureClassToFeatureClass_conversion("PavementLayers", pub_database, "ev_PavementLayers") def maintest(): #LYRS2Pub('Test') Analyze() PostAndReconcile() ##enable for nightly run Compress() ##enable for nightly run Analyze() def main(): Analyze() PostAndReconcile() ##enable for nightly run Compress() ##enable for nightly run Analyze() #RebuildIndexes() #in testing #RecreatePubGDB('Dev') #RecreatePubGDB('Prod') PublicateNetworkProd2Target('Prod') ## update prod transactional data to publication in 'dev', 'test', or 'prod' CreatePrimaryNetwork('Prod') #PublicateNetworkProd2Target('Test') #CreatePrimaryNetwork('Test') #PublicateNetworkProd2Target('Dev') #CreatePrimaryNetwork('Dev') #LYRS2Pub('Prod') print("run completed" ) main() ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ... View more