def RecordResults(resultType, values, gdb): from os.path import join from arcpy import Exists, CreateTable_management, AddField_management from arcpy.da import InsertCursor if resultType == "template": tbl = "TemplateCheckResults" elif resultType == "fieldValues": tbl = "FieldValuesCheckResults" table = join(gdb, tbl) fieldList = [] if not Exists(table): CreateTable_management(gdb, tbl) fieldInfo = getAddFieldInfo(table) for fi in fieldInfo: #add field with desired parameters AddField_management(fi[0], fi[1], fi[2], fi[3], fi[4], fi[5]) #populate field list fieldList.append(fi[1]) if fieldList == []: fieldList = getResultsFieldList(table) cursor = InsertCursor(table, fieldList) for row in values: cursor.insertRow(row) del cursor
def RecordResults(resultType, values, gdb): from os.path import join from arcpy import Exists, CreateTable_management, AddField_management from arcpy.da import InsertCursor if resultType == "template": tbl = "TemplateCheckResults" elif resultType == "fieldValues": tbl = "FieldValuesCheckResults" table = join(gdb, tbl) fieldList = [] if not Exists(table): CreateTable_management(gdb, tbl) fieldInfo = getAddFieldInfo(table) for fi in fieldInfo: #add field with desired parameters AddField_management(fi[0],fi[1],fi[2],fi[3],fi[4],fi[5]) #populate field list fieldList.append(fi[1]) if fieldList == []: fieldList = getResultsFieldList(table) cursor = InsertCursor(table, fieldList) for row in values: cursor.insertRow(row) del cursor
def RecordResults(resultType, values, gdb): # Guessed on whitespace formatting here. -- DT if resultType == "template": tbl = "TemplateCheckResults" elif resultType == "fieldValues": tbl = "FieldValuesCheckResults" table = join(gdb, tbl) fieldList = [] if not Exists(table): CreateTable_management(gdb, tbl) fieldInfo = getAddFieldInfo(table) for fi in fieldInfo: #add field with desired parameters AddField_management(fi[0],fi[1],fi[2],fi[3],fi[4],fi[5]) #populate field list fieldList.append(fi[1]) if fieldList == []: fieldList = getResultsFieldList(table) cursor = InsertCursor(table, fieldList) for row in values: try: cursor.insertRow(row) except: userMessage(row) del cursor
def buildScanAreaFC(sensor_att, poly_arr): global sr, sensorFields, sensorFieldsTypes scans_fc = arcpy.CreateFeatureclass_management(r'memory', 'scans', 'POLYGON', None, None, None, sr) for field in range(0, len(sensorFieldsTypes)): arcpy.AddField_management(scans_fc, sensorFields[field], sensorFieldsTypes[field]) polygon_cursor = InsertCursor(scans_fc, sensorFields) aa = 0 for i in range(0, len(polygon_array), 4): points = arcpy.Array() points.add(polygon_array[i]) points.add(polygon_array[i + 1]) points.add(polygon_array[i + 2]) points.add(polygon_array[i + 3]) RI, DI, DR, TRC, DT, lat, lon, alt, LaS, LoS, sua, suc, SAU, ST, SS, SVV, stu, ssu, vvu, PH, PP, PR, DCLa, DCLo, DRH, DAH, SH, SP, SR, MDV, DCT, PT, SenT, F = sensor_att[ aa].values() polygon_cursor.insertRow([ RI, DI, DR, TRC, DT, alt, SAU, ST, SS, SVV, PH, PP, PR, DCLa, DCLo, DRH, DAH, SH, SP, SR, MDV, DCT, PT, SenT, F, arcpy.Polygon(points, sr) ]) aa += 1 del polygon_cursor return scans_fc
def writeSegmentInfo(table, hilow_fields, name_wc, NG911_msag, name): seg_list = [] hilow_len = len(hilow_fields) with SearchCursor(table, hilow_fields, name_wc) as rows: for row in rows: #create sorted list of address components field_i = 0 num_list = [] while field_i < hilow_len: if row[field_i] is not None and row[field_i] != '': num_list.append(int(row[field_i])) field_i += 1 num_list.sort() if num_list != []: if num_list[0] == 0 and num_list[1] == 0: pass else: #add the high and low to the seg_list seg_list.append(num_list[0]) seg_list.append(num_list[hilow_len - 1]) if seg_list != []: seg_list.sort() i = len(seg_list) k = 1 pop_list = [] while k + 1 < i: #index 0 will definitely be the low so we don't need to worry about it #compare the high of one range with the low of the next temp_high = seg_list[k] temp_nextLow = seg_list[k + 1] if temp_nextLow - temp_high == 1: pop_list.append(temp_high) pop_list.append(temp_nextLow) k += 2 #remove values from list that can be absorbed into others if pop_list != []: for popIt in pop_list: seg_list.remove(popIt) count = len(seg_list) pair_int = 0 #write consolidated records while pair_int < count: cursor = InsertCursor(NG911_msag, ("COMPARE", "LOW","HIGH")) cursor.insertRow((name, seg_list[pair_int], seg_list[pair_int + 1])) pair_int += 2 del cursor
def generate_isochrones(locations, break_value): """Create walk shed polygons using the OpenStreetMap network from the input locations to the distance of the input break value """ # Create and configure a service area layer, these have the ability # generate isochrones network_dataset = OSM_PED_ND sa_name = 'service_area' impedance_attribute = ATTRIBUTE_LEN sa_layer = MakeServiceAreaLayer( network_dataset, sa_name, impedance_attribute, 'TRAVEL_TO', restriction_attribute_name=ATTRIBUTE_PED).getOutput(0) # Within the service area layer there are several sub-layers where # things are stored such as facilities, polygons, and barriers. sa_classes = GetNAClassNames(sa_layer) # GetNAClassNames returns a dictionary in which the values are # strings that are the names of each class, to get their # corresponding layer objects the ListLayers method must be used facilities_str = sa_classes['Facilities'] isochrones_lyr = ListLayers(sa_layer, sa_classes['SAPolygons'])[0] solver_props = GetSolverProperties(sa_layer) solver_props.defaultBreaks = break_value # Service area locations must be stored in the facilities sublayer clear_other_stops = 'CLEAR' exclude_for_snapping = 'EXCLUDE' AddLocations(sa_layer, facilities_str, locations, append=clear_other_stops, exclude_restricted_elements=exclude_for_snapping) # Generate the isochrones for this batch of stops, the output will # automatically go to the 'sa_isochrones' variable Solve(sa_layer) i_fields = ['SHAPE@', ID_FIELD, DIST_FIELD] i_cursor = InsertCursor(ISOCHRONES, i_fields) s_fields = ['SHAPE@', UNIQUE_FIELD] with SearchCursor(isochrones_lyr, s_fields) as cursor: for geom, output_name in cursor: iso_attributes = re.split('\s:\s0\s-\s', output_name) stop_id = int(iso_attributes[0]) break_value = int(iso_attributes[1]) i_cursor.insertRow((geom, stop_id, break_value)) del i_cursor
def insertReports(workingGDB, report, records, high, low): #prepare reporting report_table = join(workingGDB, "MSAG_reporting") if not Exists(report_table): CreateTable_management(dirname(report_table), basename(report_table)) if not fieldExists(report_table, "REPORT"): AddField_management(report_table, "REPORT", "TEXT", "", "", 255) if not fieldExists(report_table, "COMPARISON"): AddField_management(report_table, "COMPARISON", "TEXT", "", "", 50) fld = ["REPORT", "COMPARISON"] cursor = InsertCursor(report_table, fld) for r in records: if len(fld) == 2: cursor.insertRow((report[0:254], r))
def makeRow(out_fc, insert_fields, row_value, numFieldsIndexList, dateFieldsIndexList): # cleans up row values, creates a row # clean up row values first list_row = list(row_value) for index, item in enumerate(list_row): # if the value is null, deal with it if item is None: if index in numFieldsIndexList: list_row[index] = 0 elif index in dateFieldsIndexList: fake_date = datetime.datetime(1899, 12, 30, 0, 0) list_row[index] = fake_date else: list_row[index] = "" # insert the cleaned row into the output feature class cursor = InsertCursor(out_fc, insert_fields) cursor.insertRow(tuple(list_row)) del cursor
def polygon_to_point(input_fc, output_fc): """ copies the centroid of polygon geometry to a point feature class """ oid_field = Describe(input_fc).OIDFieldName new_field = 'Rel_OID' AddField_management(output_fc, new_field, 'Long') search_cursor = SearchCursor(input_fc, ['SHAPE@', oid_field]) insert_cursor = InsertCursor(output_fc, ["SHAPE@", new_field]) spatial_reference = Describe(output_fc).spatialReference for row in search_cursor: point = row[0].projectAs(spatial_reference).centroid oid = row[1] insert_cursor.insertRow([point, oid]) del search_cursor, insert_cursor, spatial_reference
def line_to_endpoints(input_fc, output_fc, id_field=None): """ copies the endpoints of a feature class into a point feature class """ # id field can either be provided or obtained automagically oid_field = id_field if id_field else Describe(input_fc).OIDFieldName # we add two custom fields to track the related id and the type of end point related_field = 'related_oid' type_field = 'point_type' AddField_management(output_fc, related_field, 'LONG') AddField_management(output_fc, type_field, 'TEXT', field_length=10) fields = [f.name for f in Describe(input_fc).fields if f.name not in ['SHAPE@', 'Shape', 'Shape_Length']] output_fields = fields + ['SHAPE@', related_field, type_field] #shape will be the last column selected search_cursor = SearchCursor(input_fc, fields + ['SHAPE@']) #our insert cursor insert_cursor = InsertCursor(output_fc, output_fields) #identify the spatial reference for projecting the geometry spatial_reference = Describe(output_fc).spatialReference for row in search_cursor: # project and store the geometry geom = row[len(row) - 1].projectAs(spatial_reference) # get the row id oid = row[fields.index(oid_field)] # insert two entries but with our new custom geometry insert_cursor.insertRow(row[:-1] + (geom.firstPoint, oid, 'START')) insert_cursor.insertRow(row[:-1] + (geom.lastPoint, oid, 'END')) del search_cursor, insert_cursor, spatial_reference
def main(): gdb = GetParameterAsText(0) road_name = GetParameterAsText(1).upper() road_type = GetParameterAsText(2).upper() alias_road_name = GetParameterAsText(3).upper() alias_road_type = GetParameterAsText(4).upper() label = GetParameterAsText(5) #get gdb object & set layer names gdbObject = getGDBObject(gdb) rc_file = gdbObject.RoadCenterline ra_file = gdbObject.RoadAlias rc_obj = getFCObject(rc_file) ra_obj = getFCObject(ra_file) #set where clause for query wc = rc_obj.RD + " = '" + road_name + "'" if road_type != "": wc += " AND " + rc_obj.STS + " = '" + road_type + "'" #define fields for cursors fields = (rc_obj.STEWARD, rc_obj.EFF_DATE, rc_obj.UNIQUEID, "OBJECTID") insertFields = [ra_obj.STEWARD, ra_obj.EFF_DATE, ra_obj.UNIQUEID, ra_obj.SEGID, ra_obj.A_RD, ra_obj.A_STS, ra_obj.LABEL, ra_obj.SUBMIT] #loop through records in the road centerline file with that name with SearchCursor(rc_file, (fields), wc) as rows: for row in rows: #create a new unique alias ID aliasPart = uniqueID() aliasID = aliasPart + str(row[3]) #define new values to be inserted newVals = [row[0], row[1], aliasID, row[2], alias_road_name, alias_road_type, label, 'Y'] #create insert cursor for road alias table cursor = InsertCursor(ra_file, insertFields) cursor.insertRow(newVals) AddWarning("New road alias records created for ALL roads named " + road_name + ". Please double-check new records for validity and accuracy.")
def buildMoversFC(points_att): global sr, moverFields, moverFieldsTypes movers_fc = arcpy.CreateFeatureclass_management(r'memory', 'movers', 'POINT', None, None, 'ENABLED', sr) for field in range(0, len(moverFieldsTypes)): arcpy.AddField_management(movers_fc, moverFields[field], moverFieldsTypes[field]) movers_cursor = InsertCursor(movers_fc, moverFields) for i in range(len(points_att)): rptIdx, lat, lon, dlat, dlon, ghgt, RV, WV, SNR, CLS, CU, SRU, CRU, HU, RVU, TTA, TTE, RCS, DT, DI, RI, PT, ST, F = points_att[ i].values() movers_cursor.insertRow([ rptIdx, ghgt, RV, WV, SNR, CLS, CU, SRU, CRU, HU, RVU, TTA, TTE, RCS, DT, DI, RI, PT, ST, F, arcpy.Point(lon, lat, ghgt) ]) del movers_cursor return movers_fc
def buildSensorFC(sensor_att, platformLoc_att): global sr, sensorFields, sensorFieldsTypes sensor_fc = arcpy.CreateFeatureclass_management(r'memory', 'sensor', 'POINT', None, None, 'ENABLED', sr) for field in range(0, len(sensorFieldsTypes)): arcpy.AddField_management(sensor_fc, sensorFields[field], sensorFieldsTypes[field]) sensor_cursor = InsertCursor(sensor_fc, sensorFields) for i in range(len(sensor_att)): RI, DI, DR, TRC, DT, lat, lon, alt, LaS, LoS, sua, suc, SAU, ST, SS, SVV, stu, ssu, vvu, PH, PP, PR, DCLa, DCLo, DRH, DAH, SH, SP, SR, MDV, DCT, PT, SenT, F = sensor_att[ i].values() try: sensor_cursor.insertRow([ RI, DI, DR, TRC, DT, alt, SAU, ST, SS, SVV, PH, PP, PR, DCLa, DCLo, DRH, DAH, SH, SP, SR, MDV, DCT, PT, SenT, F, arcpy.Point(lon, lat, alt / 100) ]) except: arcpy.AddMessage('{} \n {}'.format('Error inserting values:', sensor_att[i].values())) if not len(platformLoc_att) == 0: for j in range(len(platformLoc_att)): time, lat, lon, alt, track, speed, vertVel, dTime, fName = platformLoc_att[ j].values() sensor_cursor.insertRow([ None, None, None, None, time, alt, None, None, speed, vertVel, track, None, None, None, None, None, None, None, None, None, None, dTime, None, None, fName, arcpy.Point(lon, lat, alt / 100) ]) del sensor_cursor return sensor_fc
def createAndUpdateAccTable(sourceTableGDB, sourceTableName, destinationTableGDB, destinationTableName): sourceTableFullPath = os.path.join(sourceTableGDB, sourceTableName) destinationTableFullPath = os.path.join(destinationTableGDB, destinationTableName) print "Starting the AccTable transfer." if Exists(destinationTableFullPath): pass else: env.workspace = sourceTableGDB MakeTableView_management(sourceTableFullPath, "sourceView") # Uses the sourceView table view as a template for the table creation to carry over the field information. CreateTable_management(destinationTableGDB, destinationTableName, "sourceView") pass env.workspace = destinationTableGDB fieldObjectList = ListFields(sourceTableFullPath) fieldList = [field.name for field in fieldObjectList if field.name != "OBJECTID"] tableDataList = list() # Use a searchCursor to read the data in from the sourceTable. sCursor = SearchCursor(sourceTableFullPath, fieldList) for cursorItem in sCursor: tableDataList.append(cursorItem) try: del sCursor except: pass # Use an insertCursor to insert the data into the destinationTable. iCursor = InsertCursor(destinationTableFullPath, fieldList) for tableDataItem in tableDataList: returnedOID = iCursor.insertRow(tableDataItem) print "Inserted a row with an objectID of: " + str(returnedOID) try: del iCursor except: pass
def CompareThatData(fc1, fc2, resultsTable, data_obj): ## try: compare = "compare" ng911id = "ng911id" #Add comparison field if not fieldExists(fc1, compare + "1"): AddField_management(fc1, compare + "1", "TEXT", "", "", 1000) if not fieldExists(fc2, compare + "2"): AddField_management(fc2, compare + "2", "TEXT", "", "", 1000) #get object of data data_obj = getFCObject(fc1) #get unique ID uniqueID = data_obj.UNIQUEID #prep data for attribute comparison fields = data_obj.REQUIRED_FIELDS if "L_UPDATE" in fields: fields.remove("L_UPDATE") if "UPDATEBY" in fields: fields.remove("UPDATEBY") exp = '(str(!' + ('!) + str(!').join(fields) + '!)).replace(" ", "")' CalculateField_management(fc1, compare + "1", exp, "PYTHON_9.3") CalculateField_management(fc2, compare + "2", exp, "PYTHON_9.3") #convert to feature layer lyr1 = MakeLayer(fc1, "lyr1", "") lyr2 = MakeLayer(fc2, "lyr2", "") #if needed, add a field index in the unique ID if not indexExists(fc1, ng911id): AddIndex_management(lyr1, uniqueID, ng911id, "UNIQUE") if not indexExists(fc2, ng911id): AddIndex_management(lyr2, uniqueID, ng911id, "UNIQUE") #join the two layers AddJoin_management(lyr1, uniqueID, lyr2, uniqueID) #see where they don't match wc = basename(fc1) + ".compare1 <> " + basename(fc2) + ".compare2" lyr_compare = MakeLayer(lyr1, "lyr5", wc) #set up list for reporting various edits attributeEdits = [] spatialEdits = [] in1not2Records = [] in2not1Records = [] count = getFastCount(lyr_compare) #search for attribute issues if count > 0: with SearchCursor(lyr_compare, (basename(fc1) + "." + uniqueID)) as rows: for row in rows: unID = str(row[0]) attributeEdits.append(unID) del row, rows #clean up attribute issue stuff #delete joined layer Delete_management(lyr_compare) #search for spatial differences if possible dataType = Describe(fc1).dataType if dataType != "Table": with SearchCursor(fc1, (uniqueID, "SHAPE@TRUECENTROID")) as sRows: for sRow in sRows: uniqueID_val = str(sRow[0]) geom1 = sRow[1] where_clause = uniqueID + " = '" + uniqueID_val + "'" with SearchCursor(fc2, ("SHAPE@TRUECENTROID"), where_clause) as moreRows: for mRow in moreRows: geom2 = mRow[0] if geom1 != geom2: spatialEdits.append(uniqueID_val) del sRow, sRows #search for in 1 not 2 records wc1 = basename(fc2) + "." + compare + "2 is null" mismatch1 = MakeLayer(lyr1, "m1", wc1) count = getFastCount(mismatch1) if count > 0: with SearchCursor(mismatch1, (basename(fc1) + "." + uniqueID)) as rows: for row in rows: uniqueID_val = str(row[0]) in1not2Records.append(uniqueID_val) del row, rows #clean up Delete_management(mismatch1) #remove join RemoveJoin_management(lyr1) #search for in 2 not 1 records AddJoin_management(lyr2, uniqueID, lyr1, uniqueID) wc2 = basename(fc1) + "." + compare + "1 is null" mismatch2 = MakeLayer(lyr2, "m2", wc2) count = getFastCount(mismatch2) if count > 0: with SearchCursor(mismatch2, (basename(fc2) + "." + uniqueID)) as rows: for row in rows: uniqueID_val = str(row[0]) in2not1Records.append(uniqueID_val) del row, rows Delete_management(mismatch2) #remove join RemoveJoin_management(lyr2) #clean up #delete added fields DeleteField_management(fc1, [compare + "1"]) DeleteField_management(fc2, [compare + "2"]) #issue reporting issueDict = {"Attribute edit": attributeEdits, "Spatial edit": spatialEdits, "Record not in " + fc2: in1not2Records, "Record not in " + fc1: in2not1Records} if issueDict != {"Attribute edit": [], "Spatial edit": [], "Record not in " + fc2: [], "Record not in " + fc1: []}: #see if the results table already exists if not Exists(resultsTable): CreateTable_management(dirname(resultsTable), basename(resultsTable)) #see if the fields exists if not fieldExists(resultsTable, "DateChecked"): AddField_management(resultsTable, "DateChecked", "DATE") if not fieldExists(resultsTable, "FC1"): AddField_management(resultsTable, "FC1", "TEXT", "", "", 255) if not fieldExists(resultsTable, "FC2"): AddField_management(resultsTable, "FC2", "TEXT", "", "", 255) if not fieldExists(resultsTable, "EditResult"): AddField_management(resultsTable, "EditResult", "TEXT", "", "", 300) if not fieldExists(resultsTable, "FeatureID"): AddField_management(resultsTable, "FeatureID", "TEXT", "", "", 38) #create result records insertFields = ("DateChecked", "FC1", "FC2", "EditResult", "FeatureID") today = strftime("%m/%d/%y") cursor = InsertCursor(resultsTable, insertFields) for message in issueDict: IDlist = issueDict[message] if IDlist != []: for id_num in IDlist: userMessage(fc1) userMessage(fc2) userMessage(message) userMessage(id_num) cursor.insertRow((today, fc1, fc2, message, id_num)) else: userMessage("No changes were found.") Delete_management(lyr1) Delete_management(lyr2) ## except Exception as e: ## userMessage(str(e)) ## finally: cleanUp([lyr1, lyr2, lyr_compare])
def CreateUniqueRoadNameTable(optionsObject): """ From: Kansas_Unique_Road_Name_List_By_County.py This is an search cursor that creates an in_memory table listing all of the unique road names in a county. The primary purpose of the table that this function returns is to be used by the preAccidentGeocodingUpdate function to inflate the road names from 2010 and prior accident data that has been truncated to 6 characters or less. Note: The 6 character maximum was removed partway through 2010, so a significant portion of the 2010 data does not have that limitation. """ inputCenterlines = optionsObject.roadsFeaturesLocation try: # Set the variables # These should be provided by the optionsObject and # modified by input parameters # (if available how the script was called). GDBPath = "in_memory" env.workspace = "in_memory" tableName = "UniqueRoadNames" sortedTableName = "UniqueRoadNames_Sorted" fieldName2 = "RD" fieldLength2 = 99 roadList = list() newRoadList = list() cursorFields = ["RD"] ### If the table already exists, delete it. if Exists(tableName): Delete_management(tableName) else: pass ### Write a new table to the geodatabase. CreateTable_management(GDBPath, tableName) ### Add a field to the new table. AddField_management(tableName, fieldName2, "TEXT", "", "", fieldLength2) # Create a new search cursor to get road names. # Repoint this to the passed in parameter for the roads centerline. sCursor = SearchCursor(inputCenterlines, cursorFields) for rowItem in sCursor: roadListPart = [rowItem[0]] # Append the current road name to the road name list. roadList.append(roadListPart) # Delete cursor to remove locks on the data try: del sCursor except: pass # Print the first and last item in the roadList. AddMessage("roadList first item:") AddMessage(str(roadList[0])) AddMessage("roadList last item:") AddMessage(str(roadList[-1])) print "" # Assign only the unique values in roadList to a new list. for i in roadList: if (i not in newRoadList): newRoadList.append(i) # Write the newRoadList to the new table using an insert cursor. iCursor = InsertCursor(tableName, cursorFields) # Rewrite this so that it properly uses the information that it iterates over. for roadPlaceHolder in newRoadList: ##AddMessage(str(roadPlaceHolder[0])) iCursor.insertRow(roadPlaceHolder) # Delete cursor to remove locks on the data try: del iCursor except: pass Sort_management(tableName, sortedTableName, [["RD", "ASCENDING"]]) except Exception as Exception1: print str(Exception1[0]) try: del Exception1 except: pass # Even if the program has an exception, it should still delete the cursor objects. finally: try: del sCursor except: pass try: del iCursor except: pass
def CreateUniqueRoadNameTable(optionsObject): """ From: Kansas_Unique_Road_Name_List_By_County.py This is an search cursor that creates an in_memory table listing all of the unique road names in a county. The primary purpose of the table that this function returns is to be used by the preAccidentGeocodingUpdate function to inflate the road names from 2009 and prior accident data that has been truncated to 6 characters or less. """ inputCenterlines = optionsObject.roadsFeaturesLocation try: # Set the variables # These should be provided by the optionsObject and # modified by input parameters # (if available how the script was called). GDBPath = "in_memory" env.workspace = "in_memory" tableName = "UniqueRoadNames" sortedTableName = "UniqueRoadNames_Sorted" #fieldName0 = "COUNTY_L" #fieldName1 = "COUNTY_R" fieldName2 = "RD" #fieldLength0 = 25 #fieldLength1 = 25 fieldLength2 = 70 roadList = list() newRoadList = list() cursorFields = ["RD"] ### If the table already exists, delete it. if Exists(tableName): Delete_management(tableName) else: pass ### Write a new table to the geodatabase. CreateTable_management(GDBPath, tableName) ### Add fields to the new table. #AddField_management(tableName, fieldName0, "TEXT", "", "", fieldLength0) #AddField_management(tableName, fieldName1, "TEXT", "", "", fieldLength1) AddField_management(tableName, fieldName2, "TEXT", "", "", fieldLength2) # Create a new search cursor to get road names. # Repoint this to the passed in parameter for the roads centerline. sCursor = SearchCursor(inputCenterlines, cursorFields) for rowItem in sCursor: # Place the data into a 2-dimensional list, with the pairs being County Number and Road Name as strings, # with County Number padded to 3 spaces with leading zeroes. roadListPart = [rowItem[0]]#, rowItem[1], rowItem[2]] # Append the current county number and the current road name to the county number/road name list. roadList.append(roadListPart) # Delete cursor to remove locks on the data try: del sCursor except: pass # Print the first and last item in the roadList. AddMessage("roadList first item:") AddMessage(str(roadList[0])) AddMessage("roadList last item:") AddMessage(str(roadList[-1])) print "" # Assign only the unique values in roadList to a new list. for i in roadList: if (i not in newRoadList): newRoadList.append(i) # Write the newRoadList to the new table using an insert cursor. iCursor = InsertCursor(tableName, cursorFields) # Rewrite this so that it properly uses the information that it iterates over. for roadPlaceHolder in newRoadList: iCursor.insertRow(roadPlaceHolder) # Delete cursor to remove locks on the data try: del iCursor except: pass Sort_management(tableName, sortedTableName, [["RD", "ASCENDING"]]) sCursor = SearchCursor(sortedTableName, cursorFields) for rowItem in sCursor: print str(rowItem[0])# + " " + str(rowItem[1]) + " " + str(rowItem[2]) # Delete cursor to remove locks on the data try: del sCursor except: pass except Exception as Exception1: print str(Exception1[0]) try: del Exception1 except: pass # Even if the program has an exception, it should still delete the cursor objects. finally: try: del sCursor except: pass try: del iCursor except: pass
def iteratorprocess(): env.workspace = extractDataGDBPath accDataFeaturesList = ListFeatureClasses("CrashLocation.GEO.ACC*") # Use the FullTable for the overall total. accDataFullTable = os.path.join(extractDataGDBPath, r'CrashLocation.GEO.GIS_GEOCODE_ACC') withRoadsTotal = 0 gcKTotal = 0 gcNKTotal = 0 ofsKTotal = 0 ofsNKTotal = 0 NG911CoAccidents = 0 inMemoryTempLayer = 'inMemoryTempFC' for countyItem in coAbbrAndNoList: countyNumber = countyItem[1] countyName = countyItem[2] accDataPointsKDOT = "CrashLocation.GEO.ACC_PTS_" + countyNumber accDataPointsNK = "CrashLocation.GEO.ACC_PTS_" + countyNumber + "_NK" accDataOffsetKDOT = "CrashLocation.GEO.ACC_OFS_PTS_" + countyNumber accDataOffsetNK = "CrashLocation.GEO.ACC_OFS_PTS_" + countyNumber + "_NK" # Re-zero the loop variables here so that the table doesn't get incorrect information in it. totalAccidents = 0 geocodedAccidents = 0 geocodedAccidentsNK = 0 offsetAccidents = 0 offsetAccidentsNK = 0 gcPercent = '0.00' gcNKPercent = '0.00' ofsPercent = '0.00' ofsNKPercent = '0.00' if (accDataPointsKDOT in accDataFeaturesList) or (accDataPointsNK in accDataFeaturesList) or \ (accDataOffsetKDOT in accDataFeaturesList) or (accDataOffsetNK in accDataFeaturesList): if accDataPointsKDOT in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataPointsKDOTPath = os.path.join(extractDataGDBPath, accDataPointsKDOT) MakeFeatureLayer_management(accDataPointsKDOTPath, inMemoryTempLayer) #SelectLayerByAttribute_management(inMemoryTempLayer, 'CLEAR_SELECTION') tempResult = GetCount_management(inMemoryTempLayer) totalAccidents = int(tempResult.getOutput(0)) if totalAccidents > 0: withRoadsTotal += totalAccidents else: pass selectWhereClause = """ Status <> 'U' """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) geocodedAccidents = int(tempResult.getOutput(0)) else: pass if accDataPointsNK in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataPointsNKPath = os.path.join(extractDataGDBPath, accDataPointsNK) MakeFeatureLayer_management(accDataPointsNKPath, inMemoryTempLayer) selectWhereClause = """ Status <> 'U' """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) geocodedAccidentsNK = int(tempResult.getOutput(0)) else: pass if accDataOffsetKDOT in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataOffsetKDOTPath = os.path.join(extractDataGDBPath, accDataOffsetKDOT) MakeFeatureLayer_management(accDataOffsetKDOTPath, inMemoryTempLayer) selectWhereClause = """ isOffset IS NOT NULL """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) offsetAccidents = int(tempResult.getOutput(0)) else: pass if accDataOffsetNK in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataOffsetNKPath = os.path.join(extractDataGDBPath, accDataOffsetNK) MakeFeatureLayer_management(accDataOffsetNKPath, inMemoryTempLayer) selectWhereClause = """ isOffset IS NOT NULL """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) offsetAccidentsNK = int(tempResult.getOutput(0)) else: pass try: gcPercent = "{0:.2f}".format( (float(geocodedAccidents) / totalAccidents) * 100) gcNKPercent = "{0:.2f}".format( (float(geocodedAccidentsNK) / totalAccidents) * 100) ofsPercent = "{0:.2f}".format( (float(offsetAccidents) / totalAccidents) * 100) ofsNKPercent = "{0:.2f}".format( (float(offsetAccidentsNK) / totalAccidents) * 100) except ZeroDivisionError: gcPercent = None gcNKPercent = None ofsPercent = None ofsNKPercent = None except: pass gcKTotal += geocodedAccidents gcNKTotal += geocodedAccidentsNK ofsKTotal += offsetAccidents ofsNKTotal += offsetAccidentsNK NG911CoAccidents += totalAccidents print("\n" + countyName + " County has " + str(totalAccidents) + " totalAccidents.") print("gcPercent: " + gcPercent + " gcNKPercent: " + gcNKPercent + " ofsPercent: " + ofsPercent + " ofsNKPercent: " + ofsNKPercent) # To get the withRoadsTotal, sum the number for each county that # returned a non-zero result for totalAccidents. else: pass reportResult = [ countyName, totalAccidents, gcPercent, gcNKPercent, ofsPercent, ofsNKPercent ] reportResultsList.append(reportResult) try: Delete_management(inMemoryTempLayer) except: pass MakeTableView_management(accDataFullTable, inMemoryTempLayer) tempResult = GetCount_management(inMemoryTempLayer) overallTotal = int(tempResult.getOutput(0)) for reportResultItem in reportResultsList: print str(reportResultItem[0]) gcNG911Percent = "{0:.2f}".format( (float(gcKTotal) / NG911CoAccidents) * 100) gcNKNG911Percent = "{0:.2f}".format( (float(gcNKTotal) / NG911CoAccidents) * 100) ofsNG911Percent = "{0:.2f}".format( (float(ofsKTotal) / NG911CoAccidents) * 100) ofsNKNG911Percent = "{0:.2f}".format( (float(ofsNKTotal) / NG911CoAccidents) * 100) print "\n" + "The NG911Total is: " + str(NG911CoAccidents) print(" with gcPercent: " + gcNG911Percent + " gcNKPercent: " + gcNKNG911Percent + " ofsPercent: " + ofsNG911Percent + " ofsNKPercent: " + ofsNKNG911Percent) reportResult = [ "NG911Total", NG911CoAccidents, gcNG911Percent, gcNKNG911Percent, ofsNG911Percent, ofsNKNG911Percent ] reportResultsList.append(reportResult) gcOverallPercent = "{0:.2f}".format((float(gcKTotal) / overallTotal) * 100) gcNKOverallPercent = "{0:.2f}".format( (float(gcNKTotal) / overallTotal) * 100) ofsOverallPercent = "{0:.2f}".format( (float(ofsKTotal) / overallTotal) * 100) ofsNKOverallPercent = "{0:.2f}".format( (float(ofsNKTotal) / overallTotal) * 100) print "\n" + "The OverallTotal is: " + str(overallTotal) print(" with gcPercent: " + gcOverallPercent + " gcNKPercent: " + gcNKOverallPercent + " ofsPercent: " + ofsOverallPercent + " ofsNKPercent: " + ofsNKOverallPercent) reportResult = [ "OverallTotal", overallTotal, gcOverallPercent, gcNKOverallPercent, ofsOverallPercent, ofsNKOverallPercent ] reportResultsList.append(reportResult) resultsTablePath = recreateResultsTable() # Delete the previous table information, if any, then create an insert cursor # and place all of the report result items in the table. newICursor = InsertCursor(resultsTablePath, insertCursorFields) for reportResultItem in reportResultsList: insertedRowID = newICursor.insertRow(reportResultItem) print "Inserted a new row into the REPORT_INFO table with OID: " + str( insertedRowID)
def main(): fc = GetParameterAsText(0) out_table = GetParameterAsText(1) out_fc = GetParameterAsText(2) for thingy in [out_table, out_fc]: if Exists(thingy): Delete_management(thingy) # --------------set up reporting table for new field names----------------- field_name = "PRENAME" field_count = "NEWCOUNT" schema_count = "PRECOUNT" new_name = "NEWNAME" #see if the output table exists if not Exists(out_table): CreateTable_management(dirname(out_table), basename(out_table)) else: DeleteRows_management(out_table) #see if fields already exist, if not, create them if not fieldExists(out_table, field_name): AddField_management(out_table, field_name, "TEXT", "", "", 30) if not fieldExists(out_table, schema_count): AddField_management(out_table, schema_count, "LONG") if not fieldExists(out_table, field_count): AddField_management(out_table, field_count, "SHORT") if not fieldExists(out_table, new_name): AddField_management(out_table, new_name, "TEXT", "", "", 10) # loop through all fields all_fields = ListFields(fc) # create name dictionary of shortened shapefile names name_dictionary = {} shortList = [] # necessary for flagging repeated field names for fn in all_fields: short_name = fn.name if len(fn.name) > 10: short_name = fn.name[0:10] # make sure the shortened field name doesn't already exists if short_name not in shortList: shortList.append(short_name) name_dictionary[fn.name] = short_name else: i = 0 while short_name in shortList and i < 100: short_name = short_name[0:7] + "_" + str(i) i += 1 name_dictionary[fn.name] = short_name shortList.append(short_name) # -----next step, create new feature class & add all fields---------------- # -----for text fields, make the length the proper length------------------ desc = Describe(fc) geom_type = desc.shapeType SR = desc.spatialReference # create new feature class CreateFeatureclass_management(dirname(out_fc), basename(out_fc), geom_type, "", "", "", SR) # create list to hold the names of number fields (used later) numFields = [] dateFields = [] # get the name of the OID field while looping oid = "" # loop through string fields for f in all_fields: short_name = name_dictionary[f.name] data_type = f.type.upper() # check to see if the data type is "normal" if data_type in [ "TEXT", "FLOAT", "DOUBLE", "SHORT", "LONG", "DATE", "BLOB", "RASTER", "GUID", "STRING", "INTEGER", "SMALLINTEGER" ]: # special track for string fields if data_type in ["STRING", "TEXT"]: # set counter at 0 i = 0 # set up search cursor on feature class just on that field with SearchCursor(fc, (f.name)) as rows: for row in rows: if row[0] is not None: # loop through values to get the longest length if len(row[0]) > i: i = len(row[0]) # make sure i isn't bigger than 254 if i > 254: i = 254 # at this point, i equals the length of the longest field entry # insert the field name and the length into the output table cursor = InsertCursor( out_table, (field_name, field_count, schema_count, new_name)) new_row = (f.name, i, f.length, short_name) cursor.insertRow(new_row) del row, rows, cursor, new_row # add a row to the new feature class AddField_management(out_fc, short_name, "TEXT", "", "", i) # track for numbers, GUIDs & dates else: AddField_management(out_fc, short_name, data_type) # if it's a number, record the field name in the num field list if data_type in [ "SHORT", "LONG", "INTEGER", "FLOAT", "DOUBLE" ]: numFields.append(f.name) elif data_type in ["DATE"]: dateFields.append(f.name) #make sure all fields are in the translation table cursor = InsertCursor(out_table, (field_name, new_name)) new_row = (f.name, short_name) cursor.insertRow(new_row) del cursor, new_row elif data_type == "OID": AddField_management(out_fc, "LinkOID", "INTEGER") name_dictionary[f.name] = "LinkOID" # add for field mapping oid = f.name # add link field for object ID to the mapping table cursor = InsertCursor(out_table, (field_name, new_name)) new_row = (f.name, "LinkOID") cursor.insertRow(new_row) del cursor, new_row elif data_type == "GEOMETRY": pass else: print("Field " + f.name + " is type " + f.type + ". It will not be copied over.") AddWarning("Field " + f.name + " is type " + f.type + ". It will not be copied over.") del name_dictionary[f.name] # -----copy data into the new FC------------------------------------------- # set up field lists for search & insert cursors oldFields, newFields = [], [] for field in name_dictionary.keys(): oldFields.append(field) newFields.append(name_dictionary[field]) # set up a text only version of the fields oldFieldsTextOnly = tuple(oldFields) newFieldsTextOnly = tuple(newFields) # add SHAPE to the original set of fields oldFields.append("SHAPE@") newFields.append("SHAPE@") # convert the new field list to a tuple, safety first newFields = tuple(newFields) # this is the one with the shape field # create a list of the indexes of number & date fields numFieldsIndexList, dateFieldsIndexList = [], [] for numF in numFields: numFieldsIndexList.append(oldFields.index(numF)) for dateF in dateFields: dateFieldsIndexList.append(oldFields.index(dateF)) # ran into an issue with invalid geometry, so here's the workaround invalidDict = {"point": 1, "polyline": 2, "polygon": 3} # set up reporting for records that didn't copy didNotCopy = [] # fill new rows with old rows with SearchCursor(fc, oldFields) as rows: for row in rows: geomIndex = oldFields.index("SHAPE@") geom = row[geomIndex] objectID = str(row[oldFields.index(oid)]) try: try: # find the minimum number of required points minNum = invalidDict[geom_type.lower()] # get the count of points in the geometry count = geom.pointCount # if the count is smaller than the minimum number, there's a problem if count < minNum: wc = oid + " = " + objectID # here, we won't copy the geometry, only the fields userMessage("count smaller than min") with SearchCursor(fc, oldFieldsTextOnly, wc) as rows2: for row2 in rows2: makeRow(out_fc, newFieldsTextOnly, row2, numFieldsIndexList, dateFieldsIndexList) del row2, rows2, wc else: # excellent, the record is normal & will copy makeRow(out_fc, newFields, row, numFieldsIndexList, dateFieldsIndexList) except Exception as e: userMessage(str(e)) # if we're in this area, it means the record has no geometry wc = oid + " = " + objectID with SearchCursor(fc, oldFieldsTextOnly, wc) as rows2: for row2 in rows2: makeRow(out_fc, newFieldsTextOnly, row2, numFieldsIndexList, dateFieldsIndexList) del row2, rows2, wc except Exception as e: userMessage(str(e)) # for whatever reason, the record did not copy userMessage("Error copying record ObjectID " + objectID) didNotCopy.append(objectID) if didNotCopy != []: userMessage("These records did not copy- %s %s" % (oid, ", ".join(didNotCopy))) userMessage("Skinny shapefile complete.")
def checkUniqueIDFrequency(currentPathSettings): gdb = currentPathSettings.gdbPath esbList = currentPathSettings.esbList fcList = currentPathSettings.fcList layerList = [] env.workspace = gdb table = "ESB_IDS" #create temp table of esbID's if esbList <> []: layerList = ["ESB_IDS"] if Exists(table): Delete_management(table) CreateTable_management(gdb, table) AddField_management(table, "ESBID", "TEXT", "", "", 38) AddField_management(table, "ESB_LYR", "TEXT", "", "", 15) esbFields = ("ESBID") #copy ID's & esb layer type into the table for esb in esbList: with SearchCursor(esb, esbFields) as rows: for row in rows: cursor = InsertCursor(table, ('ESBID', 'ESB_LYR')) cursor.insertRow((row[0], esb)) try: #clean up del rows, row, cursor except: print "objects cannot be deleted, they don't exist" else: for fc in fcList: fc = basename(fc) layerList.append(fc) #loop through layers in the gdb that aren't esb & ESB_IDS ## layers = getCurrentLayerList(esb) ## layers.append("ESB_IDS") values = [] recordType = "fieldValues" today = strftime("%m/%d/%y") for layer in layerList: ## if layer not in esb: if layer != "ESB_IDS": #for each layer, get the unique ID field uniqueID = getUniqueIDField(layer.upper()) else: #for esb layers, get the unique ID field uniqueID = "ESBID" Statistics_analysis(layer, layer + "_freq", [[uniqueID,"COUNT"]], uniqueID) #set parameters for the search cursor where_clause = "FREQUENCY > 1" fields = (uniqueID, "FREQUENCY") fl = "fl" MakeTableView_management(layer + "_freq", fl, where_clause) result = GetCount_management(fl) count = int(result.getOutput(0)) if count > 0: #set a search cursor with just the unique ID field with SearchCursor(layer + "_freq", fields, where_clause) as rows2: stringESBReport = "" for row2 in rows2: if layer == "ESB_IDS": stringEsbInfo = [] wc2 = "ESBID = " + row2[0] with SearchCursor("ESB_IDS", ("ESB_LYR"), wc2) as esbRows: for esbRow in esbRows: stringEsbInfo.append(esbRow[0]) stringESBReport = " and ".join(stringEsbInfo) else: lyr = layer #report duplicate IDs report = str(row2[0]) + " is a duplicate ID" if stringESBReport != "": report = report + " in " + stringESBReport val = (today, report, lyr, uniqueID, row2[0]) values.append(val) Delete_management(layer + "_freq") Delete_management(fl) #report duplicate records if values != []: RecordResults(recordType, values, gdb) userMessage("Checked unique ID frequency. Results are in table FieldValuesCheckResults.") else: userMessage("All ID's are unique.") #if it exists, clean up table if Exists(table): Delete_management(table)
def prepXLS(tnxls_sheet, gdb, xls_fields): import xlrd userMessage("Converting spreadsheet to geodatabase table...") #create gdb table tn_object = getTNObject(gdb) outTable = tn_object.TN_List tn_gdb = tn_object.tn_gdb LocatorFolder = tn_object.LocatorFolder #get the correct address point object address_points = join(gdb, "NG911", "AddressPoints") a_obj = getFCObject(address_points) if not exists(LocatorFolder): mkdir(LocatorFolder) if not Exists(tn_gdb): CreateFileGDB_management(dirname(tn_gdb), basename(tn_gdb)) if Exists(outTable): Delete_management(outTable) tname = basename(outTable) CreateTable_management(tn_gdb, tname) #add fields fields = (a_obj.HNO, a_obj.HNS, a_obj.PRD, a_obj.RD, a_obj.STS, a_obj.POD, a_obj.MUNI, "NGTNID") colIDlist = ["0", "0", "0", "0", "0", "0", "0", "0"] #add fields for field in fields: AddField_management(outTable, field, "TEXT", "", "", 50) #read xls spreadsheet tnxls = dirname(tnxls_sheet) xl_workbook = xlrd.open_workbook(tnxls) xl_sheet = xl_workbook.sheet_by_index(0) header_row = xl_sheet.row(0) for idx, cell_obj in enumerate(header_row): val = xl_sheet.cell(0,idx).value if val in xls_fields: place = xls_fields.index(val) colIDlist[place] = idx #start at row 1 (maybe? depends on indexing, skip the headers is the goal) rowIdx = 1 endRow = xl_sheet.nrows userMessage("This takes a while. It's a great time to take a 10 minute walk or refresh your favorite beverage.") #loop through info rows while rowIdx < endRow: if str(rowIdx)[-3:] == "000": userMessage("Converted " + str(rowIdx) + " spreadsheet records so far...") if rowIdx == endRow/2: userMessage("Have you backed up your GIS data with DASC recently? Email [email protected] for more info!") #create list to hold info rowToInsertList = [] #look at just the fields I want to import for colID in colIDlist: if colID != "0": cellval = xl_sheet.cell(rowIdx,colID).value rowToInsertList.append(cellval) else: rowToInsertList.append("") #convert list of info to a tuple rowToInsert = tuple(rowToInsertList) #create insert cursor i = InsertCursor(outTable,fields) #insert the row of info i.insertRow(rowToInsert) #clean up del i, rowToInsert, rowToInsertList rowIdx = rowIdx + 1 # split out RD into RD, STS, & POD postRoadFields = [a_obj.STS, a_obj.POD, a_obj.RD] folder = join(dirname(dirname(realpath(__file__))), "Domains") streetSuffixDict = getFieldDomain("STS", folder).keys() postDirectionalDict = getFieldDomain("POD", folder).keys() with UpdateCursor(outTable, postRoadFields) as rows: for row in rows: fullNameList = row[2].split() i = 1 rd =[fullNameList[0]] while i < len(fullNameList): if fullNameList[i] not in streetSuffixDict and fullNameList[i] not in postDirectionalDict: rd.append(fullNameList[i]) elif fullNameList[i] in streetSuffixDict: row[0] = fullNameList[i] elif fullNameList[i] in postDirectionalDict: if fullNameList[0] not in ("AVENUE", "ROAD", "HIGHWAY", "HWY"): row[1] = fullNameList[i] else: rd.append(fullNameList[i]) i += 1 row[2] = " ".join(rd) rows.updateRow(row) userMessage("Conversion to geodatabase table successful. " + str(endRow-1) + " rows converted. VOIP and test rows were not converted.")
def accidentdataextractor(countyNo): extractDataOutGDB = r'Database Connections\[email protected]' extractDataOutName = r'GEO.ACC_' + countyNo extractDataOutPathFull = os.path.join(extractDataOutGDB, extractDataOutName) tableSelectSQL = """ "COUNTY_NBR" = '""" + countyNo + """' """ MakeTableView_management(extractDataInPathFull, tableViewName, tableSelectSQL) accidentDataFull = list() accSCursor = SearchCursor(tableViewName, accidentTableFieldNameList) for accItem in accSCursor: #print "Adding the data with acc_key of: " + str(accItem[accKeyPosition]) + " to the accidentDataFull list." accidentDataFull.append(accItem) try: del accSCursor except: pass try: Delete_management(tableViewName) except: pass if Exists(extractDataOutPathFull): print extractDataOutPathFull + " already exists." existingKeysList = list() outSCursor = SearchCursor(extractDataOutPathFull, "ACCIDENT_KEY") for outSItem in outSCursor: #print "This key already exists in the output table: " + str(outSItem[0]) existingKeysList.append(outSItem[0]) accDataKeysFull = [accDataRow[accKeyPosition] for accDataRow in accidentDataFull] # Use a set because finding membership in a set is O(1) # whereas finding membership in a list is O(n). # That's a problem when trying to find membership in # Johnson county, as it currently takes 65,000 * 65,000 * t # instead of just 65,000 * t. # This causes the script to lag for a long time on # county no 046. # When using a set instead, it takes about 3 seconds. existingKeysSet = set(existingKeysList) accDataKeysToInsert = [accDataKey for accDataKey in accDataKeysFull if accDataKey not in existingKeysSet] extractDataOutCursor = InsertCursor(extractDataOutPathFull, accidentTableFieldNameList) for accDataItem in accidentDataFull: if str(accDataItem[accKeyPosition]) in accDataKeysToInsert: insertedItem = extractDataOutCursor.insertRow(accDataItem) print "Inserted a row with OBJECTID " + str(insertedItem) + "." else: pass #searchcursor here to get all the existing accident keys from the current output table. # comparison to see which rows from the tableView don't already # exist in the current output table # insertcursor here to place only missing accident key rows # from the searchCursor into the extractDataOutPathFull. else: CreateTable_management(extractDataOutGDB, extractDataOutName, extractDataInPathFull) extractDataOutCursor = InsertCursor(extractDataOutPathFull, accidentTableFieldNameList) for accDataItem in accidentDataFull: extractDataOutCursor.insertRow(accDataItem)
def accidentdataextractor(countyNo): extractDataOutGDB = r'Database Connections\[email protected]' extractDataOutName = r'GEO.ACC_' + countyNo extractDataOutPathFull = os.path.join(extractDataOutGDB, extractDataOutName) tableSelectSQL = """ "COUNTY_NBR" = '""" + countyNo + """' """ MakeTableView_management(extractDataInPathFull, tableViewName, tableSelectSQL) accidentDataFull = list() accSCursor = SearchCursor(tableViewName, accidentTableFieldNameList) for accItem in accSCursor: #print "Adding the data with acc_key of: " + str(accItem[accKeyPosition]) + " to the accidentDataFull list." accidentDataFull.append(accItem) try: del accSCursor except: pass try: Delete_management(tableViewName) except: pass if Exists(extractDataOutPathFull): print extractDataOutPathFull + " already exists." existingKeysList = list() outSCursor = SearchCursor(extractDataOutPathFull, "ACCIDENT_KEY") for outSItem in outSCursor: #print "This key already exists in the output table: " + str(outSItem[0]) existingKeysList.append(outSItem[0]) accDataKeysFull = [ accDataRow[accKeyPosition] for accDataRow in accidentDataFull ] # Use a set because finding membership in a set is O(1) # whereas finding membership in a list is O(n). # That's a problem when trying to find membership in # Johnson county, as it currently takes 65,000 * 65,000 * t # instead of just 65,000 * t. # This causes the script to lag for a long time on # county no 046. # When using a set instead, it takes about 3 seconds. existingKeysSet = set(existingKeysList) accDataKeysToInsert = [ accDataKey for accDataKey in accDataKeysFull if accDataKey not in existingKeysSet ] extractDataOutCursor = InsertCursor(extractDataOutPathFull, accidentTableFieldNameList) for accDataItem in accidentDataFull: if str(accDataItem[accKeyPosition]) in accDataKeysToInsert: insertedItem = extractDataOutCursor.insertRow(accDataItem) print "Inserted a row with OBJECTID " + str(insertedItem) + "." else: pass #searchcursor here to get all the existing accident keys from the current output table. # comparison to see which rows from the tableView don't already # exist in the current output table # insertcursor here to place only missing accident key rows # from the searchCursor into the extractDataOutPathFull. else: CreateTable_management(extractDataOutGDB, extractDataOutName, extractDataInPathFull) extractDataOutCursor = InsertCursor(extractDataOutPathFull, accidentTableFieldNameList) for accDataItem in accidentDataFull: extractDataOutCursor.insertRow(accDataItem)
def iteratorprocess(): env.workspace = extractDataGDBPath accDataFeaturesList = ListFeatureClasses("CrashLocation.GEO.ACC*") # Use the FullTable for the overall total. accDataFullTable = os.path.join(extractDataGDBPath, r'CrashLocation.GEO.GIS_GEOCODE_ACC') withRoadsTotal = 0 gcKTotal = 0 gcNKTotal = 0 ofsKTotal = 0 ofsNKTotal = 0 NG911CoAccidents = 0 inMemoryTempLayer = 'inMemoryTempFC' for countyItem in coAbbrAndNoList: countyNumber = countyItem[1] countyName = countyItem[2] accDataPointsKDOT = "CrashLocation.GEO.ACC_PTS_" + countyNumber accDataPointsNK = "CrashLocation.GEO.ACC_PTS_" + countyNumber + "_NK" accDataOffsetKDOT = "CrashLocation.GEO.ACC_OFS_PTS_" + countyNumber accDataOffsetNK = "CrashLocation.GEO.ACC_OFS_PTS_" + countyNumber + "_NK" # Re-zero the loop variables here so that the table doesn't get incorrect information in it. totalAccidents = 0 geocodedAccidents = 0 geocodedAccidentsNK = 0 offsetAccidents = 0 offsetAccidentsNK = 0 gcPercent = '0.00' gcNKPercent = '0.00' ofsPercent = '0.00' ofsNKPercent = '0.00' if (accDataPointsKDOT in accDataFeaturesList) or (accDataPointsNK in accDataFeaturesList) or \ (accDataOffsetKDOT in accDataFeaturesList) or (accDataOffsetNK in accDataFeaturesList): if accDataPointsKDOT in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataPointsKDOTPath = os.path.join(extractDataGDBPath, accDataPointsKDOT) MakeFeatureLayer_management(accDataPointsKDOTPath, inMemoryTempLayer) #SelectLayerByAttribute_management(inMemoryTempLayer, 'CLEAR_SELECTION') tempResult = GetCount_management(inMemoryTempLayer) totalAccidents = int(tempResult.getOutput(0)) if totalAccidents > 0: withRoadsTotal += totalAccidents else: pass selectWhereClause = """ Status <> 'U' """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) geocodedAccidents = int(tempResult.getOutput(0)) else: pass if accDataPointsNK in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataPointsNKPath = os.path.join(extractDataGDBPath, accDataPointsNK) MakeFeatureLayer_management(accDataPointsNKPath, inMemoryTempLayer) selectWhereClause = """ Status <> 'U' """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) geocodedAccidentsNK = int(tempResult.getOutput(0)) else: pass if accDataOffsetKDOT in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataOffsetKDOTPath = os.path.join(extractDataGDBPath, accDataOffsetKDOT) MakeFeatureLayer_management(accDataOffsetKDOTPath, inMemoryTempLayer) selectWhereClause = """ isOffset IS NOT NULL """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) offsetAccidents = int(tempResult.getOutput(0)) else: pass if accDataOffsetNK in accDataFeaturesList: try: Delete_management(inMemoryTempLayer) except: pass accDataOffsetNKPath = os.path.join(extractDataGDBPath, accDataOffsetNK) MakeFeatureLayer_management(accDataOffsetNKPath, inMemoryTempLayer) selectWhereClause = """ isOffset IS NOT NULL """ SelectLayerByAttribute_management(inMemoryTempLayer, 'NEW_SELECTION', selectWhereClause) tempResult = GetCount_management(inMemoryTempLayer) offsetAccidentsNK = int(tempResult.getOutput(0)) else: pass try: gcPercent = "{0:.2f}".format((float(geocodedAccidents) / totalAccidents) * 100) gcNKPercent = "{0:.2f}".format((float(geocodedAccidentsNK) / totalAccidents) * 100) ofsPercent = "{0:.2f}".format((float(offsetAccidents) / totalAccidents) * 100) ofsNKPercent = "{0:.2f}".format((float(offsetAccidentsNK) / totalAccidents) * 100) except ZeroDivisionError: gcPercent = None gcNKPercent = None ofsPercent = None ofsNKPercent = None except: pass gcKTotal += geocodedAccidents gcNKTotal += geocodedAccidentsNK ofsKTotal += offsetAccidents ofsNKTotal += offsetAccidentsNK NG911CoAccidents += totalAccidents print("\n" + countyName + " County has " + str(totalAccidents) + " totalAccidents.") print("gcPercent: " + gcPercent + " gcNKPercent: " + gcNKPercent + " ofsPercent: " + ofsPercent + " ofsNKPercent: " + ofsNKPercent) # To get the withRoadsTotal, sum the number for each county that # returned a non-zero result for totalAccidents. else: pass reportResult = [countyName, totalAccidents, gcPercent, gcNKPercent, ofsPercent, ofsNKPercent] reportResultsList.append(reportResult) try: Delete_management(inMemoryTempLayer) except: pass MakeTableView_management(accDataFullTable, inMemoryTempLayer) tempResult = GetCount_management(inMemoryTempLayer) overallTotal = int(tempResult.getOutput(0)) for reportResultItem in reportResultsList: print str(reportResultItem[0]) gcNG911Percent = "{0:.2f}".format((float(gcKTotal) / NG911CoAccidents) * 100) gcNKNG911Percent = "{0:.2f}".format((float(gcNKTotal) / NG911CoAccidents) * 100) ofsNG911Percent = "{0:.2f}".format((float(ofsKTotal) / NG911CoAccidents) * 100) ofsNKNG911Percent = "{0:.2f}".format((float(ofsNKTotal) / NG911CoAccidents) * 100) print "\n" + "The NG911Total is: " + str(NG911CoAccidents) print( " with gcPercent: " + gcNG911Percent + " gcNKPercent: " + gcNKNG911Percent + " ofsPercent: " + ofsNG911Percent + " ofsNKPercent: " + ofsNKNG911Percent) reportResult = ["NG911Total", NG911CoAccidents, gcNG911Percent, gcNKNG911Percent, ofsNG911Percent, ofsNKNG911Percent] reportResultsList.append(reportResult) gcOverallPercent = "{0:.2f}".format((float(gcKTotal) / overallTotal) * 100) gcNKOverallPercent = "{0:.2f}".format((float(gcNKTotal) / overallTotal) * 100) ofsOverallPercent = "{0:.2f}".format((float(ofsKTotal) / overallTotal) * 100) ofsNKOverallPercent = "{0:.2f}".format((float(ofsNKTotal) / overallTotal) * 100) print "\n" + "The OverallTotal is: " + str(overallTotal) print (" with gcPercent: " + gcOverallPercent + " gcNKPercent: " + gcNKOverallPercent + " ofsPercent: " + ofsOverallPercent + " ofsNKPercent: " + ofsNKOverallPercent) reportResult = ["OverallTotal", overallTotal, gcOverallPercent, gcNKOverallPercent, ofsOverallPercent, ofsNKOverallPercent] reportResultsList.append(reportResult) resultsTablePath = recreateResultsTable() # Delete the previous table information, if any, then create an insert cursor # and place all of the report result items in the table. newICursor = InsertCursor(resultsTablePath, insertCursorFields) for reportResultItem in reportResultsList: insertedRowID = newICursor.insertRow(reportResultItem) print "Inserted a new row into the REPORT_INFO table with OID: " + str(insertedRowID)