def calibrationCCL_Edit():
print "deriving CCL LRS starting points and calibrations"
CCLEnd = "!" + schema + "CITY_CONNECTING_LINK_STATE.MAX_END_STATE_LOGMILE! - !" + schema + "CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!"
CCLBeg = "!" + schema + "CITY_CONNECTING_LINK_STATE.MIN_BEG_STATE_LOGMILE! - !" + schema + "CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!"
MakeFeatureLayer_management(LineFeatureClass, "CITY_CONNECTING_LINK_RESET")
resln = "CITY_CONNECTING_LINK_RESET"
AddField_management(resln, "CCL_BEGIN", "DOUBLE", 12, 3)
AddField_management(resln, "CCL_END", "DOUBLE", 12, 3)
AddJoin_management("CITY_CONNECTING_LINK_RESET", "CCL_LRS",
connection1 + "CITY_CONNECTING_LINK_STATE_D", "CCL_LRS",
"KEEP_ALL")
CalculateField_management(resln, "CCL_BEGIN", CCLBeg, "PYTHON")
CalculateField_management(resln, "CCL_END", CCLEnd, "PYTHON")
print "calibrating LRS - point calibration method"
#statecalpoints = stateroutelyr+"_Point"
#print statecalpoints
#MakeFeatureLayer_management(statecalpoints, "smlrs_pt")
#print connection1+"CITY_CONNECTING_LINK_STATE_D"
#MakeFeatureLayer_management(connection1+"CITY_CONNECTING_LINK_STATE_D", "dissolved_res_sects")
#intersects = ["dissolved_res_sects", "smlrs_pt"]
#Intersect_analysis(intersects,connection0+"\CALIBRATION_POINTS_CCL","ALL","#","POINT")
#print connection1+"CALIBRATION_POINTS_CCL"
#MakeFeatureLayer_management(connection1+"CALIBRATION_POINTS_CCL", "Calibrators")
#querystr = "Substring( CCL_LRS,4, 12)<> LRS_ROUTE"
#SelectLayerByAttribute_management("Calibrators","NEW_SELECTION",querystr)
#DeleteRows_management("Calibrators")
MakeFeatureLayer_management(connection1 + "CITY_CONNECTING_LINK_STATE",
"CCL_sections")
#DeleteIdentical_management("Calibrators","LRS_KEY;POINT_X;POINT_Y;POINT_M","#","0")
AddField_management("CCL_sections", "CCL_BEGIN", "DOUBLE", "#", "#", "#",
"#", "NULLABLE", "NON_REQUIRED", "#")
AddField_management("CCL_sections", "CCL_END", "DOUBLE", "#", "#", "#",
"#", "NULLABLE", "NON_REQUIRED", "#")
AddJoin_management("CCL_sections", "CCL_LRS", "dissolved_res_sects",
"CCL_LRS", "KEEP_ALL")
CalculateField_management(
"CCL_sections", schema + "CITY_CONNECTING_LINK_STATE.CCL_BEGIN", "!" +
schema + "CITY_CONNECTING_LINK_STATE.MIN_BEG_STATE_LOGMILE! - !" +
schema + "CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!",
"PYTHON", "#")
CalculateField_management(
"CCL_sections", schema + "CITY_CONNECTING_LINK_STATE.CCL_END", "!" +
schema + "CITY_CONNECTING_LINK_STATE.MAX_END_STATE_LOGMILE! - !" +
schema + "CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!",
"PYTHON", "#")
#AddField_management(connection1+"CALIBRATION_POINTS_CCL","CCL_MEASURE", "DOUBLE", 12, 3)
#CalculateField_management("Calibrators","CCL_MEASURE","!POINT_M! - !MIN_BEG_STATE_LOGMILE!","PYTHON","#")
CreateRoutes_lr(LineFeatureClass, NewRouteKey,
connection1 + NewRoute + "base", "TWO_FIELDS", NewBeg,
NewEnd, "UPPER_LEFT", "1", "0", "IGNORE", "INDEX")
CalibrateRoutes_lr(connection1 + "CCL_LRS_ROUTEbase", "CCL_LRS",
r"D:\workspaces\pythontests.gdb\CCL_Point_Calibrators",
"point_LRS", "point_Loc", connection1 + "CCL_LRS_ROUTE",
"MEASURES", "10 Feet", "BETWEEN", "NO_BEFORE",
"NO_AFTER", "NO_IGNORE", "KEEP", "INDEX")
AddField_management(connection1 + NewRoute, "NETWORKDATE", "DATE")
CalculateField_management(connection1 + NewRoute, "NETWORKDATE",
"datetime.datetime.now( )", "PYTHON_9.3", "#")
MakeFeatureLayer_management(connection1 + "CCL_LRS_ROUTE", NewRoute)
def CountyCode(lyr):
MakeTableView_management(Kdotdbfp+"\NG911_County", "NG911_County")
AddJoin_management(lyr,"COUNTY_L","NG911_County", "CountyName", "KEEP_COMMON")
CalculateField_management(lyr,"KDOT_COUNTY_L","!NG911_County.CountyNumber!","PYTHON_9.3","#")
RemoveJoin_management(lyr)
AddJoin_management(lyr,"COUNTY_R","NG911_County", "CountyName", "KEEP_COMMON")
CalculateField_management(lyr,"KDOT_COUNTY_R","!NG911_County.CountyNumber!","PYTHON_9.3","#")
RemoveJoin_management(lyr)
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 AttribFields(fc, tbl, layer_name, table_name, workspace):
#Updates the crossing type attribute values in the GIS database from the CANSYS table. I believe this should work but needs to be tested more.
try:
MakeFeatureLayer_management(fc, layer_name)
MakeQueryTable_management(tbl, table_name, "USE_KEY_FIELDS",
"CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGID", "#",
"#")
AddJoin_management(layer_name, "CROSSINGID", table_name, "CROSSINGID",
"KEEP_ALL")
SelectLayerByAttribute_management(
layer_name, "NEW_SELECTION",
"CIIMS.Static_Crossings.CROSSINGTYPE <> vwcrossings3.CROSSINGTYPE")
with da.Editor(
workspace) as edit: # @UnusedVariable @UndefinedVariable
CalculateField_management(layer_name,
'CIIMS.Static_Crossings.CROSSINGTYPE',
'!vwcrossings3.CROSSINGTYPE!',
'PYTHON_9.3')
CalculateField_management(layer_name,
"CIIMS.Static_Crossings.LOADDATE",
"datetime.datetime.now( )", "PYTHON_9.3",
"#")
del layer_name, fc, table_name, tbl
print "attrib fields updated for crossing type"
except ExecuteError:
print(GetMessages(2))
endingTime = datetime.datetime.now()
ScriptStatusLogging('POINT_UPDATE_PROD.py', 'CIIMS.Static_Crossings',
scriptFailure, startingTime, endingTime,
GetMessages(2))
def LatLongFields(fc, tbl, layer_name, table_name, workspace):
#Updates the XY attributes values in the GIS database from the CANSYS table
try:
MakeFeatureLayer_management(fc, layer_name)
MakeQueryTable_management(tbl, table_name, "USE_KEY_FIELDS",
"CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGID", "#",
"#")
AddJoin_management(layer_name, "CROSSINGID", table_name, "CROSSINGID",
"KEEP_ALL")
#select the rows where the CIIMS position has been changed
SelectLayerByAttribute_management(
layer_name, "NEW_SELECTION",
"CIIMS.Static_Crossings.CROSSINGLATITUDE <> vwcrossings3.CROSSINGLATITUDE OR CIIMS.Static_Crossings.CROSSINGLONGITUDE <> vwcrossings3.CROSSINGLONGITUDE"
)
with da.Editor(
workspace) as edit: # @UnusedVariable @UndefinedVariable
CalculateField_management(
layer_name, 'CIIMS.Static_Crossings.CROSSINGLATITUDE',
'!vwcrossings3.CROSSINGLATITUDE!', 'PYTHON_9.3')
CalculateField_management(
layer_name, 'CIIMS.Static_Crossings.CROSSINGLONGITUDE',
'!vwcrossings3.CROSSINGLONGITUDE!', 'PYTHON_9.3')
CalculateField_management(layer_name,
"CIIMS.Static_Crossings.LOADDATE",
"datetime.datetime.now( )", "PYTHON_9.3",
"#")
del layer_name, fc, table_name, tbl
except ExecuteError:
print(GetMessages(2))
endingTime = datetime.datetime.now()
ScriptStatusLogging('POINT_UPDATE_PROD.py', 'CIIMS.Static_Crossings',
scriptFailure, startingTime, endingTime,
GetMessages(2))
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 UpdateGISPROD_NON_STATE_Check():
print "what route number should be updated?"
try:
RID = GetParameterAsText(0)
except:
RID = '460C0387500W0'
RIDExp = "RID = '" + RID + "'"
MakeTableView_management(r"C:\temp\Nusys_Check.gdb\NON_STATE_EVENTS",
"UpdateGISPROD", "#", "in_memory")
# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script
# The following inputs are layers or table views: "UpdateGISPROD"
TableToTable_conversion(in_rows="UpdateGISPROD",
out_path="in_memory",
out_name="updatetblh",
RIDExp,
"#",
config_keyword="")
AddIndex_management("updatetblh", "FID_NON_STATE_SYSTEM", "ID2", "UNIQUE",
"ASCENDING")
MakeFeatureLayer_management(NonState, "NonStateAll")
AddJoin_management("NonStateAll", "ID2", "updatetblh",
"FID_NON_STATE_SYSTEM", "KEEP_COMMON")
print "Check the numbers one more time, and review"
print "start Edit session on NON_STATE_SYSTEM now and type /'Calc()/' if it all looks good "
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 CrashOffsetPoints(gdb):
#buffer and intersect the crash location to an offset distance, and select the points that hit the "on Road"
from arcpy import SelectLayerByAttribute_management, AddJoin_management, Intersect_analysis, Buffer_analysis
Buffer_analysis(
in_features="Geocoding Result: Geocoding_Result_9",
out_feature_class=
"//gisdata/arcgis/GISdata/Accident Geocode/GC_OFFSET_20150210.gdb/Geocoding_Result_9_Buffer",
buffer_distance_or_field="AT_ROAD_KDOT_DIST_FEET",
line_side="FULL",
line_end_type="ROUND",
dissolve_option="NONE",
dissolve_field="",
method="PLANAR")
AddJoin_management(in_layer_or_view="RoadCenterline",
in_field="SEGID",
join_table="RoadAlias",
join_field="RoadAlias.SEGID",
join_type="KEEP_ALL")
Intersect_analysis(
in_features="Geocoding_Result_9_Buffer #;RoadCenterline #",
out_feature_class=
"//gisdata/arcgis/GISdata/Accident Geocode/GC_OFFSET_20150210.gdb/Geocoding_Result_9_Buffer_In",
join_attributes="ALL",
cluster_tolerance="-1 Unknown",
output_type="POINT")
SelectLayerByAttribute_management(
in_layer_or_view="Geocoding_Result_9_Buffer_In",
selection_type="NEW_SELECTION",
where_clause=
"ON_ROAD_KDOT_NAME like RoadAlias_KDOT_ROUTENAME OR ON_ROAD_KDOT_NAME like RoadCenterline_RD"
)
def OffsetDirectionMatrix1():
#select the intersected coordinate that best describes the reported location of the on road from the intersection based on the CrashOffsetPoints function
from arcpy import AddXY_management, AddJoin_management, ListFields, da, SelectLayerByAttribute_management, AddFieldDelimiters
GeocodedLayer = 'Geocoding Result: Geocoding_Result_9'
IntersectLayer = 'Geocoding_Result_9_Buffer_In'
AddXY_management(IntersectLayer)
AddJoin_management(IntersectLayer, "ACCIDENT_KEY", GeocodedLayer,
"ACCIDENT_KEY")
CursorFieldList = [
'X', 'Y', 'AT_ROAD_KDOT_DIRECTION', 'POINT_X', 'POINT_Y', 'OBJECTID',
'ACCIDENT_KEY'
]
# cursor to add to list the Accident IDs and Object IDs
CoordFinder = da.SearchCursor(IntersectLayer,
CursorFieldList) # @UndefinedVariable
rowDictionary = dict()
for row in CoordFinder:
rowDictionary[row[5]] = row
try:
del CoordFinder
except:
print "cursor hung"
for keyname in rowDictionary.keys():
rowOfInterest = rowDictionary[keyname]
if str(rowOfInterest[2]) == "E":
print str(rowOfInterest[2])
coordlist = []
OffsetCoord = rowOfInterest[3]
coordlist.append(OffsetCoord)
print coordlist
FinalCoordinate = max(coordlist)
FinalCoordInt = int(FinalCoordinate)
print FinalCoordinate
def OffsetDirectionMatrix(gdb):
#select the intersected coordinate that best describes the reported location of the on road from the intersection based on the CrashOffsetPoints function
from arcpy import AddXY_management, AddJoin_management, ListFields, da, SelectLayerByAttribute_management, AddFieldDelimiters
GeocodedLayer = 'Geocoding Result: Geocoding_Result_9'
IntersectLayer = 'Geocoding_Result_9_Buffer_In'
AddXY_management(IntersectLayer)
AddJoin_management(IntersectLayer, "ACCIDENT_KEY", GeocodedLayer,
"ACCIDENT_KEY")
FieldsList = ListFields(IntersectLayer)
CursorFieldList = [
'X', 'Y', 'AT_ROAD_KDOT_DIRECTION', 'POINT_X', 'POINT_Y', 'OBJECTID',
'ACCIDENT_KEY'
]
# cursor to add to list the Accident IDs and Object IDs
CoordFinder = da.SearchCursor(IntersectLayer,
CursorFieldList) # @UndefinedVariable
coordlist = []
rowDictionary = dict()
for row in CoordFinder:
print
#print('{0}, {1}, {2}, {3}, {4}'.format(row[0], row[1], row[2], row[3], row[4]))
if str(row[2]) == "E":
print row[0]
EastCoord = max(row[0], row[3])
coordlist.append(EastCoord)
rowDictionary
print coordlist
FinalEastCoordinate = max(coordlist)
FinalEastCoordInt = int(FinalEastCoordinate)
print FinalEastCoordinate
CoordSelExpression = 'POINT_X -' + str(FinalEastCoordInt) + " < 1"
SelectLayerByAttribute_management(IntersectLayer, "NEW_SELECTION",
CoordSelExpression)
def LatLongFields(fc, tbl, layer_name, table_name, workspace):
#Updates the XY attributes values in the GIS database from the CANSYS table
try:
MakeFeatureLayer_management(fc, layer_name)
MakeTableView_management(tbl, table_name)
AddJoin_management(layer_name, "CROSSINGID", table_name, "CROSSINGID",
"KEEP_ALL")
SelectLayerByAttribute_management(
layer_name, "NEW_SELECTION",
"CIIMS.Static_Crossings.CROSSINGLATITUDE <> CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGLATITUDE OR CIIMS.Static_Crossings.CROSSINGLONGITUDE <> CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGLONGITUDE"
)
with da.Editor(workspace) as edit:
CalculateField_management(
layer_name, 'CIIMS.Static_Crossings.CROSSINGLATITUDE',
'!CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGLATITUDE!', 'PYTHON_9.3')
CalculateField_management(
layer_name, 'CIIMS.Static_Crossings.CROSSINGLONGITUDE',
'!CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGLONGITUDE!', 'PYTHON_9.3')
CalculateField_management(layer_name,
"CIIMS.Static_Crossings.LOADDATE",
"datetime.datetime.now( )", "PYTHON_9.3",
"#")
del layer_name, fc, table_name, tbl
except ExecuteError:
print(GetMessages(2))
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 PointDelete(
fc, layer_name, tbl, table_name
): #delete rows from SDE CIIMS that are removed from CANSYS CIIMS
MakeFeatureLayer_management(fc, layer_name)
MakeTableView_management(tbl, table_name)
AddJoin_management(layer_name, "CROSSINGID", table_name, "CROSSINGID",
"KEEP_ALL")
SelectLayerByAttribute_management(
layer_name, "NEW_SELECTION",
"CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGID IS NULL")
DeleteRows_management("Static_Crossings")
del fc, layer_name, tbl, table_name
print "Delete function completed"
def join_table_to_tracts(self, output_table):
"""
Method for creating an editable layer and joining
the Zonal Statistics table to the census tracts shapefile
:param str output_table: Path for the zonal statistics table that was created
"""
tracts_layer = "tracts_layer"
MakeFeatureLayer_management(self.tracts_file, tracts_layer)
attribute = "GEOID10"
AddJoin_management(tracts_layer, attribute, output_table, attribute)
self.run_ordinary_least_squres(tracts_layer)
def FIMS_GIS(FMIS_ADD):
#arcpy.MakeFeatureLayer_management(CPMSlyr, 'CPMS', ProjectSelect)
#only process the new rows in FMIS_ADD
MakeFeatureLayer_management(CPMSlyr, 'CPMS')
MakeFeatureLayer_management(CountyLyr, 'County')
MakeFeatureLayer_management(HPMSlyr, 'HPMS')
MakeFeatureLayer_management(MPOLyr, 'MPO')
MakeFeatureLayer_management(CONGDistlyr, 'CONG')
MakeFeatureLayer_management(ws + "/Polygons", 'Polygons')
#make the polygon analysis layer for Districts, Counties, and MPOs
#arcpy.Union_analysis("CONG #;MPO #;County #",ws+"/Polygons","ALL","1 feet","GAPS")
MakeTableView_management(FMIS_ADD, 'CPMS_STAGING_TMP')
AddJoin_management("CPMS", "PROJECT_ID", "CPMS_STAGING_TMP",
"PROJECT_NUMBER", "KEEP_COMMON")
Output_Event_Table_Properties = 'RID LINE CNTY_BEG CNTY_END'
outtblP = ws + "/FIMS_EventTableAreas"
outtblH = ws + "/FIMS_EventTableLines"
if Exists(outtblP):
Delete_management(outtblP)
#consider/testing running "in memory"
LocateFeaturesAlongRoutes_lr('CPMS', 'HPMS', "Route_ID", "30 Feet",
outtblH, Output_Event_Table_Properties,
"FIRST", "DISTANCE", "NO_ZERO", "FIELDS",
"M_DIRECTON")
MakeRouteEventLayer_lr("HPMS", "Route_ID", "FIMS_EventTableLines",
"rid LINE CNTY_BEG CNTY_END",
"FIMS_EventTableLineLyr", "#", "ERROR_FIELD",
"NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT",
"POINT")
Intersect_analysis("FIMS_EventTableLineLyr #;HPMS #;Polygons #",
ws + "/FMIS_Data", "ALL", "#", "LINE")
Dissolve_management(
"FMIS_Data", ws + r"/HPMS_Data",
"PROJECT_ID;F_SYSTEM_V;NHS_VN;DISTRICT_1;COUNTY_NUMBER;ID_1", "#",
"MULTI_PART", "UNSPLIT_LINES")
# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script
LocateFeaturesAlongRoutes_lr("HPMS_Data", "HPMS", "Route_ID", "0 Feet",
ws + "/FIMS_EventTable",
"RID LINE CNTY_BEG CNTY_END", "FIRST",
"DISTANCE", "NO_ZERO", "FIELDS", "M_DIRECTON")
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 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 AttribFields(fc, tbl, layer_name, table_name, workspace):
#Updates the crossing type attribute values in the GIS database from the CANSYS table. I believe this should work but needs to be tested more.
try:
MakeFeatureLayer_management(fc, layer_name)
MakeTableView_management(tbl, table_name)
AddJoin_management(layer_name, "CROSSINGID", table_name, "CROSSINGID",
"KEEP_ALL")
SelectLayerByAttribute_management(
layer_name, "NEW_SELECTION",
"CIIMS.Static_Crossings.CROSSINGTYPE <> CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGTYPE"
)
with da.Editor(workspace) as edit:
CalculateField_management(
layer_name, 'CIIMS.Static_Crossings.CROSSINGTYPE',
'!CIIMS.CIIMS_VWCROSSINGGIS3.CROSSINGTYPE!', 'PYTHON_9.3')
CalculateField_management(layer_name,
"CIIMS.Static_Crossings.LOADDATE",
"datetime.datetime.now( )", "PYTHON_9.3",
"#")
del layer_name, fc, table_name, tbl
print "attrib fields updated for crossing type"
except ExecuteError:
print(GetMessages(2))
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."
"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"
print "deriving CCL LRS starting points and calibrations"
CCLEnd = "!CCL.DBO.CITY_CONNECTING_LINK_STATE.MAX_END_STATE_LOGMILE!- !CCL.DBO.CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!"
CCLBeg = "!CCL.DBO.CITY_CONNECTING_LINK_STATE.MIN_BEG_STATE_LOGMILE! - !CCL.DBO.CITY_CONNECTING_LINK_STATE_D.MIN_BEG_STATE_LOGMILE!"
MakeFeatureLayer_management(LineFeatureClass, "CITY_CONNECTING_LINK_RESET")
resln = "CITY_CONNECTING_LINK_RESET"
AddField_management(resln, "CCL_BEGIN", "DOUBLE", 12, 3)
AddField_management(resln, "CCL_END", "DOUBLE", 12, 3)
AddJoin_management("CITY_CONNECTING_LINK_RESET", "CCL_LRS",
connection1 + "CITY_CONNECTING_LINK_STATE_D", "CCL_LRS",
"KEEP_ALL")
CalculateField_management(resln, "CCL_BEGIN", CCLBeg, "PYTHON")
CalculateField_management(resln, "CCL_END", CCLEnd, "PYTHON")
print "calibrating LRS - point calibration method"
FeatureVerticesToPoints_management(LineFeatureClass,
connection1 + "CALIBRATION_POINTS", "ALL")
LocateFeaturesAlongRoutes_lr(connection1 + "CALIBRATION_POINTS",
ReferenceRoute, ReferenceRouteKey, "1 Feet",
connection1 + "CP_MEAS", "RefKey POINT MEASURE",
"ALL", "DISTANCE", "ZERO", "FIELDS", "M_DIRECTON")
querystr = str("RefKey = " + ReferenceRouteKey)
MakeTableView_management(connection1 + "CP_MEAS", "CalibrationEvents",
querystr, "#")
Delete_management(stagews)
CreateFileGDB_management(stageDB, "CDRS.gdb")
env.workspace = stagews
print str(datetime.datetime.now()) + " manipulating the Oracle CDRS layers"
MakeFeatureLayer_management(sdeCDRS, "Construction", '#')
FeatureClassToFeatureClass_conversion(
"Construction", stagews, "CDRS_RAW", "#",
"""CDRS_ALERT_ROUTE_ID "CDRS_ALERT_ROUTE_ID" true false false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,CDRS_ALERT_ROUTE_ID,-1,-1;AlertID "AlertID" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_ID,-1,-1;AlertDate "AlertDate" true true false 36 Date 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_DATE,-1,-1;AlertStatus "AlertStatus" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_STATUS,-1,-1;FeaClosed "FeaClosed" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,FEA_CLOSED,-1,-1;District "District" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,DISTRICT,-1,-1;Area "Area" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,AREA,-1,-1;LRSKey "LRSKey" true true false 19 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_LRS_KEY,-1,-1;LRSRoute "LRSRoute" true true false 12 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_LRS_ROUTE,-1,-1;County "County" true true false 20 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_COUNTY_NAME,-1,-1;CountyNumber "CountyNumber" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_COUNTY_NUMBER,-1,-1;AlertType "AlertType" true true false 50 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_TYPE_TXT,-1,-1;AlertDescription "AlertDescription" true true false 50 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_DESC_TXT,-1,-1;BeginMP "BeginMP" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_STATE_LOGMILE,-1,-1;BegRP "BegRP" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,BEG_REF_POST,-1,-1;EndMP "EndMP" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,END_STATE_LOGMILE,-1,-1;EndRP "EndRP" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,END_REF_POST,-1,-1;Direction "Direction" true true false 12 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_DIREC_TXT,-1,-1;StartDate "StartDate" true true false 36 Date 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,START_DATE,-1,-1;CompDate "CompDate" true true false 36 Date 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,COMP_DATE,-1,-1;ExpireDate "ExpireDate" true true false 36 Date 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,EXPIRE_DATE,-1,-1;TimeDelay "TimeDelay" true true false 30 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,TIME_DELAY_TXT,-1,-1;WZDetailId "WZDetailId" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,CDRS_WZ_DETAIL_ID,-1,-1;WidthLimit "WidthLimit" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,WIDTH_RESTRICTION,-1,-1;HeightLimit "HeightLimit" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,VERT_RESTRICTION,-1,-1;WeightLimit "WeightLimit" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,WEIGHT_RESTRICTION,-1,-1;SpeedLimit "SpeedLimit" true true false 8 Double 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,SPEED_RESTRICTION,-1,-1;INTERNAL_COMMENT "INTERNAL_COMMENT" true true false 4000 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,INTERNAL_COMMENT,-1,-1;Comments "Comments" true true false 4000 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,PUBLIC_COMMENT,-1,-1;PUBLIC_VIEW "PUBLIC_VIEW" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,PUBLIC_VIEW,-1,-1;RPT_BY_NAME "RPT_BY_NAME" true true false 50 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,RPT_BY_NAME,-1,-1;RPT_BY_PHONE "RPT_BY_PHONE" true true false 15 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,RPT_BY_PHONE,-1,-1;RPT_BY_EMAIL "RPT_BY_EMAIL" true true false 40 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,RPT_BY_EMAIL,-1,-1;ContactName "ContactName" true true false 50 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,CONTACT_NAME,-1,-1;ContactPhone "ContactPhone" true true false 15 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,CONTACT_PHONE,-1,-1;ContactEmail "ContactEmail" true true false 30 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,CONTACT_EMAIL,-1,-1;OfficeName "OfficeName" true true false 30 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,OFFICE_NAME,-1,-1;NEW_NOTIFICATION "NEW_NOTIFICATION" true true false 8 Double 10 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,NEW_NOTIFICATION,-1,-1;ALERT_INSERT_DT "ALERT_INSERT_DT" true true false 36 Date 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_INSERT_DT,-1,-1;WebLink "WebLink" true true false 500 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ALERT_HYPERLINK,-1,-1;SITE_CR "SITE_CR" true true false 18 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,SITE_CR,-1,-1;LINE_COLOR "LINE_COLOR" true true false 8 Double 0 32 ,First,#,KANROAD.CDRS_ALERT_ROUTE,LINE_COLOR,-1,-1;GIS_VIEW "GIS_VIEW" true true false 2 Short 0 2 ,First,#,KANROAD.CDRS_ALERT_ROUTE,GIS_VIEW,-1,-1;DCAM_COMMENT "DCAM_COMMENT" true true false 1024 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,DCAM_COMMENT,-1,-1;DCAM_DATE "DCAM_DATE" true true false 12 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,DCAM_DATE,-1,-1;DISPLAY_MAP "DISPLAY_MAP" true true false 2 Short 0 1 ,First,#,KANROAD.CDRS_ALERT_ROUTE,DISPLAY_MAP,-1,-1;OBJECTID "OBJECTID" true false false 4 Long 0 38 ,First,#,KANROAD.CDRS_ALERT_ROUTE,OBJECTID,-1,-1;ROUTE "ROUTE" true true false 10 Text 0 0 ,First,#,KANROAD.CDRS_ALERT_ROUTE,ROUTE,-1,-1""",
"DEFAULTS")
MakeTableView_management(sdeCDRSWZ, "detail", "#", "#")
MakeFeatureLayer_management(stagews + "//CDRS_RAW", "ConstructionJoin")
print str(datetime.datetime.now()) + " Joining the Oracle CDRS WZ table"
AddJoin_management("ConstructionJoin", "CDRS_WZ_DETAIL_ID", "detail",
"CDRS_WZ_DETAIL_ID", "KEEP_ALL")
FeatureClassToFeatureClass_conversion(
"ConstructionJoin", stagews, "CDRS_DETAIL", "CDRS_RAW.ALERT_STATUS <> 3"
) #EDIT to remove Alert Status = 3 to remove suspended projects from CDRS 2013/11/13 - has to be here to be queryable
print str(
datetime.datetime.now()) + " reformatting the Route name for US routes"
AddField_management("CDRS_DETAIL", "RouteName", "TEXT", "#", "10")
routenamed = '!BEG_LRS_ROUTE![0:1] +str(!BEG_LRS_ROUTE![3:6]).lstrip("0")' # calculation expression
CalculateField_management("CDRS_DETAIL", "RouteName", routenamed, "PYTHON_9.3",
"#")
AddField_management("CDRS_DETAIL", "STATUS", "TEXT", "#", "10")
AddField_management("CDRS_DETAIL", "Alert_Status_I", "LONG", "#", "#")
CalculateField_management("CDRS_DETAIL", "Alert_Status_I", '!ALERT_STATUS!',
"PYTHON_9.3", "#")
r"Database Connections\\ATLASPROD.odc\\NM3.NM_MEMBERS", "Members")
MakeTableView_management(
r"Database Connections\\ATLASPROD.odc\\NM3.NM_ELEMENTS", "Elements")
lyrlist = linelyrlist
print lyrlist
for lyr in lyrlist:
lyrname = str(lyr) + "_C"
addlyr = ws + "\\" + tempmdb + "\\" + lyr
MakeFeatureLayer_management(addlyr, lyrname)
lyr = lyr + "_C"
IDField = ListFields(lyr, "*NE_ID*", "Integer")
for field in IDField:
PKfield = "{}".format(field.name)
AddJoin_management(lyr, PKfield, "Members", "NM_NE_ID_IN",
"KEEP_COMMON")
AddJoin_management(lyr, "NM_NE_ID_OF", "Elements", "NE_ID",
"KEEP_COMMON")
print str(lyr) + " elements table joined"
for lyr in linelyrlist:
print lyr + "_C is the layer I'm linear referencing now..."
outlyr = ws + "\\" + tempgdb + "\\" + str(lyr) + "_ln_1"
lyr = lyr + "_C"
LocateFeaturesAlongRoutes_lr(
lyr, "CMLRS", "LRS_KEY", '0.5 feet', outlyr,
"LRS_KEY LINE Beg_Cnty_Logmile End_Cnty_Logmile", "FIRST", "DISTANCE",
"NO_ZERO", "FIELDS", "NO_M_DIRECTION")
MakeRouteEventLayer_lr("CMLRS", "LRS_KEY", outlyr,
"LRS_KEY LINE Beg_Cnty_Logmile End_Cnty_Logmile",
lyr + "_Events", "#", "ERROR_FIELD",
mxd = mapping.MapDocument("CURRENT")
env.MTolerance = MTolerance
env.MResolution = MResolution
#Copy Network Layers from CANSYS to Staging Area. SRND and CRND layers have full history
FeatureClassToFeatureClass_conversion(r"Database Connections\CANP.sde\NM3.NM_NLT_SRND_SRND_SDO", STAGEDB, "SRND" )
FeatureClassToFeatureClass_conversion(r"Database Connections\CANP.sde\NM3.NM_NLT_CRND_CRND_SDO", STAGEDB, "CRND" )
TableToTable_conversion(r'Database Connections\CANP.sde\KDOT.MV_MAP_EXTRACT', STAGEDB, "Map_Extract")
TableToTable_conversion(r'Database Connections\CANP.sde\NM3.NM_ELEMENTS_ALL', STAGEDB, "ELEMENTS_ALL")
TableToTable_conversion(r'Database Connections\CANP.sde\NM3.NM_MEMBERS_ALL', STAGEDB, "MEMBERS_ALL") #Members takes a long time to copy
#To copy the SECT layer it must be joined to preserve RSE_HE_ID
MakeFeatureLayer_management(r"Database Connections\CANP.sde\NM3.NM3_NET_MAP_SECT_TABLE", "NM_SECT" )
MakeTableView_management(STAGEDB+r"\Elements_ALL", "ELEMENTS")
AddJoin_management("NM_SECT", "RSE_HE_ID", "ELEMENTS", "NE_ID", "KEEP_COMMON")
FeatureClassToFeatureClass_conversion("NM_SECT", STAGEDB, "SECT" )
#Remove the CANP Prod layers
for df in mapping.ListDataFrames(mxd):
for lyr in mapping.ListLayers(mxd, "", df):
mapping.RemoveLayer(df, lyr)
RefreshActiveView()
#Start the processing to rebuild layers
MakeFeatureLayer_management(STAGEDB+r"\SRND", "SRND")
SRNDelem = "NE_NT_TYPE = 'SRND' AND NE_VERSION_NO in ('EB', 'NB')"
MakeTableView_management(STAGEDB+r"\ELEMENTS_ALL", "SRND_ELEM", SRNDelem)
AddJoin_management("SRND", "NE_ID", "SRND_ELEM", "NE_ID", "KEEP_COMMON")
#Create the vertices for calibration points
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 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
def FIMS_GIS():
#arcpy.MakeFeatureLayer_management(CPMSlyr, 'CPMS', ProjectSelect)
MakeFeatureLayer_management(CPMSlyr, 'CPMS')
MakeFeatureLayer_management(CountyLyr, 'County')
MakeFeatureLayer_management(HPMSlyr, 'HPMS')
MakeFeatureLayer_management(MPOLyr, 'MPO')
MakeFeatureLayer_management(CONGDistlyr, 'CONG')
MakeFeatureLayer_management(ws + "/Polygons", 'Polygons')
MakeTableView_management(deltbl, 'DeleteView')
MakeTableView_management(newtbl, 'InsertView')
#make the polygon analysis layer for Districts, Counties, and MPOs
#arcpy.Union_analysis("CONG #;MPO #;County #",ws+"/Polygons","ALL","1 feet","GAPS")
# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script
# The following inputs are layers or table views: "CPMS", "CPMS.CPMS_STAGING_TMP"
MakeTableView_management(FMIS_PROJ, "CPMS_STAGING_TMP")
AddJoin_management("CPMS", "PROJECT_ID", "InsertView", "PROJECT_NUMBER",
"KEEP_COMMON")
Output_Event_Table_Properties = 'RID LINE CNTY_BEG CNTY_END'
outtblH = ws + "/FIMS_EventTableLines"
if Exists(outtblH):
Delete_management(outtblH)
print "locating CPMS to HPMS route"
LocateFeaturesAlongRoutes_lr('CPMS', 'HPMS', "Route_ID", "0 miles",
outtblH, Output_Event_Table_Properties,
"FIRST", "DISTANCE", "NO_ZERO", "FIELDS",
"M_DIRECTON")
#the 30 foot tolerance we allowed here also created a bunch of 30' segments at project intersections. Those should be handled.
#...Or the locate tolerance changed to 0
#cleansel = "RID <> CRND_RTE" #is thisnot the right way to handle this, because it will delete the short segments crossing the GIS county boundary? not really
#selection statement deleted non-state highway system might be better
#MakeTableView_management(outtblH, "cleanup", cleansel)
#DeleteRows_management("cleanup")
MakeRouteEventLayer_lr("HPMS", "Route_ID", ws + "/FIMS_EventTableLines",
"rid LINE CNTY_BEG CNTY_END",
"FIMS_EventTableLineLyr", "#", "ERROR_FIELD",
"NO_ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT",
"POINT")
print "Intersection routes to areas"
Intersect_analysis("FIMS_EventTableLineLyr #;HPMS #;Polygons #",
ws + "/FMIS_Data", "ALL", "#", "LINE")
if Exists(ws + "/HPMS_DataD"):
Delete_management(ws + "/HPMS_DataD")
Dissolve_management(
ws + "/FMIS_Data", ws + "/HPMS_DataD",
"PROJECT_ID;F_SYSTEM_V;NHS_VN;DISTRICT_1;COUNTY_NUMBER;ID_1", "#",
"MULTI_PART", "UNSPLIT_LINES")
# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script
print "locating processed data to HPMS mileage"
if Exists(ws + "/FIMS_EventTable"):
Delete_management(ws + "/FIMS_EventTable")
LocateFeaturesAlongRoutes_lr(ws + "/HPMS_DataD", "HPMS", "Route_ID",
"0 miles", ws + "/FIMS_EventTable",
"RID LINE CNTY_BEG CNTY_END", "FIRST",
"DISTANCE", "NO_ZERO", "FIELDS", "M_DIRECTON")
#MaleTableView(ws+"/FIMS_EventTable", )
Append_management(
ws + "/FIMS_EventTable", FMIS_LOAD, "NO_TEST",
"""ROUTE_ID "ROUTE_ID" true true false 14 Text 0 0 ,First,#,FIMS_EventTable,RID,-1,-1;BEG_CNTY_MP "BEG_CNTY_MP" true true false 8 Double 10 38 ,First,#,FIMS_EventTable,CNTY_BEG,-1,-1;END_CNTY_MP "END_CNTY_MP" true true false 8 Double 10 38 ,First,#,FIMS_EventTable,CNTY_END,-1,-1;CONGRESSIONAL_DISTRICT "CONGRESSIONAL_DISTRICT" true true false 50 Text 0 0 ,First,#,FIMS_EventTable,DISTRICT_1,-1,-1;URBAN_ID "URBAN_ID" true true false 10 Text 0 0 ,First,#,FIMS_EventTable,ID_1,-1,-1;FUN_CLASS "FUN_CLASS" true true false 3 Text 0 0 ,First,#,FIMS_EventTable,F_SYSTEM_V,-1,-1;SYSTEM_CODE "SYSTEM_CODE" true true false 10 Text 0 0 ,First,#,FIMS_EventTable,NHS_VN,-1,-1;PROJECT_NUMBER "PROJECT_NUMBER" true true false 15 Text 0 0 ,First,#,FIMS_EventTable,PROJECT_ID,-1,-1;COUNTY "COUNTY" true true false 3 Text 0 0 ,First,#,FIMS_EventTable,COUNTY_NUMBER,-1,-1""",
"#")
print "Rows appended to CPMS Load Table CPMS_HPMS_FMIS_DATA"
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")
