def CityCodes(lyr, Kdotdbfp):
#Codify the City Limit\city number for LRS , calculated for LEFT and RIGHT from NG911)
MakeTableView_management(Kdotdbfp+"\City_Limits", "City_Limits")
AddJoin_management(lyr,"MUNI_R","City_Limits", "CITY", "KEEP_COMMON")
CalculateField_management(lyr,"KDOT_CITY_R","str(!City_Limits.CITY_CD!).zfill(3)","PYTHON_9.3","#")
RemoveJoin_management(lyr)
AddJoin_management(lyr,"MUNI_L","City_Limits", "CITY", "KEEP_COMMON")
CalculateField_management(lyr,"KDOT_CITY_L","str(!City_Limits.CITY_CD!).zfill(3)","PYTHON_9.3","#")
RemoveJoin_management(lyr)
def appender_DWBI_OLD(ShapeFileDate):
print "appending the modeled data"
env.workspace = repo
filename1 = r"DWBI_SEGS"
#C:\Workspace\pydot\sde_connections_10.3\sde@KTRIPS_sqlgiprod.sde\KTRIPS.SDE.KTRIPS_ROUTES
enterprisedbRoutes = gdb + r"\KTRIPS.SDE.KTRIPS_ROUTE_Segments"
print enterprisedbRoutes
#Append_management(filename1, enterprisedbRoutes, "NO_TEST", "#")
if Exists(filename1):
MakeTableView_management(filename1, "AppendCheck", "#", "#", "#")
AddJoin_management("AppendCheck",
"PRMT_ID",
enterprisedbRoutes,
"PRMT_ID",
join_type="KEEP_COMMON")
recordsTest = str(GetCount_management("AppendCheck"))
RemoveJoin_management("AppendCheck")
if recordsTest == '0':
print recordsTest + " of these records exist, appending now"
Append_management(filename1, enterprisedbRoutes, "NO_TEST", "#")
else:
print recordsTest + " records already have been appended"
else:
print "there was a problem, " + str(filename1) + " could not be found"
pass
def NONSTATE_INT():
print "add intersection points where state routes intersect non-state routes"
MakeFeatureLayer_management(nonstate, 'NON_STATE_SYSTEM',
"CITYNUMBER IS NOT NULL AND CITYNUMBER<999")
MakeFeatureLayer_management(connection1 + NewRoute, NewRoute)
Intersect_analysis(
"CCL_LRS_ROUTE #;'NON_STATE_SYSTEM' #",
connection1 + "Intersect_NONSTATE", "ALL", "5 Feet", "POINT"
) #this doesnt reference the newroute variable, its easier that way
MakeFeatureLayer_management(connection1 + "Intersect_NONSTATE", "NSI")
LocateFeaturesAlongRoutes_lr("NSI", "CCL_LRS_ROUTE", NewRouteKey, "5 Feet",
connection1 + "INTR_CCL_NS",
"CCL_LRS POINT MEASURE", "ALL", "DISTANCE",
"ZERO", "FIELDS", "M_DIRECTON")
MakeRouteEventLayer_lr("CCL_LRS_ROUTE", NewRouteKey,
connection1 + "INTR_CCL_NS",
"CCL_LRS POINT MEASURE", "INTR_CCL_NS Events", "#",
"ERROR_FIELD", "ANGLE_FIELD", "NORMAL", "ANGLE",
"LEFT", "POINT")
AddField_management("INTR_CCL_NS Events", "CITY", "TEXT", "#", "#", "100")
AddJoin_management("INTR_CCL_NS Events", "CITYNUMBER", citylimits,
"CITYNUMBER")
#CalculateField_management("INTR_CCL_NS Events", schema+"INTR_CCL_NS_Features.CITY", "!GIS_DEV.CITY_LIMITS.CITY!", "PYTHON_9.3") #Preupdate
CalculateField_management("INTR_CCL_NS Events",
schema + "INTR_CCL_NS_Features.CITY",
"!GIS.CITY_LIMITS.CITY!", "PYTHON_9.3")
RemoveJoin_management("INTR_CCL_NS Events", "#")
print "NonState_Int completed successfully."
def UniqueIDgen():
for i in range(87, 88):
c = str(i).zfill(3)
#print "filling in unique Route IDs for county %s" %c
expression = "LRS_ROUTE_PREFIX = 'L' AND LRS_COUNTY_PRE = '%s'" % c
layer = "County" + c
MakeFeatureLayer_management(target, layer, expression)
#this part of the script performs a couple types of dissolves to create a unique set of numbers in 4 characters for every route in a county
#first, do an unsplit dissolve for each local road in the county based on the RD, STS, and POD fields
#this creates nice segments from which to build the destination routes
Dissolve_management(layer, "in_memory/" + layer + "d1",
"RD;STS;POD;LRS_COUNTY_PRE", "GCID COUNT",
"SINGLE_PART", "UNSPLIT_LINES")
#add, calculate, and index a field for a join operation
#cant add index to in memory database so skip that part
AddField_management(layer + "d1", "ConCatRtName", "TEXT", "", "", "50",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management(layer + "d1", "RouteNum1", "TEXT", "", "", "6", "",
"NULLABLE", "NON_REQUIRED", "")
AddField_management(layer + "d1", "UniqueNum1", "TEXT", "", "", "3",
"", "NULLABLE", "NON_REQUIRED", "")
CalculateField_management(
layer + "d1", "ConCatRtName",
"""[LRS_COUNTY_PRE]&[RD] & [STS] & [POD] """, "VB", "")
#dissolve the unsplit dissolve layer to a multipart, full dissolve to get unique road names
Dissolve_management(layer + "d1", "in_memory/" + layer + "d2",
"ConCatRtName;RouteNum1", "", "MULTI_PART",
"DISSOLVE_LINES")
#A spatial sort here might be nice
#Calculate a unique 4 digit number for each road name
#I'm just using the Object ID to calculate the unique number string, with a spatial sort another incrementation method would be needed
#the road names should mostly be unique, so a spatial sort at this level would only be beneficial of there is POD field is the only road name distinction
#otherwise an attribute sort would be sufficient, if necessary
CalculateField_management("in_memory/" + layer + "d2", "RouteNum1",
"str(!OBJECTID!).zfill(4)", "PYTHON_9.3", "")
# add the unique id field and increment each duplicate road name part
# calculate that unique number back to the split dissolve
AddJoin_management(layer + "d1", "ConCatRtName", "County087d2",
"ConCatRtName", "KEEP_ALL")
CalculateField_management(layer + "d1", layer + "d1.RouteNum1",
"[" + layer + "d2.RouteNum1]", "VB", "")
#AddField_management("in_memory/"+layer+"d2", "UniqueNum1", "TEXT", "", "", "3", "", "NULLABLE", "NON_REQUIRED", "")
RemoveJoin_management(layer + "d1")
#try this spatial sort thing here
Sort_management("in_memory/" + layer + "d1",
"in_memory/" + layer + "d1_Sort",
"Shape ASCENDING;RouteNum1 ASCENDING", "LL")
#now we run the incrementer to calcualte the unique ID's
#the incrementer isnt working here, but it is calculating a unique ID on for the segments, and it is going it better and much faster than the join method
#it might be better to use the incrementer to do this calculation on the sorted table, then figure out the unique ID
Incrementer("in_memory/" + layer + "d1")
Delete_management("in_memory/" + layer + "d1")
Delete_management("in_memory/" + layer + "d2")
Delete_management("in_memory/" + layer + "d2_Sort")
def RouteCheck(RID):
#when running this function, pass the RID/LRS KEY Value into the function to update the desired RID
#RID is structured '030C0011800W0'
#Class should be L, C, or RM
print "what route number should be updated?"
#RID = '030C0011800W0'
Class = RID[3]
if Class in ("R", "M"):
Class = "RM"
else:
pass
print RID
RID_ = RID.replace('-', '_')
RIDExp = "RID = '" + RID + "'"
tablename = Class + RID_
print RIDExp
print "Updating route " + str(RID) + " in table " + str(RID_)
if Exists("UpdateGISPROD"):
print "this exists"
pass
else:
AddTable = Class + "P_NON_STATE_EVENTS"
MakeTableView_management(r"in_memory/" + AddTable, tablename + "_view",
"#")
MakeFeatureLayer_management(
NonState, "NonStateUpdate",
"((LRS_KEY LIKE '%C%' OR LRS_ROUTE_PREFIX = 'C') AND (MILEAGE_COUNTED = -1)) OR (LRS_DIR_OF_TRAVEL = 'P' and SURFACE = 'Propose')"
)
TableToTable_conversion(tablename + "_view", "in_memory", tablename,
RIDExp)
if str(GetCount_management(tablename)) == '0':
print "No Records to Calculate"
else:
try:
RemoveJoin_management("NonStateUpdate")
except:
print "no NonStateUpdate, creating the NonStateSystem layer"
MakeFeatureLayer_management(
NonState, "NonStateUpdate",
"(MILEAGE_COUNTED = -1 OR SURFACE = 'Propose')")
AddJoin_management("NonStateUpdate", "ID2", tablename,
"FID_NON_STATE_SYSTEM", "KEEP_COMMON")
print "Check the numbers one more time, and review"
print "start Edit session on NonStateUpdate now and type RouteCalc(RID) if it all looks good"
print "RteChk script completed successfully"
def STATE_INT():
print "add intersect intersection points that are state - state intersections and interchanges"
MakeFeatureLayer_management(interchange, 'INTR', "ON_STATE_NONSTATE = 'S'")
LocateFeaturesAlongRoutes_lr("INTR", "CCL_LRS_ROUTE", NewRouteKey,
"5 Feet", connection1 + "INTR_CCL",
"CCL_LRS POINT MEASURE", "ALL", "DISTANCE",
"ZERO", "FIELDS", "M_DIRECTON")
MakeRouteEventLayer_lr("CCL_LRS_ROUTE", NewRouteKey,
connection1 + "INTR_CCL", "CCL_LRS POINT MEASURE",
"INTR_CCL_Events", "#", "ERROR_FIELD",
"ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT")
AddField_management("INTR_CCL_Events", "CITY", "TEXT", "#", "#", "100")
AddField_management("INTR_CCL_Events", "CITYNUMBER", "Long", "#", "#", "#")
CalculateField_management("INTR_CCL_Events", "CITYNUMBER",
"int(!CCL_LRS![0:3])", "PYTHON_9.3")
AddJoin_management("INTR_CCL_Events", "CITYNUMBER", citylimits,
"CITYNUMBER")
#CalculateField_management("INTR_CCL_Events", schema+"INTR_CCL_Features.CITY", "!GIS_DEV.CITY_LIMITS.CITY!", "PYTHON_9.3") #Preupdate
CalculateField_management("INTR_CCL_Events",
schema + "INTR_CCL_Features.CITY",
"!GIS.CITY_LIMITS.CITY!", "PYTHON_9.3")
RemoveJoin_management("INTR_CCL_Events", "#")
print "State_Int completed successfully."
print "show lane classification referenced to city connecting link LRS"
MakeFeatureLayer_management(laneclass, 'LNCL')
#LANE CLASS LOOKUPS
LocateFeaturesAlongRoutes_lr("LNCL", connection1 + "CCL_LRS_ROUTE",
NewRouteKey, "#", connection1 + "LANECLASS_CCL",
"CCL_LRS LINE CCL_BEGIN CCL_END", "ALL",
"DISTANCE", "ZERO", "FIELDS", "M_DIRECTON")
AddField_management(connection1 + "LANECLASS_CCL", "Lanes", "LONG")
MakeTableView_management(connection1 + "LANECLASS_CCL", "LANES_LUT")
AddJoin_management("LANES_LUT", "LNCL_CLS_ID", connection1 + "LC_LN_CLS_ID",
"IAL_VALUE")
CalculateField_management("LANES_LUT", "CCL.DBO.LANECLASS_CCL.Lanes",
'Left([CCL.DBO.LC_LN_CLS_ID.IAL_MEANING],1)', "VB")
RemoveJoin_management("LANES_LUT", "CCL.DBO." + "LC_LN_CLS_ID")
OverlayRouteEvents_lr(
connection1 + "MAINTENANCE_CCL", "CCL_LRS LINE CCL_BEGIN CCL_END",
connection1 + "LANECLASS_CCL", "CCL_LRS LINE CCL_BEGIN CCL_END", "UNION",
connection1 + "CCL_Report_M", "CCL_LRS LINE CCL_MA_BEGIN CCL_MA_END",
"NO_ZERO", "FIELDS", "INDEX")
DissolveRouteEvents_lr(connection1 + "CCL_Report_M",
"CCL_LRS LINE CCL_MA_BEGIN CCL_MA_END",
"CITYNO;MAINT_DESC;CITY_NAME;Lanes",
connection1 + "CCL_Report_D",
"CCL_LRS LINE CCL_MA_BEGIN CCL_MA_END", "CONCATENATE",
"INDEX")
#cleanup border errors - make feature layers based on City, city number, and CCLLRS and delete where they are not consistent between Maintenance and Resolution sections
MakeTableView_management(connection1 + "CCL_Report", "Report_Clean1",
"CCL_LRS2 <> CCL_LRS")
def RouteFix(RID):
#when running this function, pass the RID/LRS KEY Value into the function to update the desired RID
#RID is structured '030C0011800W0'
#Class should be L, C, or RM
print "what route number should be updated?"
#RID = '030C0011800W0'
Class = RID[3]
if Class in ("R", "M"):
Class = "RM"
else:
pass
print RID
RID_ = RID.replace('-', '_')
tablename = Class + RID_
if RID == "000C":
RIDExp = "#"
else:
RIDExp = "RID = '" + RID + "'"
print "Updating route " + str(RID)
if Exists("UpdateGISPROD"):
pass
else:
AddTable = Class + "P_NON_STATE_EVENTS"
MakeTableView_management("in_memory/" + AddTable, tablename + "_view",
"#")
TableToTable_conversion(tablename + "_view", "in_memory", tablename,
RIDExp)
if str(GetCount_management(tablename)) == '0':
print "No Records to Calculate"
else:
try:
RemoveJoin_management("NonStateUpdate")
except:
print "no NonStateUpdate, creating the NonStateSystem layer"
#AddIndex_management("updatetblh", "FID_NON_STATE_SYSTEM", "ID2", "UNIQUE", "ASCENDING")
MakeFeatureLayer_management(
NonState, "NonStateUpdate",
"(MILEAGE_COUNTED = -1 OR SURFACE = 'Propose')")
AddJoin_management("NonStateUpdate", "ID2", tablename,
"FID_NON_STATE_SYSTEM", "KEEP_COMMON")
print "Check the numbers one more time, and review"
print "start Edit session on NonStateUpdate now and type RouteCalc(RID) if it all looks good"
print "RteChk script completed successfully & RteCalc script starting..."
Class = RID[3]
if Class in ("R", "M"):
Class = "RM"
else:
pass
print RID
tablename = Class + RID
SelectLayerByAttribute_management("NonStateUpdate", "NEW_SELECTION", "1=1")
CalculateField_management("NonStateUpdate",
"SHARED.NON_STATE_SYSTEM.LRS_BEG_CNTY_LOGMILE",
"[" + tablename + ".NEW_BEGLOG]", "VB", "")
CalculateField_management("NonStateUpdate",
"SHARED.NON_STATE_SYSTEM.LRS_END_CNTY_LOGMILE",
"[" + tablename + ".NEW_ENDLOG]", "VB", "")
CalculateField_management("NonStateUpdate",
"SHARED.NON_STATE_SYSTEM.LENGTH",
"[" + tablename + ".AdjLength]", "VB", "")
print "adjusted begin and end logmiles were recalculated to the source GISPROD database"
print "end your edit session"
#Delete_management("in_memory/"+tablename)"
print "RteFix script completed successfully"
def CRB():
print "querying the shared.NON_STATE_SYSTEM to obtain only urban classified primary C routes with mileage that should be counted and for resolution segments."
MakeFeatureLayer_management(
NonState, "NonStateCP",
"((LRS_KEY LIKE '%C%' OR LRS_ROUTE_PREFIX = 'C') AND (MILEAGE_COUNTED = -1)) OR (LRS_DIR_OF_TRAVEL = 'P' and SURFACE = 'Propose')"
)
print "querying the shared.NON_STATE_SYSTEM to obtain only urban classified NonPrimary C routes with mileage that should be counted and for resolution segments."
MakeFeatureLayer_management(
NonState, "NonStateCNP",
"(LRS_KEY LIKE '%C%' OR LRS_ROUTE_PREFIX = 'C') AND (MILEAGE_COUNTED = 0) AND (LRS_DIR_OF_TRAVEL = 'S') and (COUNTY_NUMBER <> 0)"
)
print "shared.Non_State_System has unique IDS that we desire to keep as persistent IDs for comparison with GeoMedia, so we are spatially intersecting the state boundary to keep them as is."
Buffer_analysis(StateBnd, "State_Boundary_1Mile", "5280 Feet", "FULL",
"ROUND", "NONE", "", "PLANAR")
MakeFeatureLayer_management("State_Boundary_1Mile", "StateBnd")
Intersect_analysis("NonStateCP #;StateBnd #",
"Non_State_Classified_Primary", "ALL", "-1 Unknown",
"LINE")
Intersect_analysis("NonStateCNP #;StateBnd #",
"Non_State_Classified_NonPrimary", "ALL", "-1 Unknown",
"LINE")
NonStateCP_fx = r'Non_State_Classified_Primary'
NonStateCNP_fx = r'Non_State_Classified_NonPrimary'
MakeFeatureLayer_management(NonStateCP_fx, "Non_State_Classified_Primary")
MakeFeatureLayer_management(NonStateCNP_fx,
"Non_State_Classified_NonPrimary")
CP_ET = 'CP_NON_STATE_EVENTS'
CNP_ET = 'CNP_NON_STATE_EVENTS'
MakeFeatureLayer_management(NUSYS, "Nusys_Extract",
"NSEC_SUB_CLASS <> 'R'")
print "creating primary C Non_State_Routes by the Shape Length"
MakeFeatureLayer_management(
"Non_State_Classified_Primary", "BackwardSegsCP",
"LRS_BACKWARDS = -1 AND (MILEAGE_COUNTED = -1 OR (LRS_DIR_OF_TRAVEL = 'P' and SURFACE = 'Propose'))"
)
FlipLine_edit("BackwardSegsCP")
Dissolve_management("Non_State_Classified_Primary", "CPRouteShapeLength",
"NQR_DESCRIPTION", "", "MULTI_PART", "DISSOLVE_LINES")
AddField_management("CPRouteShapeLength", "BCM", "DOUBLE", "", "", "", "",
"NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CPRouteShapeLength", "BCM", "0", "VB", "")
AddField_management("CPRouteShapeLength", "ECM", "DOUBLE", "", "", "", "",
"NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CPRouteShapeLength", "ECM",
"!Shape.length@miles!", "Python")
CreateRoutes_lr("CPRouteShapeLength", "NQR_DESCRIPTION",
destdb + "\CP_ShapeLengthRoute", "TWO_FIELDS", "BCM",
"ECM", "UPPER_LEFT", "1", "0", "IGNORE", "INDEX")
#Flip them back to the original direction
FlipLine_edit(in_features="BackwardSegsCP")
LocateFeaturesAlongRoutes_lr("Non_State_Classified_Primary",
"CP_ShapeLengthRoute", "NQR_DESCRIPTION",
"0 Feet", "CP_NON_STATE_EVENTS",
"RID LINE FMEAS TMEAS", "FIRST", "DISTANCE",
"ZERO", "FIELDS", "M_DIRECTON")
AddField_management("CP_NON_STATE_EVENTS", "AdjBegin", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "AdjEnd", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "CHG_BEGLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "CHG_ENDLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "NEW_BEGLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "NEW_ENDLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "AdjLength", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CP_NON_STATE_EVENTS", "CHANGE", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CP_NON_STATE_EVENTS",
"AdjBegin",
"round( [FMEAS] , 3 )",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"AdjEnd",
"round( [TMEAS] , 3 )",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"CHG_BEGLOG",
"[AdjBegin] - [LRS_BEG_CNTY_LOGMILE]",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"CHG_ENDLOG",
"[AdjEnd] - [LRS_END_CNTY_LOGMILE]",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"NEW_BEGLOG",
"[AdjBegin]",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"NEW_ENDLOG",
"[AdjEnd]",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"AdjLength",
"[AdjEnd] - [AdjBegin]",
"VB",
code_block="")
CalculateField_management("CP_NON_STATE_EVENTS",
"CHANGE",
"abs([LENGTH] - [AdjLength])",
"VB",
code_block="")
MakeRouteEventLayer_lr("CP_ShapeLengthRoute", "NQR_DESCRIPTION",
"CP_NON_STATE_EVENTS", "RID LINE FMEAS TMEAS",
"CP_LRS_Review_Events", "", "ERROR_FIELD",
"NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT",
"POINT")
print "CP-Rte Builder script completed successfully"
print "creating NonPrimary C Non_State_Routes by the Shape Length"
MakeFeatureLayer_management(
"Non_State_Classified_NonPrimary", "BackwardSegsCNP",
"LRS_BACKWARDS = -1 AND LRS_DIR_OF_TRAVEL = 'S' and COUNTY_NUMBER <> 0"
)
FlipLine_edit("BackwardSegsCNP")
Dissolve_management("Non_State_Classified_NonPrimary",
"CNPRouteShapeLength", "LRS_KEY", "", "MULTI_PART",
"DISSOLVE_LINES")
AddField_management("CNPRouteShapeLength", "BCM", "DOUBLE", "", "", "", "",
"NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CNPRouteShapeLength", "BCM", "0", "VB", "")
AddField_management("CNPRouteShapeLength", "ECM", "DOUBLE", "", "", "", "",
"NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CNPRouteShapeLength", "ECM",
"!Shape.length@miles!", "Python")
CreateRoutes_lr("CNPRouteShapeLength", "LRS_KEY",
destdb + "\CNP_ShapeLengthRoute", "TWO_FIELDS", "BCM",
"ECM", "UPPER_LEFT", "1", "0", "IGNORE", "INDEX")
#Flip them back to the original direction
FlipLine_edit(in_features="BackwardSegsCNP")
LocateFeaturesAlongRoutes_lr("Non_State_Classified_NonPrimary",
"CNP_ShapeLengthRoute", "LRS_KEY", "0 Feet",
"CNP_NON_STATE_EVENTS",
"RID LINE FMEAS TMEAS", "FIRST", "DISTANCE",
"ZERO", "FIELDS", "M_DIRECTON")
AddField_management("CNP_NON_STATE_EVENTS", "AdjBegin", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "AdjEnd", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "CHG_BEGLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "CHG_ENDLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "NEW_BEGLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "NEW_ENDLOG", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "AdjLength", "DOUBLE", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNP_NON_STATE_EVENTS", "CHANGE", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "AdjBegin",
"round( [FMEAS] , 3 )", "VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "AdjEnd",
"round( [TMEAS] , 3 )", "VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "CHG_BEGLOG",
"[AdjBegin] - [LRS_BEG_CNTY_LOGMILE]", "VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "CHG_ENDLOG",
"[AdjEnd] - [LRS_END_CNTY_LOGMILE]", "VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "NEW_BEGLOG",
"[AdjBegin]", "VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS", "NEW_ENDLOG", "[AdjEnd]",
"VB", "")
CalculateField_management("CNP_NON_STATE_EVENTS",
"AdjLength",
"[AdjEnd] - [AdjBegin]",
"VB",
code_block="")
CalculateField_management("CNP_NON_STATE_EVENTS",
"CHANGE",
"abs([LENGTH] - [AdjLength])",
"VB",
code_block="")
MakeRouteEventLayer_lr("CNP_ShapeLengthRoute", "LRS_KEY",
"CNP_NON_STATE_EVENTS", "RID LINE FMEAS TMEAS",
"CNP_LRS_Review_Events", "", "ERROR_FIELD",
"NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT",
"POINT")
AddField_management("CNPRouteShapeLength", "PersistentID", "LONG", "", "",
"", "", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNPRouteShapeLength", "Pbeg", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNPRouteShapeLength", "Pend", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
CalculateField_management("CNPRouteShapeLength", "PersistentID", "!ID2!",
"PYTHON_9.3", "")
TableToTable_conversion("CNPRouteShapeLength", "in_memory", "CNP_Events",
"")
endpoints = ["beg", "end"]
for pos in endpoints:
out_event = "CNP_Events_" + pos
print out_event
out_lyr = "CNP_Events_Features_" + pos
print out_lyr
outfield = "P" + pos
print outfield
if pos == "beg":
print "Will locate begin point"
routesettings = "LRS_KEY POINT BCM"
else:
print "locating end point"
routesettings = "LRS_KEY POINT ECM"
MakeRouteEventLayer_lr("CNP_ShapeLengthRoute", "LRS_KEY", "CNP_Events",
routesettings, out_event, "", "ERROR_FIELD",
"ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT",
"POINT")
LocateFeaturesAlongRoutes_lr(out_event, "CP_ShapeLengthRoute",
"NQR_DESCRIPTION", "500 Feet", out_lyr,
"RID POINT MEAS", "ALL", "DISTANCE",
"ZERO", "FIELDS", "M_DIRECTON")
AddJoin_management(out_lyr, "PersistentID", "CNPRouteShapeLength",
"PersistentID", "KEEP_ALL")
selexp = out_lyr + ".RID <> CNPRouteShapeLength.LRS_KEY"
print selexp
SelectLayerByAttribute_management(out_lyr, "NEW_SELECTION", selexp)
DeleteRows_management(out_lyr)
RemoveJoin_management(out_lyr)
AddJoin_management("CNPRouteShapeLength", "PersistentID", out_lyr,
"PersistentID", "KEEP_ALL")
#expression = "[CNP_Events_Features_Begin.MEAS]"
expression = "[" + out_lyr + ".MEAS]"
print expression
calcfield = "CNPRouteShapeLength." + outfield
#CNPRouteShapeLength.Pbeg
CalculateField_management("CNPRouteShapeLength", calcfield, expression,
"VB", "")
RemoveJoin_management("CNPRouteShapeLength", "")
#test flipped routes and calculate mileage and flip flag
AddField_management("CNPRouteShapeLength", "FlipTest", "LONG", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNPRouteShapeLength", "Adj_Beg", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
AddField_management("CNPRouteShapeLength", "Adj_End", "DOUBLE", "", "", "",
"", "NULLABLE", "NON_REQUIRED", "")
SelectLayerByAttribute_management("CNPRouteShapeLength", "NEW_SELECTION",
'"Pbeg" > "Pend"')
CalculateField_management("CNPRouteShapeLength", "FlipTest", "1", "VB", "")
CalculateField_management("CNPRouteShapeLength", "Adj_Beg", "!Pend!",
"Python", "")
CalculateField_management("CNPRouteShapeLength", "Adj_End", "!Pbeg!",
"Python", "")
SelectLayerByAttribute_management("CNPRouteShapeLength", "NEW_SELECTION",
'"Pbeg" < "Pend"')
CalculateField_management("CNPRouteShapeLength", "FlipTest", "0", "VB", "")
CalculateField_management("CNPRouteShapeLength", "Adj_Beg", "!Pbeg!",
"Python", "")
CalculateField_management("CNPRouteShapeLength", "Adj_End", "!Pend!",
"Python", "")
SelectLayerByAttribute_management("CNPRouteShapeLength", "NEW_SELECTION",
'"Adj_Beg" <= 0.003')
CalculateField_management("CNPRouteShapeLength", "Adj_Beg", "0", "Python",
"")
print "CNP-Rte Builder script completed successfully"
def StateHighwayCalibrate(theStateHighwaySegments):
#theStateHighwaySegments is defined as the roadway segments intended for calibration to the EXOR measures
#this function is being called by
#here are the GIS routes with measures extracted regularly from EXOR using the FME extraction and python route reference tools from DT00ar60
#these routes contain the current exor measures
#Smlrs = r'Database Connections\RO@sqlgisprod_GIS_cansys.sde\GIS_CANSYS.SHARED.SMLRS'
#need to define a route source represntative of the correct network year, about 2015
#should have K-10 on 23rd street still
Cmlrs = r'Database Connections\RO@sqlgisprod_GIS_cansys.sde\GIS_CANSYS.SHARED.CMLRS_2015'
from arcpy import FeatureClassToFeatureClass_conversion, FeatureVerticesToPoints_management, LocateFeaturesAlongRoutes_lr, CalculateField_management
from arcpy import env, MakeFeatureLayer_management, SelectLayerByAttribute_management, DeleteRows_management, AddJoin_management, AddField_management, RemoveJoin_management
env.overwriteOutput = 1
# Start by loading NG911 aggregated, conflated road centerlines to an in-memory feature class
#FeatureClassToFeatureClass_conversion(Roads, "in_memory", "RoadCenterlines", "StateKey1 IS NOT NULL ")
#FeatureClassToFeatureClass_conversion(Roads, "in_memory", "RoadCenterlines", "StateKey1 IS NOT NULL ")
MakeFeatureLayer_management(theStateHighwaySegments,
"CalibrateRoadCenterlines")
RoadCenterlines = "CalibrateRoadCenterlines"
#these are the two linear referencing networks we're going to use to calibrate the state highway system
#for iteration 2, no source data should refer to the state LRM, so we're only doing the County LRM
Lrm_Dict = {'COUNTY': Cmlrs}
#and this is the beginning and end of a line, for which we are going to create a vertex point
End_List = ['START', 'END']
# First, create points at the begin and end of each road centerline segment using Vertices to Points.
for end in End_List:
i_end_output = "in_memory/CalibrationPoint" + str(end)
FeatureVerticesToPoints_management(RoadCenterlines, i_end_output,
str(end))
#Iterate through the LRMs to bring them into memory and do the processing for each segment begin and end point!
for key, value in Lrm_Dict.items():
FeatureClassToFeatureClass_conversion(value, "in_memory",
"LRM" + str(key))
for end in End_List:
outtable = "in_memory/" + str(end) + "_" + str(key)
outstore = spampathfd + r"/" + str(end) + "_" + str(key)
outproperties = str(key) + "_LRS POINT MEAS_" + str(key)
if key == "STATE":
lrskey = str(key) + "_NQR_DESCRIPTION"
else:
lrskey = "NQR_DESCRIPTION"
LocateFeaturesAlongRoutes_lr(
"in_memory/CalibrationPoint" + str(end),
"in_memory/LRM" + str(key), lrskey, "500 Feet", outtable,
outproperties, "ALL", "DISTANCE", "ZERO", "FIELDS",
"M_DIRECTON")
#that LFAR function located begin/end segment points to ALL ROUTES within 500 feet of the segment endpoint
#for calibrating, we are only interested in the points and LFAR Results that where this query is NOT true:
qNotThisRoad = '"COUNTY_LRS" <> "KDOT_LRS_KEY"'
#so we will delete the records where this query is trye
SelectLayerByAttribute_management(
str(end) + "_" + str(key), "NEW_SELECTION", qNotThisRoad)
DeleteRows_management(str(end) + "_" + str(key))
#DeleteField_management(outtable, "Mileage_Length;Mileage_Logmile;ROUTE_PREFIX_TARGET;LRS_ROUTE_NUM_TARGET;LRS_UNIQUE_TARGET;Non_State_System_OBJECTID;LRS_BACKWARD;F_CNTY_2;T_CNTY_2;F_STAT_2;T_STAT_2;CountyKey2;MileFlipCheck;InLine_FID;SimLnFLag")
#TableToTable_conversion(outtable, ConflationDatabase, outstore)
#One Method, if using SQL Server, is to use table to table conversion to export to SQL server, then run these query in #CalcUsingSQLserver()
#If not using SQL server this will suffice, although if there are multiple orig FID's to the original data source FID, there's no logic or handling to discern between the many to one relationship.
#In the case of hte many to one, or duplicate Orig_FID in the measure table, it might be desirable to choose the closest result
#A few of the duplicates I reviewed had identical measure values, if that's always the case, then handling the duplicates is unnecessary
measfield = str(end) + "_" + str(key) + "_meas"
try:
AddField_management(theStateHighwaySegments, measfield,
"DOUBLE", "", "", "", "", "NULLABLE",
"NON_REQUIRED")
except:
print "could not add the field for calibrated measures"
jointable = str(end) + "_" + str(key)
AddJoin_management(theStateHighwaySegments, "OBJECTID", jointable,
"ORIG_FID", "KEEP_ALL")
exp = "!" + jointable + ".MEAS_" + str(key) + "!"
measfieldcalc = theStateHighwaySegments + "." + measfield
CalculateField_management(theStateHighwaySegments, measfieldcalc,
exp, "PYTHON")
RemoveJoin_management(theStateHighwaySegments)
# NEed to now test for direction again based on begin < end, handle flipping and assemble the routes
from arcpy import CreateRoutes_lr
CreateRoutes_lr("CalibrateRoadCenterlines", "KDOT_LRS_KEY",
"in_memory/Simplified_CreateRoutes_test1", "TWO_FIELDS",
"START_COUNTY_meas", "END_COUNTY_meas", "UPPER_LEFT", "1",
"0", "IGNORE", "INDEX")
def TnA():
try:
env.workspace = stagews
#copying oracle tables to memory
print str(datetime.datetime.now()) + ' copying oracle tables to memory'
FeatureClassToFeatureClass_conversion(sdeCDRS,"in_memory","Construction","#","ALERT_STATUS <> 3")
MakeQueryTable_management(sdeCDRSWZ,"wz1","USE_KEY_FIELDS","KANROAD.CDRS_WZ_DETAIL.CDRS_WZ_DETAIL_ID",
"""KANROAD.CDRS_WZ_DETAIL.CDRS_WZ_DETAIL_ID #;KANROAD.CDRS_WZ_DETAIL.CDRS_DETOUR_TYPE_ID #;
KANROAD.CDRS_WZ_DETAIL.WORK_ZONE_DESC #;KANROAD.CDRS_WZ_DETAIL.WORK_ZONE_SPEED_RESTRIC #;
KANROAD.CDRS_WZ_DETAIL.DETOUR_TYPE_TXT #;KANROAD.CDRS_WZ_DETAIL.DETOUR_SPEED_RESTRIC #;
KANROAD.CDRS_WZ_DETAIL.DETOUR_DESC #""", "#")
TableToTable_conversion("wz1", 'in_memory', 'wz')
#Joining the Oracle CDRS WZ table
print str(datetime.datetime.now()) + " Joining the Oracle CDRS WZ table"
MakeFeatureLayer_management("Construction", "ConstJoin")
AddJoin_management("ConstJoin","CDRS_WZ_DETAIL_ID","wz","KANROAD_CDRS_WZ_DETAIL_CDRS_WZ_DETAIL_ID","KEEP_ALL")
FeatureClassToFeatureClass_conversion("ConstJoin","in_memory","CDRS","#",'ConstJoin.ALERT_STATUS < 3', "#")
#reformatting the Route name for US routes
print str(datetime.datetime.now()) + " reformatting the Route name for US routes"
AddField_management("CDRS", "RouteName", "TEXT", "#", "10")
routenamed = '!Construction_BEG_LRS_ROUTE![0:1] +str(!Construction_BEG_LRS_ROUTE![3:6]).lstrip("0")' # calculation expression
#Calculate the Route names for User Display
print routenamed
CalculateField_management("CDRS", "RouteName", routenamed, "PYTHON_9.3","#")
AddField_management("CDRS", "STATUS", "TEXT", "#", "10")
AddField_management("CDRS", "Alert_Status_I", "LONG", "#", "#")
CalculateField_management("CDRS", "Alert_Status_I", '!Construction_ALERT_STATUS!' , "PYTHON_9.3", "#")
#Assigning projection for KanRoad CDRS Alert Route Layer
print str(datetime.datetime.now()) + " Assigning projection for KanRoad CDRS Alert Route Layer"
DefineProjection_management("CDRS", lambertCC)
#reformatting the Route name for US routes
print str(datetime.datetime.now()) + " reformatting the Route name for US routes"
MakeFeatureLayer_management("CDRS", "ACTIVERoutes", '"Construction_ALERT_STATUS" = 2' )
CalculateField_management("ACTIVERoutes","STATUS",'"Active"',"PYTHON_9.3","#")
MakeFeatureLayer_management("CDRS", "ClosedRoutes", '"Construction_ALERT_STATUS" = 2 AND "Construction_FEA_CLOSED" = 1')
CalculateField_management("ClosedRoutes","STATUS",'"Closed"',"PYTHON_9.3","#")
MakeFeatureLayer_management("CDRS", "PlannedRoutes", '"Construction_ALERT_STATUS" = 1' )
CalculateField_management("PlannedRoutes","STATUS",'"Planned"',"PYTHON_9.3","#")
#copying joined oracle tables to memory for loading in Wichway Schema
print str(datetime.datetime.now()) + " copying joined oracle tables to memory for loading in Wichway Schema"
FeatureClassToFeatureClass_conversion(sdeKandriveConstruction, "in_memory", "CDRS_Segments", "#", "#")
#delete rows in the destination feature class
DeleteRows_management("CDRS_Segments")
###############################################################################################################
# Maintainability information:
# 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.
###############################################################################################################
featuresToTransfer = list()
# searchCursorFields go to "in_memory\CDRS". (Input table)
searchCursorFields = ['SHAPE@', 'RouteName', 'Construction_BEG_STATE_LOGMILE', 'Construction_END_STATE_LOGMILE', 'Construction_BEG_COUNTY_NAME',
'Construction_ALERT_DATE', 'Construction_COMP_DATE', 'Construction_ALERT_TYPE_TXT', 'Construction_ALERT_DESC_TXT',
'Construction_VERT_RESTRICTION', 'Construction_WIDTH_RESTRICTION', 'Construction_TIME_DELAY_TXT',
'Construction_PUBLIC_COMMENT', 'wz_KANROAD_CDRS_WZ_DETAIL_DETOUR_TYPE_TXT',
'wz_KANROAD_CDRS_WZ_DETAIL_DETOUR_DESC', 'Construction_CONTACT_NAME', 'Construction_CONTACT_PHONE',
'Construction_CONTACT_EMAIL', 'Construction_ALERT_HYPERLINK', 'Alert_Status_I',
'Construction_FEA_CLOSED', 'STATUS', 'Construction_ALERT_DIREC_TXT', 'Construction_BEG_LONGITUDE',
'Construction_BEG_LATITUDE']
# insertCursorFields go to sdeKandriveConstruction. (Output table)
insertCursorFields = ['SHAPE@', 'RouteName', 'BeginMP', 'EndMP', 'County', 'StartDate', 'CompDate', 'AlertType', 'AlertDescription',
'HeightLimit', 'WidthLimit', 'TimeDelay', 'Comments', 'DetourType', 'DetourDescription',
'ContactName', 'ContactPhone', 'ContactEmail', 'WebLink', 'AlertStatus', 'FeaClosed', 'Status',
'AlertDirectTxt', 'X', 'Y', 'LoadDate']
cdrsSearchCursor = daSearchCursor(r"in_memory\CDRS", searchCursorFields, """ "Alert_Status_I" <> 3""")
for cdrsCursorItem in cdrsSearchCursor:
featureItem = list(cdrsCursorItem)
featureItem.append(starttime)
featuresToTransfer.append(featureItem)
##Debug
for feature in featuresToTransfer:
print feature
##
RemoveJoin_management("ConstJoin", "wz")
#truncating CDRS segments in WICHWAY SPATIAL
print str(datetime.datetime.now()) + " truncating CDRS segments in WICHWAY SPATIAL"
TruncateTable_management(sdeKandriveConstruction)
cdrsInsertCursor = daInsertCursor(sdeKandriveConstruction, insertCursorFields)
for cdrsFeature in featuresToTransfer:
insertOID = cdrsInsertCursor.insertRow(cdrsFeature)
print "Inserted a row with the OID of: " + str(insertOID)
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
print errorItem.args[0]
try:
del errorItem
except:
pass
raise
finally:
try:
del cdrsSearchCursor
except:
pass
try:
del cdrsInsertCursor
except:
pass
print "Updating route "+ str(RID)
if Exists("UpdateGISPROD"):
pass
else:
AddTable = Class+"_NON_STATE_EVENTS"
MakeTableView_management(r"C:/temp/Nusys_Check.gdb/"+AddTable, tablename+"_view","#")
TableToTable_conversion(tablename+"_view", "in_memory", tablename, RIDExp)
RecordCount = str(GetCount_management(tablename))
if RecordCount = '0':
print "No Records to Calculate"
else:
print "calculating "+RecordCount+" records"
try:
RemoveJoin_management("NonStateAll")
except:
print "no NonStateAll, creating the NonStateSystem layer"
#AddIndex_management("updatetblh", "FID_NON_STATE_SYSTEM", "ID2", "UNIQUE", "ASCENDING")
MakeFeatureLayer_management(NonState, "NonStateAll")
AddJoin_management("NonStateAll", "ID2", tablename, "FID_NON_STATE_SYSTEM", "KEEP_COMMON")
print "Check the numbers one more time, and review"
print "start Edit session on NonStateAll now and type /'Calc()/' if it all looks good"
def RouteCalc(RID):
Class = RID[3]
if Class in ("R", "M"):
Class = "RM"
else:
pass