def TruncateAndAppend(mxd, TargetLT, TargetST): lyrs = mapping.ListLayers(mxd) print "Updating data for " +str(lyrs[0]) TruncateTable_management(TargetST) Append_management(lyrs[0], TargetST, "NO_TEST", "#") print "Updating data for " +str(lyrs[1]) TruncateTable_management(TargetLT) Append_management(lyrs[1], TargetLT, "NO_TEST", "#")
def UpdateSniceBoundaries():
from arcpy import TruncateTable_management, Append_management
subarea = r'Database Connections\GISPROD_Shared.sde\SHARED.KDOT_SNICE_SUBAREA'
area = r'Database Connections\GISPROD_Shared.sde\SHARED.KDOT_SNICE_AREAS'
district = r'Database Connections\GISPROD_Shared.sde\SHARED.KDOT_SNICE_Districts'
#TruncateTable_management(subarea)
TruncateTable_management(area)
TruncateTable_management(district)
#Append_management("in_memory/KDOT_SNICE_SubArea", subarea, "NO_TEST")
Append_management("in_memory/KDOT_SNICE_Area", area, "NO_TEST")
Append_management("in_memory/KDOT_SNICE_District", district, "NO_TEST")
def AnnualizeData(YearToAnnualize):
annualLayer = gdb + "\KTRIPS.SDE.Ktrips_Annual"
currentyYear = gdb + "\KTRIPS.SDE.Ktrips_CurrentYear"
SelectYear = YearSelTest
CalcYear = str(int(YearSelTest - 1))
YearSelTest = "TripYear = '" + SelectYear + "'"
if Exists("Check1"):
Delete_management("Check1")
MakeFeatureLayer_management(annualLayer, "Check1", YearSelTest)
CheckExistence = GetCount_management("Check1")
print CheckExistence
if int(str(CheckExistence)) > 0:
print "This source file info is already in the target feature"
runnext = False
elif int(str(CheckExistence)) == 0:
print 'the information is new for this source file and will be added.'
runnext = True
Append_management(currentyYear, annualLayer, "NO_TEST", "#")
CalculateField_management(annualLayer, "TripYear", CalcYear,
"PYTHON_9.3")
TruncateTable_management(currentyYear)
else:
print 'something isnt working here'
print runnext
pass
def writeAggregateTableToKanDrive():
try:
#truncating CDRS segments in KanDrive Spatial
print str(datetime.datetime.now()) + " truncating RCRS segments in KanDrive Spatial."
TruncateTable_management(kanDriveSpatialConditions)
featuresToTransfer = list()
# searchCursorFields go to r"in_memory\RCRS". (Input table)(Indirect)
descObject = Describe(r"in_memory\RCRS")
searchCursorFields = [field.name for field in descObject.fields if
field.name != descObject.OIDFieldName and field.name != "Shape" and
field.name != "ID1"]
searchCursorFields.append('SHAPE@')
RCRS_SearchCursor = daSearchCursor(aggregateTable, searchCursorFields)
for RCRS_CursorItem in RCRS_SearchCursor:
featureItem = list(RCRS_CursorItem)
featuresToTransfer.append(featureItem)
# Make the insertCursor use the same fields as the searchCursor.
insertCursorFields = searchCursorFields
RCRS_InsertCursor = daInsertCursor(kanDriveSpatialConditions, insertCursorFields)
for RCRS_Feature in featuresToTransfer:
insertOID = RCRS_InsertCursor.insertRow(RCRS_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
errorStatement = str(errorItem.args[0])
print errorStatement
if len(errorStatement) > 253:
errorStatement = errorStatement[0:253]
else:
pass
endTime = datetime.datetime.now()
ScriptStatusLogging('KanDrive_Spatial_Conditions_Update', 'kandrive_spatial.DBO.Conditions',
scriptFailure, startTime, endTime, errorItem.args[0], pythonLogTable)
try:
del errorItem
except:
pass
def XYFC(source, dst, Lat, Long, GCS, loaded):
if Exists("FCtbl"):
Delete_management("FCtbl")
else:
pass
if Exists("FC_Layer"):
Delete_management("FC_Layer")
else:
pass
print "start XYFC "+ str(datetime.datetime.now())
MakeTableView_management(source, 'FCtbl', "#", "#", "")
MakeXYEventLayer_management("FCtbl",Long, Lat,"FC_Layer", GCS,"#")
TruncateTable_management(dst)
Append_management("FC_Layer",dst,"NO_TEST","#","#")
CalculateField_management(dst, loaded,"datetime.datetime.now( )","PYTHON_9.3","#")
print "XYFC complete for " +str(dst)+ " at " + str(datetime.datetime.now())
def manageLogLength():
logTableDesc = Describe(pythonLogTable)
logLengthCheckCursor = daSearchCursor(pythonLogTable, logTableDesc.OIDFieldName)
shouldTruncate = False
for logItem in logLengthCheckCursor:
if int(logItem[0]) > 5000:
shouldTruncate = True
else:
pass
if shouldTruncate == True:
print "Log table size is too big."
print "Truncating log table."
TruncateTable_management(pythonLogTable)
else:
pass
def AnnualStats(ShapeFileDate):
env.overwriteOutput = 1
#SourceFileTxt = str(ShapeFileDate[12:-4].replace("-", ""))
#infileMonthly = yeardb+r"\Kansas\LRS"+SourceFileTxt
qyear = ShapeFileDate[16:20]
#try:
# Delete_management(yeardb+"/KTRIPS_MonthlySum_Statistics")
# Delete_management(yeardb+"/RunningTotal")
#except:
# print "nothing deleted"
#sumfile = gdb+"\INTERMODAL.DBO.KTRIPS_MonthlySum"
sumfile = gdb + "\KTRIPS.SDE.KTRIPS_MonthlySum"
whereclause = str(BuildWhereClauseLike(sumfile, "SourceFile", qyear))
if Exists("ThisYearMonthly"):
Delete_management("ThisYearMonthly")
MakeFeatureLayer_management(sumfile, "ThisYearMonthly", whereclause, "#",
"#")
Statistics_analysis("ThisYearMonthly",
"in_memory/KTRIPS_MonthlySum_Statistics",
"Join_Count SUM;Tonnage SUM",
"LRS_KEY;BEG_CNTY_LOGMILE;END_CNTY_LOGMILE")
AddField_management("in_memory/KTRIPS_MonthlySum_Statistics", "TonMiles",
"DOUBLE", "#", "#", "#", "#", "NULLABLE",
"NON_REQUIRED", "#")
CalculateField_management(
"in_memory/KTRIPS_MonthlySum_Statistics", "TonMiles",
"!SUM_Tonnage! /(!END_CNTY_LOGMILE! - !BEG_CNTY_LOGMILE!)",
"PYTHON_9.3", "#")
if Exists("KTRIPS_RunningTotal_CurentYear"):
Delete_management("KTRIPS_RunningTotal_CurentYear")
MakeRouteEventLayer_lr(cansys, "LRS_KEY",
"in_memory/KTRIPS_MonthlySum_Statistics",
"LRS_KEY LINE BEG_CNTY_LOGMILE END_CNTY_LOGMILE",
"KTRIPS_RunningTotal_CurentYear", "#",
"ERROR_FIELD", "NO_ANGLE_FIELD", "NORMAL", "ANGLE",
"LEFT", "POINT")
CurrentYrStat = gdb + "\KTRIPS.SDE.Ktrips_CurrentYear"
TruncateTable_management(CurrentYrStat)
Append_management("KTRIPS_RunningTotal_CurentYear", CurrentYrStat,
"NO_TEST", "#", "")
print "annual Stats have been recalculated from the latest Monthly Statistics"
Delete_management("in_memory/KTRIPS_MonthlySum_Statistics")
def TruncateAndAppend():
#UPDATE THE LOCAL DATA SOURCE FROM THE HOSTED DATA SOURCE
'''for key, value in dict.items():
print(key)
for featureclass in value:
print (" "+featureclass)
'''
from arcpy import management, TruncateTable_management
for source in m.listLayers():
if source.isWebLayer is True and source.isFeatureLayer is True:
print("hosted ", source.name)
for target in m.listLayers():
if target.isWebLayer is False and target.isFeatureLayer is True:
print(target)
db, owner, targetname = target.name.split(".")
print(targetname)
if targetname == source.name:
print("local ", targetname)
TruncateTable_management(target)
management.Append(source, target, "NO_TEST")
'''
Created on Aug 13, 2014
Truncate and Append LRS elements from the Gateway MXD to static features classes in the Gateway SQL server GEodatabase
Moved to Production on Aug 20 2014
@author: kyleg
'''
from arcpy import mapping, Append_management, TruncateTable_management
GDB = r'D:\HNTB_GATEWAY\ProductionMOT\SQL54_GATEWAY15.sde'
mxd = mapping.MapDocument(
r'D:\HNTB_GATEWAY\ProductionMOT\2014111401_GatewayExec.mxd')
lyrs = mapping.ListLayers(mxd)
#D:\HNTB_GATEWAY\ProductionMOT\SQL54_GATEWAY15.sde\Gateway2015.GATEWAY_SPATIAL.LongTermApproved
TargetLT = r"D:\HNTB_GATEWAY\ProductionMOT\SQL54_GATEWAY15.sde\Gateway2015.GATEWAY_SPATIAL.LongTermApproved"
TargetST = r'D:\HNTB_GATEWAY\ProductionMOT\SQL54_GATEWAY15.sde\Gateway2015.Gateway_Spatial.ShortTermApproved'
print lyrs[0]
TruncateTable_management(TargetST)
Append_management(lyrs[0], TargetST, "NO_TEST", "#")
print lyrs[1]
TruncateTable_management(TargetLT)
Append_management(lyrs[1], TargetLT, "NO_TEST", "#")
#if __name__ == '__main__':
# pass
def transferFeatures():
env.workspace = in_memory
featuresToTransfer = list()
try:
# Create an in_memory feature class which to hold the features from
# the Oracle table.
FeatureClassToFeatureClass_conversion(kanRoadRCRSRoads, "in_memory",
"RCRS")
# Then, define a projection on it, since the original Oracle table
# is lacking the proper information.
DefineProjection_management("in_memory\RCRS", lambertCC)
#truncating CDRS segments in KanDrive Spatial
print str(datetime.datetime.now()
) + " truncating RCRS segments in KanDrive Spatial."
TruncateTable_management(kanDriveSpatialConditions)
###############################################################################################################
# Maintainability information:
# If you need to add another field to transfer between the two, just add it to both of the
# tables and give it the same name in both.
###############################################################################################################
# searchCursorFields go to r"in_memory\RCRS". (Input table)(Indirect)
descObject = Describe(r"in_memory\RCRS")
searchCursorFields = [
field.name for field in descObject.fields
if field.name != descObject.OIDFieldName and field.name != "Shape"
and field.name != "ID1"
]
searchCursorFields.append('SHAPE@')
# Make the insertCursor use the same fields as the searchCursor.
insertCursorFields = searchCursorFields
print "OIDFieldname = " + descObject.OIDFieldName
print "fieldNames to be used in the searchCursor (and insertCursor):"
for fieldName in searchCursorFields:
print fieldName
RCRS_SearchCursor = daSearchCursor(r"in_memory\RCRS",
searchCursorFields)
for RCRS_CursorItem in RCRS_SearchCursor:
featureItem = list(RCRS_CursorItem)
featuresToTransfer.append(featureItem)
RCRS_InsertCursor = daInsertCursor(kanDriveSpatialConditions,
insertCursorFields)
for RCRS_Feature in featuresToTransfer:
insertOID = RCRS_InsertCursor.insertRow(RCRS_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
errorStatement = str(errorItem.args[0])
print errorStatement
if len(errorStatement) > 253:
errorStatement = errorStatement[0:253]
else:
pass
endTime = datetime.datetime.now()
ScriptStatusLogging('KanDrive_Spatial_Conditions_Update',
'kandrive_spatial.DBO.Conditions', scriptFailure,
startTime, endTime, errorItem.args[0],
pythonLogTable)
try:
del errorItem
except:
pass
# Reraise the error to stop execution and prevent a success message
# from being inserted into the table.
raise
finally:
try:
del RCRS_SearchCursor
except:
pass
try:
del RCRS_InsertCursor
except:
pass
else:
pass
maxlog = round(row[2], round_dec)
if maxlog < row[2]:
maxlog = maxlog + HatchSep
else:
pass
#set the starting point for the route segment
itermile = minlog
#now loop between the start and end logmiles for each route segment, and insert the step increment into the event table in memory
while itermile <= maxlog and itermile >= minlog:
#print str(itermile)
with da.InsertCursor(mem_table, ("RouteID", "LogMile")) as insert:# @UndefinedVariable
insertfields = [LRSKEY, itermile]
insert.insertRow(insertfields)
itermile = itermile + HatchSep
del minlog
del maxlog
MakeRouteEventLayer_lr("RouteLyr", method[2][0], mem_table, "RouteID POINT LogMile", "HatchPoints_events", "", "ERROR_FIELD", "ANGLE_FIELD", "NORMAL", "COMPLEMENT", "LEFT", "POINT")
#add XY points, later on these coordinates can be used to hyperlink to google street view, bing maps, etc. Make sure we are in NAD83 Lat/Long CRS here.
AddXY_management("HatchPoints_events")
try:
Delete_management(wsPath+'//'+method[1])
FeatureClassToFeatureClass_conversion("HatchPoints_events", wsPath, method[1], "LOC_ERROR = 'NO ERROR'")
except:
TruncateTable_management(wsPath+'//'+method[1])
Append_management("HatchPoints_events", wsPath+'//'+method[1])
cleanup = [mem_table, "RouteLyr", "HatchPoints_events"]
for layer in cleanup:
Delete_management(layer)
#something right here is corrupting the file geodatabase... changed stagews+"//CDRS_Project" to stagews+"\CDRS_Project"
MakeFeatureLayer_management(stagews + "\CDRS_Project", "ClosedRoutes",
'"ALERT_STATUS" = 2 AND "FEA_CLOSED" = 1')
CalculateField_management("ClosedRoutes", "STATUS", '"Closed"', "PYTHON_9.3",
"#")
MakeFeatureLayer_management(stagews + "\CDRS_Project", "PlannedRoutes",
'"ALERT_STATUS" = 1')
CalculateField_management("PlannedRoutes", "STATUS", '"Planned"', "PYTHON_9.3",
"#")
print str(
datetime.datetime.now()) + " truncating CDRS segments in WICHWAY SPATIAL"
TruncateTable_management(sdeWichwayCDRS)
print str(datetime.datetime.now()) + " appending CDRS segments"
Append_management(
"CDRS_Project", sdeWichwayCDRS, "NO_TEST",
"""RouteName "RouteName" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,RouteName,-1,-1;BeginMP "BeginMP" true true false 8 Double 8 38 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,BEG_STATE_LOGMILE,-1,-1;EndMP "EndMP" true true false 8 Double 8 38 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,END_STATE_LOGMILE,-1,-1;County "County" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,BEG_COUNTY_NAME,-1,-1;StartDate "StartDate" true true false 36 Date 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,ALERT_DATE,-1,-1,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,COMP_DATE,-1,-1;CompDate "CompDate" true true false 36 Date 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,COMP_DATE,-1,-1;AlertType "AlertType" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,ALERT_TYPE_TXT,-1,-1;AlertDescription "AlertDescription" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,ALERT_DESC_TXT,-1,-1;HeightLimit "HeightLimit" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,VERT_RESTRICTION,-1,-1;WidthLimit "WidthLimit" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,WIDTH_RESTRICTION,-1,-1;TrafficRouting "TrafficRouting" true true false 50 Text 0 0 ,First,#;TimeDelay "TimeDelay" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,TIME_DELAY_TXT,-1,-1;Comments "Comments" true true false 4000 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,PUBLIC_COMMENT,-1,-1;DetourType "DetourType" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,KANROAD_CDRS_WZ_DETAIL_DETOUR_TYPE_TXT,-1,-1;DetourDescription "DetourDescription" true true false 1500 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,KANROAD_CDRS_WZ_DETAIL_DETOUR_DESC,-1,-1;ContactName "ContactName" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,CONTACT_NAME,-1,-1;ContactPhone "ContactPhone" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,CONTACT_PHONE,-1,-1;ContactEmail "ContactEmail" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,CONTACT_EMAIL,-1,-1;WebLink "WebLink" true true false 500 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,ALERT_HYPERLINK,-1,-1;X "X" true true false 8 Double 8 38 ,First,#;Y "Y" true true false 8 Double 8 38 ,First,#;AlertStatus "AlertStatus" true true false 4 Long 0 10 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,Alert_Status_I,-1,-1;FeaClosed "FeaClosed" true true false 4 Long 0 10 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,FEA_CLOSED,-1,-1;Status "Status" true true false 50 Text 0 0 ,First,#,D:/wichway/harvesters/python/CDRS.gdb/CDRS_Project,STATUS,-1,-1;LoadDate "LoadDate" true true false 36 Date 0 0 ,First,#;SHAPE_STLength__ "SHAPE_STLength__" true false false 8 Double 8 38 ,First,#;Shape.STLength() "Shape.STLength()" false false true 0 Double 0 0 ,First,#""",
"#")
try:
MakeFeatureLayer_management(sdeWichwayCDRS, "loaded", "#")
CalculateField_management("loaded", "LoadDate", "datetime.datetime.now( )",
"PYTHON_9.3", "#")
print str(datetime.datetime.now()) + " It Ran, time for lunch"
except:
print str(datetime.datetime.now()
) + " It Ran, but didn't calc the LoadDate field"
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 DissolveNonDirectionalItems(OpEnvironmentMode):
OpRunIn = OpEnvironment.OpRunInSum # @UndefinedVariable
OpRunOut = OpEnvironment.OpRunOut # @UndefinedVariable
adm = OpEnvironment.adm # @UndefinedVariable
Owner = OpEnvironment.Owner # @UndefinedVariable
DB = OpEnvironment.DB # @UndefinedVariable
env.workspace = OpRunIn
env.overwriteOutput = True
print OpRunIn
#combine the connection, db, and owner to the destination path for enterprise geodatabase output
OpRunFullOut = OpRunOut + "/" + DB + "." + Owner + "."
print OpRunFullOut
FCList = ListFeatureClasses()
print "dissolving items in the primary direction"
FCGlobalFieldsDissolve = [
"LRS_KEY", "COUNTY_CD", "COUNTY_NAME", "DISTRICT"
]
FCGlobalFieldsSummarize = "BSMP MIN;ESMP MAX;BCMP MIN;ECMP MAX"
FCFieldsIgnore = [
"OBJECTID", "CRND_RTE", "LANE_DIRECTION", "DIV_UNDIV", "SHAPE",
"SHAPE.STLength()", "BSMP", "ESMP", "BCMP", "ECMP", "OLD_FUN_CLASS",
"FUN_DT"
]
for Item in FCList:
ItemOut = Item[2:]
ItemDissolveFields = []
print ItemOut
fields = ListFields(Item)
for field in fields:
if field.name not in FCFieldsIgnore:
#print " "+field.name
ItemDissolveFields.append(field.name)
dissolvelist = ItemDissolveFields + FCGlobalFieldsDissolve
DissolveFields = ';'.join(dissolvelist)
if Exists(OpRunFullOut + ItemOut):
try:
print "feature class " + str(
ItemOut) + " exists and will be updated"
Dissolve_management(Item, "in_memory/" + ItemOut,
DissolveFields, FCGlobalFieldsSummarize,
"MULTI_PART", "DISSOLVE_LINES")
TruncateTable_management(OpRunFullOut + ItemOut)
Append_management("in_memory/" + ItemOut,
OpRunFullOut + ItemOut, "NO_TEST", "#")
Delete_management("in_memory/" + ItemOut)
print "feature class " + str(
ItemOut) + " was successfully updated"
except ExecuteError:
print "update failed because the schema has changed from what existed"
#need to add locking
DisconnectUser(adm, "readonly")
AcceptConnections(adm, True)
Delete_management(OpRunFullOut + ItemOut)
print "recreating the dissolved feature class for " + str(
ItemOut)
Dissolve_management(Item, OpRunFullOut + ItemOut,
DissolveFields, FCGlobalFieldsSummarize,
"MULTI_PART", "DISSOLVE_LINES")
ChangePrivileges_management(OpRunFullOut + ItemOut, "readonly",
"GRANT", "AS_IS")
except:
print "another error happened on updating the feature class"
else:
print "feature class " + str(
ItemOut) + " will be created or overwritten"
DisconnectUser(adm, "readonly")
AcceptConnections(adm, True)
Dissolve_management(Item, OpRunFullOut + ItemOut, DissolveFields,
FCGlobalFieldsSummarize, "MULTI_PART",
"DISSOLVE_LINES")
ChangePrivileges_management(OpRunFullOut + ItemOut, "readonly",
"GRANT", "AS_IS")
try:
Delete_management("in_memory/" + Item)
except:
pass
datetime.datetime.now()) + " reformatting the Route name for US routes"
AddField_management("CDRS_DETAIL", "RouteName", "TEXT", "#", "10")
CalculateField_management("CDRS_DETAIL", "RouteName",
"""Left([ROUTE], 1) & "-"&Mid( [ROUTE], 2, 9)""",
"VB", "#")
MakeFeatureLayer_management("CDRS_DETAIL", "USRoutes", "ROUTE LIKE 'U%'")
CalculateField_management("USRoutes", "RouteName",
"""Left([ROUTE], 1) & "S-"&Mid( [ROUTE], 2, 9)""",
"VB", "#")
print str(datetime.datetime.now()) + " reprojection processing"
DefineProjection_management("CDRS_DETAIL", labmertCC)
Project_management(
"CDRS_DETAIL", "CDRS_Project",
"PROJCS['WGS_1984_Web_Mercator_Auxiliary_Sphere',GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Mercator_Auxiliary_Sphere'],PARAMETER['False_Easting',0.0],PARAMETER['False_Northing',0.0],PARAMETER['Central_Meridian',0.0],PARAMETER['Standard_Parallel_1',0.0],PARAMETER['Auxiliary_Sphere_Type',0.0],UNIT['Meter',1.0]]",
"WGS_1984_(ITRF00)_To_NAD_1983",
"PROJCS['NAD_83_Kansas_Lambert_Conformal_Conic_Meters',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['false_easting',0.0],PARAMETER['false_northing',0.0],PARAMETER['central_meridian',-98.0],PARAMETER['standard_parallel_1',38.0],PARAMETER['standard_parallel_2',39.0],PARAMETER['scale_factor',1.0],PARAMETER['latitude_of_origin',38.5],UNIT['Meter',1.0]]"
)
print str(datetime.datetime.now()) + " truncating CDRS segments"
TruncateTable_management(sdeConnectionString)
print str(datetime.datetime.now()) + " appending CDRS segments"
Append_management(
"CDRS_Project", sdeConnectionString, "NO_TEST",
"""RouteName "RouteName" true true false 50 Text 0 0 ,First,#,CDRS_Project,RouteName,-1,-1;BeginMP "BeginMP" true true false 8 Double 8 38 ,First,#;EndMP "EndMP" true true false 8 Double 8 38 ,First,#,CDRS_Project,EndMP,-1,-1;County "County" true true false 50 Text 0 0 ,First,#,CDRS_Project,County,-1,-1;StartDate "StartDate" true true false 36 Date 0 0 ,First,#,CDRS_Project,StartDate,-1,-1;CompDate "CompDate" true true false 36 Date 0 0 ,First,#,CDRS_Project,CompDate,-1,-1;AlertType "AlertType" true true false 50 Text 0 0 ,First,#,CDRS_Project,AlertType,-1,-1;AlertDescription "AlertDescription" true true false 50 Text 0 0 ,First,#,CDRS_Project,AlertDescription,-1,-1;HeightLimit "HeightLimit" true true false 50 Text 0 0 ,First,#,CDRS_Project,HeightLimit,-1,-1;WidthLimit "WidthLimit" true true false 50 Text 0 0 ,First,#,CDRS_Project,WidthLimit,-1,-1;TrafficRouting "TrafficRouting" true true false 50 Text 0 0 ,First,#;TimeDelay "TimeDelay" true true false 50 Text 0 0 ,First,#,CDRS_Project,TimeDelay,-1,-1;Comments "Comments" true true false 1073741822 Text 0 0 ,First,#,CDRS_Project,Comments,-1,-1;DetourType "DetourType" true true false 50 Text 0 0 ,First,#,CDRS_Project,DetourType,-1,-1;ContactName "ContactName" true true false 50 Text 0 0 ,First,#,CDRS_Project,ContactName,-1,-1;ContactPhone "ContactPhone" true true false 50 Text 0 0 ,First,#,CDRS_Project,ContactPhone,-1,-1;ContactEmail "ContactEmail" true true false 50 Text 0 0 ,First,#,CDRS_Project,ContactEmail,-1,-1;X "X" true true false 8 Double 8 38 ,First,#;Y "Y" true true false 8 Double 8 38 ,First,#;AlertStatus "AlertStatus" true true false 4 Long 0 10 ,First,#,CDRS_Project,AlertStatus,-1,-1;FeaClosed "FeaClosed" true true false 4 Long 0 10 ,First,#,CDRS_Project,FeaClosed,-1,-1;Status "Status" true true false 50 Text 0 0 ,First,#;WebLink "WebLink" true true false 255 Text 0 0 ,First,#,CDRS_Project,WebLink,-1,-1;DetourDescription "DetourDescription" true true false 500 Text 0 0 ,First,#,CDRS_Project,DetourDescription,-1,-1;SHAPE.STLength() "SHAPE.STLength()" false false true 0 Double 0 0 ,First,#""",
"#")
print str(datetime.datetime.now()) + " It Ran, time for lunch"
#ran on AR52 in about 1.5 min
def transferFeatures():
env.workspace = in_memory
featuresToTransfer = list()
try:
# Create an in_memory feature class which to hold the features from
# the Oracle table.
FeatureClassToFeatureClass_conversion(sdeCMLRS,"in_memory","CMLRS")
#truncating CDRS segments in KanDrive Spatial
print str(datetime.datetime.now()) + " truncating CMLRS segments in KanDrive Spatial."
env.workspace = gdbCMLRSLocation
TruncateTable_management(gdbCMLRSTable)
env.workspace = in_memory
###############################################################################################################
# Maintainability information:
# If you need to add another field to transfer between the two, just add it to both of the
# tables and give it the same name in both.
###############################################################################################################
# searchCursorFields go to r"in_memory\CMLRS". (Input table)(Indirect)
Sde_CMLRS_Object = Describe(r"in_memory\CMLRS")
Sde_CMLRS_Fields = [field.name for field in Sde_CMLRS_Object.fields]
Gdb_CMLRS_Object = Describe(gdb_93_CMLRS)
Gdb_CMLRS_Fields = [field.name for field in Gdb_CMLRS_Object.fields]
# This Python list comprehension creates the intersection of the two *_Fields lists
# and makes sure that the Shape field and Object ID fields are not directly
# transfered. -- The 'SHAPE@' token indirectly transfers the geometry instead
# and the Object ID of the target feature class is automatically calculated
# by the insert cursor.
searchCursorFields = [fieldName for fieldName in Sde_CMLRS_Fields if
fieldName in Gdb_CMLRS_Fields and
fieldName != Sde_CMLRS_Object.OIDFieldName and
fieldName != Gdb_CMLRS_Object.OIDFieldName and
fieldName != 'Shape']
searchCursorFields.append('SHAPE@')
# Make the insertCursor use the same fields as the searchCursor.
insertCursorFields = searchCursorFields
print "fieldNames to be used in the searchCursor (and insertCursor):"
for fieldName in searchCursorFields:
print fieldName
CMLRS_SearchCursor = daSearchCursor(r"in_memory\CMLRS", searchCursorFields)
for CMLRS_CursorItem in CMLRS_SearchCursor:
featureItem = list(CMLRS_CursorItem)
featuresToTransfer.append(featureItem)
CMLRS_InsertCursor = daInsertCursor(gdb_93_CMLRS, insertCursorFields)
for CMLRS_Feature in featuresToTransfer:
insertOID = CMLRS_InsertCursor.insertRow(CMLRS_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
errorStatement = str(errorItem.args[0])
print errorStatement
if len(errorStatement) > 253:
errorStatement = errorStatement[0:253]
else:
pass
endTime = datetime.datetime.now()
ScriptStatusLogging('Cansys_CMLRS_Transfer', 'SharedSDEProd.gdb\SHARED_CANSYS_CMLRS',
scriptFailure, startTime, endTime, errorStatement, pythonLogTable)
try:
del errorItem
except:
pass
# Reraise the error to stop execution and prevent a success message
# from being inserted into the table.
raise
finally:
try:
del CMLRS_SearchCursor
except:
pass
try:
del CMLRS_InsertCursor
except:
pass
def transferFeatures():
env.workspace = in_memory
featuresToTransfer = list()
try:
print str(
datetime.datetime.now()) + ' copying the oracle table to memory'
# Create an in_memory feature class which to hold the features from
# the Oracle table.
FeatureClassToFeatureClass_conversion(
sdeCDRS, "in_memory", "CDRS", "#",
"ALERT_STATUS <> 3 AND AlertType = 'Road Incident'")
# Then, define a projection on it, since the original Oracle table
# is lacking the proper information.
DefineProjection_management("in_memory\CDRS", lambertCC)
#truncating CDRS segments in KanDrive Spatial
print str(datetime.datetime.now()
) + " truncating Incident segments in KanDrive Spatial."
TruncateTable_management(kanDriveSpatialIncidents)
###############################################################################################################
# Maintainability information:
# If you need to add another field to transfer between the two, just add it to both of the
# tables and give it the same name in both.
# If you need to add another field to just one, the list comprehension will automatically
# exclude it.
#
# If you need to add another field to transfer between the two and it has to be named
# different things in each table, you will need to append the matching name to the searchCursorFields list
# and insertCursorFields list are generated.
#
# I.e. if you add a field named "snowFlakeCount" to the CDRS_ALERT_ROUTE table and a field named
# "snowFlakeCounter" to the kandrive_spatial.DBO.Incidents table, you would need to append
# "snowFlakeCount" to the searchCursorFields and "snowFlakeCounter" to the insertCursorFields for
# them to match up and transfer properly. -- If possible, avoid this by naming them both the same thing.
#
# If you're having to do several appends, it may be best to just write out all of the field names for
# each list in the order that you would like for them to be transfered. This is how the field names
# are listed in the Kandrive_Construction_Update.py script.
###############################################################################################################
CDRS_Desc_Object = Describe(r"in_memory\CDRS")
CDRS_Desc_Fields = [field.name for field in CDRS_Desc_Object.fields]
Incidents_Desc_Object = Describe(kanDriveSpatialIncidents)
Incidents_Desc_Fields = [
field.name for field in Incidents_Desc_Object.fields
]
# This Python list comprehension creates the intersection of the two *_Fields lists
# and makes sure that the Shape field and Object ID fields are not directly
# transfered. -- The 'SHAPE@' token indirectly transfers the geometry instead
# and the Object ID of the target feature class is automatically calculated
# by the insert cursor.
searchCursorFields = [
fieldName for fieldName in CDRS_Desc_Fields
if fieldName in Incidents_Desc_Fields
and fieldName != CDRS_Desc_Object.OIDFieldName and fieldName !=
Incidents_Desc_Object.OIDFieldName and fieldName != 'Shape'
]
searchCursorFields.append('SHAPE@')
# Make the insertCursor use the same fields as the searchCursor.
insertCursorFields = searchCursorFields
print "OIDFieldnames: " + CDRS_Desc_Object.OIDFieldName + " & " + Incidents_Desc_Object.OIDFieldName + "."
print "fieldNames to be used in the searchCursor (and insertCursor):"
for fieldName in searchCursorFields:
print fieldName
Incidents_Where_Clause = "ALERT_TYPE_TXT = 'Road Incident'"
CDRS_SearchCursor = daSearchCursor(r"in_memory\CDRS",
searchCursorFields,
Incidents_Where_Clause)
for CDRS_CursorItem in CDRS_SearchCursor:
featureItem = list(CDRS_CursorItem)
featuresToTransfer.append(featureItem)
##Debug
for feature in featuresToTransfer:
print feature
##
Incidents_InsertCursor = daInsertCursor(kanDriveSpatialIncidents,
insertCursorFields)
for CDRS_Feature in featuresToTransfer:
insertOID = Incidents_InsertCursor.insertRow(CDRS_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
'''
print "fieldNames to be used in the searchCursor:"
for fieldName in searchCursorFields:
print fieldName
incidents_Where_Clause = "AlertType = 'Road Incident'"
Construction_SearchCursor = daSearchCursor(sdeCDRS, searchCursorFields, incidents_Where_Clause)
for Construction_CursorItem in Construction_SearchCursor:
featureItem = list(Construction_CursorItem)
featuresToTransfer.append(featureItem)
Incidents_InsertCursor = daInsertCursor(kanDriveSpatialIncidents, insertCursorFields)
for Construction_Feature in featuresToTransfer:
insertOID = Incidents_InsertCursor.insertRow(Construction_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
'''
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
errorStatement = str(errorItem.args[0])
print errorStatement
if len(errorStatement) > 253:
errorStatement = errorStatement[0:253]
else:
pass
endTime = datetime.datetime.now()
ScriptStatusLogging('KanDrive_Spatial_Incidents_Update',
'kandrive_spatial.DBO.Incidents', scriptFailure,
startTime, endTime, errorItem.args[0],
pythonLogTable)
try:
del errorItem
except:
pass
# Reraise the error to stop execution and prevent a success message
# from being inserted into the table.
raise
finally:
try:
del CDRS_SearchCursor
except:
pass
try:
del Incidents_InsertCursor
except:
pass