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