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 createShortGradiculeLinesForEachCounty():
# Get/use the same projection as the one used for the county roads.
spatialReferenceProjection = Describe(sharedNonStateSystem).spatialReference
env.workspace = sqlGdbLocation
inputCountyGradicule = countyCountyGradicule
bufferedCounties = 'bufferedCounties'
countiesToCopy = 'countiesToCopy'
gradiculeToCopy = 'gradiculeToCopy'
loadedGradiculeCopy = 'loadedGradiculeCopy'
loadedTempGradicule = 'loadedTempGradicule'
#unBufferedCounties = 'unBufferedCounties'
# Using the miniBuffered process changes it from
# 1457 total output features to 1481 (at 2.1k)
# total output features.
miniBufferedCounties = 'miniBufferedCounties'
loadedOutputGradicule = 'loadedOutputGradicule'
tempCounties = r'in_memory\tempCounties'
tempCountyGradicule = r'in_memory\tempCountyGradicule'
tempCountyGradiculePostErase = r'in_memory\tempCountyGradiculePostErase'
tempCountyGradiculeSinglePart = r'in_memory\tempCountyGradiculeSinglePart'
bufferCursorFields = ["OBJECTID", "COUNTY_NAME"]
MakeFeatureLayer_management(sharedCounties, countiesToCopy)
MakeFeatureLayer_management(countyCountyGradicule, gradiculeToCopy)
CopyFeatures_management(gradiculeToCopy, countyGradiculeCopied)
MakeFeatureLayer_management(countyGradiculeCopied, loadedGradiculeCopy)
# Might be worth dissolving based on COORD & County_Name prior
# to removing the County_Name field, if that's a possibility.
# Or better yet, just make it so that the Gradicule lines for
# a particular county are eligible for intersecting and
# erasing with that same county's polygon's. All we're
# trying to do here is make it so that the county's original
# gradicule lines are about half of their original size.
# Don't need to find out which gradicule lines are close to
# the county or anything else like that. Just need to reduce
# the size of the lines and keep the parts that are nearest
# the county that they go with.
# Remove the County_Name field so that the intersect can add it
# back and populate it only where the county buffer actually
# intersects the lines.
#DeleteField_management(countyGradiculeCopied, "County_Name")
# Elaine requested that this be 1000 Feet shorter.
# I made it 2000 feet shorter, because it still seemed too big.
Buffer_analysis(sharedCounties, countiesBuffered, "8000 Feet")
Buffer_analysis(sharedCounties, countiesMiniBuffered, "1500 Feet")
bufferedCountyPolygonList = list()
outputFeatureList = list()
# 1st SearchCursor
newCursor = daSearchCursor(countiesBuffered, bufferCursorFields)
for newRow in newCursor:
bufferedCountyPolygonList.append(list(newRow))
if 'newCursor' in locals():
del newCursor
else:
pass
MakeFeatureLayer_management(countiesBuffered, bufferedCounties)
MakeFeatureLayer_management(countiesMiniBuffered, miniBufferedCounties)
loadedCountiesFields = ListFields(bufferedCounties)
for loadedCountiesField in loadedCountiesFields:
print "A loadedCountiesField was found: " + str(loadedCountiesField.name)
countyGradiculeFields = ListFields(loadedGradiculeCopy)
for countyGradiculeField in countyGradiculeFields:
print "A countyGradiculeField was found: " + str(countyGradiculeField.name)
for listedRow in bufferedCountyPolygonList:
print str(listedRow)
selectCounty = listedRow[1]
whereClause = """ "COUNTY_NAME" = '""" + str(selectCounty) + """' """
print "The whereClause is " + str(whereClause)
SelectLayerByAttribute_management(bufferedCounties, "NEW_SELECTION", whereClause)
SelectLayerByAttribute_management(loadedGradiculeCopy, "NEW_SELECTION", whereClause)
Intersect_analysis([loadedGradiculeCopy, bufferedCounties], tempCountyGradicule, "ALL")
MultipartToSinglepart_management(tempCountyGradicule, tempCountyGradiculeSinglePart)
# Selects the same county as the other Select, but does it from the miniBufferedCounties
# so that the lines which lay inside of the county and running just along its edges
# are erased, as they should only exist as gradicules for the counties adjoining this
# one, but not for this one itself.
SelectLayerByAttribute_management(miniBufferedCounties, "NEW_SELECTION", whereClause)
MakeFeatureLayer_management(tempCountyGradiculeSinglePart, loadedTempGradicule)
SelectLayerByAttribute_management(loadedTempGradicule, "NEW_SELECTION", whereClause)
secVerGradiculeFields = ListFields(loadedTempGradicule)
#for secVerGradiculeField in secVerGradiculeFields:
# print "A secVerGradiculeField was found: " + str(secVerGradiculeField.name)
Erase_analysis(loadedTempGradicule, miniBufferedCounties, tempCountyGradiculePostErase, xyToleranceVal)
fieldsToCopy = ["SHAPE@", "County_Number", "County_Name", "DIRECTION", "COORD"]
# 2nd SearchCursor
newCursor = daSearchCursor(tempCountyGradiculePostErase, fieldsToCopy)
for newRow in newCursor:
outputFeatureList.append(newRow)
if 'newCursor' in locals():
del newCursor
else:
pass
try:
Delete_management(countyGradiculeShortWithUser)
except:
pass
CreateFeatureclass_management(sqlGdbLocation, countyGradiculeShortNoPath, "POLYLINE", "", "", "", spatialReferenceProjection)
AddField_management(countyGradiculeShortNoPath, "County_Number", "DOUBLE", "", "", "")
AddField_management(countyGradiculeShortNoPath, "County_Name", "TEXT", "", "", "55")
AddField_management(countyGradiculeShortNoPath, "DIRECTION", "TEXT", "", "", "5")
AddField_management(countyGradiculeShortNoPath, "COORD", "TEXT", "", "", "30")
print "First Intersected County Gradicule Row: " + str(outputFeatureList[0])
newCursor = daInsertCursor(countyGradiculeShortPath, fieldsToCopy)
counter = 1
for outputFeature in outputFeatureList:
rowToInsert = ([outputFeature])
insertedOID = newCursor.insertRow(outputFeature)
counter += 1
print "Inserted Row with Object ID of " + str(insertedOID)
# Load the feature class. Remove anything shorter than 850 feet.
MakeFeatureLayer_management(countyGradiculeShortPath, loadedOutputGradicule)
# Select the rows that have geometry which is shorter than 850 feet.
## Note that Shape.STLength() returns units in the projection
## or coordinate system that it the feature class is stored in.
whereClause = """ Shape.STLength() < 850 """
print "The whereClause is " + str(whereClause)
SelectLayerByAttribute_management(loadedOutputGradicule, "NEW_SELECTION", whereClause)
# If there is at least one row selected, delete each selected row.
if int(GetCount_management(loadedOutputGradicule).getOutput(0)) > 0:
print str(GetCount_management(loadedOutputGradicule).getOutput(0)) + "rows selected."
DeleteRows_management(loadedOutputGradicule)
else:
print "No rows were selected to delete."
if 'newCursor' in locals():
del newCursor
else:
pass
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)
for cdrsCursorItem in cdrsSearchCursor:
featureItem = list(cdrsCursorItem)
featureItem.append(starttime)
featuresToTransfer.append(featureItem)
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 createShortGradiculeLinesForEachCounty():
# Get/use the same projection as the one used for the county roads.
spatialReferenceProjection = Describe(
sharedNonStateSystem).spatialReference
env.workspace = sqlGdbLocation
inputCountyGradicule = countyCountyGradicule
bufferedCounties = 'bufferedCounties'
countiesToCopy = 'countiesToCopy'
gradiculeToCopy = 'gradiculeToCopy'
loadedGradiculeCopy = 'loadedGradiculeCopy'
loadedTempGradicule = 'loadedTempGradicule'
#unBufferedCounties = 'unBufferedCounties'
# Using the miniBuffered process changes it from
# 1457 total output features to 1481 (at 2.1k)
# total output features.
miniBufferedCounties = 'miniBufferedCounties'
loadedOutputGradicule = 'loadedOutputGradicule'
tempCounties = r'in_memory\tempCounties'
tempCountyGradicule = r'in_memory\tempCountyGradicule'
tempCountyGradiculePostErase = r'in_memory\tempCountyGradiculePostErase'
tempCountyGradiculeSinglePart = r'in_memory\tempCountyGradiculeSinglePart'
bufferCursorFields = ["OBJECTID", "COUNTY_NAME"]
MakeFeatureLayer_management(sharedCounties, countiesToCopy)
MakeFeatureLayer_management(countyCountyGradicule, gradiculeToCopy)
CopyFeatures_management(gradiculeToCopy, countyGradiculeCopied)
MakeFeatureLayer_management(countyGradiculeCopied, loadedGradiculeCopy)
# Might be worth dissolving based on COORD & County_Name prior
# to removing the County_Name field, if that's a possibility.
# Or better yet, just make it so that the Gradicule lines for
# a particular county are eligible for intersecting and
# erasing with that same county's polygon's. All we're
# trying to do here is make it so that the county's original
# gradicule lines are about half of their original size.
# Don't need to find out which gradicule lines are close to
# the county or anything else like that. Just need to reduce
# the size of the lines and keep the parts that are nearest
# the county that they go with.
# Remove the County_Name field so that the intersect can add it
# back and populate it only where the county buffer actually
# intersects the lines.
#DeleteField_management(countyGradiculeCopied, "County_Name")
# Elaine requested that this be 1000 Feet shorter.
# I made it 2000 feet shorter, because it still seemed too big.
Buffer_analysis(sharedCounties, countiesBuffered, "8000 Feet")
Buffer_analysis(sharedCounties, countiesMiniBuffered, "1500 Feet")
bufferedCountyPolygonList = list()
outputFeatureList = list()
# 1st SearchCursor
newCursor = daSearchCursor(countiesBuffered, bufferCursorFields)
for newRow in newCursor:
bufferedCountyPolygonList.append(list(newRow))
if 'newCursor' in locals():
del newCursor
else:
pass
MakeFeatureLayer_management(countiesBuffered, bufferedCounties)
MakeFeatureLayer_management(countiesMiniBuffered, miniBufferedCounties)
loadedCountiesFields = ListFields(bufferedCounties)
for loadedCountiesField in loadedCountiesFields:
print "A loadedCountiesField was found: " + str(
loadedCountiesField.name)
countyGradiculeFields = ListFields(loadedGradiculeCopy)
for countyGradiculeField in countyGradiculeFields:
print "A countyGradiculeField was found: " + str(
countyGradiculeField.name)
for listedRow in bufferedCountyPolygonList:
print str(listedRow)
selectCounty = listedRow[1]
whereClause = """ "COUNTY_NAME" = '""" + str(selectCounty) + """' """
print "The whereClause is " + str(whereClause)
SelectLayerByAttribute_management(bufferedCounties, "NEW_SELECTION",
whereClause)
SelectLayerByAttribute_management(loadedGradiculeCopy, "NEW_SELECTION",
whereClause)
Intersect_analysis([loadedGradiculeCopy, bufferedCounties],
tempCountyGradicule, "ALL")
MultipartToSinglepart_management(tempCountyGradicule,
tempCountyGradiculeSinglePart)
# Selects the same county as the other Select, but does it from the miniBufferedCounties
# so that the lines which lay inside of the county and running just along its edges
# are erased, as they should only exist as gradicules for the counties adjoining this
# one, but not for this one itself.
SelectLayerByAttribute_management(miniBufferedCounties,
"NEW_SELECTION", whereClause)
MakeFeatureLayer_management(tempCountyGradiculeSinglePart,
loadedTempGradicule)
SelectLayerByAttribute_management(loadedTempGradicule, "NEW_SELECTION",
whereClause)
secVerGradiculeFields = ListFields(loadedTempGradicule)
#for secVerGradiculeField in secVerGradiculeFields:
# print "A secVerGradiculeField was found: " + str(secVerGradiculeField.name)
Erase_analysis(loadedTempGradicule, miniBufferedCounties,
tempCountyGradiculePostErase, xyToleranceVal)
fieldsToCopy = [
"SHAPE@", "County_Number", "County_Name", "DIRECTION", "COORD"
]
# 2nd SearchCursor
newCursor = daSearchCursor(tempCountyGradiculePostErase, fieldsToCopy)
for newRow in newCursor:
outputFeatureList.append(newRow)
if 'newCursor' in locals():
del newCursor
else:
pass
try:
Delete_management(countyGradiculeShortWithUser)
except:
pass
CreateFeatureclass_management(sqlGdbLocation, countyGradiculeShortNoPath,
"POLYLINE", "", "", "",
spatialReferenceProjection)
AddField_management(countyGradiculeShortNoPath, "County_Number", "DOUBLE",
"", "", "")
AddField_management(countyGradiculeShortNoPath, "County_Name", "TEXT", "",
"", "55")
AddField_management(countyGradiculeShortNoPath, "DIRECTION", "TEXT", "",
"", "5")
AddField_management(countyGradiculeShortNoPath, "COORD", "TEXT", "", "",
"30")
print "First Intersected County Gradicule Row: " + str(
outputFeatureList[0])
newCursor = daInsertCursor(countyGradiculeShortPath, fieldsToCopy)
counter = 1
for outputFeature in outputFeatureList:
rowToInsert = ([outputFeature])
insertedOID = newCursor.insertRow(outputFeature)
counter += 1
print "Inserted Row with Object ID of " + str(insertedOID)
# Load the feature class. Remove anything shorter than 850 feet.
MakeFeatureLayer_management(countyGradiculeShortPath,
loadedOutputGradicule)
# Select the rows that have geometry which is shorter than 850 feet.
## Note that Shape.STLength() returns units in the projection
## or coordinate system that it the feature class is stored in.
whereClause = """ Shape.STLength() < 850 """
print "The whereClause is " + str(whereClause)
SelectLayerByAttribute_management(loadedOutputGradicule, "NEW_SELECTION",
whereClause)
# If there is at least one row selected, delete each selected row.
if int(GetCount_management(loadedOutputGradicule).getOutput(0)) > 0:
print str(GetCount_management(loadedOutputGradicule).getOutput(
0)) + "rows selected."
DeleteRows_management(loadedOutputGradicule)
else:
print "No rows were selected to delete."
if 'newCursor' in locals():
del newCursor
else:
pass
def extendAndIntersectRoadFeatures(
quarterOrHalf
): # Place the operations that extend each road line segment by a certain distance here.
# Should extend all the features that exist in the post-erase dataset. Might be more difficult
# to calculate the angle of these lines accurately, but it should be easier to figure out
# than trying to get the lines to split correctly with the buggy SplitLineAtPoint tool.
if quarterOrHalf.lower() == "quarter":
extensionLinesTextName = "createdExtensionLines_Q"
createdExtensionLines = createdExtensionLines_Q
# 9000 ft increase for _Q version.
# Must be larger than the county bufferDistance (20000)
extensionDistance = 31176
extensionLinesTextName = "createdExtensionLines_Q"
countyRoadNameRosette = countyRoadNameRosette_Q
rosetteTextName = "countyRoadNameRosette_Q"
tempRoadNameRosette = tempRoadNameRosette_Q
tempRosetteTextName = "tempRoadNameRosette_Q"
tempRoadNameRosetteSP = tempRoadNameRosetteSinglePoint_Q
tempRosetteSPTextName = "tempRoadNameRosetteSinglePoint_Q"
countyBorderFeature = countyBorderFeature_Q
elif quarterOrHalf.lower() == "half":
extensionLinesTextName = "createdExtensionLines_H"
createdExtensionLines = createdExtensionLines_H
# Must be larger than the county bufferDistance (11000)
extensionDistance = 22176
extensionLinesTextName = "createdExtensionLines_H"
countyRoadNameRosette = countyRoadNameRosette_H
rosetteTextName = "countyRoadNameRosette_H"
tempRoadNameRosette = tempRoadNameRosette_H
tempRosetteTextName = "tempRoadNameRosette_H"
tempRoadNameRosetteSP = tempRoadNameRosetteSinglePoint_H
tempRosetteSPTextName = "tempRoadNameRosetteSinglePoint_H"
countyBorderFeature = countyBorderFeature_H
else:
print "quarterOrHalf variable not correctly defined."
raise (Exception("quarterOrHalf value error."))
print "Starting to extend and intersect road features."
if Exists(createdExtensionLines):
Delete_management(createdExtensionLines)
else:
pass
CreateFeatureclass_management(inMemGDB, extensionLinesTextName, "POLYLINE",
"", "", "", spatialReferenceProjection)
# Add a column for roadname called roadNameForSplit.
AddField_management(createdExtensionLines, "roadNameForSplit", "TEXT", "",
"", "55")
# Add a column which stores the angle to display a label called called LabelAngle.
AddField_management(createdExtensionLines, "LabelAngle", "DOUBLE", "", "",
"") # Change to double.
# Add a column which stores the County Number.
AddField_management(createdExtensionLines, "County_Number", "DOUBLE", "",
"", "")
roadLinesToInsertList = list()
roadLinesList = getRoadLinesList()
for roadLinesItem in roadLinesList:
roadNameToUse = roadLinesItem[2]
countyNumber = roadLinesItem[3]
linePointsArray = ArcgisArray()
firstPointTuple = (roadLinesItem[1].firstPoint.X,
roadLinesItem[1].firstPoint.Y)
lastPointTuple = (roadLinesItem[1].lastPoint.X,
roadLinesItem[1].lastPoint.Y)
# Make this a two-step process.
# Might be as simple as
# adding _1 to the end of the first set of variables,
# adding _2 to the end of the second set of variables,
# then making the extensions in both directions
# and creating a new line that has the endpoints
# from both sides as it's first and last point.
# if necessary, could add the other points in between
# but probably not necessary just for generating
# an intersection point.
yValue_1 = -(lastPointTuple[1] - firstPointTuple[1]
) # made y value negative
xValue_1 = lastPointTuple[0] - firstPointTuple[0]
lineDirectionAngle_1 = math.degrees(math.atan2(
xValue_1, yValue_1)) # reversed x and y
lineDirectionAngle_1 = -(((lineDirectionAngle_1 + 180) % 360) - 180
) # correction for certain quadrants
#print "lineDirectionAngle: " + str(lineDirectionAngle_1)
origin_x_1 = firstPointTuple[0]
origin_y_1 = firstPointTuple[1]
yValue_2 = -(firstPointTuple[1] - lastPointTuple[1]
) # made y value negative
xValue_2 = firstPointTuple[0] - lastPointTuple[0]
lineDirectionAngle_2 = math.degrees(math.atan2(
xValue_2, yValue_2)) # reversed x and y
lineDirectionAngle_2 = -(((lineDirectionAngle_2 + 180) % 360) - 180
) # correction for certain quadrants
#print "lineDirectionAngle: " + str(lineDirectionAngle_2)
origin_x_2 = lastPointTuple[0]
origin_y_2 = lastPointTuple[1]
(disp_x_1, disp_y_1) = (extensionDistance *
math.sin(math.radians(lineDirectionAngle_1)),
extensionDistance *
math.cos(math.radians(lineDirectionAngle_1)))
(end_x_1, end_y_1) = (origin_x_1 + disp_x_1, origin_y_1 + disp_y_1)
(disp_x_2, disp_y_2) = (extensionDistance *
math.sin(math.radians(lineDirectionAngle_2)),
extensionDistance *
math.cos(math.radians(lineDirectionAngle_2)))
(end_x_2, end_y_2) = (origin_x_2 + disp_x_2, origin_y_2 + disp_y_2)
startPoint = ArcgisPoint()
endPoint = ArcgisPoint()
startPoint.ID = 0
startPoint.X = end_x_1
startPoint.Y = end_y_1
endPoint.ID = 1
endPoint.X = end_x_2
endPoint.Y = end_y_2
linePointsArray.add(startPoint)
linePointsArray.add(endPoint)
newLineFeature = ArcgisPolyLine(linePointsArray)
# Need to create an extension for both ends of the line and add them
# to the array.
#newLineFeature = createdExtensionLinesCursor.newRow()
#newLineFeature.SHAPE = linePointsArray
lineDirectionOutput = "0"
if lineDirectionAngle_1 > 0:
lineDirectionOutput = lineDirectionAngle_1
elif lineDirectionAngle_2 > 0:
lineDirectionOutput = lineDirectionAngle_2
else:
pass
roadLinesToInsertList.append(
[newLineFeature, roadNameToUse, lineDirectionOutput, countyNumber])
#createdExtensionLinesCursor.insertRow([newLineFeature, roadNameToUse, lineDirectionOutput])
if "newLineFeature" in locals():
del newLineFeature
else:
pass
# Consider building this as a separate list and then just looping
# through the list to put it into the cursor instead
# of doing logic and inserting into the cursor at the same place.
#start editing session
#newEditingSession = daEditor(sqlGdbLocation)
#newEditingSession.startEditing()
#newEditingSession.startOperation()
createdExtensionLinesCursor = daInsertCursor(
createdExtensionLines,
["SHAPE@", "roadNameForSplit", "LabelAngle", "County_Number"])
for roadLinesToInsertItem in roadLinesToInsertList:
createdExtensionLinesCursor.insertRow(roadLinesToInsertItem)
# End editing session
#newEditingSession.stopOperation()
#newEditingSession.stopEditing(True)
if "createdExtensionLinesCursor" in locals():
del createdExtensionLinesCursor
else:
pass
# Remove the previous countyRoadNameRosette so that it can be recreated.
if Exists(rosetteTextName):
Delete_management(rosetteTextName)
else:
pass
CreateFeatureclass_management(sqlGdbLocation, rosetteTextName, "POINT", "",
"", "", spatialReferenceProjection)
AddField_management(countyRoadNameRosette, "roadNameForSplit", "TEXT", "",
"", "55")
AddField_management(countyRoadNameRosette, "LabelAngle", "DOUBLE", "", "",
"") # Change to double.
AddField_management(countyRoadNameRosette, "County_Number", "DOUBLE", "",
"", "")
AddField_management(countyRoadNameRosette, "COUNTY_NAME", "TEXT", "", "",
"55")
# Now then, need to check for the existence
# of and delete the point intersection layer
# if it exists.
# Then, recreate it and the proper fields.
inMemoryCountyBorderExtension = "aCountyBorderExtensionBuffer"
inMemoryExtensionLines = "aLoadedExtensionLines"
try:
Delete_management(inMemoryCountyBorderExtension)
except:
pass
try:
Delete_management(inMemoryExtensionLines)
except:
pass
# Temporary layer, use CopyFeatures_management to persist to disk.
MakeFeatureLayer_management(
countyBorderFeature,
inMemoryCountyBorderExtension) # County Border extension feature
# Temporary layer, use CopyFeatures_management to persist to disk.
MakeFeatureLayer_management(
createdExtensionLines,
inMemoryExtensionLines) # Line extension feature
borderFeatureList = getBorderFeatureList(quarterOrHalf)
borderFeatureList = sorted(borderFeatureList,
key=lambda feature: feature[3])
for borderFeature in borderFeatureList:
borderFeatureName = borderFeature[2]
borderFeatureNumber = borderFeature[3]
print "borderFeatureName: " + str(
borderFeatureName) + " & borderFeatureNumber: " + str(
int(borderFeatureNumber))
countyBorderWhereClause = ' "COUNTY_NUMBER" = ' + str(
int(borderFeatureNumber)) + ' '
SelectLayerByAttribute_management(inMemoryCountyBorderExtension,
"NEW_SELECTION",
countyBorderWhereClause)
countyBorderSelectionCount = GetCount_management(
inMemoryCountyBorderExtension)
print "County Borders Selected: " + str(countyBorderSelectionCount)
# Had to single-quote the borderFeatureNumber because it is stored as a string in the table.
# Unsingle quoted because it was changed to a float.
extensionLinesWhereClause = ' "COUNTY_NUMBER" = ' + str(
int(borderFeatureNumber)) + ' '
SelectLayerByAttribute_management(inMemoryExtensionLines,
"NEW_SELECTION",
extensionLinesWhereClause)
extensionLineSelectionCount = GetCount_management(
inMemoryExtensionLines)
print "Extension Lines Selected: " + str(extensionLineSelectionCount)
if Exists(tempRosetteTextName):
Delete_management(tempRosetteTextName)
else:
pass
if Exists(tempRosetteSPTextName):
Delete_management(tempRosetteSPTextName)
else:
pass
Intersect_analysis(
[inMemoryCountyBorderExtension, inMemoryExtensionLines],
tempRoadNameRosette, "ALL", "", "POINT")
# Intersect to an output temp layer.
# Next, need to loop through all of the counties.
# Get the county number and use it to select
# a county extension buffer in the county
# extension buffers layer.
# Then, use the county number to select
# all of the lines for that county
# in the extension lines layer.
# Then, export those to a temp layer in the fgdb.
# Change multipoint to singlepoint.
# Was working until I moved from gisprod to sdedev for the data source.
# not sure why. Check to make sure projections match.
# ^ Fixed.
try:
# Run the tool to create a new fc with only singlepart features
MultipartToSinglepart_management(tempRoadNameRosette,
tempRoadNameRosetteSP)
# Check if there is a different number of features in the output
# than there was in the input
inCount = int(
GetCount_management(tempRoadNameRosette).getOutput(0))
outCount = int(
GetCount_management(tempRoadNameRosetteSP).getOutput(0))
if inCount != outCount:
print "Found " + str(outCount -
inCount) + " multipart features."
#print "inCount, including multipart = " + str(inCount)
#print "outCount, singlepart only = " + str(outCount)
else:
print "No multipart features were found"
except ExecuteError:
print GetMessages()
except Exception as e:
print e
print "Appending the temp point layer to the county point intersection layer."
Append_management([tempRoadNameRosetteSP], countyRoadNameRosette,
"NO_TEST")
# K, worked correctly. Just need to change LabelAngle to a float and it might be what
# I want.
print "Done adding points to the countyRoadNameRosette feature class."
def createCountyLinesForEachCounty():
env.workspace = sqlGdbLocation
inputCountyLines = sharedCountyLines
inputCountyPolygons = sharedCounties
dissolvedCountyLines = countyLinesDissolved
loadedCounties = 'loadedCounties'
tempCountyLines = r'in_memory\tempCountyLines'
outputCountyLines = countyLinesIntersectedNoPath
bufferCursorFields = ["OBJECTID"]
# Dissolve all of those county lines into one set of lines
# then, need to create 105 features that are are intersected
# with the polygons from said line dissolve.
Dissolve_management(inputCountyLines, dissolvedCountyLines)
Buffer_analysis(inputCountyPolygons, countiesBuffered, "15500 Feet")
bufferedCountyPolygonList = list()
outputFeatureList = list()
# 1st SearchCursor
newCursor = daSearchCursor(countiesBuffered, bufferCursorFields)
for newRow in newCursor:
bufferedCountyPolygonList.append(list(newRow))
if 'newCursor' in locals():
del newCursor
else:
pass
MakeFeatureLayer_management(countiesBuffered, loadedCounties)
loadedCountiesFields = ListFields(loadedCounties)
for loadedCountiesField in loadedCountiesFields:
print "A loadedCountiesField was found: " + str(
loadedCountiesField.name)
for listedRow in bufferedCountyPolygonList:
selectNumber = listedRow[0]
whereClause = """ "OBJECTID" = """ + str(selectNumber)
print "The whereClause = " + str(whereClause)
SelectLayerByAttribute_management(loadedCounties, "NEW_SELECTION",
whereClause)
Intersect_analysis([dissolvedCountyLines, loadedCounties],
tempCountyLines, "ALL")
# 2nd SearchCursor
newCursor = daSearchCursor(tempCountyLines,
["SHAPE@", "County_Number", "County_Name"])
for newRow in newCursor:
outputFeatureList.append(newRow)
if 'newCursor' in locals():
del newCursor
else:
pass
try:
Delete_management(countyLinesIntersectedWithUser)
except:
pass
CreateFeatureclass_management(sqlGdbLocation, outputCountyLines,
"POLYLINE", "", "", "",
spatialReferenceProjection)
AddField_management(outputCountyLines, "County_Number", "DOUBLE", "", "",
"")
AddField_management(outputCountyLines, "County_Name", "TEXT", "", "", "55")
print "First Intersected County Row: " + str(outputFeatureList[0])
countyLinesIntersectFields = ["SHAPE@", "County_Number", "County_Name"]
newCursor = daInsertCursor(countyLinesIntersectedPath,
countyLinesIntersectFields)
counter = 1
for outputFeature in outputFeatureList:
rowToInsert = ([outputFeature])
insertedOID = newCursor.insertRow(outputFeature)
counter += 1
print "Inserted Row with Object ID of " + str(insertedOID)
if 'newCursor' in locals():
del newCursor
else:
pass
def featureReplacement(sourceFL, targetFL, featuresToSelect):
# 1c.) Get the common fields so that you can search and insert correctly.
targetFeatureDesc = Describe(targetFL)
targetFeatureFields = targetFeatureDesc.fields
targetFeatureOIDField = targetFeatureDesc.OIDFieldName
targetFeatureShapeField = targetFeatureDesc.shapeFieldName
targetFeatureFieldNames = [x.name for x in targetFeatureFields]
sourceFeatureDesc = Describe(sourceFL)
sourceFeatureFields = sourceFeatureDesc.fields
sourceFeatureOIDField = sourceFeatureDesc.OIDFieldName
sourceFeatureShapeField = sourceFeatureDesc.shapeFieldName
sourceFeatureFieldNames = [x.name for x in sourceFeatureFields]
excludeFieldNames = [targetFeatureOIDField, targetFeatureShapeField, sourceFeatureOIDField, sourceFeatureShapeField]
searchCursorFields = [x for x in targetFeatureFieldNames if x in sourceFeatureFieldNames and x not in excludeFieldNames]
searchCursorFields.append('SHAPE@')
# Remove and then re-add the uniqueKeyField so that it is the last column and can be easily referenced.
searchCursorFields.remove(str(uniqueKeyFieldToUse))
searchCursorFields.append(str(uniqueKeyFieldToUse))
insertCursorFields = searchCursorFields
# Select the features in the source layer
SelectLayerByAttribute_management(sourceFL, "NEW_SELECTION", featuresToSelect)
# Repeat the selection in the target layer
SelectLayerByAttribute_management(targetFL, "NEW_SELECTION", featuresToSelect)
# Then select the common segments spatially, with some room for possible movement.
SelectLayerByLocation_management(targetFL, 'WITHIN_A_DISTANCE', sourceFL, 50, 'SUBSET_SELECTION')
# 5.) Count selected features in the target and delete them if there is at least 1.
countResult0 = GetCount_management(targetFL)
intCount0 = int(countResult0.getOutput(0))
if intCount0 >= 1:
# 12.) Delete the selected features in the input layer, if any.
try:
DeleteFeatures_management(targetFL)
except:
print("Could not delete features for the selection " + str(featuresToSelect) + ".")
else:
pass
# 10.) Count to make sure that at least one feature is selected.
countResult1 = GetCount_management(sourceFL)
intCount1 = int(countResult1.getOutput(0))
if intCount1 >= 1:
# If so, cursor the features out
featureList = list()
newCursor = daSearchCursor(sourceFL, searchCursorFields)
for cursorItem in newCursor:
featureList.append(list(cursorItem))
try:
del newCursor
except:
pass
# 11.) Insert the selected source features into the copy of the centerlines.
newCursor = daInsertCursor(targetFL, insertCursorFields)
for featureItem in featureList:
newCursor.insertRow(featureItem)
try:
del newCursor
except:
pass
try:
del featureList
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
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 dissolveBasedOnLocalRouteKeys(routesToDissolve, subsetSelectionQuery):
# Moved out the selection building code to the other function where
# it makes more sense.
# Use similar code here to what is found in the main dissolve loop.
# Just need to do multiselection on all of the possible routes that
# match the subsetSelectionQuery and for each multiselection, create
# a dissolve feature set, then use the same reintroduction tests
# that are used in the main dissolve to reintroduce the dissolved
# lines without removing any that weren't dissolved or adding
# any new overlaps.
fcAsFeatureLayerForDissolves = 'FCAsFeatureLayer_Dissolves'
if Exists(fcAsFeatureLayerForDissolves):
Delete_management(fcAsFeatureLayerForDissolves)
else:
pass
MakeFeatureLayer_management(routesToDissolve, fcAsFeatureLayerForDissolves)
lrsKeyFieldList = [str(lrsKeyToUse)]
newCursor = daSearchCursor(routesToDissolve, lrsKeyFieldList,
subsetSelectionQuery)
uniqueLRSKeysDict = dict()
for cursorRow in newCursor:
uniqueLRSKeysDict[str(cursorRow[0])] = 1
try:
del newCursor
except:
pass
uniqueLRSKeysList = uniqueLRSKeysDict.keys()
try:
uniqueLRSKeysList.remove('None')
except:
print(
"Could not remove 'None' from the list of uniqueLRSKeys since it was not a part of the list."
)
print("LRSKey list creation successful.")
print('Found ' + str(len(uniqueLRSKeysList)) +
' unique LRS Keys in the centerline data for this query:')
print(str(subsetSelectionQuery))
#Use multiSelection
multiSelectionQueryBase = str(
str(subsetSelectionQuery) + ''' AND ''' + ''' "''' + str(lrsKeyToUse) +
'''" IS NOT NULL AND "''' + str(lrsKeyToUse) + '''" IN (''')
multiSelectionQuery = multiSelectionQueryBase
multiCounter = 0
multiDissolveFields = [
str(lrsKeyToUse), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R'
]
##multiDissolveFields = str(lrsKeyToUse) + ';LRS_COUNTY_PRE;LRS_ROUTE_PREFIX;LRS_ROUTE_NUM;LRS_ROUTE_SUFFIX;LRS_UNIQUE_IDENT;LRS_UNIQUE_IDENT1'
##multiStatsFields = str(n1FromMeas) + " MIN;" + str(n1ToMeas) + " MAX"
multiStatsFields = ""
singlePart = "SINGLE_PART"
unsplitLines = "UNSPLIT_LINES"
# 3.) Loop through the list of unique LRS Keys
for uniqueKeyItem in uniqueLRSKeysList:
# Make a selection list that includes 50 keys, then select the keys and dissolve to make a new
# feature class.
# After the selection is dissolved, use a spatial select on the original feature class and
# an attribute selection on the original feature class to see which original features should
# be deleted.
# Then, delete the selected features (if at least 1 selected).
#
try:
Delete_management(dissolveOutFC)
except:
print("Could not delete the dissolveOutFC layer.")
# 4.) For groups of 2000 LRS Keys, select all the features with those LRS Keys.
if multiCounter <= 1999:
multiSelectionQuery += """'""" + str(
uniqueKeyItem) + """'""" + """, """
multiCounter += 1
else:
# Add the current item, then
multiSelectionQuery += """'""" + str(
uniqueKeyItem) + """'""" + """, """
# Remove the trailing ", " and add a closing parenthesis.
multiSelectionQuery = multiSelectionQuery[:-2] + """) """
SelectLayerByAttribute_management(fcAsFeatureLayerForDissolves,
"NEW_SELECTION",
multiSelectionQuery)
# Have to do from step 5 on here also.
### -shouldbeafunctionblock#1- ###
# 5.) Count selected features.
countResult0 = GetCount_management(fcAsFeatureLayerForDissolves)
intCount0 = int(countResult0.getOutput(0))
if intCount0 >= 1:
# 6.) Make a new layer or dissolved layer from this selection.
Dissolve_management(fcAsFeatureLayerForDissolves,
dissolveOutFC, multiDissolveFields,
multiStatsFields, singlePart, unsplitLines)
# 7.) Count the number of dissolved features.
countResult1 = GetCount_management(dissolveOutFC)
intCount1 = int(countResult1.getOutput(0))
print('Counted ' + str(intCount1) +
' features returned for that dissolve.')
# 8a.) If the number of dissolved features is 0, then append the error to the error file
# and go on to the next LRS Key in the loop.
if intCount1 == 0:
with open(dissolveErrorsFile, 'a') as errorFile:
errorFile.write(str(multiSelectionQuery))
# 8b.) From the spatial select, select the subset of features that also have a matching LRS Key.
else:
SelectLayerByAttribute_management(
fcAsFeatureLayerForDissolves, 'NEW_SELECTION',
multiSelectionQuery)
# 9.) Else, spatially select features in the original feature class with 'SHARE_A_LINE_SEGMENT_WITH'.
SelectLayerByLocation_management(
fcAsFeatureLayerForDissolves,
'SHARE_A_LINE_SEGMENT_WITH', dissolveOutFC, 0,
'SUBSET_SELECTION')
# 10.) Count to make sure that at least one feature is selected.
countResult2 = GetCount_management(
fcAsFeatureLayerForDissolves)
intCount2 = int(countResult2.getOutput(0))
print(
'There were ' + str(intCount2) +
' features selected for replacement in the fcAsFeatureLayerForDissolves layer.'
)
if intCount2 >= 1:
# 11.) If so, cursor the features out of the dissolve layer.
featureList = list()
searchCursorFields = [
str(lrsKeyToUse), 'LRS_COUNTY_PRE',
'LRS_ROUTE_PREFIX', 'LRS_ROUTE_NUM',
'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L',
'KDOT_COUNTY_R', 'SHAPE@'
]
newCursor = daSearchCursor(dissolveOutFC,
searchCursorFields)
for cursorItem in newCursor:
featureList.append(list(cursorItem))
try:
del newCursor
except:
pass
# 12.) Delete the selected features in the input layer.
try:
DeleteFeatures_management(
fcAsFeatureLayerForDissolves)
except:
print(
"Could not delete features for the selection "
+ str(multiSelectionQuery) + ".")
# 13.) Insert the features from the dissolve layer into the copy of the centerlines.
insertCursorFields = [
str(lrsKeyToUse), 'LRS_COUNTY_PRE',
'LRS_ROUTE_PREFIX', 'LRS_ROUTE_NUM',
'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L',
'KDOT_COUNTY_R', 'SHAPE@'
]
newCursor = daInsertCursor(
fcAsFeatureLayerForDissolves, insertCursorFields)
for featureItem in featureList:
newCursor.insertRow(featureItem)
try:
del newCursor
except:
pass
try:
del featureList
except:
pass
else:
pass
multiSelectionQuery = ''' "''' + str(
lrsKeyToUse) + '''" IS NOT NULL AND "''' + str(
lrsKeyToUse) + '''" IN ('''
multiCounter = 0
### -shouldbeafunctionblock#1- ###
# After the for loop, if there is still anything remaining which was unselected in the
# the previous multiSelectionQuery steps.
# Remove the trailing ", " and add a closing parenthesis.
if multiSelectionQuery != multiSelectionQueryBase:
multiSelectionQuery = multiSelectionQuery[:-2] + """) """
else:
# The selection query would not select anything.
return
SelectLayerByAttribute_management(fcAsFeatureLayerForDissolves,
"NEW_SELECTION", multiSelectionQuery)
# Then redo from step 5 on at the end of the loop IF there is anything left to select
# which was not selected... so if selectionCounter != 0.
### -shouldbeafunctionblock#2- ###
# 5.) Count selected features.
countResult0 = GetCount_management(fcAsFeatureLayerForDissolves)
intCount0 = int(countResult0.getOutput(0))
if intCount0 >= 1:
# 6.) Make a new layer or dissolved layer from this selection. -- Question about fields.
Dissolve_management(fcAsFeatureLayerForDissolves, dissolveOutFC,
multiDissolveFields, multiStatsFields, singlePart,
unsplitLines)
# 7.) Count the number of dissolved features.
countResult1 = GetCount_management(dissolveOutFC)
intCount1 = int(countResult1.getOutput(0))
print('Counted ' + str(intCount1) +
' features returned for that dissolve.')
# 8a.) If the number of dissolved features is 0, then append the error to the error file
# and go on to the next LRS Key in the loop.
if intCount1 == 0:
with open(dissolveErrorsFile, 'a') as errorFile:
errorFile.write(str(multiSelectionQuery))
# 8b.) From the spatial select, select the subset of features that also have a matching LRS Key.
else:
SelectLayerByAttribute_management(fcAsFeatureLayerForDissolves,
'NEW_SELECTION',
multiSelectionQuery)
# 9.) Else, spatially select features in the original feature class with 'SHARE_A_LINE_SEGMENT_WITH'.
SelectLayerByLocation_management(fcAsFeatureLayerForDissolves,
'SHARE_A_LINE_SEGMENT_WITH',
dissolveOutFC, 0,
'SUBSET_SELECTION')
# 10.) Count to make sure that at least one feature is selected.
countResult2 = GetCount_management(fcAsFeatureLayerForDissolves)
intCount2 = int(countResult2.getOutput(0))
print(
'There were ' + str(intCount2) +
' features selected in the fcAsFeatureLayerForDissolves layer.'
)
if intCount2 >= 1:
# 11.) If so, cursor the features out of the dissolve layer.
featureList = list()
searchCursorFields = [
str(lrsKeyToUse), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R',
'SHAPE@'
]
newCursor = daSearchCursor(dissolveOutFC, searchCursorFields)
for cursorItem in newCursor:
featureList.append(list(cursorItem))
try:
del newCursor
except:
pass
# 12.) Delete the selected features in the input layer.
try:
DeleteFeatures_management(fcAsFeatureLayerForDissolves)
except:
print("Could not delete features for the selection " +
str(multiSelectionQuery) + ".")
# 13.) Insert the features from the dissolve layer into the copy of the centerlines.
insertCursorFields = [
str(lrsKeyToUse), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R',
'SHAPE@'
]
newCursor = daInsertCursor(fcAsFeatureLayerForDissolves,
insertCursorFields)
for featureItem in featureList:
newCursor.insertRow(featureItem)
try:
del newCursor
except:
pass
try:
del featureList
except:
pass
else:
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,
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 RCRS_SearchCursor
except:
pass
try:
del RCRS_InsertCursor
except:
pass
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 createCountyLinesForEachCounty():
inputCountyLines = r'Database Connections\sdedev_GIS_DEV.sde\SHARED.COUNTY_LINES'
inputCountyPolygons = r'Database Connections\sdedev_GIS_DEV.sde\SHARED.COUNTIES'
dissolvedCountyLines = r'Database Connections\countyMapsSQLSDE.sde\CountyLinesDissolved'
bufferedCountyPolygons = r'Database Connections\countyMapsSQLSDE.sde\CountiesBuffered'
loadedCounties = 'loadedCounties'
tempCountyLines = r'in_memory\tempCountyLines'
outputCountyLines = r'Database Connections\countyMapsSQLSDE.sde\CountyLinesIntersected'
bufferCursorFields = ["OBJECTID"]
# Need to dissolve all of those county lines into one set of lines
# then, need to create 105 features that are are intersected
# with the polygons from said line dissolve.
arcpy.Dissolve_management(inputCountyLines, dissolvedCountyLines)
arcpy.Buffer_analysis(inputCountyPolygons, bufferedCountyPolygons, "15500 Feet")
bufferedCountyPolygonList = list()
outputFeatureList = list()
# 1st SearchCursor
newCursor = daSearchCursor(bufferedCountyPolygons, bufferCursorFields)
for newRow in newCursor:
bufferedCountyPolygonList.append(list(newRow))
if 'newCursor' in locals():
del newCursor
else:
pass
arcpy.MakeFeatureLayer_management(bufferedCountyPolygons, loadedCounties)
for listedRow in bufferedCountyPolygonList:
selectNumber = listedRow[0]
whereClause = " \"OBJECTID\" = '" + str(selectNumber) + "' "
arcpy.SelectLayerByAttribute_management(loadedCounties, "NEW_SELECTION", whereClause)
arcpy.Intersect_analysis([dissolvedCountyLines, loadedCounties], tempCountyLines, "ALL")
# 2nd SearchCursor
newCursor = daSearchCursor(tempCountyLines, ["SHAPE@", "County_Number", "County_Name"])
for newRow in newCursor:
outputFeatureList.append(newRow)
if 'newCursor' in locals():
del newCursor
else:
pass
# 1st CreateFeatures_management
# 1st, 2nd AddField_management
lambertProjectionLocationKansas = r"\\gisdata\ArcGIS\GISdata\NAD_83_Kansas_Lambert_Conformal_Conic_Feet.prj"
arcpy.CreateFeatureclass_management(r'Database Connections\countyMapsSQLSDE.sde', "CountyLinesIntersected", "POLYLINE", "", "", "", lambertProjectionLocationKansas)
arcpy.AddField_management(outputCountyLines, "County_Number", "DOUBLE", "", "", "")
arcpy.AddField_management(outputCountyLines, "County_Name", "TEXT", "", "", "55")
print "First Intersected County Row: " + str(outputFeatureList[0])
# 1st InsertCursor
# Use this again later. Very useful. -- Will need when trying to deserialize arcpy objects from multiprocessing and
# then write them to a feature class, since I'll have to call arcpy.point(array) where array is an x,y coord in a
# list format prior to writing them to a gdb or sde.
newCursor = daInsertCursor(os.path.join(r'Database Connections\countyMapsSQLSDE.sde', 'CountyLinesIntersected'), ["SHAPE@", "County_Number", "County_Name"])
counter = 1
for outputFeature in outputFeatureList:
rowToInsert = ([outputFeature])
insertedOID = newCursor.insertRow(outputFeature)
counter += 1
print "Inserted Row with Object ID of " + str(insertedOID)
if 'newCursor' in locals():
del newCursor
else:
pass
def transferBasedOnLocalRouteKeys():
# Build a list of the unique route keys that match the given criteria:
# Then, use that list to select the features in the source with those
# keys.
# Next, spatially select features in the target layer with the
# selected features from the source layer.
# If there are more than 0 features selected, delete the selected
# target features.
# Then, cursor in the selected source features.
subsetSelectionQuery = """ KDOT_LRS_KEY LIKE '%L%' AND NOT KDOT_LRS_KEY LIKE '%W%' """
fcAsFeatureLayerForTransferring = 'FCAsFeatureLayer_Transferring'
if Exists(fcAsFeatureLayerForTransferring):
Delete_management(fcAsFeatureLayerForTransferring)
else:
pass
MakeFeatureLayer_management(fcWithroutesToTransferFrom,
fcAsFeatureLayerForTransferring)
MakeFeatureLayer_management(targetFC1, targetFC1asFeatureLayer)
lrsKeyFieldList = [str(lrsKeyToUse)]
newCursor = daSearchCursor(fcWithroutesToTransferFrom, lrsKeyFieldList,
subsetSelectionQuery)
uniqueLRSKeysDict = dict()
for cursorRow in newCursor:
uniqueLRSKeysDict[str(cursorRow[0])] = 1
try:
del newCursor
except:
pass
uniqueLRSKeysList = uniqueLRSKeysDict.keys()
try:
uniqueLRSKeysList.remove('None')
except:
print(
"Could not remove 'None' from the list of uniqueLRSKeys since it was not a part of the list."
)
print("LRSKey list creation successful.")
print('Found ' + str(len(uniqueLRSKeysList)) +
' unique LRS Keys in the centerline data for this query:')
print(str(subsetSelectionQuery))
#Use multiSelection
multiSelectionQueryBase = str(
str(subsetSelectionQuery) + ''' AND ''' + ''' "''' + str(lrsKeyToUse) +
'''" IS NOT NULL AND "''' + str(lrsKeyToUse) + '''" IN (''')
multiSelectionQuery = multiSelectionQueryBase
multiCounter = 0
##multiDissolveFields = [str(lrsKeyToUse), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX', 'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
## 'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R']
##multiDissolveFields = str(lrsKeyToUse) + ';LRS_COUNTY_PRE;LRS_ROUTE_PREFIX;LRS_ROUTE_NUM;LRS_ROUTE_SUFFIX;LRS_UNIQUE_IDENT;LRS_UNIQUE_IDENT1'
##multiStatsFields = str(n1FromMeas) + " MIN;" + str(n1ToMeas) + " MAX"
##multiStatsFields = ""
##singlePart = "SINGLE_PART"
##unsplitLines = "UNSPLIT_LINES"
# 3.) Loop through the list of unique LRS Keys
for uniqueKeyItem in uniqueLRSKeysList:
# Make a selection list that includes 50 keys, then select the keys and dissolve to make a new
# feature class.
# After the selection is dissolved, use a spatial select on the original feature class and
# an attribute selection on the original feature class to see which original features should
# be deleted.
# Then, delete the selected features (if at least 1 selected).
# Basically, doing it piece by piece is a problem since I'm not including LRS KEY
# selections to prevent the spatial selection from deleting pieces that overlap, but
# that should have different LRS Keys. Need to do all of the features
# at once to make sure that I'm not deleting pieces that should actually be there.
# Seems like it shouldn't be a problem, but the numbers say it is.
# 4.) For groups of 200000 LRS Keys, select all the features with those LRS Keys.
if multiCounter <= 199999:
multiSelectionQuery += """'""" + str(
uniqueKeyItem) + """'""" + """, """
multiCounter += 1
else:
# Add the current item, then
multiSelectionQuery += """'""" + str(
uniqueKeyItem) + """'""" + """, """
# Remove the trailing ", " and add a closing parenthesis.
multiSelectionQuery = multiSelectionQuery[:-2] + """) """
SelectLayerByAttribute_management(fcAsFeatureLayerForTransferring,
"NEW_SELECTION",
multiSelectionQuery)
# Have to do from step 5 on here also.
### -shouldbeafunctionblock#1- ###
# 5.) Count selected features.
countResult0 = GetCount_management(fcAsFeatureLayerForTransferring)
intCount0 = int(countResult0.getOutput(0))
if intCount0 >= 1:
print(
"Spatially selecting with the fcAsFeatureLayerForTransferring features, of which there are "
+ str(intCount0) + " selected.")
##print("Selected by this query:")
##print(str(multiSelectionQuery))
# 9.) Else, spatially select features in the original feature class with 'SHARE_A_LINE_SEGMENT_WITH'.
SelectLayerByLocation_management(
targetFC1asFeatureLayer, "SHARE_A_LINE_SEGMENT_WITH",
fcAsFeatureLayerForTransferring, 0, "NEW_SELECTION")
# Added to prevent the Selection from taking over '%W%' routes at this time.
SelectLayerByAttribute_management(targetFC1asFeatureLayer,
"SUBSET_SELECTION",
subsetSelectionQuery)
# 10.) Count to make sure that at least one feature is selected.
countResult2 = GetCount_management(targetFC1asFeatureLayer)
intCount2 = int(countResult2.getOutput(0))
print(
'There were ' + str(intCount2) +
' features selected for replacement in the targetFC1asFeatureLayer layer.'
)
if intCount2 >= 1:
# 11.) If so, cursor the features out of the dissolve layer.
featureList = list()
searchCursorFields = [
str(lrsKeyToUse),
str(startMeasure),
str(endMeasure), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX',
'LRS_UNIQUE_IDENT', 'LRS_UNIQUE_IDENT1',
'KDOT_COUNTY_L', 'KDOT_COUNTY_R', 'LABEL', 'SHAPE@'
]
newCursor = daSearchCursor(fcAsFeatureLayerForTransferring,
searchCursorFields)
for cursorItem in newCursor:
featureList.append(list(cursorItem))
try:
del newCursor
except:
pass
# 12.) Delete the selected features in the input layer.
try:
DeleteFeatures_management(targetFC1asFeatureLayer)
except:
print("Could not delete features for the selection " +
str(multiSelectionQuery) + ".")
# 13.) Insert the features from the dissolve layer into the copy of the centerlines.
insertCursorFields = [
str(lrsKeyToUse),
str(startMeasure),
str(endMeasure), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX',
'LRS_UNIQUE_IDENT', 'LRS_UNIQUE_IDENT1',
'KDOT_COUNTY_L', 'KDOT_COUNTY_R', 'LABEL', 'SHAPE@'
]
newCursor = daInsertCursor(targetFC1asFeatureLayer,
insertCursorFields)
for featureItem in featureList:
newCursor.insertRow(featureItem)
try:
del newCursor
except:
pass
try:
del featureList
except:
pass
else:
pass
### -shouldbeafunctionblock#1- ###
multiSelectionQuery = ''' "''' + str(
lrsKeyToUse) + '''" IS NOT NULL AND "''' + str(
lrsKeyToUse) + '''" IN ('''
multiCounter = 0
# After the for loop, if there is still anything remaining which was unselected in the
# the previous multiSelectionQuery steps.
# Remove the trailing ", " and add a closing parenthesis.
if multiSelectionQuery != multiSelectionQueryBase:
multiSelectionQuery = multiSelectionQuery[:-2] + """) """
else:
# The selection query would not select anything.
return
SelectLayerByAttribute_management(fcAsFeatureLayerForTransferring,
"NEW_SELECTION", multiSelectionQuery)
# Then redo from step 5 on at the end of the loop IF there is anything left to select
# which was not selected... so if selectionCounter != 0.
### -shouldbeafunctionblock#2- ###
# 5.) Count selected features.
countResult0 = GetCount_management(fcAsFeatureLayerForTransferring)
intCount0 = int(countResult0.getOutput(0))
if intCount0 >= 1:
print(
"Spatially selecting with the fcAsFeatureLayerForTransferring features, of which there are "
+ str(intCount0) + " selected.")
##print("Selected by this query:")
##print(str(multiSelectionQuery))
# 9.) Else, spatially select features in the original feature class with 'SHARE_A_LINE_SEGMENT_WITH'.
SelectLayerByLocation_management(targetFC1asFeatureLayer,
"SHARE_A_LINE_SEGMENT_WITH",
fcAsFeatureLayerForTransferring, 0,
"NEW_SELECTION")
# Added to prevent the Selection from taking over '%W%' routes at this time.
SelectLayerByAttribute_management(targetFC1asFeatureLayer,
"SUBSET_SELECTION",
subsetSelectionQuery)
# 10.) Count to make sure that at least one feature is selected.
countResult2 = GetCount_management(targetFC1asFeatureLayer)
intCount2 = int(countResult2.getOutput(0))
print(
'There were ' + str(intCount2) +
' features selected for replacement in the targetFC1asFeatureLayer layer.'
)
if intCount2 >= 1:
# 11.) If so, cursor the features out of the dissolve layer.
featureList = list()
searchCursorFields = [
str(lrsKeyToUse),
str(startMeasure),
str(endMeasure), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R', 'LABEL',
'SHAPE@'
]
newCursor = daSearchCursor(fcAsFeatureLayerForTransferring,
searchCursorFields)
for cursorItem in newCursor:
featureList.append(list(cursorItem))
try:
del newCursor
except:
pass
# 12.) Delete the selected features in the input layer.
try:
DeleteFeatures_management(targetFC1asFeatureLayer)
except:
print("Could not delete features for the selection " +
str(multiSelectionQuery) + ".")
# 13.) Insert the features from the dissolve layer into the copy of the centerlines.
insertCursorFields = [
str(lrsKeyToUse),
str(startMeasure),
str(endMeasure), 'LRS_COUNTY_PRE', 'LRS_ROUTE_PREFIX',
'LRS_ROUTE_NUM', 'LRS_ROUTE_SUFFIX', 'LRS_UNIQUE_IDENT',
'LRS_UNIQUE_IDENT1', 'KDOT_COUNTY_L', 'KDOT_COUNTY_R', 'LABEL',
'SHAPE@'
]
newCursor = daInsertCursor(targetFC1asFeatureLayer,
insertCursorFields)
for featureItem in featureList:
newCursor.insertRow(featureItem)
try:
del newCursor
except:
pass
try:
del featureList
except:
pass
else:
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
def extendAndIntersectRoadFeatures(): # Place the operations that extend each road line segment by a certain distance here.
# Should extend all the features that exist in the post-erase dataset. Might be more difficult
# to calculate the angle of these lines accurately, but it should be easier to figure out
# than trying to get the lines to split correctly with the buggy SplitLineAtPoint tool.
# Extend the lines in both directions by X_Val Feet.
print "Starting to extend and intersect road features."
if arcpy.Exists(createdExtensionLines):
arcpy.Delete_management(createdExtensionLines)
else:
pass
arcpy.CreateFeatureclass_management(gdb, "createdExtensionLines", "POLYLINE", "", "", "", spatialReferenceProjection)
# Add a column for roadname called roadNameForSplit.
arcpy.AddField_management(createdExtensionLines, "roadNameForSplit", "TEXT", "", "", "55")
# Add a column which stores the angle to display a label called called LabelAngle.
arcpy.AddField_management(createdExtensionLines, "LabelAngle", "DOUBLE", "", "", "") # Change to double.
# Add a column which stores the County Number.
arcpy.AddField_management(createdExtensionLines, "County_Number", "DOUBLE", "", "", "")
# Hard coded the buffer distance to 22176 instead of calculating based on SQRT_DIV8 field value.
extensionDistance = 22176 # (4.2 * 5280)
roadLinesToInsertList = list()
roadLinesList = getRoadLinesList()
for roadLinesItem in roadLinesList:
roadNameToUse = roadLinesItem[3]
countyNumber = roadLinesItem[4]
linePointsArray = arcpy.Array()
firstPointTuple = (roadLinesItem[2].firstPoint.X, roadLinesItem[2].firstPoint.Y)
lastPointTuple = (roadLinesItem[2].lastPoint.X, roadLinesItem[2].lastPoint.Y)
# Make this a two-step process.
# Might... maybe, possibly, be as simple as
# adding _1 to the end of the first set of variables,
# adding _2 to the end of the second set of variables,
# then making the extensions in both directions
# and creating a new line that has the endpoints
# from both sides as it's first and last point.
# if necessary, could add the other points in between
# but probably not necessary just for generating
# an intersection point.
yValue_1 = -(lastPointTuple[1] - firstPointTuple[1]) # made y value negative
xValue_1 = lastPointTuple[0] - firstPointTuple[0]
lineDirectionAngle_1 = math.degrees(math.atan2(xValue_1, yValue_1)) # reversed x and y
lineDirectionAngle_1 = -(((lineDirectionAngle_1 + 180) % 360) - 180) # correction for certain quadrants
#print "lineDirectionAngle: " + str(lineDirectionAngle_1)
origin_x_1 = firstPointTuple[0]
origin_y_1 = firstPointTuple[1]
yValue_2 = -(firstPointTuple[1] - lastPointTuple[1]) # made y value negative
xValue_2 = firstPointTuple[0] - lastPointTuple[0]
lineDirectionAngle_2 = math.degrees(math.atan2(xValue_2, yValue_2)) # reversed x and y
lineDirectionAngle_2 = -(((lineDirectionAngle_2 + 180) % 360) - 180) # correction for certain quadrants
#print "lineDirectionAngle: " + str(lineDirectionAngle_2)
origin_x_2 = lastPointTuple[0]
origin_y_2 = lastPointTuple[1]
(disp_x_1, disp_y_1) = (extensionDistance * math.sin(math.radians(lineDirectionAngle_1)),
extensionDistance * math.cos(math.radians(lineDirectionAngle_1)))
(end_x_1, end_y_1) = (origin_x_1 + disp_x_1, origin_y_1 + disp_y_1)
(disp_x_2, disp_y_2) = (extensionDistance * math.sin(math.radians(lineDirectionAngle_2)),
extensionDistance * math.cos(math.radians(lineDirectionAngle_2)))
(end_x_2, end_y_2) = (origin_x_2 + disp_x_2, origin_y_2 + disp_y_2)
startPoint = arcpy.Point()
endPoint = arcpy.Point()
startPoint.ID = 0
startPoint.X = end_x_1
startPoint.Y = end_y_1
endPoint.ID = 1
endPoint.X = end_x_2
endPoint.Y = end_y_2
linePointsArray.add(startPoint)
linePointsArray.add(endPoint)
newLineFeature = arcpy.Polyline(linePointsArray)
# Need to create an extension for both ends of the line and add them
# to the array.
#newLineFeature = createdExtensionLinesCursor.newRow()
#newLineFeature.SHAPE = linePointsArray
lineDirectionOutput = "0"
if lineDirectionAngle_1 > 0:
lineDirectionOutput = lineDirectionAngle_1
elif lineDirectionAngle_2 > 0:
lineDirectionOutput = lineDirectionAngle_2
else:
pass
roadLinesToInsertList.append([newLineFeature, roadNameToUse, lineDirectionOutput, countyNumber])
#createdExtensionLinesCursor.insertRow([newLineFeature, roadNameToUse, lineDirectionOutput])
if "newLineFeature" in locals():
del newLineFeature
else:
pass
# Consider building this as a separate list and then just looping
# through the list to put it into the cursor instead
# of doing logic and inserting into the cursor at the same place.
#start editing session
newEditingSession = daEditor(gdb)
newEditingSession.startEditing()
newEditingSession.startOperation()
createdExtensionLinesCursor = daInsertCursor(createdExtensionLines, ["SHAPE@", "roadNameForSplit", "LabelAngle", "County_Number"])
for roadLinesToInsertItem in roadLinesToInsertList:
createdExtensionLinesCursor.insertRow(roadLinesToInsertItem)
# End editing session
newEditingSession.stopOperation()
newEditingSession.stopEditing(True)
if "createdExtensionLinesCursor" in locals():
del createdExtensionLinesCursor
else:
pass
if arcpy.Exists("countyRoadNameRosette"):
arcpy.Delete_management("countyRoadNameRosette")
else:
pass
arcpy.CreateFeatureclass_management(gdb, "countyRoadNameRosette", "POINT", "", "", "", spatialReferenceProjection)
arcpy.AddField_management(countyRoadNameRosette, "roadNameForSplit", "TEXT", "", "", "55")
arcpy.AddField_management(countyRoadNameRosette, "LabelAngle", "DOUBLE", "", "", "") # Change to double.
arcpy.AddField_management(countyRoadNameRosette, "County_Number", "DOUBLE", "", "", "")
arcpy.AddField_management(countyRoadNameRosette, "COUNTY_NAME", "TEXT", "", "", "55")
# Now then, need to check for the existence
# of and delete the point intersection layer
# if it exists.
# Then, recreate it and the proper fields.
inMemoryCountyBorderExtension = "aCountyBorderExtensionBuffer"
inMemoryExtensionLines = "aLoadedExtensionLines"
# Temporary layer, use CopyFeatures_management to persist to disk.
arcpy.MakeFeatureLayer_management(countyBorderFeature, inMemoryCountyBorderExtension) # County Border extension feature
# Temporary layer, use CopyFeatures_management to persist to disk.
arcpy.MakeFeatureLayer_management(createdExtensionLines, inMemoryExtensionLines) # Line extension feature
borderFeatureList = getBorderFeatureList()
borderFeatureList = sorted(borderFeatureList, key=lambda feature: feature[4])
for borderFeature in borderFeatureList:
borderFeatureName = borderFeature[3]
borderFeatureNumber = borderFeature[4]
print "borderFeatureName: " + str(borderFeatureName) + " & borderFeatureNumber: " + str(int(borderFeatureNumber))
countyBorderWhereClause = ' "COUNTY_NUMBER" = ' + str(int(borderFeatureNumber)) + ' '
arcpy.SelectLayerByAttribute_management(inMemoryCountyBorderExtension, "NEW_SELECTION", countyBorderWhereClause)
countyBorderSelectionCount = arcpy.GetCount_management(inMemoryCountyBorderExtension)
print "County Borders Selected: " + str(countyBorderSelectionCount)
# Had to single-quote the borderFeatureNumber because it is stored as a string in the table.
# Unsingle quoted because it was changed to a float.
extensionLinesWhereClause = ' "COUNTY_NUMBER" = ' + str(int(borderFeatureNumber)) + ' '
arcpy.SelectLayerByAttribute_management(inMemoryExtensionLines, "NEW_SELECTION", extensionLinesWhereClause)
extensionLineSelectionCount = arcpy.GetCount_management(inMemoryExtensionLines)
print "Extension Lines Selected: " + str(extensionLineSelectionCount)
if arcpy.Exists("tempRoadNameRosette"):
arcpy.Delete_management("tempRoadNameRosette")
else:
pass
if arcpy.Exists("tempRoadNameRosetteSinglePoint"):
arcpy.Delete_management("tempRoadNameRosetteSinglePoint")
else:
pass
arcpy.Intersect_analysis([inMemoryCountyBorderExtension, inMemoryExtensionLines], tempRoadNameRosette, "ALL", "", "POINT")
# Intersect to an output temp layer.
# Next, need to loop through all of the counties.
# Get the county number and use it to select
# a county extension buffer in the county
# extension buffers layer.
# Then, use the county number to select
# all of the lines for that county
# in the extension lines layer.
# Then, export those to a temp layer in the fgdb.
# Change multipoint to singlepoint.
# Was working until I moved from gisprod to sdedev for the data source.
# not sure why. Check to make sure projections match.
try:
# Run the tool to create a new fc with only singlepart features
arcpy.MultipartToSinglepart_management(tempRoadNameRosette, tempRoadNameRosetteSinglePoint)
# Check if there is a different number of features in the output
# than there was in the input
inCount = int(arcpy.GetCount_management(tempRoadNameRosette).getOutput(0))
outCount = int(arcpy.GetCount_management(tempRoadNameRosetteSinglePoint).getOutput(0))
if inCount != outCount:
print "Found " + str(outCount - inCount) + " multipart features."
#print "inCount, including multipart = " + str(inCount)
#print "outCount, singlepart only = " + str(outCount)
else:
print "No multipart features were found"
except arcpy.ExecuteError:
print arcpy.GetMessages()
except Exception as e:
print e
print "Appending the temp point layer to the county point intersection layer."
arcpy.Append_management([tempRoadNameRosetteSinglePoint], countyRoadNameRosette, "NO_TEST")
# K, worked once. Just need to change LabelAngle to a float and it might be what
# I want... except for too slow, but that's why we have the multiprocessing module.
# So add multiprocessing capability after it correctly creates the desired output.
# Will need to change the way the temp files are handled/named, but should be
# fairly simple. -- Getting the multiprocessing module to work might be less
# simple, but still probably worth it for the speed increase.
# For multiprocessing: have each county receive a random number after its temp file,
# i.e. try: write feature with random number, except retry with different number.
# then, read all of the temp file
# Then, print a completion message
# and check the points out in a map.
print "Done adding points to the countyRoadNameRosette feature class."
print "Normalizing the label angle values."
def transferConstructionWeather():
env.workspace = in_memory
featuresToTransfer = list()
try:
print str(
datetime.datetime.now()) + ' copying the oracle table to memory'
# Create an in_memory feature class which to hold the features from
# the Oracle table.
FeatureClassToFeatureClass_conversion(sdeCDRS, "in_memory",
"CDRS_Weather", "#")
# Then, define a projection on it, since the original Oracle table
# is lacking the proper information.
DefineProjection_management("in_memory\CDRS_Weather", lambertCC)
###############################################################################################################
# Maintainability information:
# ToDo:
# If you need to add another field to transfer between the two, just add it to the searchCursorFields and the
# insertCursorFields lists and make sure that it is in the same position in the list order for both of
# them.
# Besides 'LoadDate', the order does not matter, so long as each field name in the
# searchCursorFields has a counterpart in the insertCursorFields and vice versa.
# 'LoadDate' should always be last for the insertCursorFields as it is appended to each row after all
# of the other items from the searchCursorFields.
###############################################################################################################
# searchCursorFields go to "in_memory\CDRS". (Input table)
# Removed 'GEOMETRY', then replaced functionality with 'SHAPE@'.
# Also removed 'OBJECTID', 'RPT_BY_NAME', 'RPT_BY_PHONE',
# 'RPT_BY_EMAIL', 'CONTACT_NAME', 'CONTACT_PHONE', 'CONTACT_EMAIL',
# 'BEG_LATITUDE', 'BEG_LONGITUDE', 'BEG_REF_POST', 'END_REF_POST',
# 'END_LATITUDE', 'END_LONGITUDE', 'INTERNAL_COMMENT',
# 'WIDTH_RESTRICTION', 'VERT_RESTRICTION', 'WEIGHT_RESTRICTION',
# 'SPEED_RESTRICTION', 'PUBLIC_VIEW', 'DCAM_COMMENT', 'DCAM_DATE',
# 'DISPLAY_MAP', 'LINE_COLOR', 'CDRS_ALERT_ROUTE_ID', 'ALERT_ID',
# 'ALERT_DATE', 'COMP_DATE', 'BEG_COUNTY_NAME',
# 'END_LRS_KEY', 'END_LRS_ROUTE', 'END_COUNTY_NAME',
# 'BEG_LRS_DIREC_TXT', 'END_LRS_DIREC_TXT', 'TIME_DELAY_TXT',
# 'CDRS_WZ_DETAIL_ID', 'CDRS_FHWA_ID', 'CONST_PROJ_NUM',
# 'PUBLIC_COMMENT', 'OFFICE_NAME', 'NEW_NOTIFICATION',
# 'EMAIL_REMINDER', 'NOTIFICATION_SENT', 'LOC_ENTRY_TYPE',
# 'ALERT_HYPERLINK', 'GIS_VIEW',
# 'END_COUNTY_NUMBER', 'ALERT_TYPE_TXT', 'ALERT_STATUS',
# 'CLOSED_MSG_SENT', 'ALERT_INSERT_DT', 'FEA_CLOSED'
# Public Comment is where to get the output text data from.
######################################################################
searchCursorFields = [
'SHAPE@', 'ADMIN_OWNER', 'BEG_LRS_KEY', 'ALERT_DIREC_TXT',
'ALERT_DESC_TXT', 'BEG_COUNTY_NUMBER', 'BEG_STATE_LOGMILE',
'END_STATE_LOGMILE', 'LENGTH', 'DISTRICT', 'AREA', 'ROUTE',
'BEG_LRS_ROUTE', 'BEG_DESC', 'END_DESC', 'SITE_CR', 'LAST_UPDATE',
'START_DATE', 'EXPIRE_DATE'
]
# insertCursorFields go to kandrive_spatial.DBO.Conditions. (Output table)
# Removed 'GEOMETRY' and 'GEOMETRY.STLength()', then replaced their
# functionality with 'SHAPE@'.
# Also removed 'OBJECTID'
######################################################################
insertCursorFields = [
'SHAPE@',
'ADMIN_OWNER',
'LRS_KEY',
'LANE_DESCRIPTION',
'ROAD_COND_TEXT',
'COUNTY_NO',
'BEG_STATE_LOGMILE',
'END_STATE_LOGMILE',
'SEG_LENGTH_MILES',
'SNOW_DIST',
'SNOW_AREA',
'RD_SUFX_SUBC',
'ROUTE_ID',
'BEG_DESC',
'END_DESC',
'SITE_CR',
'LAST_UPDATE', ### End of directly transfered columns. Next are set columns.
'STALE_MSG',
'UNIQUE_ID',
'RCRS_ROADS_ID',
'SNOW_SUBAREA',
'ROAD_CONDITION',
'RE_OPENING',
'CLOSED_MSG_SENT',
'CLOSED_FROM',
'CLOSED_TO',
'ACCOMMODATION',
'USER_ID',
'CLOSED_COND',
'MSG_CODE',
'LINE_COLOR',
'MSG_SENT',
'SNOW_ICE_CATEGORY',
'RCRSP_VIEW',
## End of set columns. Next are calculated columns
'SEGMENT_ID',
'LRS_PREFIX',
'LRS_SUFFIX',
'LRS_SUBCLASS',
'RT_NUMBER',
'BEG_POINT_DESC',
'END_POINT_DESC',
'RCRSP_DIST',
'SNICE_BEGIN',
'SNICE_END',
'REPORT_TIME',
'ROAD_COND_TIME'
]
# 46 Total with 29 following LAST_UPDATE
weatherTypesString = "('Flooding', 'Fog', 'Blowing Dust/Smoke', 'Wind Damage')"
whereClause = """ALERT_STATUS = 2 AND FEA_CLOSED = 1 AND ALERT_TYPE_TXT = 'Road Closing' AND ALERT_DESC_TXT IN """ + weatherTypesString
cdrsSearchCursor = daSearchCursor(r"in_memory\CDRS_Weather",
searchCursorFields, whereClause)
for cdrsCursorItem in cdrsSearchCursor:
featureItem = list(cdrsCursorItem)
## Copied columns
copiedFeatureItem = featureItem[:-2]
lrsKey = featureItem[2]
startDateCDRS = featureItem[-2]
startDateStr = startDateCDRS.strftime('%m/%d/%y')
expDateCDRS = featureItem[-1]
expDateStr = expDateCDRS.strftime('%m/%d/%y')
## Set Columns
copiedFeatureItem.append(0) ## STALE_MSG
copiedFeatureItem.append(0) ## UNIQUE_ID
copiedFeatureItem.append(9999) ## RCRS_ROADS_ID
copiedFeatureItem.append(9) ## SNOW_SUBAREA
copiedFeatureItem.append(
11) ## ROAD_CONDITION should always == 11, for closed.
copiedFeatureItem.append(None) ## RE_OPENING
copiedFeatureItem.append(None) ## CLOSED_MSG_SENT
copiedFeatureItem.append(None) ## CLOSED_FROM
copiedFeatureItem.append(None) ## CLOSED_TO
copiedFeatureItem.append(None) ## ACCOMMODATION
copiedFeatureItem.append(100) ## USER_ID
copiedFeatureItem.append(2) ## CLOSED_COND
copiedFeatureItem.append('0000') ## MSG_CODE
copiedFeatureItem.append(255) ## LINE_COLOR
copiedFeatureItem.append(0) ## MSG_SENT
copiedFeatureItem.append(3) ## SNOW_ICE_CATEGORY
copiedFeatureItem.append(3) ## RCRSP_VIEW
## Calculated Columns
copiedFeatureItem[4] = 'Closed - ' + copiedFeatureItem[
4] ## ROAD_COND_TEXT
copiedFeatureItem.append(
copiedFeatureItem[12]) ## SEGMENT_ID = ROUTE_ID
copiedFeatureItem.append(lrsKey[3:4]) ## LRS_PREFIX
copiedFeatureItem.append(lrsKey[9:10]) ## LRS_SUFFIX
copiedFeatureItem.append(lrsKey[-1:]) ## LRS_SUBCLASS
rtNumberToFormat = lrsKey[6:9]
rtNumberFormatted = int(rtNumberToFormat)
copiedFeatureItem.append(rtNumberFormatted) ## RT_NUMBER
# Only get the first 60 chars on the next two columns.
copiedFeatureItem.append(
copiedFeatureItem[13][:60]) ## BEG_POINT_DESC
copiedFeatureItem.append(
copiedFeatureItem[14][:60]) ## END_POINT_DESC
copiedFeatureItem.append(copiedFeatureItem[9]) ## RCRSP_DIST
copiedFeatureItem.append(startDateStr +
' 12:04 AM') ## SNICE_BEGIN
copiedFeatureItem.append(expDateStr + ' 11:54 PM') ## SNICE_END
copiedFeatureItem.append(startDateStr +
' 12:04 AM') ## REPORT_TIME
copiedFeatureItem.append(startDateStr +
' 12:04 AM') ## ROAD_COND_TIME
featuresToTransfer.append(copiedFeatureItem)
RCRS_InsertCursor = daInsertCursor(kanDriveSpatialConditions,
insertCursorFields)
for RCRS_Feature in featuresToTransfer:
insertOID = RCRS_InsertCursor.insertRow(RCRS_Feature)
print "Inserted a row with the OID of: " + str(insertOID)
except:
print "An error occurred."
errorItem = sys.exc_info()[1]
errorStatement = str(errorItem.args[0])
print errorStatement
if len(errorStatement) > 253:
errorStatement = errorStatement[0:253]
else:
pass
endTime = datetime.datetime.now()
ScriptStatusLogging('KanDrive_Spatial_Conditions_Update',
'kandrive_spatial.DBO.Conditions', scriptFailure,
startTime, endTime, errorItem.args[0],
pythonLogTable)
try:
del errorItem
except:
pass
# Reraise the error to stop execution and prevent a success message
# from being inserted into the table when the script completes.
raise
finally:
try:
del cdrsSearchCursor
except:
pass
try:
del RCRS_InsertCursor
except:
pass
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)
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
