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