def PostProcLRS(): #this will create the calibrated network LRMs and the calibration points which are useful for other referential methods MResolution = 0.0005 MTolerance = 0.001 env.MTolerance = MTolerance env.MResolution = MResolution try: #copying these layers, the routes measures are already calibrated, measures as they should be #the FC2Fc was changed in the document to show only the primary route, mitigating hte need for the V_LRSNETS view FeatureClassToFeatureClass_conversion( admin_workspace + "/GIS_CANSYS.DBO.V_LRSS_SDO_R", admin_workspace, "SMLRS", "DIRECTION in (1, 2)", "#", "#") ChangePrivileges_management(admin_workspace + "/GIS_CANSYS.DBO.SMLRS", "readonly", "GRANT", "AS_IS") FeatureClassToFeatureClass_conversion( admin_workspace + "/GIS_CANSYS.DBO.V_LRSC_SDO_R", admin_workspace, "CMLRS", "DIRECTION in (1, 2)", "#", "#") ChangePrivileges_management(admin_workspace + "/GIS_CANSYS.DBO.CMLRS", "readonly", "GRANT", "AS_IS") #Oracle EXOR require M values at ever vertex #over time EXOR measures have become a bit of a mess, because of non-functional route calibration tools prior to 2012-2013 #measures should be based on stationing and increase linearly along a project except at the location of an equation #assets are based on whatever section reference they are given, so if the section measures change, so does the asset location except: endingTime = datetime.datetime.now() ScriptStatusLogging('CANP_LRS_EXPORT.py', 'PostProcLRS Function', scriptFailure, startingTime, endingTime, GetMessages(2)) pass
def FindOverlapsSausages(): '''KDOT Checks are already simplifying lines technically this is an unnecessary step and it is a time consuming, resource intensive process KDOT will run this script on lines that have already been simplified Data stewards are encouraged to simplify lines to improve their data quality and system performance using a tool or the following command, which is commented out''' #SimplifyLine_cartography(inroadcenterlines, ", algorithm="POINT_REMOVE", tolerance="3 Feet", error_resolving_option="RESOLVE_ERRORS", collapsed_point_option="NO_KEEP", error_checking_option="CHECK") #Check to Make sure that Shape_Length is the appropriate geometry column length in all geodatabases MakeFeatureLayer_management(roadcenterlines, "RoadCenterlinesS3_L10", "Shape_Length>=10") #test overlapping with a centerline midpoint FeatureVerticesToPoints_management("RoadCenterlinesS3_L10", "in_memory/RoadCenterlinesS3_L10_Mid", point_location="MID") #Get the count of roads within a distance of the centerline midpoint SpatialJoin_analysis("RoadCenterlinesS3_L10_Mid", roadcenterlines, "in_memory/ValidateOverlaps2", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "INTERSECT", "2 Feet", "Distance") #return the midpoints with a count greater than 1 - indicating possible overlaps #output this next line into validation geodatabase and add to arcmap for user interface FeatureClassToFeatureClass_conversion("in_memory/ValidateOverlaps2", r"C:\temp\New File Geodatabase (2).gdb", "ValidateOverlaps",""""Join_Count" > 1""") #may need to dev code to test for endpoints #clean up in memory artifact Delete_management("ValidateOverlaps2") #In aggregated data, county boundary overlaps are expected. It's also assumed that county bound left right should be coded correctly. #the following feature layer are the most important overlaps for review MakeFeatureLayer_management("ValidateOverlaps", "ValidateOverlaps_Non_County_Boundary", "COUNTY_L = COUNTY_R") #now using same midpoints look for sausages, midpoints less than 60 feet apart, might explore different distances under 60 feet #it might help and also hurt to lengthen the minimum segment length to longer than 10 feet for this, test a little more SpatialJoin_analysis("RoadCenterlinesS3_L10_Mid", roadcenterlines, "in_memory/ValidateSausages60", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "INTERSECT", "120 Feet", "Distance") FeatureClassToFeatureClass_conversion("in_memory/ValidateSausages60", r"C:\temp\New File Geodatabase (2).gdb", "ValidateSausages_60ft",""""Join_Count" > 1""") Delete_management("ValidateSausages60") MakeFeatureLayer_management("ValidateSausages_60ft", "ValidateSausages_Non_County_Boundary", "COUNTY_L = COUNTY_R")
def PostProcLRS(): MResolution = 0.0005 MTolerance = 0.001 env.MTolerance = MTolerance env.MResolution = MResolution LRM_NAMES = ["CMLRS", "SMLRS"] FeatureClassToFeatureClass_conversion( admin_workspace + "/GIS_CANSYS.DBO.V_LRSNETS", admin_workspace, "SMLRS", "DIRECTION in (1, 2)", "#", "#") ChangePrivileges_management(admin_workspace + "/GIS_CANSYS.DBO.SMLRS", "readonly", "GRANT", "AS_IS") FeatureClassToFeatureClass_conversion( admin_workspace + "/GIS_CANSYS.DBO.V_LRSNETC", admin_workspace, "CMLRS", "DIRECTION in (1, 2)", "#", "#") ChangePrivileges_management(admin_workspace + "/GIS_CANSYS.DBO.CMLRS", "readonly", "GRANT", "AS_IS") for LRM in LRM_NAMES: print "converting " + LRM + " to point features " + str( datetime.datetime.now()) FeatureVerticesToPoints_management( admin_workspace + "/GIS_CANSYS.DBO." + LRM, admin_workspace + "/GIS_CANSYS.DBO." + LRM + "_Point", "ALL") print "adding calibration values to " + LRM + " point features " + str( datetime.datetime.now()) AddXY_management(admin_workspace + "/GIS_CANSYS.DBO." + LRM + "_Point") print "finished " + LRM + " LRM processing " + str( datetime.datetime.now()) ChangePrivileges_management( admin_workspace + "/GIS_CANSYS.DBO." + LRM + "_Point", "readonly", "GRANT", "AS_IS")
def setupEnv(): print "run at "+ str(datetime.datetime.now()) rsel = "ENDDATE IS NULL" MakeTableView_management(resolve, "CCL_Resolution_tbl", rsel) CalculateField_management("CCL_Resolution_tbl", "CCL_LRS", 'str(!CITYNUMBER!)+str(!LRS_KEY![3:14])', "PYTHON" ) MakeTableView_management(connection1+"CCL_Resolution", "CCL_Resolution_tbl10", 'CITYNUMBER<100') CalculateField_management("CCL_Resolution_tbl10", "CCL_LRS", '"0"+str(!CITYNUMBER!)+str(!LRS_KEY![3:14])', "PYTHON") MakeFeatureLayer_management(cntyroutelyr, "cmlrs") MakeFeatureLayer_management(stateroutelyr, "smlrs") MakeFeatureLayer_management(citylimits, "CityLimits", "TYPE IN ( 'CS', 'ON')") LocateFeaturesAlongRoutes_lr(citylimits,"cmlrs","LRS_KEY","0 Feet",connection1+"GIS_CITY","LRS_KEY LINE Beg_CMP End_CMP","FIRST","DISTANCE","NO_ZERO","FIELDS","M_DIRECTON") MakeRouteEventLayer_lr("cmlrs","LRS_KEY","CCL_Resolution_tbl","LRS_KEY LINE BEG_CNTY_LOGMILE END_CNTY_LOGMILE","City_Connecting_Links","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") MakeTableView_management(connection1+"GIS_CITY", "GIS_CITY") MakeTableView_management(laneclass, "LaneClass") MakeRouteEventLayer_lr("cmlrs","LRS_KEY","GIS_CITY","LRS_KEY LINE BEG_CMP END_CMP","GIS_BASED_CCL","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") OverlayRouteEvents_lr(connection1+"CCL_Resolution","LRS_KEY LINE BEG_CNTY_LOGMILE END_CNTY_LOGMILE",laneclass,"LRS_KEY LINE BCMP ECMP","INTERSECT",connection1+"CCL_LANE_CLASS_OVERLAY","LRS_KEY LINE BEG_CNTY_LOGMILE END_CNTY_LOGMILE","NO_ZERO","FIELDS","INDEX") print "create Route Layer specific to City Connecting Link locations" FeatureClassToFeatureClass_conversion("City_Connecting_Links", connection0, "CITY_CONNECTING_LINK_CENTERLINE") LocateFeaturesAlongRoutes_lr(connection1+"CITY_CONNECTING_LINK_CENTERLINE",stateroutelyr,"LRS_ROUTE","0 Meters",connection1+"CCL_STATE_LRS_tbl","LRS_ROUTE LINE BEG_STATE_LOGMILE END_STATE_LOGMILE","FIRST","DISTANCE","ZERO","FIELDS","M_DIRECTON") MakeRouteEventLayer_lr("smlrs", "LRS_ROUTE",connection1+"CCL_STATE_LRS_tbl","LRS_ROUTE LINE BEG_STATE_LOGMILE END_STATE_LOGMILE","CCL_STATE_LRS","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") FeatureClassToFeatureClass_conversion("CCL_STATE_LRS", connection0, "CITY_CONNECTING_LINK_STATEREF") if Exists(connection1+"CITY_CONNECTING_LINK_STATE"): Delete_management(connection1+"CITY_CONNECTING_LINK_STATE") Dissolve_management(connection1+"CITY_CONNECTING_LINK_STATEREF",connection1+"CITY_CONNECTING_LINK_STATE","LRS_ROUTE;CITY;CITYNUMBER;DESCRIPTION;CCL_LRS","BEG_STATE_LOGMILE MIN;END_STATE_LOGMILE MAX","MULTI_PART","UNSPLIT_LINES") Dissolve_management(connection1+"CITY_CONNECTING_LINK_STATEREF",connection1+"CITY_CONNECTING_LINK_STATE_D","CCL_LRS","BEG_STATE_LOGMILE MIN;END_STATE_LOGMILE MAX","MULTI_PART","DISSOLVE_LINES") print "processes to Create the layer that will be used to create a new LRS for city connecting links"
def UpdateLocalFileGDB(): import datetime, time fDateTime = datetime.datetime.now() from arcpy import FeatureClassToFeatureClass_conversion, CreateFileGDB_management, Exists, Delete_management from KhubCode25.KhubCode25Config import ( localProProjectPath, localProFileGDBWorkspace, prodDataSourceSDE, devDataSourceSDE, dbname, dbownername, countylines, devorprod) if devorprod == 'prod': database = prodDataSourceSDE print("running on " + devorprod) else: database = devDataSourceSDE print("running on " + devorprod) fileformatDateStr = fDateTime.strftime("%Y%m%d") localfilegdb = localProFileGDBWorkspace + '\\' + 'KhubRoadCenterlines' + fileformatDateStr + '.gdb' #print(fileformatDateStr) if Exists(localfilegdb): print(localfilegdb + " exists and will be deleted") Delete_management(localfilegdb) time.sleep(1) CreateFileGDB_management(localProFileGDBWorkspace, "KhubRoadCenterlines" + fileformatDateStr, "CURRENT") FeatureClassesUsed = [ 'All_Road_Centerlines', 'All_Road_Centerlines_D1', 'MARKUP_POINT', 'All_Roads_Stitch_Points', 'Videolog_CURRENT_LANETRACE', 'Videolog_CURRENT_RAMPTRACE', 'HPMS_RAMPS' ] for FeatureClass in FeatureClassesUsed: loopFC = localProProjectPath + '/' + database + "/" + dbname + "." + dbownername + "." + FeatureClass FeatureClassToFeatureClass_conversion(loopFC, localfilegdb, FeatureClass) FeatureClassToFeatureClass_conversion( localProProjectPath + '/' + countylines, localfilegdb, "SHARED_COUNTY_LINES")
def MakeRouteLayers(OpEnvironmentMode): from EXOR_GIS_CONFIG import OpEnvironment OpRunIn= OpEnvironment.OpRunInRoutes # @UndefinedVariable OpRunOut= OpEnvironment.OpRunOut # @UndefinedVariable #adm=OpEnvironment.adm # @UndefinedVariable Owner=OpEnvironment.Owner # @UndefinedVariable DB=OpEnvironment.DB # @UndefinedVariable env.workspace = OpRunIn env.overwriteOutput = True print OpRunIn #combine the connection, db, and owner to the destination path for enterprise geodatabase output OpRunFullOut = OpRunOut+r"/"+DB+"."+Owner+"." print "Updating CRND" #add the Map Extract Event Table limited to primary direction into memory TruncateTable_management(OpRunFullOut+"CRND") Append_management("CRND", OpRunFullOut+"CRND", "NO_TEST") print "Updating SRND" TruncateTable_management(OpRunFullOut+"SRND") Append_management("SRND", OpRunFullOut+"SRND", "NO_TEST") print "Updating NSND" TruncateTable_management(OpRunFullOut+"NSND") Append_management("NSND", OpRunFullOut+"NSND", "NO_TEST") if GetCount_management("MAP_EXTRACT")>0: MakeTableView_management("MAP_EXTRACT", "V_MV_MAP_EXTRACT", "DIRECTION < 3") #Add the CRND CANSYS rotue layer, dynseg the event table, truncate and load to CMLRS MakeFeatureLayer_management("CRND", "CRND") MakeRouteEventLayer_lr("CRND", "NE_UNIQUE", "MAP_EXTRACT", "NQR_DESCRIPTION LINE BEG_CNTY_LOGMILE END_CNTY_LOGMILE", "CMLRS1", "", "ERROR_FIELD", "NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT") try: print "truncation and appending the CMLRS" TruncateTable_management(OpRunFullOut+"CMLRS") Append_management("CMLRS1", OpRunFullOut+"CMLRS", "NO_TEST") except: print "could not truncate, overwriting CMLRS" FeatureClassToFeatureClass_conversion("CMLRS1", OpRunOut, "CMLRS","#", "#", "#") #except: # print "could not update the CMLRS" MakeFeatureLayer_management("SRND", "SRND") MakeRouteEventLayer_lr("SRND", "NE_UNIQUE", "MAP_EXTRACT", "STATE_NQR_DESCRIPTION LINE BEG_STATE_LOGMILE END_STATE_LOGMILE", out_layer="SMLRS1", offset_field="", add_error_field="ERROR_FIELD", add_angle_field="NO_ANGLE_FIELD", angle_type="NORMAL", complement_angle="ANGLE", offset_direction="LEFT", point_event_type="POINT") try: print "truncation and appending the SMLRS" TruncateTable_management(OpRunFullOut+"SMLRS") Append_management("SMLRS1", OpRunFullOut+"SMLRS", "NO_TEST") except: print "could not truncate, overwriting SMLRS" FeatureClassToFeatureClass_conversion("SMLRS1", OpRunOut, "SMLRS","#", "#", "#") #except: # print "could not update the SMLRS" print "Route Layers Updated" else: print "the map extract is unreliable and was not exported"
def copyfromstaged(lyrlist, admin, fdset, fcoutpath): for lyr in lyrlist: print(fcoutpath + admin + '.' + lyr) if Exists(fcoutpath + admin + '.' + lyr): DeleteFeatures_management(fcoutpath + admin + '.' + lyr) Append_management(ws + "/" + tempgdb + '/' + lyr, fcoutpath + admin + '.' + lyr, "NO_TEST", "#") print "updated " + lyr + " in " + fcoutpath else: FeatureClassToFeatureClass_conversion( ws + "/" + tempgdb + '/' + lyr, fcoutpath, lyr) print "copied new " + lyr + " feature class to " + fcoutpath print " Check roles and privleges on this " + lyr + " at " + fcoutpath try: CalculateField_management(fcoutpath + admin + '.' + lyr, "NETWORKDATE", "datetime.datetime.now( )", "PYTHON_9.3", "#") print "copy date field updated" except: AddField_management(fcoutpath + admin + '.' + lyr, "NETWORKDATE", "DATE") CalculateField_management(fcoutpath + admin + '.' + lyr, "NETWORKDATE", "datetime.datetime.now( )", "PYTHON_9.3", "#") print "copy date field added and updated" pass return
def AddInsert(fc, layer_name, newtbl, workspace): MakeTableView_management(newtbl, "NEWROWS_View", "#", "#", "#") addcount = int(GetCount_management("NEWROWS_View").getOutput(0)) if addcount == 0: print "no new records" pass else: MakeFeatureLayer_management(fc, layer_name) MakeXYEventLayer_management( "NEWROWS_View", "CROSSINGLONGITUDE", "CROSSINGLATITUDE", "NEWROWS_Layer", "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],VERTCS['NAVD_1988',VDATUM['North_American_Vertical_Datum_1988'],PARAMETER['Vertical_Shift',0.0],PARAMETER['Direction',1.0],UNIT['Meter',1.0]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119522E-09;0.001;0.001;IsHighPrecision", "#") FeatureClassToFeatureClass_conversion( "NEWROWS_Layer", "D:/Temp", "LOADTHIS1.shp", "#", """CROSSINGID "CROSSINGID" true false false 30 Text 0 0 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGID,-1,-1;CROSSINGLA "CROSSINGLA" true true false 8 Double 10 38 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGLATITUDE,-1,-1;CROSSINGLO "CROSSINGLO" true true false 8 Double 10 38 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGLONGITUDE,-1,-1;CROSSINGTY "CROSSINGTY" true true false 2 Text 0 0 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGTYPE,-1,-1""", "#") Append_management( "D:/Temp/LOADTHIS1.shp", layer_name, "NO_TEST", """CROSSINGID "CROSSINGID" true false false 30 Text 0 0 ,First,#,D:/Temp/LOADTHIS1.shp,CROSSINGID,-1,-1;CROSSINGLATITUDE "CROSSINGLATITUDE" true true false 8 Double 10 38 ,First,#,D:/Temp/LOADTHIS1.shp,CROSSINGLA,-1,-1;CROSSINGLONGITUDE "CROSSINGLONGITUDE" true true false 8 Double 10 38 ,First,#,D:/Temp/LOADTHIS1.shp,CROSSINGLO,-1,-1;CROSSINGTYPE "CROSSINGTYPE" true true false 2 Text 0 0 ,First,#,D:/Temp/LOADTHIS1.shp,CROSSINGTY,-1,-1;LOADDATE "LOADDATE" true true false 36 Date 0 0 ,First,#""", "#") Delete_management("D:/Temp/LOADTHIS1.shp", "#") updatelyr = layer_name + "new" MakeFeatureLayer_management(layer_name, updatelyr, "LOADDATE IS NULL") with da.Editor(workspace) as edit: CalculateField_management(updatelyr, "LOADDATE", "datetime.datetime.now( )", "PYTHON_9.3", "#") del fc, layer_name, newtbl, workspace, updatelyr print "new rows inserted into Static_Crossings"
def createSourceRoutes123(): from arcpy import CreateRoutes_lr, FlipLine_edit, AddField_management, FeatureClassToFeatureClass_conversion, SelectLayerByAttribute_management, CalculateField_management from KhubCode25.KhubCode25Config import localProFileGDBWorkspace fileformatDateStr = startDateTime.strftime("%Y%m%d") localfilegdb = localProFileGDBWorkspace + '\\' + 'KhubRoadCenterlines' + fileformatDateStr + '.gdb' FeatureClassToFeatureClass_conversion( localfilegdb + "\\All_Road_Centerlines", "in_memory", "SourceRoadCenterlines123", "LRS_ROUTE_SUFFIX in ( '0' , 'A' , 'B' , 'C' , 'Y' , 'S' ) AND LRS_ROUTE_PREFIX in ( 'I' , 'K' , 'U' )" ) #flip backward routes #selecting by End less than begin mileage: 3/7/2018, 14948 routes need flipped #Selection by State Flip Flag = "Y" 3/7/2018, 14752 routes need flipped #for county_log_end < county_log_begin AND STATE_FLIP_FLAG NOT LIKE 'Y' there are 5 records, all 5 are K-14 on the mitchell/jewell county line #flip flag has become unreliable, do not use, just use mileage relationship AddField_management("SourceRoadCenterlines123", "TmpDblVal", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") SelectLayerByAttribute_management("SourceRoadCenterlines123", "NEW_SELECTION", "county_log_end < county_log_begin") CalculateField_management("SourceRoadCenterlines123", "TmpDblVal", "!county_log_begin!", "PYTHON") CalculateField_management("SourceRoadCenterlines123", "county_log_begin", "!county_log_end!", "PYTHON", "") CalculateField_management("SourceRoadCenterlines123", "county_log_end", "!TmpDblVal!", "PYTHON", "") FlipLine_edit("SourceRoadCenterlines123") #once these lines have been flipped, we will flag them with an F in the state flip flag field CalculateField_management("SourceRoadCenterlines123", "STATE_FLIP_FLAG", "'F'", "PYTHON")
def process_feature_classes(input_ws, output_ws, foreach_layer = None): """ processes each featureclass with an optional function input_ws - the database or dataset path to process feature classes output_ws - the output for the feature classes foreach_layer - the function to process the feature classes """ from arcpy import env, ListFeatureClasses, FeatureClassToGeodatabase_conversion, \ AddWarning, AddMessage, GetCount_management, FeatureClassToFeatureClass_conversion from os.path import join env.workspace = input_ws feature_classes = ListFeatureClasses() for feature_class in feature_classes: AddMessage('Processing {}...'.format(feature_class)) if env.skipEmpty: count = int(GetCount_management(feature_class)[0]) if count == 0: AddWarning('Skipping because table is empty: {}'.format(feature_class)) continue try: if foreach_layer: foreach_layer(input_ws, output_ws, feature_class) else: #copy each feature class over output_path = join(output_ws, get_name(feature_class)) delete_existing(output_path) FeatureClassToFeatureClass_conversion(feature_class, output_ws, get_name(feature_class)) except Exception as e: AddWarning('Error processing feature class {} - {}'.format(feature_class, e))
def CITY (): itemname = "CITY" cantbl = itemname+"_ln_1" domname0 = 'CITY_TYPE' domstate = """IAL_DOMAIN = 'CITY_TYPE' AND IAL_END_DATE is NULL""" domfields = "CT_CITY_NBRR_INCORPORATED_CITY" domtbl = itemname+"_"+domfields disfields = domfields domname = "CT_CITY_NBR" MakeTableView_management("Database Connections/ATLASPROD.odc/V_NM_CTY",domname) TableToTable_conversion(domname, wsouttbl, domname+"R", "#") MakeTableView_management(wsouttbl+"//"+cantbl,itemname+"PD",""""NE_OWNER" IN ( 'EB' , 'NB' )""","#") DissolveRouteEvents_lr(itemname+"PD","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile", "CITY_NBR", wsouttbl+"//"+itemname+"_SD","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile","CONCATENATE","INDEX") AddIndex_management(wsouttbl+"//"+itemname+"_SD","CITY_NBR","CITY_NBR","NON_UNIQUE","NON_ASCENDING") AddField_management(wsouttbl+"//"+domname+"R", "CITY_NBR", "FLOAT", "#", "#", "#") CalculateField_management(wsouttbl+"//"+domname+"R", "CITY_NBR", "[CITY_NUMBER]", "VB") MakeTableView_management(wsouttbl+"//"+itemname+"_SD", itemname+"_view", "#") AddJoin_management(itemname+"_view", "CITY_NBR", wsouttbl+"//"+domname+"R", "CITY_NBR", "KEEP_ALL") TableToTable_conversion(itemname+"_view", wsouttbl, itemname+"_EVENT", "#") AssignDomainToField_management(wsouttbl+"//"+itemname+"_EVENT",domfields,domname0) DeleteField_management(wsouttbl+"//"+itemname+"_EVENT","CTYR_OBJECTID;CTYR_IIT_NE_ID;CTYR_IIT_INV_TYPE;CTYR_IIT_PRIMARY_KEY;CTYR_IIT_START_DATE;CTYR_IIT_DATE_CREATED;CTYR_IIT_DATE_MODIFIED;CTYR_IIT_CREATED_BY;CTYR_IIT_MODIFIED_BY;CTYR_IIT_ADMIN_UNIT;CTYR_IIT_NOTE;CTYR_IIT_PEO_INVENT_BY_ID;CTYR_NAU_UNIT_CODE;CTYR_IIT_END_DATE;CTYR_CITY_NBR") MakeRouteEventLayer_lr(wsouttbl+"//SMLRS","LRS_ROUTE",wsouttbl+"//"+itemname+"_EVENT","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile",itemname+"_ITEM","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") FeatureClassToFeatureClass_conversion(itemname+"_ITEM", wsouttbl, itemname) DeleteField_management(wsouttbl+"//"+itemname,"CTYR_OBJECTID;CTYR_IIT_NE_ID;CTYR_IIT_INV_TYPE;CTYR_IIT_PRIMARY_KEY;CTYR_IIT_START_DATE;CTYR_IIT_DATE_CREATED;CTYR_IIT_DATE_MODIFIED;CTYR_IIT_CREATED_BY;CTYR_IIT_MODIFIED_BY;CTYR_IIT_ADMIN_UNIT;CTYR_IIT_NOTE;CTYR_IIT_PEO_INVENT_BY_ID;CTYR_NAU_UNIT_CODE;CTYR_IIT_END_DATE;CTYR_CITY_NBR") print "we have cities"
def ExportGISProdLyrs(owner_workspace, admin_workspace): #similar to CANP, only for layers in another geodatabase, like GISPROD. owner = "GIS_CANSYS.SHARED." outpre = owner_workspace + "/" + owner print "exporting initialized at " + str(datetime.datetime.now()) destConnection = owner_workspace #once again, this could be change to the admin workspace for lyr in mapping.ListLayers(mxd): if lyr.name in gisprodlist: try: #manipulate the layer name a little bit differently lyrname = lyr.name[7:] print lyrname + " exporting..." outlyrname = lyrname outlyrobj = outpre + outlyrname Unlock(admin_workspace) FeatureClassToFeatureClass_conversion(lyr, destConnection, outlyrname, "#", "#", "#") ChangePrivileges_management(outlyrobj, "readonly", "GRANT", "AS_IS") print lyrname + " exported to " + outlyrname + " " + str( datetime.datetime.now()) except ExecuteError: msgs = GetMessages(2) AddError(msgs) print msgs endingTime = datetime.datetime.now() ScriptStatusLogging('CANP_LRS_EXPORT.py', 'ExportGISProdLyrs Function', scriptFailure, startingTime, endingTime, GetMessages(2)) pass except (RuntimeError, TypeError, NameError): print "TypeError on item" + lyr.name endingTime = datetime.datetime.now() ScriptStatusLogging('CANP_LRS_EXPORT.py', 'ExportGISProdLyrs Function', scriptFailure, startingTime, endingTime, GetMessages(2)) pass except: tb = sys.exc_info()[2] tbinfo = traceback.format_tb(tb)[0] pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str( sys.exc_info()[1]) msgs = "ArcPy ERRORS:\n" + GetMessages(2) + "\n" print pymsg + "\n" print msgs endingTime = datetime.datetime.now() ScriptStatusLogging('CANP_LRS_EXPORT.py', 'ExportGISProdLyrs Function', scriptFailure, startingTime, endingTime, GetMessages(2)) else: #print lyr.name +" was not in the export list and will be skipped" return
def INTR (): TableToTable_conversion("INTR_pt_1",wsouttbl,"INTR_pt_2","#","""LRS_KEY "LRS_KEY" true true false 13 Text 0 0 ,First,#,INTR_pt_1,LRS_KEY,-1,-1;Cnty_Logmile "Cnty_Logmile" true true false 8 Double 0 0 ,First,#,INTR_pt_1,Cnty_Logmile,-1,-1;Distance "Distance" true true false 8 Double 0 0 ,First,#,INTR_pt_1,Distance,-1,-1;IIT_NE_ID "IIT_NE_ID" true false false 4 Long 0 0 ,First,#,INTR_pt_1,IIT_NE_ID,-1,-1;IIT_INV_TYPE "IIT_INV_TYPE" true false false 4 Text 0 0 ,First,#,INTR_pt_1,IIT_INV_TYPE,-1,-1;IIT_PRIMARY_KEY "IIT_PRIMARY_KEY" true false false 50 Text 0 0 ,First,#,INTR_pt_1,IIT_PRIMARY_KEY,-1,-1;IIT_START_DATE "IIT_START_DATE" true false false 8 Date 0 0 ,First,#,INTR_pt_1,IIT_START_DATE,-1,-1;IIT_DATE_CREATED "IIT_DATE_CREATED" true false false 8 Date 0 0 ,First,#,INTR_pt_1,IIT_DATE_CREATED,-1,-1;IIT_DATE_MODIFIED "IIT_DATE_MODIFIED" true false false 8 Date 0 0 ,First,#,INTR_pt_1,IIT_DATE_MODIFIED,-1,-1;IIT_CREATED_BY "IIT_CREATED_BY" true false false 30 Text 0 0 ,First,#,INTR_pt_1,IIT_CREATED_BY,-1,-1;IIT_MODIFIED_BY "IIT_MODIFIED_BY" true false false 30 Text 0 0 ,First,#,INTR_pt_1,IIT_MODIFIED_BY,-1,-1;IIT_ADMIN_UNIT "IIT_ADMIN_UNIT" true false false 4 Long 0 0 ,First,#,INTR_pt_1,IIT_ADMIN_UNIT,-1,-1;IIT_DESCR "IIT_DESCR" true true false 40 Text 0 0 ,First,#,INTR_pt_1,IIT_DESCR,-1,-1;IIT_NOTE "IIT_NOTE" true true false 40 Text 0 0 ,First,#,INTR_pt_1,IIT_NOTE,-1,-1;IIT_PEO_INVENT_BY_ID "IIT_PEO_INVENT_BY_ID" true true false 4 Long 0 0 ,First,#,INTR_pt_1,IIT_PEO_INVENT_BY_ID,-1,-1;NAU_UNIT_CODE "NAU_UNIT_CODE" true true false 10 Text 0 0 ,First,#,INTR_pt_1,NAU_UNIT_CODE,-1,-1;IIT_END_DATE "IIT_END_DATE" true true false 8 Date 0 0 ,First,#,INTR_pt_1,IIT_END_DATE,-1,-1;INTRSCTN_NAME "INTRSCTN_NAME" true true false 50 Text 0 0 ,First,#,INTR_pt_1,INTRSCTN_NAME,-1,-1;ON_STATE_NONSTATE "ON_STATE_NONSTATE" true true false 16 Text 0 0 ,First,#,INTR_pt_1,ON_STATE_NONSTATE,-1,-1;INTRSCTN_DESC "INTRSCTN_DESC" true true false 200 Text 0 0 ,First,#,INTR_pt_1,INTRSCTN_DESC,-1,-1;INTRSCTN_ID "INTRSCTN_ID" true false false 50 Text 0 0 ,First,#,INTR_pt_1,INTRSCTN_ID,-1,-1;TFO_IND "TFO_IND" true true false 1 Text 0 0 ,First,#,INTR_pt_1,TFO_IND,-1,-1;CART_NODE_ID "CART_NODE_ID" true true false 4 Long 0 0 ,First,#,INTR_pt_1,CART_NODE_ID,-1,-1;LEFT_TURN_LN "LEFT_TURN_LN" true true false 4 Long 0 0 ,First,#,INTR_pt_1,LEFT_TURN_LN,-1,-1;RIGHT_TURN_LN "RIGHT_TURN_LN" true true false 4 Long 0 0 ,First,#,INTR_pt_1,RIGHT_TURN_LN,-1,-1;INTERSECTION_CONTROL "INTERSECTION_CONTROL" true true false 4 Long 0 0 ,First,#,INTR_pt_1,INTERSECTION_CONTROL,-1,-1;PER_GREEN_TIME "PER_GREEN_TIME" true true false 4 Double 0 0 ,First,#,INTR_pt_1,PER_GREEN_TIME,-1,-1;LONGITUDE "LONGITUDE" true true false 4 Float 0 0 ,First,#,INTR_pt_1,LONGITUDE,-1,-1;LATITUDE "LATITUDE" true true false 4 Float 0 0 ,First,#,INTR_pt_1,LATITUDE,-1,-1;NM_NE_ID_IN "NM_NE_ID_IN" true false false 4 Long 0 0 ,First,#,INTR_pt_1,NM_NE_ID_IN,-1,-1;NM_NE_ID_OF "NM_NE_ID_OF" true false false 4 Long 0 0 ,First,#,INTR_pt_1,NM_NE_ID_OF,-1,-1;NM_TYPE "NM_TYPE" true false false 4 Text 0 0 ,First,#,INTR_pt_1,NM_TYPE,-1,-1;NM_OBJ_TYPE "NM_OBJ_TYPE" true false false 4 Text 0 0 ,First,#,INTR_pt_1,NM_OBJ_TYPE,-1,-1;NM_BEGIN_MP "NM_BEGIN_MP" true false false 8 Double 0 0 ,First,#,INTR_pt_1,NM_BEGIN_MP,-1,-1;NM_START_DATE "NM_START_DATE" true false false 8 Date 0 0 ,First,#,INTR_pt_1,NM_START_DATE,-1,-1;NM_END_DATE "NM_END_DATE" true true false 8 Date 0 0 ,First,#,INTR_pt_1,NM_END_DATE,-1,-1;NM_END_MP "NM_END_MP" true false false 8 Double 0 0 ,First,#,INTR_pt_1,NM_END_MP,-1,-1;NM_SLK "NM_SLK" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_SLK,-1,-1;NM_CARDINALITY "NM_CARDINALITY" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_CARDINALITY,-1,-1;NM_ADMIN_UNIT "NM_ADMIN_UNIT" true false false 4 Long 0 0 ,First,#,INTR_pt_1,NM_ADMIN_UNIT,-1,-1;NM_DATE_CREATED "NM_DATE_CREATED" true true false 8 Date 0 0 ,First,#,INTR_pt_1,NM_DATE_CREATED,-1,-1;NM_DATE_MODIFIED "NM_DATE_MODIFIED" true true false 8 Date 0 0 ,First,#,INTR_pt_1,NM_DATE_MODIFIED,-1,-1;NM_MODIFIED_BY "NM_MODIFIED_BY" true true false 30 Text 0 0 ,First,#,INTR_pt_1,NM_MODIFIED_BY,-1,-1;NM_CREATED_BY "NM_CREATED_BY" true true false 30 Text 0 0 ,First,#,INTR_pt_1,NM_CREATED_BY,-1,-1;NM_SEQ_NO "NM_SEQ_NO" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_SEQ_NO,-1,-1;NM_SEG_NO "NM_SEG_NO" true true false 4 Long 0 0 ,First,#,INTR_pt_1,NM_SEG_NO,-1,-1;NM_TRUE "NM_TRUE" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_TRUE,-1,-1;NM_END_SLK "NM_END_SLK" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_END_SLK,-1,-1;NM_END_TRUE "NM_END_TRUE" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NM_END_TRUE,-1,-1;NE_ID "NE_ID" true false false 4 Long 0 0 ,First,#,INTR_pt_1,NE_ID,-1,-1;NE_UNIQUE "NE_UNIQUE" true false false 30 Text 0 0 ,First,#,INTR_pt_1,NE_UNIQUE,-1,-1;NE_TYPE "NE_TYPE" true false false 4 Text 0 0 ,First,#,INTR_pt_1,NE_TYPE,-1,-1;NE_NT_TYPE "NE_NT_TYPE" true false false 4 Text 0 0 ,First,#,INTR_pt_1,NE_NT_TYPE,-1,-1;NE_DESCR "NE_DESCR" true false false 240 Text 0 0 ,First,#,INTR_pt_1,NE_DESCR,-1,-1;NE_LENGTH "NE_LENGTH" true true false 8 Double 0 0 ,First,#,INTR_pt_1,NE_LENGTH,-1,-1;NE_ADMIN_UNIT "NE_ADMIN_UNIT" true false false 4 Long 0 0 ,First,#,INTR_pt_1,NE_ADMIN_UNIT,-1,-1;NE_DATE_CREATED "NE_DATE_CREATED" true false false 8 Date 0 0 ,First,#,INTR_pt_1,NE_DATE_CREATED,-1,-1;NE_DATE_MODIFIED "NE_DATE_MODIFIED" true false false 8 Date 0 0 ,First,#,INTR_pt_1,NE_DATE_MODIFIED,-1,-1;NE_MODIFIED_BY "NE_MODIFIED_BY" true false false 30 Text 0 0 ,First,#,INTR_pt_1,NE_MODIFIED_BY,-1,-1;NE_CREATED_BY "NE_CREATED_BY" true false false 30 Text 0 0 ,First,#,INTR_pt_1,NE_CREATED_BY,-1,-1;NE_START_DATE "NE_START_DATE" true false false 8 Date 0 0 ,First,#,INTR_pt_1,NE_START_DATE,-1,-1;NE_END_DATE "NE_END_DATE" true true false 8 Date 0 0 ,First,#,INTR_pt_1,NE_END_DATE,-1,-1;NE_GTY_GROUP_TYPE "NE_GTY_GROUP_TYPE" true true false 4 Text 0 0 ,First,#,INTR_pt_1,NE_GTY_GROUP_TYPE,-1,-1;NE_OWNER "NE_OWNER" true true false 4 Text 0 0 ,First,#,INTR_pt_1,NE_OWNER,-1,-1;NE_NAME_1 "NE_NAME_1" true true false 80 Text 0 0 ,First,#,INTR_pt_1,NE_NAME_1,-1,-1;NE_NAME_2 "NE_NAME_2" true true false 80 Text 0 0 ,First,#,INTR_pt_1,NE_NAME_2,-1,-1;NE_PREFIX "NE_PREFIX" true true false 4 Text 0 0 ,First,#,INTR_pt_1,NE_PREFIX,-1,-1;NE_NUMBER "NE_NUMBER" true true false 8 Text 0 0 ,First,#,INTR_pt_1,NE_NUMBER,-1,-1;NE_SUB_TYPE "NE_SUB_TYPE" true true false 2 Text 0 0 ,First,#,INTR_pt_1,NE_SUB_TYPE,-1,-1;NE_GROUP "NE_GROUP" true true false 30 Text 0 0 ,First,#,INTR_pt_1,NE_GROUP,-1,-1;NE_NO_START "NE_NO_START" true true false 4 Long 0 0 ,First,#,INTR_pt_1,NE_NO_START,-1,-1;NE_NO_END "NE_NO_END" true true false 4 Long 0 0 ,First,#,INTR_pt_1,NE_NO_END,-1,-1;NE_SUB_CLASS "NE_SUB_CLASS" true true false 4 Text 0 0 ,First,#,INTR_pt_1,NE_SUB_CLASS,-1,-1;NE_NSG_REF "NE_NSG_REF" true true false 240 Text 0 0 ,First,#,INTR_pt_1,NE_NSG_REF,-1,-1;NE_VERSION_NO "NE_VERSION_NO" true true false 240 Text 0 0 ,First,#,INTR_pt_1,NE_VERSION_NO,-1,-1""","#") #convert EXOR domains to SDE Database domains - intersection type itemname = 'INTR' itemdom = 'IN_INTRSCTN_TYPE' #IN INTRSCTN_TYPE, TURN_LANE newdomname = 'INTRSCTN_TYPE' itemsel = """"IAL_DOMAIN" = '"""+itemdom+"'" itemdescr = 'Intersection Type' #convert EXOR domains to SDE Database domains - turn lanes type itemdom1 = 'TURN_LANE' #IN INTRSCTN_TYPE, TURN_LANE newdomname1 = 'INTERSECTION_TURN_LANE' itemsel1 = """"IAL_DOMAIN" = '"""+itemdom1+"'" itemdescr1 = 'Turn Lane Type' MakeTableView_management(itemname+"_pt_2", itemname+"PD","#","#") print ("Intersections are located on a directional basis in some cases, so primary and secondary directions are allowed until advised differently") DissolveRouteEvents_lr(itemname+"PD","LRS_Key POINT Cnty_Logmile","IIT_NE_ID;INTRSCTN_NAME;ON_STATE_NONSTATE;INTRSCTN_DESC;INTRSCTN_ID;TFO_IND;CART_NODE_ID;LEFT_TURN_LN;RIGHT_TURN_LN;INTERSECTION_CONTROL;PER_GREEN_TIME;NE_OWNER",wsouttbl+"//"+itemname+"_EVENT","LRS_Key POINT Cnty_Logmile","DISSOLVE","INDEX") AssignDomainToField_management(itemname+"_EVENT","INTERSECTION_CONTROL",itemdom) AssignDomainToField_management(itemname+"_EVENT","LEFT_TURN_LN",itemdom1) AssignDomainToField_management(itemname+"_EVENT","RIGHT_TURN_LN",itemdom1) MakeRouteEventLayer_lr("CMLRS","LRS_KEY",itemname+"_EVENT","LRS_Key POINT Cnty_Logmile",itemname+"_ITEM","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") #arcpy.FeatureClassToFeatureClass_conversion(itemname+"_ITEM", wsouttbl, itemname) lyrlist = ["INTR"] for lyr in lyrlist: lyrin = lyr+"_ITEM"# FeatureClassToFeatureClass_conversion(lyrin, ws+"\\"+tempgdb,lyr) #23 sec
def DirectionalUrbanClass(): #do the same as for State Sys but for Non State Urban Classified Highways (Nusys) FeatureVerticesToPoints_management("RoadCenterlinesC", "in_memory/UrbanPoints", "MID") FeatureClassToFeatureClass_conversion( NSND, "in_memory", "NSND_NPD", where_clause="NETWORK_DIRECTION IN ( 'SB' , 'WB' )") LocateFeaturesAlongRoutes_lr("UrbanPoints", "NSND_NPD", "NE_UNIQUE", "200 Feet", "in_memory/UrbanPointsMeasures", "RID POINT MEAS", "ALL", "DISTANCE", "ZERO", "FIELDS", "M_DIRECTON") Delete_management("UrbanPoints") #is the query stil valid for non state system? Explore hte NE Unique. State System did not use the county number prefix SelectLayerByAttribute_management( "UrbanPointsMeasures", "NEW_SELECTION", """SUBSTRING( "RID", 4, 7) NOT LIKE SUBSTRING("NON_State_System_LRSKey" ,4, 7)""" ) DeleteRows_management(in_rows="UrbanPointsMeasures") MakeRouteEventLayer_lr("NSND_NPD", "NE_UNIQUE", "UrbanPointsMeasures", "rid POINT MEAS", "UrbanPointEvents", offset_field="Distance", add_error_field="ERROR_FIELD", add_angle_field="ANGLE_FIELD", angle_type="NORMAL", complement_angle="ANGLE", offset_direction="RIGHT", point_event_type="POINT") MakeFeatureLayer_management("UrbanPointEvents", "UNPD_ID", """"Distance">=0""") SelectLayerByLocation_management("UNPD_ID", "INTERSECT", "RoadCenterlinesC", "1 Feet", "NEW_SELECTION") #at this point, there are a lot of false positives, places with single carriagway roads and nusys divided #we need to incorporate the check overlap process to identify where their are single and dual carriagways here #starting with the technique to find sausages or dual carraigeways #SpatialJoin_analysis("UNPD_ID", "RoadCenterlinesC", "in_memory/ValidateSausages120", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "INTERSECT", "120 Feet", "Distance") #this Spatial Join step is improving the results, removing most false positives. It still shows overlapping segments #it would be improved even more, potentially, by testing the non-primary direction against dissolve somehow. #except, the calculate method is by segment to the source, a dissolve would complicate the process of calculating back to the source data #we are looking for count grater than 0 of the offset point to hte segment, so a dissolved segment should work import EliminateOverlaps from EliminateOverlaps import CollectorDissolve #set the roadcenterline input and dissolve output for RoadCenterline dissolve for this subroutine roadcenterlines = "RoadCenterlinesC" ClassOutput = r"in_memory/RMC2" CollectorDissolve() SpatialJoin_analysis("UNPD_ID", "RMC2dissolve", "in_memory/ValidateSausages120", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "INTERSECT", "120 Feet", "Distance")
def FUN (): itemname = 'FUN' cantbl = itemname+"_ln_1" domname = 'FC_FUN_CLASS' domfields = "FUN_CLASS" domtbl = itemname+"_"+domfields+"_tbl" disfields = domfields MakeTableView_management(wsouttbl+"//"+cantbl,itemname+"PD",""""NE_OWNER" IN ( 'EB' , 'NB' )""","#") DissolveRouteEvents_lr(itemname+"PD","LRS_KEY LINE Beg_Cnty_Logmile End_Cnty_Logmile",disfields,wsouttbl+"//"+itemname+"_EVENT","LRS_Key LINE Beg_Cnty_Logmile End_Cnty_Logmile","CONCATENATE","INDEX") AssignDomainToField_management(wsouttbl+"//"+itemname+"_EVENT",domfields,domname) MakeRouteEventLayer_lr(wsouttbl+"//CMLRS","LRS_KEY",wsouttbl+"//"+itemname+"_EVENT","LRS_Key LINE Beg_Cnty_Logmile End_Cnty_Logmile",itemname+"_ITEM","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") FeatureClassToFeatureClass_conversion(itemname+"_ITEM", wsouttbl, itemname) lyrlist = ["FUN"] for lyr in lyrlist: lyrin = lyr+"_ITEM"# FeatureClassToFeatureClass_conversion(lyrin, ws+"\\"+tempgdb,lyr) #23 sec
def CreateNonStateRoutes(): FeatureClassToFeatureClass_conversion(in_features="Database Connections/SDEPROD_SHARED.sde/SHARED.NON_STATE_SYSTEM", out_path="C:/temp/Nusys_Check.gdb", out_name="Non_State_System", where_clause="", field_mapping="#", config_keyword="") NonState = r"C:/temp/Nusys_Check.gdb/Non_State_System" MakeFeatureLayer_management(NonState, "NonState", "MILEAGE_COUNTED = -1 AND SURFACE NOT LIKE 'Propose'") MakeFeatureLayer_management(NonState, "BackwardSegs", "LRS_BACKWARDS = -1") FlipLine_edit("BackwardSegs") CreateRoutes_lr("NonState", "LRS_KEY", destdb+"\Route", "TWO_FIELDS", "LRS_BEG_CNTY_LOGMILE", "LRS_BEG_CNTY_LOGMILE", "UPPER_LEFT", "1", "0", "IGNORE", "INDEX") #Flip them back to the original direction FlipLine_edit(in_features="BackwardSegs")
def StateHighwaySystemDissolve(): # Create an in-memory copy of state highay system routes based on LRS Route Prefix FeatureClassToFeatureClass_conversion(routesSourceCenterlines, "in_memory", inMemFeatureClassName, "LRS_ROUTE_PREFIX in ('I', 'U', 'K')") MakeFeatureLayer_management(inMemFeatureClass, stateSystemFeatureLayer) #about 941 records in Southwest Kansas had reverse mileages and need to be flipped #this should be corrected in the final conflation delivery #if it is not corrected, these route segments should be explored in more detail SelectLayerByAttribute_management(stateSystemFeatureLayer, "NEW_SELECTION", """("COUNTY_BEGIN_MP" > "COUNTY_END_MP" OR "STATE_BEGIN_MP" > "STATE_END_MP") AND "STATE_FLIP_FLAG" IS NULL""") CalculateField_management(stateSystemFeatureLayer, "STATE_FLIP_FLAG", """'Y'""", "PYTHON_9.3", "") SelectLayerByAttribute_management(stateSystemFeatureLayer, "NEW_SELECTION", """"STATE_FLIP_FLAG" = 'Y' """) FlipLine_edit(stateSystemFeatureLayer) #need to flip mileages where geometry was flipped so add fields AddField_management(stateSystemFeatureLayer, "F_CNTY_2", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(stateSystemFeatureLayer, "T_CNTY_2", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(stateSystemFeatureLayer, "F_STAT_2", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(stateSystemFeatureLayer, "T_STAT_2", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") #check if there are any state system segments where the to is greater than the from and flag them for review AddField_management(stateSystemFeatureLayer, "MileFlipCheck", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") CalculateField_management(stateSystemFeatureLayer, "F_CNTY_2", "!COUNTY_END_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "T_CNTY_2", "!COUNTY_BEGIN_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "F_STAT_2", "!STATE_END_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "T_STAT_2", "!STATE_BEGIN_MP!", "PYTHON_9.3", "") # Switch selection and calculate mileages SelectLayerByAttribute_management(in_layer_or_view=stateSystemFeatureLayer, selection_type="SWITCH_SELECTION", where_clause="") CalculateField_management(stateSystemFeatureLayer, "F_CNTY_2", "!COUNTY_BEGIN_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "T_CNTY_2", "!COUNTY_END_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "F_STAT_2", "!STATE_BEGIN_MP!", "PYTHON_9.3", "") CalculateField_management(stateSystemFeatureLayer, "T_STAT_2", "!STATE_END_MP!", "PYTHON_9.3", "") #KDOT Direction should already be calculated, by running "DualCarriagweayIdentity.py" and updating the KDOT_DIRECTION_CALC to 1 where dual carriagway is found #Validation_CheckOverlaps can also help do identify sausage link/parallel geometries that may indicate dual carriagway, but that script does not yet #identify and calculate the KDOT_DIRECTION_CALC flag. It probably could with more development # Select the EB routes and change the LRS_Direction to WB SelectLayerByAttribute_management(stateSystemFeatureLayer, "NEW_SELECTION", """"KDOT_DIRECTION_CALC" = '1' AND "LRS_DIRECTION" = 'EB'""") CalculateField_management(stateSystemFeatureLayer, "LRS_DIRECTION", "'WB'", "PYTHON_9.3", "") #Select the SB routes to chante hte LRS direction to SB SelectLayerByAttribute_management(stateSystemFeatureLayer, "NEW_SELECTION", """"KDOT_DIRECTION_CALC" = '1' AND "LRS_DIRECTION" = 'NB'""") CalculateField_management(stateSystemFeatureLayer, "LRS_DIRECTION", "'SB'", "PYTHON_9.3", "") # Clear the selections SelectLayerByAttribute_management(stateSystemFeatureLayer, "CLEAR_SELECTION", "") #Calculate County LRS Key in CountyKey1 field for State Highway system #Need to add CountyKey2 for iteration 2, also go ahead and add new LRS Key format CalculateField_management(stateSystemFeatureLayer, "CountyKey1", """[LRS_COUNTY_PRE] + [LRS_ROUTE_PREFIX] + [LRS_ROUTE_NUM] + [LRS_ROUTE_SUFFIX] + [LRS_UNIQUE_IDENT] +"-" + [LRS_DIRECTION]""", "VB") CalculateField_management(stateSystemFeatureLayer, "StateKey1", """[LRS_ROUTE_PREFIX] + [LRS_ROUTE_NUM] + [LRS_ROUTE_SUFFIX] + [LRS_UNIQUE_IDENT] +"-" + [LRS_DIRECTION]""", "VB") #this is the dissolve - the output of this is a feature class which is clean for route creation of the state highway system Dissolve_management(stateSystemFeatureLayer, routesSourceDissolveOutput+"_Dissolve_EO", "CountyKey1;LRS_COUNTY_PRE;LRS_ROUTE_PREFIX;LRS_ROUTE_NUM;LRS_ROUTE_SUFFIX;LRS_UNIQUE_IDENT;LRS_DIRECTION", "F_CNTY_2 MIN;T_CNTY_2 MAX", "SINGLE_PART", "DISSOLVE_LINES") Dissolve_management(stateSystemFeatureLayer, routesSourceDissolveOutput+"_Unsplit_EO", "CountyKey1;LRS_COUNTY_PRE;LRS_ROUTE_PREFIX;LRS_ROUTE_NUM;LRS_ROUTE_SUFFIX;LRS_UNIQUE_IDENT;LRS_DIRECTION", "F_CNTY_2 MIN;T_CNTY_2 MAX", "SINGLE_PART", "UNSPLIT_LINES")
def ConflateKDOTrestart(gdb, DOTRoads): """Conflation restart for selecting KDOT roads to conflate to the NG911 Network""" from arcpy import SelectLayerByLocation_management, FeatureClassToFeatureClass_conversion from arcpy import env env.overwriteOutput = 1 checkfile = gdb MakeFeatureLayer_management(DOTRoads+"/KDOT_HPMS_2012","KDOT_Roads","#","#","#") MakeFeatureLayer_management(checkfile+"/RoadCenterline","RoadCenterline","#","#","#") SelectLayerByLocation_management("KDOT_Roads","INTERSECT","RoadCenterline","60 Feet","NEW_SELECTION") FeatureClassToFeatureClass_conversion("KDOT_Roads",checkfile+r"/NG911","KDOT_Roads_Review","#","#","#")
def ConflateKDOTrestart(): """Conflation restart for selecting KDOT roads to conflate to the NG911 Network""" MakeFeatureLayer_management(DOTRoads + "/KDOT_HPMS_2012", "KDOT_Roads", "#", "#", "#") MakeFeatureLayer_management(gdb + "/RoadCenterline", "RoadCenterline", "#", "#", "#") SelectLayerByLocation_management("KDOT_Roads", "INTERSECT", "RoadCenterline", "60 Feet", "NEW_SELECTION") FeatureClassToFeatureClass_conversion("KDOT_Roads", gdb + r"/NG911", "KDOT_Roads_Review", "#", "#", "#")
def prepareAggregateTable(): env.workspace = in_memory FeatureClassToFeatureClass_conversion(kanDriveSpatialConditions,"in_memory","aggregateTable") rowDeletionCursor = daUpdateCursor(aggregateTable, '*') for rowToDelete in rowDeletionCursor: rowDeletionCursor.deleteRow() try: del rowDeletionCursor except: pass
def MigrateFeatureClasses(): fclist = ListFeatureClasses() for fc in fclist: if Exists(OracleDB + "/" + fc): print str(OracleDB + "/" + fc) + " Aleady exist" print else: print str(fc) + "... exporting to SDO in " + str(OracleDB) FeatureClassToFeatureClass_conversion( fc, OracleDB, str(fc), where_clause="#", config_keyword="SDO_GEOMETRY")
def create_active_only_FC(inpath): """ Copies an input FC applying timestamp filters to create active-only shapefiles Requires: inpath -- the path of the source feature class with all records outdir -- the output location for the FGDB """ from arcpy import FeatureClassToFeatureClass_conversion fc_name = os.path.basename(inpath) active_fc_name = "active_" + fc_name FeatureClassToFeatureClass_conversion(inpath, os.path.dirname(inpath), active_fc_name, " enddate = timestamp '2100-01-01 00:00:00'")
def AppendIt(): for fc in KSdb_fc_list: fcappendfrom = source + fd + r'/' + fc fcappendto = final + fd2 + r'/' + fc tempfc = temp + r'/' + fc if Exists(tempfc): Delete_management(tempfc) else: pass FeatureClassToFeatureClass_conversion(fcappendfrom, temp, fc) Append_management(tempfc, fcappendto, "NO_TEST", "#", "#") print "appended " + str(fc) for tbl in KSdb_Tbl_list: tblappendfrom = source + r'/' + tbl tblappendto = final + tbl Append_management(tblappendfrom, tblappendto, "NO_TEST", "#", "#") print "appended " + str(tbl)
def UAB(): itemname = "UAB" cantbl = itemname+"_ln_1" domname = "UABR" MakeTableView_management("Database Connections/ATLASPROD.odc/V_NM_UABR",domname) TableToTable_conversion(domname, wsouttbl, domname+"R", "#") MakeTableView_management(wsouttbl+"//"+cantbl,itemname+"PD",""""NE_OWNER" IN ( 'EB' , 'NB' )""","#") DissolveRouteEvents_lr(itemname+"PD","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile", "CITY_NBR", wsouttbl+"//"+itemname+"_SD","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile","CONCATENATE","INDEX") AddIndex_management(wsouttbl+"//"+itemname+"_SD","CITY_NBR","CITY_NBR","NON_UNIQUE","NON_ASCENDING") AddField_management(wsouttbl+"//"+domname+"R", "CITY_NBR", "FLOAT", "#", "#", "#") CalculateField_management(wsouttbl+"//"+domname+"R", "CITY_NBR", "[CITY_NUMBER]", "VB") MakeTableView_management(wsouttbl+"//"+itemname+"_SD", itemname+"_view", "#") AddJoin_management(itemname+"_view", "CITY_NBR", wsouttbl+"//"+domname+"R", "CITY_NBR", "KEEP_ALL") TableToTable_conversion(itemname+"_view", wsouttbl, domname+"J", "#") MakeRouteEventLayer_lr(wsouttbl+"//SMLRS","LRS_ROUTE",wsouttbl+"//"+domname+"J","LRS_ROUTE LINE Beg_State_Logmile End_State_Logmile",itemname+"_ITEM","#","ERROR_FIELD","NO_ANGLE_FIELD","NORMAL","ANGLE","LEFT","POINT") FeatureClassToFeatureClass_conversion(itemname+"_ITEM", wsouttbl, itemname) print "we have UABs"
def ExportCANPLyrs(): print "exporting initialized at " + str(datetime.datetime.now()) destConnection = r"D:\SQL61_GIS_CANSYS.sde" TableToTable_conversion("MAP_EXTRACT", r"Database Connections/SQL61_GIS_CANSYS.sde", "Map_Extract", "#", "#", "#") for lyr in mapping.ListLayers(mxd): if lyr.name in CANPlist: try: lyrname = lyr.name[11:] print lyrname + " exporting..." outlyrname = "V_" + lyrname outlyrobj = destConnection + "\\GIS_CANSYS.DBO." + outlyrname FeatureClassToFeatureClass_conversion(lyr, destConnection, outlyrname, "#", "#", "#") ChangePrivileges_management(outlyrobj, "readonly", "GRANT", "AS_IS") print lyrname + " exported to " + outlyrname + " " + str( datetime.datetime.now()) except ExecuteError: msgs = GetMessages(2) AddError(msgs) print msgs pass except (RuntimeError, TypeError, NameError): print "TypeError on item" + lyr.name pass except: tb = sys.exc_info()[2] tbinfo = traceback.format_tb(tb)[0] # Concatenate information together concerning the error into a message string # pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str( sys.exc_info()[1]) msgs = "ArcPy ERRORS:\n" + GetMessages(2) + "\n" print pymsg + "\n" print msgs else: #print lyr.name +" was not in the export list and will be skipped" pass
def AddInsert(fc, layer_name, newtbl): MakeFeatureLayer_management(fc, layer_name) MakeTableView_management(newtbl, "NEWROWS_View", "#", "#", "#") MakeXYEventLayer_management( "NEWROWS_View", "CROSSINGLONGITUDE", "CROSSINGLATITUDE", "NEWROWS_Layer", "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],VERTCS['NAVD_1988',VDATUM['North_American_Vertical_Datum_1988'],PARAMETER['Vertical_Shift',0.0],PARAMETER['Direction',1.0],UNIT['Meter',1.0]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119522E-09;0.001;0.001;IsHighPrecision", "#") FeatureClassToFeatureClass_conversion( "NEWROWS_Layer", "D:/Temp", "LOADTHIS.shp", "#", """CROSSINGID "CROSSINGID" true false false 30 Text 0 0 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGID,-1,-1;CROSSINGLA "CROSSINGLA" true true false 8 Double 10 38 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGLATITUDE,-1,-1;CROSSINGLO "CROSSINGLO" true true false 8 Double 10 38 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGLONGITUDE,-1,-1;CROSSINGTY "CROSSINGTY" true true false 2 Text 0 0 ,First,#,Database Connections/sdedev_ciims.sde/CIIMS.NEWROWS_Features,CROSSINGTYPE,-1,-1""", "#") Append_management( "D:/Temp/LOADTHIS.shp", layer_name, "NO_TEST", """CROSSINGID "CROSSINGID" true false false 30 Text 0 0 ,First,#,D:/Temp/LOADTHIS.shp,CROSSINGID,-1,-1;CROSSINGLATITUDE "CROSSINGLATITUDE" true true false 8 Double 10 38 ,First,#,D:/Temp/LOADTHIS.shp,CROSSINGLA,-1,-1;CROSSINGLONGITUDE "CROSSINGLONGITUDE" true true false 8 Double 10 38 ,First,#,D:/Temp/LOADTHIS.shp,CROSSINGLO,-1,-1;CROSSINGTYPE "CROSSINGTYPE" true true false 2 Text 0 0 ,First,#,D:/Temp/LOADTHIS.shp,CROSSINGTY,-1,-1;LOADDATE "LOADDATE" true true false 36 Date 0 0 ,First,#""", "#") Delete_management("D:/Temp/LOADTHIS.shp", "#") del fc, layer_name, newtbl print "new rows inserted into Static_Crossings"
def ConflateKDOT(): """detects road centerline changes and transfers the HPMS key field from the KDOT roads via ESRI conflation tools""" from arcpy import TransferAttributes_edit, DetectFeatureChanges_management, GenerateRubbersheetLinks_edit, RubbersheetFeatures_edit spatialtolerance = "20 feet" MakeFeatureLayer_management(DOTRoads + "/KDOT_HPMS_2012", "KDOT_Roads", "#", "#", "#") MakeFeatureLayer_management( gdb + "/RoadCenterline", "RoadCenterline", "#", "#", "#") #this may already exist so check, and use FD if Exists(gdb + r"/NG911/KDOT_Roads_Review"): print "selection of KDOT roads for conflation already exists" else: SelectLayerByLocation_management("KDOT_Roads", "INTERSECT", "RoadCenterline", "60 Feet", "NEW_SELECTION") FeatureClassToFeatureClass_conversion("KDOT_Roads", gdb + r"/NG911", "KDOT_Roads_Review", "#", "#", "#") MakeFeatureLayer_management(gdb + "/KDOT_Roads_Review", "KDOT_Roads_Review", "#", "#", "#") GenerateRubbersheetLinks_edit("KDOT_Roads_Review", "RoadCenterline", gdb + r"/NG911/RoadLinks", spatialtolerance, "ROUTE_ID LRSKEY", gdb + r"/RoadMatchTbl") MakeFeatureLayer_management(gdb + "/NG911/RoadLinks", "RoadLinks", "#", "#", "#") MakeFeatureLayer_management(gdb + "/NG911/RoadLinks_pnt", "RoadLinks_pnt", "#", "#", "#") RubbersheetFeatures_edit("KDOT_Roads_Review", "RoadLinks", "RoadLinks_pnt", "LINEAR") DetectFeatureChanges_management("KDOT_Roads_Review", "RoadCenterline", gdb + r"/NG911/RoadDifference", spatialtolerance, "#", gdb + r"/RoadDifTbl", spatialtolerance, "#") MakeFeatureLayer_management(gdb + "/NG911/RoadDifference", "RoadDifference", "#", "#", "#") TransferAttributes_edit("KDOT_Roads_Review", "RoadCenterline", "YEAR_RECORD;ROUTE_ID", spatialtolerance, "#", gdb + r"/LRS_MATCH")
def FormatGeocoding(): fcname = "ReferencePosts" fcnameout = "CardinalRefposts" cardfcfull = gdbin + r"/" + fcnameout print cardfcfull from arcpy import AddField_management, CalculateField_management, FeatureClassToFeatureClass_conversion try: FeatureClassToFeatureClass_conversion(gdbin + r"/" + fcname, gdbin, fcnameout, """DIRECTION in (1, 3)""") AddField_management(cardfcfull, "Address", "TEXT", "", "", "55", "", "NULLABLE", "NON_REQUIRED", "") AddField_management(cardfcfull, "CountyNum", "TEXT", "", "", "3", "", "NULLABLE", "NON_REQUIRED", "") except: print "stuff exists" address = """Int([REFPOST]) &" "& [LRS]""" print address countynum = 'Left( [LRS_KEY],3)' CalculateField_management(cardfcfull, "CountyNum", countynum, "VB") CalculateField_management(cardfcfull, "Address", address, "VB")
def ConflateKDOT(gdb, DOTRoads): """detects road centerline changes and transfers the HPMS key field from the KDOT roads via ESRI conflation tools""" from arcpy import MakeFeatureLayer_management, Exists, TransferAttributes_edit, DetectFeatureChanges_management, RubbersheetFeatures_edit, SelectLayerByLocation_management, FeatureClassToFeatureClass_conversion, GenerateRubbersheetLinks_edit, RubbersheetFeatures_edit from arcpy import env env.overwriteOutput = 1 checkfile = gdb spatialtolerance = "20 feet" #MakeFeatureLayer_management(checkfile+"/AuthoritativeBoundary","AuthoritativeBoundary_Layer","#","#","#") #MakeFeatureLayer_management(checkfile+"/CountyBoundary","CountyBoundary_Layer","#","#","#") MakeFeatureLayer_management(DOTRoads+"/KDOT_HPMS_2012","KDOT_Roads","#","#","#") MakeFeatureLayer_management(checkfile+"/RoadCenterline","RoadCenterline","#","#","#") if Exists(checkfile+r"/NG911/KDOT_Roads_Review"): print "selection of KDOT roads for conflation already exists" else: SelectLayerByLocation_management("KDOT_Roads","INTERSECT","RoadCenterline","60 Feet","NEW_SELECTION") FeatureClassToFeatureClass_conversion("KDOT_Roads",checkfile+r"/NG911","KDOT_Roads_Review","#","#","#") MakeFeatureLayer_management(checkfile+"/KDOT_Roads_Review","KDOT_Roads_Review","#","#","#") GenerateRubbersheetLinks_edit("KDOT_Roads_Review","RoadCenterline",checkfile+r"/NG911/RoadLinks",spatialtolerance,"ROUTE_ID LRSKEY",checkfile+r"/RoadMatchTbl") MakeFeatureLayer_management(checkfile+"/NG911/RoadLinks","RoadLinks","#","#","#") MakeFeatureLayer_management(checkfile+"/NG911/RoadLinks_pnt","RoadLinks_pnt","#","#","#") RubbersheetFeatures_edit("KDOT_Roads_Review","RoadLinks","RoadLinks_pnt","LINEAR") DetectFeatureChanges_management("KDOT_Roads_Review","RoadCenterline",checkfile+r"/NG911/RoadDifference",spatialtolerance,"#",checkfile+r"/RoadDifTbl",spatialtolerance,"#") MakeFeatureLayer_management(checkfile+"/NG911/RoadDifference","RoadDifference","#","#","#") TransferAttributes_edit("KDOT_Roads_Review","RoadCenterline","YEAR_RECORD;ROUTE_ID",spatialtolerance,"#",checkfile+r"/LRS_MATCH")
def transferConstructionWeather(): env.workspace = in_memory featuresToTransfer = list() try: print str( datetime.datetime.now()) + ' copying the oracle table to memory' # Create an in_memory feature class which to hold the features from # the Oracle table. FeatureClassToFeatureClass_conversion(sdeCDRS, "in_memory", "CDRS_Weather", "#") # Then, define a projection on it, since the original Oracle table # is lacking the proper information. DefineProjection_management("in_memory\CDRS_Weather", lambertCC) ############################################################################################################### # Maintainability information: # ToDo: # If you need to add another field to transfer between the two, just add it to the searchCursorFields and the # insertCursorFields lists and make sure that it is in the same position in the list order for both of # them. # Besides 'LoadDate', the order does not matter, so long as each field name in the # searchCursorFields has a counterpart in the insertCursorFields and vice versa. # 'LoadDate' should always be last for the insertCursorFields as it is appended to each row after all # of the other items from the searchCursorFields. ############################################################################################################### # searchCursorFields go to "in_memory\CDRS". (Input table) # Removed 'GEOMETRY', then replaced functionality with 'SHAPE@'. # Also removed 'OBJECTID', 'RPT_BY_NAME', 'RPT_BY_PHONE', # 'RPT_BY_EMAIL', 'CONTACT_NAME', 'CONTACT_PHONE', 'CONTACT_EMAIL', # 'BEG_LATITUDE', 'BEG_LONGITUDE', 'BEG_REF_POST', 'END_REF_POST', # 'END_LATITUDE', 'END_LONGITUDE', 'INTERNAL_COMMENT', # 'WIDTH_RESTRICTION', 'VERT_RESTRICTION', 'WEIGHT_RESTRICTION', # 'SPEED_RESTRICTION', 'PUBLIC_VIEW', 'DCAM_COMMENT', 'DCAM_DATE', # 'DISPLAY_MAP', 'LINE_COLOR', 'CDRS_ALERT_ROUTE_ID', 'ALERT_ID', # 'ALERT_DATE', 'COMP_DATE', 'BEG_COUNTY_NAME', # 'END_LRS_KEY', 'END_LRS_ROUTE', 'END_COUNTY_NAME', # 'BEG_LRS_DIREC_TXT', 'END_LRS_DIREC_TXT', 'TIME_DELAY_TXT', # 'CDRS_WZ_DETAIL_ID', 'CDRS_FHWA_ID', 'CONST_PROJ_NUM', # 'PUBLIC_COMMENT', 'OFFICE_NAME', 'NEW_NOTIFICATION', # 'EMAIL_REMINDER', 'NOTIFICATION_SENT', 'LOC_ENTRY_TYPE', # 'ALERT_HYPERLINK', 'GIS_VIEW', # 'END_COUNTY_NUMBER', 'ALERT_TYPE_TXT', 'ALERT_STATUS', # 'CLOSED_MSG_SENT', 'ALERT_INSERT_DT', 'FEA_CLOSED' # Public Comment is where to get the output text data from. ###################################################################### searchCursorFields = [ 'SHAPE@', 'ADMIN_OWNER', 'BEG_LRS_KEY', 'ALERT_DIREC_TXT', 'ALERT_DESC_TXT', 'BEG_COUNTY_NUMBER', 'BEG_STATE_LOGMILE', 'END_STATE_LOGMILE', 'LENGTH', 'DISTRICT', 'AREA', 'ROUTE', 'BEG_LRS_ROUTE', 'BEG_DESC', 'END_DESC', 'SITE_CR', 'LAST_UPDATE', 'START_DATE', 'EXPIRE_DATE' ] # insertCursorFields go to kandrive_spatial.DBO.Conditions. (Output table) # Removed 'GEOMETRY' and 'GEOMETRY.STLength()', then replaced their # functionality with 'SHAPE@'. # Also removed 'OBJECTID' ###################################################################### insertCursorFields = [ 'SHAPE@', 'ADMIN_OWNER', 'LRS_KEY', 'LANE_DESCRIPTION', 'ROAD_COND_TEXT', 'COUNTY_NO', 'BEG_STATE_LOGMILE', 'END_STATE_LOGMILE', 'SEG_LENGTH_MILES', 'SNOW_DIST', 'SNOW_AREA', 'RD_SUFX_SUBC', 'ROUTE_ID', 'BEG_DESC', 'END_DESC', 'SITE_CR', 'LAST_UPDATE', ### End of directly transfered columns. Next are set columns. 'STALE_MSG', 'UNIQUE_ID', 'RCRS_ROADS_ID', 'SNOW_SUBAREA', 'ROAD_CONDITION', 'RE_OPENING', 'CLOSED_MSG_SENT', 'CLOSED_FROM', 'CLOSED_TO', 'ACCOMMODATION', 'USER_ID', 'CLOSED_COND', 'MSG_CODE', 'LINE_COLOR', 'MSG_SENT', 'SNOW_ICE_CATEGORY', 'RCRSP_VIEW', ## End of set columns. Next are calculated columns 'SEGMENT_ID', 'LRS_PREFIX', 'LRS_SUFFIX', 'LRS_SUBCLASS', 'RT_NUMBER', 'BEG_POINT_DESC', 'END_POINT_DESC', 'RCRSP_DIST', 'SNICE_BEGIN', 'SNICE_END', 'REPORT_TIME', 'ROAD_COND_TIME' ] # 46 Total with 29 following LAST_UPDATE weatherTypesString = "('Flooding', 'Fog', 'Blowing Dust/Smoke', 'Wind Damage')" whereClause = """ALERT_STATUS = 2 AND FEA_CLOSED = 1 AND ALERT_TYPE_TXT = 'Road Closing' AND ALERT_DESC_TXT IN """ + weatherTypesString cdrsSearchCursor = daSearchCursor(r"in_memory\CDRS_Weather", searchCursorFields, whereClause) for cdrsCursorItem in cdrsSearchCursor: featureItem = list(cdrsCursorItem) ## Copied columns copiedFeatureItem = featureItem[:-2] lrsKey = featureItem[2] startDateCDRS = featureItem[-2] startDateStr = startDateCDRS.strftime('%m/%d/%y') expDateCDRS = featureItem[-1] expDateStr = expDateCDRS.strftime('%m/%d/%y') ## Set Columns copiedFeatureItem.append(0) ## STALE_MSG copiedFeatureItem.append(0) ## UNIQUE_ID copiedFeatureItem.append(9999) ## RCRS_ROADS_ID copiedFeatureItem.append(9) ## SNOW_SUBAREA copiedFeatureItem.append( 11) ## ROAD_CONDITION should always == 11, for closed. copiedFeatureItem.append(None) ## RE_OPENING copiedFeatureItem.append(None) ## CLOSED_MSG_SENT copiedFeatureItem.append(None) ## CLOSED_FROM copiedFeatureItem.append(None) ## CLOSED_TO copiedFeatureItem.append(None) ## ACCOMMODATION copiedFeatureItem.append(100) ## USER_ID copiedFeatureItem.append(2) ## CLOSED_COND copiedFeatureItem.append('0000') ## MSG_CODE copiedFeatureItem.append(255) ## LINE_COLOR copiedFeatureItem.append(0) ## MSG_SENT copiedFeatureItem.append(3) ## SNOW_ICE_CATEGORY copiedFeatureItem.append(3) ## RCRSP_VIEW ## Calculated Columns copiedFeatureItem[4] = 'Closed - ' + copiedFeatureItem[ 4] ## ROAD_COND_TEXT copiedFeatureItem.append( copiedFeatureItem[12]) ## SEGMENT_ID = ROUTE_ID copiedFeatureItem.append(lrsKey[3:4]) ## LRS_PREFIX copiedFeatureItem.append(lrsKey[9:10]) ## LRS_SUFFIX copiedFeatureItem.append(lrsKey[-1:]) ## LRS_SUBCLASS rtNumberToFormat = lrsKey[6:9] rtNumberFormatted = int(rtNumberToFormat) copiedFeatureItem.append(rtNumberFormatted) ## RT_NUMBER # Only get the first 60 chars on the next two columns. copiedFeatureItem.append( copiedFeatureItem[13][:60]) ## BEG_POINT_DESC copiedFeatureItem.append( copiedFeatureItem[14][:60]) ## END_POINT_DESC copiedFeatureItem.append(copiedFeatureItem[9]) ## RCRSP_DIST copiedFeatureItem.append(startDateStr + ' 12:04 AM') ## SNICE_BEGIN copiedFeatureItem.append(expDateStr + ' 11:54 PM') ## SNICE_END copiedFeatureItem.append(startDateStr + ' 12:04 AM') ## REPORT_TIME copiedFeatureItem.append(startDateStr + ' 12:04 AM') ## ROAD_COND_TIME featuresToTransfer.append(copiedFeatureItem) RCRS_InsertCursor = daInsertCursor(kanDriveSpatialConditions, insertCursorFields) for RCRS_Feature in featuresToTransfer: insertOID = RCRS_InsertCursor.insertRow(RCRS_Feature) print "Inserted a row with the OID of: " + str(insertOID) except: print "An error occurred." errorItem = sys.exc_info()[1] errorStatement = str(errorItem.args[0]) print errorStatement if len(errorStatement) > 253: errorStatement = errorStatement[0:253] else: pass endTime = datetime.datetime.now() ScriptStatusLogging('KanDrive_Spatial_Conditions_Update', 'kandrive_spatial.DBO.Conditions', scriptFailure, startTime, endTime, errorItem.args[0], pythonLogTable) try: del errorItem except: pass # Reraise the error to stop execution and prevent a success message # from being inserted into the table when the script completes. raise finally: try: del cdrsSearchCursor except: pass try: del RCRS_InsertCursor except: pass