def ExportReport(table, delta_date): env.overwriteOutput = True env.workspace = "Database Connections/RPUD_TRANSDB.sde" #env.workspace = os.path.join(os.path.dirname(sys.argv[0]), "RPUD_TESTDB - MOBILE_EDIT_VERSION.sde") #the name of database connection may need to be changed when in production #convert local time to UTC for query now = datetime.date.today().strftime('%Y%m%d') today = datetime.datetime(int(now[:4]), int(now[4:6]), int(now[6:]), 00, 00, 00) + datetime.timedelta(hours=4) yesterday = today - datetime.timedelta(days=delta_date) outputExcel = os.path.join("//corfile/Public_Utilities_NS/5215_Capital_Improvement_Projects/636_Geographic_Info_System/Joe/Collector App/Flushing app/Daily Report/", table + "_" + yesterday.strftime("%Y%m%d") + ".xls") logMessage("Input table is: " + table) logMessage("Output Excel file is: " + os.path.basename(outputExcel)) print ("Exporting table to Excel...") #query report table for records in previous day whereClause = '"CREATED_DATE" < timestamp \'{0}\' AND "CREATED_DATE" > timestamp \'{1}\' AND "CREW" NOT LIKE \'_GIS\' AND "CREW" NOT LIKE \'_test\' ORDER BY REPORT_DATE'.format(str(today), str(yesterday)) arcpy.MakeQueryTable_management(table, 'queryTable', "NO_KEY_FIELD", "", "", whereClause) recordNum = arcpy.GetCount_management('queryTable') logMessage(str(recordNum) + " " + table + " reports for " + (yesterday).strftime("%b %d, %Y")) #for test, print out fiels in queryTable # fields = arcpy.ListFields('queryTable') # for field in fields: # print("{0} is a type of {1}".format(field.aliasName, field.type)) #export queried table to excel, ALIAS option does not work here so far, need a solution arcpy.TableToExcel_conversion('queryTable', outputExcel, 'ALIAS') logMessage(os.path.basename(outputExcel) + " has been exported.") #return yesterday date for naming return yesterday, recordNum
def get_precipitation(dataspace, date, idw_pow, rastercellsize, parameter_safe): """ Funktion zur Interpolation des Niederschlags anhand des Inverse Distance Weighting (IDW). Die Berechnung erfolgt über das IDW-Tool der Erweiterung "Spatial Analyst". Zunächst werden die Niederschlagswerte eines Tages je Station in eine Liste geschrieben. Der Index der Niederschlagswerte je Station entspricht dem Index des dazugehörigen Stationsnamens in einer zweiten Liste. Diese Listen werden mittels eines Searchcursors erstellt. Über einen Updatecursor werden die Niederschlagsmesswerte in eine Tabelle geschrieben, die Informationen zum Stationsnamen und deren Koordinaten enthalten. Der Interpolationsradius ist auf das EZG Schotten 2 angepasst. :param dataspace: Dateipfad der Basisdaten (Typ: string) :param date: Tages ID (Typ: integer) :param idw_pow: Exponent des IDW (Typ: integer) :param rastercellsize: Groesse der Rasterzellen (Typ: foat) :param parameter_safe: Werte der Parameterkombination (Typ: string) :return: Interpolation des Niederschlags (Typ: raster) """ arcpy.MakeQueryTable_management( r'{}\N_Messstationen;'.format(dataspace) + r'{}\N_Zeitreihen'.format(dataspace), "p_temp", "USE_KEY_FIELDS", "N_Messstationen.Stationsnummer;N_Zeitreihen.TagesID", "N_Messstationen.Stationsnummer;N_Messstationen.Stationsname; N_Messstationen.Shape\ ;N_Zeitreihen.Tagessumme_mm;N_Zeitreihen.TagesID", "N_Zeitreihen.Stationsnummer =\ N_Messstationen.Stationsnummer AND N_Zeitreihen.TagesID = {}" .format(date)) # Niederschlagsinterpolation mittels Inverse Distance Weighting (IDW) idw = Idw("p_temp", "N_Zeitreihen.Tagessumme_mm", rastercellsize, idw_pow, RadiusFixed(20000.00000, 5), "") idw.save("IDW_rp{}_c{}_{}.tif".format(parameter_safe[0], parameter_safe[1], date)) arcpy.Delete_management("p_temp") print(time.strftime("%H:%M:%S: ") + "P ausgeführt.") return idw
def get_precipitation(dataspace, date, idw_pow, rastercellsize, parameter_safe): """ Interpolates the precipitation by Inverse Distance Weighting (IDW). The calculation is done by the "IDW" tool of the "Spatial Analyst" extension of Arc GIS. The precipitation data is selected by a query table from the timeseries table ("N_Zeitreihen") and the attribute table from the precipitation stations ("N_Messstationen"). :param dataspace: directory of the base data (:type: string) :param date: daily ID (:type: integer) :param idw_pow: IDW power (:type: integer) :param rastercellsize: raster cellsize (:type: float) :param parameter_safe: values of the variable combination (:type: tuple) :return: precipitation interpolation (:type: raster) """ arcpy.MakeQueryTable_management( r'{}\N_Messstationen;'.format(dataspace) + r'{}\N_Zeitreihen'.format(dataspace), "p_temp", "USE_KEY_FIELDS", "N_Messstationen.Stationsnummer;N_Zeitreihen.TagesID", "N_Messstationen.Stationsnummer;N_Messstationen.Stationsname; N_Messstationen.Shape\ ;N_Zeitreihen.Tagessumme_mm;N_Zeitreihen.TagesID", "N_Zeitreihen.Stationsnummer =\ N_Messstationen.Stationsnummer AND N_Zeitreihen.TagesID = {}" .format(date)) idw = Idw("p_temp", "N_Zeitreihen.Tagessumme_mm", rastercellsize, idw_pow, RadiusFixed(20000.00000, 5), "") idw.save("IDW_rp{}_c{}_{}.tif".format(parameter_safe[0], parameter_safe[1], date)) arcpy.Delete_management("p_temp") arcpy.AddMessage( time.strftime("%H:%M:%S: ") + "Niederschlag interpoliert.") return idw
def AMEALL(self): '''Método que retorna la subred de la zona productora''' # Process: Make Query Table variable = g_ESRI_variable_1 arcpy.MakeQueryTable_management( os.path.join(self.base, "Zona_Productora"), variable, "USE_KEY_FIELDS", "", "", "No_Zona = '{}'".format(self.subred)) return variable
def importar_tablas_trabajo(data, campos): arcpy.env.overwriteOutput = True db = 'CPV_SEGMENTACION_GDB' ip = '172.18.1.93' usuario = 'sde' password = '******' path_conexion = conx.conexion_arcgis(db, ip, usuario, password) arcpy.env.workspace = path_conexion temp_ubigeos = "" i = 0 for x in data: i = i + 1 if (i == 1): temp_ubigeos = "'{}'".format(x[0]) else: temp_ubigeos = "{},'{}'".format(temp_ubigeos, x[0]) if len(data) > 0: sql = expresion.Expresion_2(data, campos) else: sql = ' FLAG_NUEVO=1' list_capas = [ ["{}.sde.VW_FRENTES".format(db), tb_frentes_temp, 1], ] for i, capa in enumerate(list_capas): if capa[2] == 1: print "select * from {} where {} ".format(capa[0], sql) x = arcpy.MakeQueryLayer_management( path_conexion, 'capa{}'.format(i), "select * from {} where {} ".format(capa[0], sql)) else: x = arcpy.MakeQueryTable_management(capa[0], "capa{}".format(i), "USE_KEY_FIELDS", "objectid", "", sql) if capa[1] in [tb_frentes_temp]: temp = arcpy.CopyFeatures_management(x, 'in_memory/temp_{}'.format(i)) arcpy.AddField_management(temp, 'MANZANA2', 'TEXT', 50) arcpy.CalculateField_management(temp, 'MANZANA2', '!MANZANA!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA']) arcpy.CopyFeatures_management(temp, capa[1]) arcpy.AddField_management(capa[1], 'MANZANA', 'TEXT', 50) arcpy.CalculateField_management(capa[1], 'MANZANA', '!MANZANA2!', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['MANZANA2']) else: arcpy.CopyFeatures_management(x, capa[1]) arcpy.Sort_management(tb_frentes_temp, tb_frentes, ['UBIGEO', 'ZONA', 'MANZANA', 'FRENTE_ORD'])
def copy_to_memory_with_id_filter(self, input_table, in_memory_output_table_name, id_field_name, id_list): in_memory_table = self.workspace + "\\" + in_memory_output_table_name where_clause = id_field_name + " in (" for count, id in enumerate(id_list): where_clause += str(id) if len(id_list) > 1 and count + 1 < len(id_list): where_clause += "," where_clause += ")" arcpy.MakeQueryTable_management(input_table, in_memory_table, "", "", "", where_clause)
def exportToCSV(field_alias, field_names, table, outFile, outDir): #calculate date and local timestamp now = datetime.date.today().strftime('%Y%m%d') today = datetime.datetime(int(now[:4]), int(now[4:6]), int(now[6:]), 00, 00, 00) + datetime.timedelta(hours=4) yesterday = today - datetime.timedelta(days=1) #build query clause whereClause = '"CREATED_DATE" <= timestamp \'{0}\' AND "CREATED_DATE" > timestamp \'{1}\' AND "CREW" NOT LIKE \'_GIS\' AND "CREW" NOT LIKE \'_test\' ORDER BY REPORT_DATE'.format(str(today), str(yesterday)) arcpy.MakeQueryTable_management(table, 'queryTable' + table, "NO_KEY_FIELD", "", "", whereClause) recordNum = arcpy.GetCount_management('queryTable' + table) logMessage(str(recordNum) + " " + table + " reports for " + (yesterday).strftime("%b %d, %Y")) #list fields fields = arcpy.ListFields('queryTable' + table) #write to csv outFullFile = outFile + "_" + yesterday.strftime("%Y%m%d") + ".csv" with open(os.path.join(outDir, outFullFile), 'wb') as f: w = csv.writer(f) w.writerow(field_alias) rows = arcpy.SearchCursor("queryTable" + table) row = rows.next() for row in rows: field_vals = [] for field in fields: if field.name in field_names: #remove none and invalid value and convert utc time to local if row.getValue(field.name) == None: field_val = "" elif field.type == "Date": if row.getValue(field.name).year == 1899: field_val = "" else: field_val = (row.getValue(field.name) - datetime.timedelta(hours=4)).strftime("%Y-%m-%d %H:%M:%S") # if field.name == "REPORT_DATE": # field_val = (row.getValue(field.name) - datetime.timedelta(hours=4)).strftime("%Y-%m-%d") field_val = row.getValue(field.name) if isinstance(field_val, unicode): field_val = field_val.encode('utf-8') # handle utf characters # print field_val field_vals.append(field_val) w.writerow(field_vals) del row del rows f.close() return recordNum, outFullFile
def _create_query_table(feature, table, input_type, geodatabase): workspace_path = _get_workspace(geodatabase) _logger.info("Building Query Table parameters") qf_parameters = _build_qf(feature, table, workspace_path, input_type) _logger.info("Generating Query Table") QT = arcpy.MakeQueryTable_management(qf_parameters[0], qf_parameters[1], "USE_KEY_FIELDS", qf_parameters[2], qf_parameters[3], qf_parameters[4])[0] _logger.info("Copying final Query Table to\n" "{}".format(qf_parameters[5])) _logger.info("feature count: {}".format(arcpy.GetCount_management(QT)[0])) out__q_t = os.path.join(workspace_path, qf_parameters[5]) out_feature = arcpy.CopyFeatures_management(QT, out__q_t)[0] _logger.info("OUT: {}".format(out__q_t)) return out_feature
# -*- coding: utf-8 -*- # --------------------------------------------------------------------------- # shCreateFeatureClassInScracth.py # Created on: 2014-12-19 16:26:26.00000 # (generated by ArcGIS/ModelBuilder) # Description: # --------------------------------------------------------------------------- # Import arcpy module import arcpy arcpy.env.overwriteOutput = 1 # Local variables: AucklandStravaMetro_Edges_NZTM = "D:\\TEMP\\scratch.gdb\\AucklandStravaMetro_Edges_NZTM" CycleEvents = "D:\\TEMP\\scratch.gdb\\CycleEvents" AllCycleEventsIn2013 = "AllCycleEventsIn2013" AllCycleEventsIn2013FC = "D:\\TEMP\\scratch.gdb\\AllCycleEvents" # Process: Make Query Table arcpy.MakeQueryTable_management("D:\\TEMP\\scratch.gdb\\AucklandStravaMetro_Edges_NZTM;D:\\TEMP\\scratch.gdb\\CycleEvents", AllCycleEventsIn2013, "ADD_VIRTUAL_KEY_FIELD", "", "AucklandStravaMetro_Edges_NZTM.OBJECTID #;AucklandStravaMetro_Edges_NZTM.ID #;AucklandStravaMetro_Edges_NZTM.OSM_ID #;AucklandStravaMetro_Edges_NZTM.OSM_NAME #;AucklandStravaMetro_Edges_NZTM.OSM_META #;AucklandStravaMetro_Edges_NZTM.OSM_SOURCE #;AucklandStravaMetro_Edges_NZTM.OSM_TARGET #;AucklandStravaMetro_Edges_NZTM.CLAZZ #;AucklandStravaMetro_Edges_NZTM.FLAGS #;AucklandStravaMetro_Edges_NZTM.SOURCE #;AucklandStravaMetro_Edges_NZTM.TARGET #;AucklandStravaMetro_Edges_NZTM.KM #;AucklandStravaMetro_Edges_NZTM.KMH #;AucklandStravaMetro_Edges_NZTM.COST #;AucklandStravaMetro_Edges_NZTM.REVERSE_CO #;AucklandStravaMetro_Edges_NZTM.X1 #;AucklandStravaMetro_Edges_NZTM.Y1 #;AucklandStravaMetro_Edges_NZTM.X2 #;AucklandStravaMetro_Edges_NZTM.Y2 #;AucklandStravaMetro_Edges_NZTM.OID_1 #;AucklandStravaMetro_Edges_NZTM.EDGE_ID #;AucklandStravaMetro_Edges_NZTM.TRIDERCNT #;AucklandStravaMetro_Edges_NZTM.TRRIDERCNT #;AucklandStravaMetro_Edges_NZTM.TRIDECNT #;AucklandStravaMetro_Edges_NZTM.TRRIDECNT #;AucklandStravaMetro_Edges_NZTM.BIKECNT #;AucklandStravaMetro_Edges_NZTM.BIKETIME #;AucklandStravaMetro_Edges_NZTM.RBIKETIME #;AucklandStravaMetro_Edges_NZTM.COMMUTECNT #;AucklandStravaMetro_Edges_NZTM.AMRIDER #;AucklandStravaMetro_Edges_NZTM.AMRRIDER #;AucklandStravaMetro_Edges_NZTM.AMRIDE #;AucklandStravaMetro_Edges_NZTM.AMRRIDE #;AucklandStravaMetro_Edges_NZTM.AMBIKECNT #;AucklandStravaMetro_Edges_NZTM.AMBIKET #;AucklandStravaMetro_Edges_NZTM.AMRBIKET #;AucklandStravaMetro_Edges_NZTM.AMCOMMUTE #;AucklandStravaMetro_Edges_NZTM.PMRIDER #;AucklandStravaMetro_Edges_NZTM.PMRRIDER #;AucklandStravaMetro_Edges_NZTM.PMRIDE #;AucklandStravaMetro_Edges_NZTM.PMRRIDE #;AucklandStravaMetro_Edges_NZTM.PMBIKECNT #;AucklandStravaMetro_Edges_NZTM.PMBIKET #;AucklandStravaMetro_Edges_NZTM.PMRBIKET #;AucklandStravaMetro_Edges_NZTM.PMCOMMUTE #;AucklandStravaMetro_Edges_NZTM.IPRIDER #;AucklandStravaMetro_Edges_NZTM.IPRRIDER #;AucklandStravaMetro_Edges_NZTM.IPRIDE #;AucklandStravaMetro_Edges_NZTM.IPRRIDE #;AucklandStravaMetro_Edges_NZTM.IPBIKECNT #;AucklandStravaMetro_Edges_NZTM.IPBIKET #;AucklandStravaMetro_Edges_NZTM.IPRBIKET #;AucklandStravaMetro_Edges_NZTM.IPCOMMUTE #;AucklandStravaMetro_Edges_NZTM.Shape #;CycleEvents.OBJECTID #;CycleEvents.edge_id #;CycleEvents.year #;CycleEvents.day #;CycleEvents.hour #;CycleEvents.minute #;CycleEvents.athlete_count #;CycleEvents.rev_athlete_count #;CycleEvents.activity_count #;CycleEvents.rev_activity_count #;CycleEvents.total_activity_count #;CycleEvents.activity_time #;CycleEvents.rev_activity_time #;CycleEvents.commute_count #;CycleEvents.activity_count_norm #", "CycleEvents.edge_id = AucklandStravaMetro_Edges_NZTM.EDGE_ID") # Process: Copy Features arcpy.CopyFeatures_management(AllCycleEventsIn2013, AllCycleEventsIn2013FC, "", "0", "0", "0")
arcpy.JoinField_management(WIC__4_, "WORKORDERID_RINDEX", WIC_trim_xy_PARCELS, "WORKORDERID_RINDEX", "FACILITYID_Text") # Process: Alter Field (8) arcpy.AlterField_management(WIC__5_, "FACILITYID_Text", "PARCEL_FACILITYID_FromWOXY", "", "", "255", "NON_NULLABLE", "false") # Process: Add Field (PARCEL_FACILITYID) arcpy.AddField_management(WIC__6_, "PARCEL_FACILITYID", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") # Process: Calculate Field (5) arcpy.CalculateField_management( WIC__7_, "PARCEL_FACILITYID", "SelectFACID(!PARCEL_FACILITYID_FromWO_FACID!, !PARCEL_FACILITYID_FromLOCATION!, !PARCEL_FACILITYID_FromWOXY!)", "PYTHON", "def SelectFACID(ParcelFID, LocationFID, WOXYFID):\\n if ParcelFID != None :\\n return ParcelFID\\n elif LocationFID != None :\\n return LocationFID\\n elif WOXYFID != None :\\n return WOXYFID\\n return None" ) # Process: [CREATE WIC PARCEL POLYGONS] Make Query Table arcpy.MakeQueryTable_management( "C:\\Users\\william.bezts\\Documents\\ArcGIS\\scratch.gdb\\WIC;C:\\Users\\william.bezts\\Documents\\ArcGIS\\scratch.gdb\\PWD_PARCELS", WIC_PARCELS_Layer, "ADD_VIRTUAL_KEY_FIELD", "", "WIC.WORKORDERID #;WIC.WO_INITIATEDATE #;WIC.WORKORDERID_RINDEX #;PWD_PARCELS.Shape #;PWD_PARCELS.PARCELID #;PWD_PARCELS.ADDRESS #;PWD_PARCELS.BLDG_DESC #;PWD_PARCELS.BILLING_CLASS #;PWD_PARCELS.CHECKEDBY #;PWD_PARCELS.CHECKEDDATE #;PWD_PARCELS.FACILITYID #;PWD_PARCELS.FACILITYID_Text #", "WIC.PARCEL_FACILITYID = PWD_PARCELS.FACILITYID_Text") # Process: Select [remove NULL NONE PARCEL_FACILITYID] arcpy.Select_analysis(WIC_PARCELS_Layer, WIC_PARCELS, "PWD_PARCELS_FACILITYID_Text IS NOT NULL")
def process_store_facts(args): logging.basicConfig(level=logging.DEBUG) _log.info('Process stores and facts started') #Set-up inputs store_fc = os.path.join(os.path.dirname(__file__), 'RBAMRGIS03.sde/RBGIS01.DBO.s_gis_store_pnt') #store_facts_month = os.path.join(os.path.dirname(__file__),'RBAMRGIS03.sde/RBGIS01.dbo.f_gis_month_store') store_facts_year = os.path.join( os.path.dirname(__file__), 'RBAMRGIS03.sde/RBGIS01.dbo.f_gis_year_store') store_deltas = os.path.join( os.path.dirname(__file__), 'RBAMRGIS03.sde/RBGIS01.dbo.d_gis_store_delta') locator = args[1] #Set-up outputs geocode_result = arcpy.env.scratchWorkspace + '/geocode_result' geocode_projected = arcpy.env.scratchWorkspace + '/stores_projected' deltas_view_new = 'deltas_new' deltas_table_new = arcpy.env.scratchWorkspace + '/select_new_result' deltas_view_removed = 'deltas_removed' deltas_table_removed = arcpy.env.scratchWorkspace + '/select_remove_result' fact_view = 'fact_view' fact_table = arcpy.env.scratchWorkspace + '/fact_table' store_feature_layer = 'store_feature_layer' store_fact_result = arcpy.env.scratchWorkspace + '/store_fact_result' #Select new deltas deltas_where_new = "type = 'New'" arcpy.MakeQueryTable_management(store_deltas, deltas_view_new, 'NO_KEY_FIELD', '', '', deltas_where_new) deltas_new_count = int( arcpy.GetCount_management(deltas_view_new).getOutput(0)) arcpy.CopyRows_management(deltas_view_new, deltas_table_new) _log.info('Created table view of store deltas where ' + deltas_where_new + ' with ' + str(deltas_new_count) + ' rows') #Geocode new deltas _log.info('Starting to geocode new store deltas') arcpy.GeocodeAddresses_geocoding( deltas_table_new, locator, "Address store_addr1 VISIBLE NONE;City store_city VISIBLE NONE;State state_code VISIBLE NONE;Zip zip VISIBLE NONE", geocode_result) _log.info('Finished geocoding new store deltas') try: #Begin append new deltas to master FC #Begin remove deltas from master store FC workspace = os.path.dirname(store_fc) #Set-up fields for use in search, update, and insert cursors store_fields = arcpy.ListFields(store_deltas) store_field_names = [] for store_field in store_fields: if store_field.name != 'sub_channel' and store_field.name != 'store_status' and store_field.name != 'OBJECTID': store_field_names.append(store_field.name) store_fields_string = '; '.join(store_field_names) #Set-up where clauses geocodes_where_matched = ''' "Status" = 'M' OR "Status" = 'T' ''' deltas_where_remove = "type = 'Removed'" #Begin insert and remove deltas with edit session with arcpy.da.Editor(workspace) as edit: #Set-up cursors for inserts insert_cursor = arcpy.InsertCursor(store_fc, '') search_cursor_geocodes = arcpy.SearchCursor( geocode_result, geocodes_where_matched, '', store_fields_string) _log.info( 'Begin to insert new store deltas into master store feature class' ) #Begin inserts for delta_row_new in search_cursor_geocodes: insert_cursor.insertRow(delta_row_new) _log.info('Inserted new store with store id ' + str(delta_row_new.store_id)) _log.info( 'Finished inserting new store deltas into master store feature class' ) #Clean-up insert-related cursors del delta_row_new del search_cursor_geocodes del insert_cursor #Make table view to include rows that should be removed arcpy.MakeQueryTable_management(store_deltas, deltas_view_removed, 'NO_KEY_FIELD', '', '', deltas_where_remove) deltas_remove_count = int( arcpy.GetCount_management(deltas_view_removed).getOutput(0)) arcpy.CopyRows_management(deltas_view_removed, deltas_table_removed) _log.info('Created table view of remove store deltas where ' + deltas_where_remove + ' with ' + str(deltas_remove_count) + ' rows') #Get IDs to delete search_cursor_remove = arcpy.SearchCursor(deltas_table_removed, '', '', u'store_id') remove_ids = [] for delta_row_remove in search_cursor_remove: remove_ids.append(delta_row_remove.store_id) #Clean-up cursor del delta_row_remove del search_cursor_remove _log.info( 'Begin to delete store deltas from master store feature class where' + deltas_where_remove) #Begin delete update_cursor = arcpy.da.UpdateCursor(store_fc, [u'store_id']) for row in update_cursor: if row[0] in remove_ids: update_cursor.deleteRow() _log.info('Deleted row with id ' + str(row[0])) #Clean-up delete-related cursor del row del update_cursor _log.info( 'Finished deleting store deltas from master store feature class' ) _log.info('Master store feature class now contains ' + str(arcpy.GetCount_management(store_fc))) #Project the geocodes results projectGeocodeResult(arcpy.env.scratchWorkspace, store_fc, geocode_projected, "WGS 1984 Web Mercator Auxiliary Sphere") except arcpy.ExecuteError as e: log_msg = 'Could not insert or remove all store deltas from master store feature class - rolling back changes' _log.error(log_msg) exception_report = Exception(log_msg + '<br>' + e.message) send_alert_email(exception_report)
';Shape' + \ ';Id' + \ ';RigScheduleName' + \ ';SubSiteName' + \ ';EntityId' + \ ';RigId' + \ ';StartDate' + \ ';EndDate' + \ ';ActivityType' + \ ';SegmentType' #Make Query Table #MakeQueryTable_management (in_table, out_table, in_key_field_option, {in_key_field}, {in_field}, {where_clause}) arcpy.MakeQueryTable_management( '{0};{1}'.format(in1, in2), RigSched, "USE_KEY_FIELDS", keyFields, inFields, 'pegistest.STAGE.EPEX_RigActivityPlans.EntityId = pegistest.GIS.INDUSTRY_WELLS.EPEX_ID AND pegistest.STAGE.EPEX_RigActivityPlans.EndDate >getdate()' ) log.write( str(datetime.datetime.today().strftime('%H:%M:%S')) + ' Make Query Table Management Complete to {0}.\n'.format( 'RigSched')) print str(datetime.datetime.today().strftime('%H:%M:%S') ) + ' Make Query Table Management Complete to {0}.\n'.format( 'RigSched') #TEST OUTPUT #FeatureClassToFeatureClass_conversion (in_features, out_path, out_name, {where_clause}, {field_mapping}, {config_keyword}) #arcpy.FeatureClassToFeatureClass_conversion ('Test1', fgdbPath, 'Test') #Point to Line
# them in the same geodatabase as the feature class. # 2. MakeQueryTable generates a temporary layer. Use CopyFeatures_management to export # the layer and save it as a permanent feature class. ### # Import libraries import arcpy,os from arcpy import env # Set workspace env.overwriteOutput = True env.workspace = r"PATH TO GEODATABASE" # List the polygon feature class and table that want to be joined tableList = [ r"PATH TO GEOMETRY FILE", \r"PATH TO TABLE"] # Define the query for matching whereClause = "GEOMETRYLAYERNAME.FIELD = TABLENAME.FIELD" # Name the temporary layer name created by MakeQueryTable lyrName = "TEMPORARY LAYER NAME" # Name the output fc name outFeatureClass = "PERMANENT LAYER NAME" arcpy.MakeQueryTable_management(tableList, lyrName,"USE_KEY_FIELDS", "", "", whereClause) # Since lyrName created by MakeQueryTable is temporary, save it as permanent gdb file arcpy.CopyFeatures_management(lyrName, outFeatureClass)
###print feature count ##print str(n) + ' Features' #Join Scats Features with Attributes startTask = datetime.datetime.now() print '\n2. Join Scats Features with Attributes' arcpy.MakeTableView_management(in_table=tbl, out_view="scatstbl") fields = arcpy.ListFields("scatstbl") for f in fields: print f.name tables = ["scatstbl", "scatslyr"] sql = "Scats_Attributes.Id = Scats.Id" arcpy.MakeQueryTable_management(in_table=tables, out_table="scatsFeatures", in_key_field_option="ADD_VIRTUAL_KEY_FIELD", where_clause=sql) endTask = datetime.datetime.now() print 'elapsed ' + str(endTask - startTask) #check and delete startTask = datetime.datetime.now() print '\n3. Copy Features to ScatsFeatures' if arcpy.Exists("Scats_Features"): arcpy.Delete_management(in_data="Scats_Features") arcpy.CopyFeatures_management(in_features="scatsFeatures", out_feature_class="Scats_Features") print arcpy.GetCount_management("scatsFeatures") endTask = datetime.datetime.now() print 'elapsed ' + str(endTask - startTask)
def generate_soil_report(order_obj): arcpy.AddMessage(' -- Start generating PSR soil report...') start = timeit.default_timer() ### set scratch folder arcpy.env.workspace = config.scratch_folder arcpy.env.overwriteOutput = True ### extract buffer size for soil report eris_id = 0 if '9334' not in order_obj.psr.search_radius.keys(): arcpy.AddMessage(' -- Soil search radius is not availabe') return config.buffer_dist_soil = str( order_obj.psr.search_radius['9334']) + ' MILES' arcpy.Buffer_analysis(config.order_geometry_pcs_shp, config.order_buffer_shp, config.buffer_dist_soil) if order_obj.province == 'HI': config.data_path_soil = config.data_path_soil_HI elif order_obj.province == 'AK': config.data_path_soil = config.data_path_soil_AK else: config.data_path_soil = config.data_path_soil_CONUS data_soil = os.path.join(config.data_path_soil, 'MUPOLYGON') # select soil data by using spatial query of order buffere layer config.soil_lyr = arcpy.MakeFeatureLayer_management(data_soil, 'soil_lyr') arcpy.SelectLayerByLocation_management(config.soil_lyr, 'intersect', config.order_buffer_shp) arcpy.CopyFeatures_management(config.soil_lyr, config.soil_selectedby_order_shp) table_muaggatt = os.path.join(config.data_path_soil, 'muaggatt') table_component = os.path.join(config.data_path_soil, 'component') table_chorizon = os.path.join(config.data_path_soil, 'chorizon') table_chtexturegrp = os.path.join(config.data_path_soil, 'chtexturegrp') stable_muaggatt = os.path.join(config.temp_gdb, "muaggatt") stable_component = os.path.join(config.temp_gdb, "component") stable_chorizon = os.path.join(config.temp_gdb, "chorizon") stable_chtexture_grp = os.path.join(config.temp_gdb, "chtexturegrp") data_array = [] if (int(arcpy.GetCount_management('soil_lyr').getOutput(0)) == 0 ): # no soil polygons selected arcpy.AddMessage('no soil data in order geometry buffer') psr_obj = models.PCR() eris_id = eris_id + 1 psr_obj.insert_flex_rep(order_obj.id, eris_id, '9334', 2, 'N', 1, 'No soil data available in the project area.', '') else: soil_selectedby_order_pcs_shp = arcpy.Project_management( config.soil_selectedby_order_shp, config.soil_selectedby_order_pcs_shp, order_obj.spatial_ref_pcs) # create map keys arcpy.Statistics_analysis( soil_selectedby_order_pcs_shp, os.path.join(config.scratch_folder, "summary_soil.dbf"), [['mukey', 'FIRST'], ["Shape_Area", "SUM"]], 'musym') arcpy.Sort_management( os.path.join(config.scratch_folder, "summary_soil.dbf"), os.path.join(config.scratch_folder, "summary_sorted_soil.dbf"), [["musym", "ASCENDING"]]) seq_array = arcpy.da.TableToNumPyArray( os.path.join(config.scratch_folder, 'summary_sorted_soil.dbf'), '*') #note: it could contain 'NOTCOM' record # retrieve attributes unique_MuKeys = utility.return_unique_setstring_musym( soil_selectedby_order_pcs_shp) if len( unique_MuKeys ) > 0: # special case: order only returns one "NOTCOM" category, filter out where_clause_select_table = "muaggatt.mukey in " + unique_MuKeys arcpy.TableSelect_analysis(table_muaggatt, stable_muaggatt, where_clause_select_table) where_clause_select_table = "component.mukey in " + unique_MuKeys arcpy.TableSelect_analysis(table_component, stable_component, where_clause_select_table) unique_co_keys = utility.return_unique_set_string( stable_component, 'cokey') where_clause_select_table = "chorizon.cokey in " + unique_co_keys arcpy.TableSelect_analysis(table_chorizon, stable_chorizon, where_clause_select_table) unique_achkeys = utility.return_unique_set_string( stable_chorizon, 'chkey') if len( unique_achkeys ) > 0: # special case: e.g. there is only one Urban Land polygon where_clause_select_table = "chorizon.chkey in " + unique_achkeys arcpy.TableSelect_analysis(table_chtexturegrp, stable_chtexture_grp, where_clause_select_table) table_list = [ stable_muaggatt, stable_component, stable_chorizon, stable_chtexture_grp ] field_list = config.fc_soils_field_list #[['muaggatt.mukey','mukey'], ['muaggatt.musym','musym'], ['muaggatt.muname','muname'],['muaggatt.drclassdcd','drclassdcd'],['muaggatt.hydgrpdcd','hydgrpdcd'],['muaggatt.hydclprs','hydclprs'], ['muaggatt.brockdepmin','brockdepmin'], ['muaggatt.wtdepannmin','wtdepannmin'], ['component.cokey','cokey'],['component.compname','compname'], ['component.comppct_r','comppct_r'], ['component.majcompflag','majcompflag'],['chorizon.chkey','chkey'],['chorizon.hzname','hzname'],['chorizon.hzdept_r','hzdept_r'],['chorizon.hzdepb_r','hzdepb_r'], ['chtexturegrp.chtgkey','chtgkey'], ['chtexturegrp.texdesc1','texdesc'], ['chtexturegrp.rvindicator','rv']] key_list = config.fc_soils_key_list #['muaggatt.mukey', 'component.cokey','chorizon.chkey','chtexturegrp.chtgkey'] where_clause_query_table = config.fc_soils_where_clause_query_table #"muaggatt.mukey = component.mukey and component.cokey = chorizon.cokey and chorizon.chkey = chtexturegrp.chkey" #Query tables may only be created using data from a geodatabase or an OLE DB connection query_table = arcpy.MakeQueryTable_management( table_list, 'query_table', 'USE_KEY_FIELDS', key_list, field_list, where_clause_query_table ) #note: outTable is a table view and won't persist arcpy.TableToTable_conversion( query_table, config.temp_gdb, 'soil_table' ) #note: 1. <null> values will be retained using .gdb, will be converted to 0 using .dbf; 2. domain values, if there are any, will be retained by using .gdb data_array = arcpy.da.TableToNumPyArray(os.path.join( config.temp_gdb, 'soil_table'), '*', null_value=-99) for i in range(0, len(seq_array)): map_unit_data = {} mukey = seq_array['FIRST_MUKE'][ i] #note the column name in the .dbf output was cut off # arcpy.AddMessage(' - multiple pages map unit ' + str(i)) # arcpy.AddMessage(' - musym is ' + str(seq_array['MUSYM'][i])) # arcpy.AddMessage(' - mukey is ' + str(mukey)) map_unit_data['Seq'] = str( i + 1) # note i starts from 0, but we want labels to start from 1 if (seq_array['MUSYM'][i].upper() == 'NOTCOM'): map_unit_data['Map Unit Name'] = 'No Digital Data Available' map_unit_data['Mukey'] = mukey map_unit_data['Musym'] = 'NOTCOM' else: if 'data_array' not in locals( ): #there is only one special polygon(urban land or water) cursor = arcpy.SearchCursor(stable_muaggatt, "mukey = '" + str(mukey) + "'") row = cursor.next() map_unit_data['Map Unit Name'] = row.muname # arcpy.AddMessage(' - map unit name: ' + row.muname) map_unit_data['Mukey'] = mukey #note map_unit_data['Musym'] = row.musym row = None cursor = None elif ((utility.return_map_unit_attribute( data_array, mukey, 'muname')).upper() == '?'): #Water or Unrban Land cursor = arcpy.SearchCursor(stable_muaggatt, "mukey = '" + str(mukey) + "'") row = cursor.next() map_unit_data['Map Unit Name'] = row.muname # arcpy.AddMessage(' - map unit name: ' + row.muname) map_unit_data['Mukey'] = mukey #note map_unit_data['Musym'] = row.musym row = None cursor = None else: map_unit_data['Mukey'] = utility.return_map_unit_attribute( data_array, mukey, 'mukey') map_unit_data['Musym'] = utility.return_map_unit_attribute( data_array, mukey, 'musym') map_unit_data[ 'Map Unit Name'] = utility.return_map_unit_attribute( data_array, mukey, 'muname') map_unit_data[ 'Drainage Class - Dominant'] = utility.return_map_unit_attribute( data_array, mukey, 'drclassdcd') map_unit_data[ 'Hydrologic Group - Dominant'] = utility.return_map_unit_attribute( data_array, mukey, 'hydgrpdcd') map_unit_data[ 'Hydric Classification - Presence'] = utility.return_map_unit_attribute( data_array, mukey, 'hydclprs') map_unit_data[ 'Bedrock Depth - Min'] = utility.return_map_unit_attribute( data_array, mukey, 'brockdepmin') map_unit_data[ 'Watertable Depth - Annual Min'] = utility.return_map_unit_attribute( data_array, mukey, 'wtdepannmin') component_data = utility.return_componen_attribute_rv_indicator_Y( data_array, mukey) map_unit_data['component'] = component_data map_unit_data["Soil_Percent"] = "%s" % round( seq_array['SUM_Shape_'][i] / sum(seq_array['SUM_Shape_']) * 100, 2) + r'%' config.report_data.append(map_unit_data) # create the map create_map(order_obj) end = timeit.default_timer() arcpy.AddMessage((' -- End generating PSR soil report. Duration:', round(end - start, 4)))
) # Process: Delete Field (5) arcpy.DeleteField_management(RockTicketsSR, "Date_Year") arcpy.AddMessage( "deleted Date_Year field from RockTicketsSR feature class to keep same schema for copying updated data" ) # Process: Convert Time Field arcpy.ConvertTimeField_management(RockTicketsSR, "DATE_R", "'Not Used'", "Date_Year", "TEXT", "yyyy;1033;;") arcpy.AddMessage("created Date_Year field") # Process: Make Query Table (3) arcpy.MakeQueryTable_management(RockTicketsSR, QueryTable1, "USE_KEY_FIELDS", "RockTicketsSR.PROJ_Num", "", "Date_Year =" + "\'" + str(Year_Edit) + "\'") arcpy.AddMessage( "Query out only the year you are calculating and dump the data into QueryTable1" ) # Process: Copy Rows (3) arcpy.CopyRows_management(QueryTable1, QueryTable_CopyRows, "") arcpy.AddMessage( "copy rows from QueryTable1 into QueryTable_copyRows of the year we are calculating" ) # Process: Create Relationship Class arcpy.CreateRelationshipClass_management( Rock_Shoulder_Right, QueryTable_CopyRows, Rock_Shoulder_Right_QueryTable_CopyRows, "SIMPLE",
def mainFunction(propertyTitlesFeatureClass,memorialsTable,suburbsFeatureClass,mortgageFeatureClass,mortgageSuburbsFeatureClass): # Get parameters from ArcGIS Desktop tool by seperating by comma e.g. (var1 is 1st parameter,var2 is 2nd parameter,var3 is 3rd parameter) try: # --------------------------------------- Start of code --------------------------------------- # # Set the banks banks = ["Auckland Savings Bank","Australia and New Zealand Banking Group","Australian Mutual Provident Society","Bank of New Zealand","Heartland Bank","Kiwibank","New Zealand Home Loans","PGG Wrightson","Rabobank","Southland Building Society","Sovereign","Taranaki Savings Bank","The Co-Operative Bank","Wairarapa Building Society","Welcome Home Loan","Westpac","Other"] # Copy property titles and select out current mortgage data arcpy.AddMessage("Extracting mortgage data...") arcpy.TableSelect_analysis(memorialsTable, os.path.join(arcpy.env.scratchGDB, "Mortgage"), "instrument_type = 'Mortgage' AND current = 'T'") arcpy.Select_analysis(propertyTitlesFeatureClass, os.path.join(arcpy.env.scratchGDB, "PropertyTitles"), "") arcpy.AddMessage("Creating mortgage feature class...") # Join property titles and mortgage data arcpy.MakeQueryTable_management(os.path.join(arcpy.env.scratchGDB, "PropertyTitles") + ";" + os.path.join(arcpy.env.scratchGDB, "Mortgage"), "PropertyTitlesMortgageLayer", "USE_KEY_FIELDS", "", "", "PropertyTitles.title_no = Mortgage.title_no") arcpy.Select_analysis("PropertyTitlesMortgageLayer", mortgageFeatureClass, "") # Cleaning up fields arcpy.AddMessage("Cleaning up fields...") arcpy.AddField_management(mortgageFeatureClass, "mortgage_provider", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") # Calculating mortgage provider field from memorial text arcpy.CalculateField_management(mortgageFeatureClass, "mortgage_provider", "changeValue(!memorial_text!)", "PYTHON_9.3", "def changeValue(var):\\n if \"ANZ\" in var:\\n return \"Australia and New Zealand Banking Group\"\\n if \"National Bank of New Zealand\" in var:\\n return \"Australia and New Zealand Banking Group\"\\n if \"Post Office Bank\" in var:\\n return \"Australia and New Zealand Banking Group\"\\n if \"Westpac\" in var:\\n return \"Westpac\"\\n if \"Home Mortgage Company\" in var:\\n return \"Westpac\"\\n if \"Trust Bank New Zealand\" in var:\\n return \"Westpac\"\\n if \"ASB\" in var:\\n return \"Auckland Savings Bank\"\\n if \"Bank of New Zealand\" in var:\\n return \"Bank of New Zealand\"\\n if \"Kiwibank\" in var:\\n return \"Kiwibank\"\\n if \"TSB\" in var:\\n return \"Taranaki Savings Bank\"\\n if \"Southland Building Society\" in var:\\n return \"Southland Building Society\"\\n if \"AMP\" in var:\\n return \"Australian Mutual Provident Society\"\\n if \"Rabobank\" in var:\\n return \"Rabobank\"\\n if \"Rabo Wrightson\" in var:\\n return \"Rabobank\"\\n if \"Countrywide\" in var:\\n return \"Australia and New Zealand Banking Group\"\\n if \"Mortgage Holding Trust\" in var:\\n return \"Sovereign\"\\n if \"Co-operative Bank\" in var:\\n return \"The Co-Operative Bank\"\\n if \"Co-Operative Bank\" in var:\\n return \"The Co-Operative Bank\"\\n if \"PSIS\" in var:\\n return \"The Co-Operative Bank\"\\n if \"New Zealand Home Lending\" in var:\\n return \"New Zealand Home Loans\"\\n if \"Wairarapa Building Society\" in var:\\n return \"Wairarapa Building Society\"\\n if \"PGG Wrightson\" in var:\\n return \"PGG Wrightson\"\\n if \"Heartland Bank\" in var:\\n return \"Heartland Bank\"\\n if \"Heartland Building Society\" in var:\\n return \"Heartland Bank\"\\n if \"Housing New Zealand\" in var:\\n return \"Welcome Home Loan\"\\n if \"Housing Corporation of New Zealand\" in var:\\n return \"Welcome Home Loan\"\\n else:\\n return \"Other\"") arcpy.DeleteField_management(mortgageFeatureClass, "id;spatial_extents_shared;OBJECTID_1;id_1;title_no_1;land_district_1") arcpy.AddField_management(mortgageFeatureClass, "land_area", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") arcpy.CalculateField_management(mortgageFeatureClass, "land_area", "!SHAPE_Area!", "PYTHON_9.3", "") # Copy suburbs data arcpy.Select_analysis(suburbsFeatureClass, os.path.join(arcpy.env.scratchGDB, "Suburb"), "") # Spatially join suburb info arcpy.AddMessage("Analysing mortgages by suburb...") arcpy.SpatialJoin_analysis(mortgageFeatureClass, os.path.join(arcpy.env.scratchGDB, "Suburb"), os.path.join(arcpy.env.scratchGDB, "MortgageSuburbs"), "JOIN_ONE_TO_ONE", "KEEP_ALL", "", "INTERSECT", "", "") # Summary stats for suburbs arcpy.Statistics_analysis(os.path.join(arcpy.env.scratchGDB, "MortgageSuburbs"), os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStats"), "mortgage_provider COUNT", "SUBURB_4THORDER;mortgage_provider") # Add the banks count fields for bank in banks: arcpy.AddField_management(os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStats"), bank.replace(" ", "_").replace("-", "_"), "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") arcpy.CalculateField_management(os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStats"), bank.replace(" ", "_").replace("-", "_"), "0", "PYTHON_9.3", "") # Get the banks fields for count,value in enumerate(banks): banks[count] = value.replace(" ", "_").replace("-", "_").replace("(", "_").replace(")", "_") fields = ["SUBURB_4THORDER","mortgage_provider","FREQUENCY"] + banks with arcpy.da.UpdateCursor(os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStats"), fields) as cursor: # For each row for row in cursor: suburb = row[0] mortgageProvider = row[1] mortgageProvider = mortgageProvider.replace(" ", "_").replace("-", "_").replace("(", "_").replace(")", "_") count = row[2] # Update the mortgage provider row with its count row[fields.index(mortgageProvider)] = count cursor.updateRow(row) # Dissolve the stats arcpy.Statistics_analysis(os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStats"), os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStatsDissolved"), "FREQUENCY SUM;Auckland_Savings_Bank SUM;Australia_and_New_Zealand_Banking_Group SUM;Australian_Mutual_Provident_Society SUM;Bank_of_New_Zealand SUM;Heartland_Bank SUM;Kiwibank SUM;New_Zealand_Home_Loans SUM;PGG_Wrightson SUM;Rabobank SUM;Southland_Building_Society SUM;Sovereign SUM;Taranaki_Savings_Bank SUM;The_Co_Operative_Bank SUM;Wairarapa_Building_Society SUM;Welcome_Home_Loan SUM;Westpac SUM;Other SUM;", "SUBURB_4THORDER") # Create mortgage suburbs feature class arcpy.AddMessage("Creating mortgage suburbs feature class...") arcpy.Select_analysis(suburbsFeatureClass, mortgageSuburbsFeatureClass, "") arcpy.AddField_management(mortgageSuburbsFeatureClass, "land_area", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "") arcpy.CalculateField_management(mortgageSuburbsFeatureClass, "land_area", "!SHAPE_Area!", "PYTHON_9.3", "") # Join on mortgages suburb data arcpy.JoinField_management(mortgageSuburbsFeatureClass, "SUBURB_4THORDER", os.path.join(arcpy.env.scratchGDB, "MortgageSuburbsStatsDissolved"), "SUBURB_4THORDER", "") arcpy.DeleteField_management(mortgageSuburbsFeatureClass, "FREQUENCY;SUBURB_1STORDER;SUBURB_2NDORDER;SUBURB_3RDORDER") arcpy.AlterField_management(mortgageSuburbsFeatureClass, "SUBURB_4THORDER", "Suburb", "", "TEXT", "60", "NULLABLE", "false") arcpy.AlterField_management(mortgageSuburbsFeatureClass, "SUM_FREQUENCY", "SUM_ALL", "", "DOUBLE", "8", "NULLABLE", "false") # --------------------------------------- End of code --------------------------------------- # # If called from gp tool return the arcpy parameter if __name__ == '__main__': # Return the output if there is any if output: arcpy.SetParameterAsText(1, output) # Otherwise return the result else: # Return the output if there is any if output: return output # Logging if (enableLogging == "true"): # Log end of process logger.info("Process ended.") # Remove file handler and close log file logging.FileHandler.close(logMessage) logger.removeHandler(logMessage) # If arcpy error except arcpy.ExecuteError: # Build and show the error message errorMessage = arcpy.GetMessages(2) arcpy.AddError(errorMessage) # Logging if (enableLogging == "true"): # Log error logger.error(errorMessage) # Log end of process logger.info("Process ended.") # Remove file handler and close log file logging.FileHandler.close(logMessage) logger.removeHandler(logMessage) if (sendErrorEmail == "true"): # Send email sendEmail(errorMessage) # If python error except Exception as e: errorMessage = "" # Build and show the error message for i in range(len(e.args)): if (i == 0): # Python version check if sys.version_info[0] >= 3: # Python 3.x errorMessage = str(e.args[i]).encode('utf-8').decode('utf-8') else: # Python 2.x errorMessage = unicode(e.args[i]).encode('utf-8') else: # Python version check if sys.version_info[0] >= 3: # Python 3.x errorMessage = errorMessage + " " + str(e.args[i]).encode('utf-8').decode('utf-8') else: # Python 2.x errorMessage = errorMessage + " " + unicode(e.args[i]).encode('utf-8') arcpy.AddError(errorMessage) # Logging if (enableLogging == "true"): # Log error logger.error(errorMessage) # Log end of process logger.info("Process ended.") # Remove file handler and close log file logging.FileHandler.close(logMessage) logger.removeHandler(logMessage) if (sendErrorEmail == "true"): # Send email sendEmail(errorMessage)
import arcpy, os, datetime from arcpy import env tempData = os.path.join(os.getcwd(), "OARS Temp") env.workspace = os.path.join(tempData, "OARS_Temp.gdb") env.overwriteOutput = False year = "2018" # <-------------------------------------------------------------------------------- (1) Change Year # Bring Shapefile into Geodatabase shapefile = "Treatments_{0}_merge.shp".format(year) featureClass = "Treatments_{0}_merge".format(year) arcpy.CopyFeatures_management(os.path.join(tempData, shapefile), featureClass) arcpy.Rename_management(featureClass, "Treatments") fc = "Treatments" # "Shape_Length" and "Shape_Area" are required fields, but unfortunately are not in the order that I need to match OARS template (cannot delete these and simply add them again); Work-around below. arcpy.MakeQueryTable_management(fc, "QueryTable", "USE_KEY_FIELDS", "", "Treatments.OBJECTID; Treatments.Shape; Treatments.Orig_Name; Treatments.ProjectNam; Treatments.GrantTitle; Treatments.FundNumber; Treatments.Coop_ID; Treatments.WkplanNum; Treatments.District; Treatments.Landowner; Treatments.AgencyInv; Treatments.CWPP; Treatments.CARS; Treatments.ForestType; Treatments.Treatment; Treatments.AccompDate; Treatments.Input_Date; Treatments.FY; Treatments.Acres") arcpy.CopyFeatures_management("QueryTable", "Treatments_Edited") arcpy.Delete_management("QueryTable") arcpy.Delete_management("Treatments") # This step & the next allow me to keep original "Treatments" name arcpy.Rename_management("Treatments_Edited", "Treatments") # Another cleanup - MakeQueryTable function corrects the order of "Shape_Length" & "Shape_Area", but unfortunately changes the FC's field names & aliases. arcpy.AlterField_management(fc, "Treatments_Orig_Name", "Orig_Name", "Original Shapefile Name") arcpy.AlterField_management(fc, "Treatments_ProjectNam", "ProjectNam", "Project Name") arcpy.AlterField_management(fc, "Treatments_GrantTitle", "GrantTitle", "Grant Title") arcpy.AlterField_management(fc, "Treatments_FundNumber", "FundNumber", "Fund Number") arcpy.AlterField_management(fc, "Treatments_Coop_ID", "Coop_ID", "Cooperator ID") arcpy.AlterField_management(fc, "Treatments_WkplanNum", "WkplanNum", "Workplan Number") arcpy.AlterField_management(fc, "Treatments_District", "District", "District") arcpy.AlterField_management(fc, "Treatments_Landowner", "Landowner", "Landowner") arcpy.AlterField_management(fc, "Treatments_AgencyInv", "AgencyInv", "Agencies Involved")
ListVarSummer = ['Qcsf','Qwwf','tsc','trc'] ListVarwinter = ['Qhsf','Qwwf','tsh','trh'] for N in Season: if N == Namesummer: List = ListVarSummer var2 = '_SU' else: List = ListVarwinter var2 = '_WIN' for var in List: fieldList = [[N+'.NAME','NAME'],[N+'.TIME','TIME'],[N+'.'+var,var], [CQ_name+'.Name','Name'],[CQ_name+'.Shape','Shape']] whereClause = CQ_name+'.Name = '+N+'.NAME' tableList = Database+'\\'+N layerCQ = CQ_name+'.lyr' arcpy.MakeFeatureLayer_management(Database+'\\'+CQ_name,layerCQ) # make query of table arcpy.MakeQueryTable_management([tableList,layerCQ],"QueryTable","USE_KEY_FIELDS","#",fieldList,whereClause) Layer = "QueryTable" # set temporal directories if not os.path.exists(Animation+'\\'+CQ_name): os.makedirs(Animation+'\\'+CQ_name) arcpy.FeatureClassToFeatureClass_conversion(Layer,Animation+'\\'+CQ_name,var+var2) # <rawcell> # The rest is done in ArcGIS manually. Visualization!
def crs6_add_data_preparation(args): # parameters wkgFolder = args[0] labelGDBname = args[1] # Set locations, etc labelGDBpath = os.path.join(wkgFolder, labelGDBname) fcEPrclPath = os.path.join(labelGDBpath, fcEsmntPrcl) fcELeasePath = os.path.join(labelGDBpath, fcEsmntLease) fcLPrclPath = os.path.join(labelGDBpath, fcLeasePrcl) fcRCLPath = os.path.join(labelGDBpath, fcRoadCL) fcRCdsslvPath = os.path.join(labelGDBpath, fcRCLdsslv) fcCadP = fcCadastre + "_P" fcCadPPath = os.path.join(labelGDBpath, fcCadP) tblPLnkPath = os.path.join(labelGDBpath, tblPropLink) fcPrclPLPath = os.path.join(labelGDBpath, fcPrclPLink) tblPropPath = os.path.join(labelGDBpath, tblProperty) fcCnnctPPath = os.path.join(labelGDBpath, fcConnectProp) fcDsslvIDPath = os.path.join(labelGDBpath, dsslvIDFC) # Set environment arcpy.env.workspace = labelGDBpath arcpy.env.overwriteOutput = True arcpy.env.configkeyword = "GEOMETRY" # log function log_msg('calling {}'.format(script_name)) # variables err_message = None try: ### Easement lease # Copy easement parcel log_msg('Copying easement parcel data in labels gdb ...') arcpy.Copy_management(fcEPrclPath, fcELeasePath) # Select records to append log_msg('Selecting lease parcels to append...') delete_layer("leaseplyr") arcpy.MakeFeatureLayer_management(fcLPrclPath, "leaseplyr") parcelClause = '"PARCEL_INTENT" = ' + "'LCOV'" + ' OR "PARCEL_INTENT" = ' + "'EASM'" arcpy.SelectLayerByAttribute_management("leaseplyr", "NEW_SELECTION", parcelClause) log_msg('Appending lease parcels...') arcpy.Append_management("leaseplyr", fcELeasePath, "NO_TEST") ### Road CL log_msg('Working on road data...') if field_exist(fcRoadCL, rfield) == False: # Add field arcpy.AddField_management(fcRoadCL, rfield, "TEXT", "", "", rfieldlen) # Calculate values log_msg('Calculate values: {} ...'.format(rfield)) calcexpr = ('!{}!.upper() + ", " + !{}!.upper()').format( statsfields[0][0], statsfields[1][0]) arcpy.CalculateField_management(fcRoadCL, rfield, calcexpr, "PYTHON_9.3") # Dissolve data, using statistics fields log_msg('Dissolving ...') arcpy.Dissolve_management(fcRCLPath, fcRCdsslvPath, rfield, statsfields) # Add fields arcpy.AddField_management(fcRCLdsslv, statsfields[0][0], "TEXT", "", "", sfieldlen) arcpy.AddField_management(fcRCLdsslv, statsfields[1][0], "TEXT", "", "", sfieldlen) # Calculate values sfields = [] for i in range(len(statsfields)): sfields.append(statsfields[i][0]) arcpy.AddField_management(fcRCLdsslv, statsfields[i][0], "TEXT", "", "", sfieldlen) sfield = statsfields[i][1] + "_" + statsfields[i][0] calcexpr = ('!{}!').format(sfield) arcpy.CalculateField_management(fcRCLdsslv, statsfields[i][0], calcexpr, "PYTHON_9.3") ### Connect_Property log_msg('Working on Connect_Property') # Make query table mqtblList = [fcCadPPath, tblPLnkPath] whereClause = tblPropLink + "." + parIDfield + " = " + fcCadP + "." + parIDfield # NOTE: no quotes - known bug arcpy.MakeQueryTable_management(mqtblList, "propQueryTbl", "ADD_VIRTUAL_KEY_FIELD", "", "", whereClause) # Get number of rows numMQrows = int(arcpy.GetCount_management("propQueryTbl").getOutput(0)) # Export log_msg('Exporting...') arcpy.CopyFeatures_management("propQueryTbl", fcPrclPLPath) # Check number of rows numPPLrows = int(arcpy.GetCount_management(fcPrclPLPath).getOutput(0)) if numPPLrows != numMQrows: log_msg( 'ERROR: Wrong number of rows exported for link FC; {} versus {}' .format(numMQrows, numPPLrows)) else: log_msg('Correct number of rows exported for link FC.') # Dissolve on ID log_msg('Dissolving on ID...') dfield = tblPropLink + "_" + propIDfield sfield = tblPropLink + "_" + parIDfield statsfield = [[sfield, "COUNT"]] arcpy.Dissolve_management(fcPrclPLink, fcDsslvIDPath, dfield, statsfield) # Join the TP_Property table log_msg('Preparing to join property table...') # Create temporary layer/view delete_layer('dsslvlyr') arcpy.MakeFeatureLayer_management(fcDsslvIDPath, "dsslvlyr") delete_layer('proptblview') arcpy.MakeTableView_management(tblPropPath, "proptblview") # Make join log_msg('Adding join ...') arcpy.AddJoin_management("dsslvlyr", dfield, "proptblview", propIDfield, "KEEP_ALL") log_msg('Property table joined') # Output log_msg('Copying features...') arcpy.CopyFeatures_management("dsslvlyr", fcCnnctPPath) log_msg("Process time: %s \n" % str(datetime.datetime.now() - starttime)) except Exception as e: err_message = "ERROR while running {0}: {1}".format(script_name, e) return err_message, log_messages
else: return counts[0] # Expand looping for crop in uniqueCrops: crop = int(crop) crop = str(crop) # check for existing geodatabase if not arcpy.Exists("{}CDL{}x_cnty.gdb".format(outws, crop)): print "Creating CDL{}x_cnty Geodatabase".format(crop) arcpy.CreateFileGDB_management(outws, "CDL{}x_cnty.gdb".format(crop)) # Return a unique set of values in the FIPS field for counties with higher NASS acreage than the CDL gtacres = "gtlayer" arcpy.MakeQueryTable_management(acres, gtacres, "USE_KEY_FIELDS", "OBJECTID", "", "NASS_{0} > CDL_{0}".format( crop)) uniqueValues = {(row[0], row[1]) for row in arcpy.da.SearchCursor(gtacres, (geoid, "NASS_{}".format(crop)))} # Loop through counties needing expansion for fips, nassacres in uniqueValues: arcpy.env.workspace = "{}CDL{}x_cnty.gdb/".format(outws, crop) arcpy.env.scratchWorkspace = "{}CDL{}x_cnty.gdb/".format(outws, crop) fips = str(fips) croptime1 = datetime.datetime.now() print "expanding CDL{}_{}".format(crop, fips) # Set mask from script 5 mask = Raster("{}Cult_2017_{}".format(maskws, fips)) infile = Raster("{0}CDL_{1}_cnty.gdb/CDL_{1}_{2}".format(inws, crop, fips)) # Expand flagged county by 1 pixel outfile = Expand(infile, 1, 1) # Using the environmental setting for mask does not work here. Use mask in map algebra instead.
inputDB = arcpy.GetParameterAsText( 0) # Input database. Assuming SSURGO or gSSURGO soils database outputTbl = arcpy.GetParameterAsText( 1) # Output table containing surface texture for the dominant component try: # Get surface texture for all components PrintMsg(" \nGetting surface texture for each component...", 0) env.workspace = inputDB qTbl = "QueryTexture" tbls = ["chtexture", "chtexturegrp", "chorizon"] qFlds = "chtexture.texcl texcl;chtexture.lieutex lieutex;chtexturegrp.texture texture;chtexturegrp.texdesc texdesc;chorizon.hzname hzname;chorizon.desgnmaster desgnmaster;chorizon.hzdept_r hzdept_r;chorizon.hzdepb_r hzdepb_r;chorizon.cokey cokey" query = "chtexture.chtgkey = chtexturegrp.chtgkey AND chtexturegrp.chkey = chorizon.chkey AND chorizon.hzdept_r = 0 AND chtexturegrp.rvindicator = 'Yes'" arcpy.MakeQueryTable_management(tbls, qTbl, "ADD_VIRTUAL_KEY_FIELD", "#", qFlds, query) # OBJECTID # chtexture_texcl # chtexture_lieutex # chtexturegrp_texture # chtexturegrp_texdesc # chorizon_hzname # chorizon_desgnmaster # chorizon_hzdept_r # chorizon_hzdepb_r # chorizon_cokey dTexture = dict() tFlds = ("chorizon_cokey", "chtexture_texcl", "chtexture_lieutex", "chtexturegrp_texture")
lyrselection = arcpy.SelectLayerByLocation_management( in_layer="templyr", overlap_type="WITHIN_A_DISTANCE", select_features=selfeat, search_distance="500 Feet", selection_type="NEW_SELECTION", invert_spatial_relationship="NOT_INVERT") arcpy.CopyFeatures_management(lyrselection, outfeat) selbyloc(WorkingGDB + "\\BCParcels", WorkingGDB + "\\" + "Parcels_selection") # GENERATE QUERY TABLE (AS LAYER - THEREFORE MUST BE COPIED) QueryTable123 = arcpy.MakeQueryTable_management( ["Account_Parcels", "Parcels_selection"], "QueryTable123", "USE_KEY_FIELDS", "", in_field= "Parcels_selection.Shape #;Account_Parcels.strap #;Account_Parcels.PARCEL_NO #", where_clause="Account_Parcels.PARCEL_NO = Parcels_selection.PARCEL_NO") # END PRODUCT QueryTable_Final = WorkingGDB + "\\" + "QueryTable_Final" # Process: Copy Features if arcpy.Exists("QueryTable_Final"): arcpy.Delete_management("QueryTable_Final") arcpy.CopyFeatures_management(QueryTable123, QueryTable_Final, "", "0", "0", "0") # Process: Join Field (Buildings) arcpy.JoinField_management(QueryTable_Final, "Account_Parcels_strap", "Buildings", "strap") # Process: Join Field (Owner_Address)
def exportToCSV(field_alias, field_names, table, outFile, outDir): #calculate date and local timestamp firstDayofM = date(date.today().year, date.today().month, 1).strftime('%Y%m%d') firstDayofLM = date(date.today().year, date.today().month - 1, 1).strftime('%Y%m%d') thisMonth = datetime.datetime(int(firstDayofM[:4]), int(firstDayofM[4:6]), int(firstDayofM[6:]), 00, 00, 00) + datetime.timedelta(hours=4) lastMonth = datetime.datetime(int(firstDayofLM[:4]), int( firstDayofLM[4:6]), int(firstDayofLM[6:]), 00, 00, 00) + datetime.timedelta(hours=4) print thisMonth print lastMonth #build query clause whereClause = '"CREATED_DATE" < timestamp \'{0}\' AND "CREATED_DATE" >= timestamp \'{1}\' ORDER BY CREATED_DATE'.format( str(thisMonth), str(lastMonth)) print whereClause arcpy.MakeQueryTable_management(table, 'queryTable' + table, "NO_KEY_FIELD", "", "", whereClause) recordNum = arcpy.GetCount_management('queryTable' + table) logMessage( str(recordNum) + " " + table + " reports for " + (lastMonth).strftime("%b, %Y")) #list fields fields = arcpy.ListFields('queryTable' + table) #write to csv outFullFile = outFile + "_" + lastMonth.strftime("%Y%m") + ".csv" with open(os.path.join(outDir, outFullFile), 'wb') as f: w = csv.writer(f) w.writerow(field_alias) rows = arcpy.SearchCursor("queryTable" + table) row = rows.next() for row in rows: field_vals = [] for field in fields: if field.name in field_names: #remove none and invalid value and convert utc time to local if row.getValue(field.name) == None: field_val = "" elif field.type == "Date": if row.getValue(field.name).year == 1899: field_val = "" else: field_val = (row.getValue(field.name) - datetime.timedelta(hours=4) ).strftime("%Y-%m-%d %H:%M:%S") # if field.name == "REPORT_DATE": # field_val = (row.getValue(field.name) - datetime.timedelta(hours=4)).strftime("%Y-%m-%d") else: field_val = row.getValue(field.name) field_vals.append(field_val) w.writerow([ s.encode('utf-8') if type(s) is unicode else s for s in field_vals ]) del row del rows f.close() return recordNum, outFullFile
def importar_tablas_corregidas(data,campos): arcpy.env.overwriteOutput = True if arcpy.Exists("CPV_SEGMENTACION2.sde") == False: arcpy.CreateDatabaseConnection_management("Database Connections", "CPV_SEGMENTACION2.sde", "SQL_SERVER", ip_server, "DATABASE_AUTH", "us_arcgis_seg_2", "MBs0p0rt301", "#", "CPV_SEGMENTACION", "#", "#", "#", "#") arcpy.env.workspace = "Database Connections/CPV_SEGMENTACION2.sde" path_conexion="Database Connections/CPV_SEGMENTACION2.sde" where_expression=expresion.Expresion(data, campos) temp_ubigeos = "" i=0 for x in data: i=i+1 if (i==1): temp_ubigeos="'{}'".format(x[0]) else: temp_ubigeos = "{},'{}'".format(temp_ubigeos,x[0]) print temp_ubigeos sql=expresion.Expresion(data, campos) manzanas_Layer = arcpy.MakeQueryLayer_management(path_conexion, 'TB_MZS',"SELECT geom,UBIGEO,CODCCPP,ZONA,MANZANA,VIV_MZ,FALSO_COD,MZS_COND,CANT_BLOCK FROM TB_MANZANA WHERE UBIGEO IN ({}) ".format(temp_ubigeos)) zonas_Layer = arcpy.MakeQueryLayer_management(path_conexion, 'CPV_SEGMENTACION.dbo.VW_ZONA_CENSAL',"SELECT * FROM CPV_SEGMENTACION.dbo.VW_ZONA_CENSAL WHERE {} ".format(sql)) eje_vial_Layer = arcpy.MakeQueryLayer_management(path_conexion, 'CPV_SEGMENTACION.dbo.TB_EJE_VIAL',"SELECT * FROM CPV_SEGMENTACION.dbo.TB_EJE_VIAL where UBIGEO IN ({}) ".format(temp_ubigeos)) #viviendas_Layer = arcpy.MakeQueryLayer_management(path_conexion, 'CPV_SEGMENTACION.dbo.VW_VIVIENDAS_U',"SELECT * FROM CPV_SEGMENTACION.dbo.VW_VIVIENDAS_U WHERE {} ".format(sql)) #frentes_mfl = arcpy.MakeQueryLayer_management(path_conexion, 'CPV_SEGMENTACION.dbo.TB_FRENTES',"SELECT * FROM CPV_SEGMENTACION.dbo.VW_FRENTES WHERE {} ".format(sql)) manzanas_mfl = arcpy.MakeFeatureLayer_management(manzanas_Layer,"manzanas_mfl") zonas_mfl=arcpy.MakeFeatureLayer_management(zonas_Layer, "zonas_mfl") eje_vial_mfl = arcpy.MakeFeatureLayer_management(eje_vial_Layer, "eje_vial_mfl") list_mfl=[[manzanas_mfl,tb_manzanas], #[frentes_mfl,tb_frentes] ] i=0 for x in list_mfl: i=i+1 temp= arcpy.CopyFeatures_management(x[0], 'in_memory/temp_{}'.format(i)) arcpy.AddField_management(temp, 'MANZANA2', 'TEXT', 50) arcpy.CalculateField_management(temp, 'MANZANA2', '!MANZANA!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA']) arcpy.CopyFeatures_management(temp, x[1]) arcpy.AddField_management(x[1], 'MANZANA', 'TEXT', 50) arcpy.CalculateField_management(x[1], 'MANZANA', '!MANZANA2!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA2']) arcpy.CopyFeatures_management(zonas_mfl, tb_zonas) arcpy.CopyFeatures_management(eje_vial_mfl, tb_ejes_viales) arcpy.TableToTable_conversion(path_conexion + '/CPV_SEGMENTACION.dbo.VW_MZS_CONDOMINIOS', path_ini,"tb_mzs_condominios.dbf") arcpy.env.workspace = path_ini+"" arcpy.DeleteField_management(tb_manzanas, ['AEU','IDMANZANA']) arcpy.AddField_management(tb_manzanas, "IDMANZANA", "TEXT") expression = "(!UBIGEO!)+(!ZONA!)+(!MANZANA!)" arcpy.CalculateField_management(tb_manzanas, "IDMANZANA", expression, "PYTHON_9.3") arcpy.AddField_management(tb_manzanas, "AEU", "SHORT") arcpy.AddField_management(tb_manzanas, "AEU_2", "SHORT") arcpy.AddField_management(tb_manzanas, "FLG_MZ", "SHORT") arcpy.Dissolve_management(tb_frentes, tb_frentes_dissolve,['UBIGEO', 'ZONA', 'MANZANA', 'FRENTE_ORD']) database = "CPV_SEGMENTACION_GDB" if arcpy.Exists("{}.sde".format(database)) == False: arcpy.CreateDatabaseConnection_management("Database Connections", "{}.sde".format(database), "SQL_SERVER", ip_server, "DATABASE_AUTH", "sde", "$deDEs4Rr0lLo", "#", database, "#", "#", "#", "#") arcpy.env.workspace = "Database Connections/{}.sde".format(database) path_conexion2 = "Database Connections/{}.sde".format(database) list_capas=[ ["{}.sde.SEGM_AEU".format(database),tb_aeus,2], ["{}.sde.SEGM_RUTA".format(database), tb_rutas, 2], ["{}.sde.SEGM_SECCION".format(database), tb_secciones, 1], ["{}.sde.SEGM_SITIOS_INTERES".format(database), tb_sitios_interes, 1], ["{}.sde.SEGM_SUBZONA".format(database), tb_subzonas, 2], ] ##capa orin, destino for i,capa in enumerate(list_capas): if capa[2]==1: #print 'aqui' #if capa[1]==tb_viviendas_ordenadas_temp: # x = arcpy.MakeQueryLayer_management(path_conexion2, 'capa{}'.format(i),"select * from {} where {}".format(capa[0], sql)) # #else: x = arcpy.MakeQueryLayer_management(path_conexion2, 'capa{}'.format(i),"select * from {} where {} ".format(capa[0],sql)) else: x = arcpy.MakeQueryTable_management(capa[0], "capa{}".format(i), "USE_KEY_FIELDS", "objectid", "", sql) if capa[1] in (tb_rutas,tb_rutas_puntos): #####tratamiento del campo manzana para las Ñ ##### if capa[1] in (tb_rutas) : temp = arcpy.CopyRows_management(capa[0], 'in_memory/temp_m2{}'.format(i)) else: temp = arcpy.CopyFeatures_management(capa[0], 'in_memory/temp_m{}'.format(i)) arcpy.AddField_management(temp, 'MANZANA2', 'TEXT', 50) arcpy.CalculateField_management(temp, 'MANZANA2', '!MANZANA!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA']) #####copiando archivos if capa[1] in (tb_rutas): print capa[1] temp = arcpy.CopyRows_management(temp, capa[1]) else: temp = arcpy.CopyFeatures_management(temp, capa[1]) arcpy.AddField_management(capa[1], 'MANZANA', 'TEXT', 50) arcpy.CalculateField_management(capa[1], 'MANZANA', '!MANZANA2!', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['MANZANA2']) arcpy.AddField_management(capa[1], 'AEU2', 'TEXT', 3) arcpy.CalculateField_management(capa[1], 'AEU2', 'str(!AEU!).zfill(3)', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['AEU']) arcpy.AddField_management(capa[1], 'AEU', 'TEXT', 3) arcpy.CalculateField_management(capa[1], 'AEU', '!AEU2!', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['AEU2']) else: print capa[1] if capa[2] > 1: arcpy.CopyRows_management(x, capa[1]) else: arcpy.CopyFeatures_management(x, capa[1])
def importar_tablas(data,campos): arcpy.env.overwriteOutput = True database='CPV_SEGMENTACION_GDB' ip = '172.18.1.93' usuario='sde' password='******' path_conexion=conx.conexion_arcgis(database,ip,usuario,password) arcpy.env.workspace =path_conexion #temp_ubigeos = "" #i=0 #for x in data: # i=i+1 # if (i==1): # temp_ubigeos="'{}'".format(x[0]) # else: # temp_ubigeos = "{},'{}'".format(temp_ubigeos,x[0]) sql=expresion.Expresion_2(data, campos) list_capas=[ ["{}.sde.TB_EJE_VIAL".format(database), tb_ejes_viales, 1], ["{}.sde.VW_ZONA_CENSAL".format(database), tb_zonas, 1], ["{}.sde.TB_MANZANA".format(database), tb_manzanas, 1], ["{}.sde.SEGM_SITIOS_INTERES".format(database), tb_sitios_interes, 1], ] for i,capa in enumerate(list_capas): print capa if capa[2] == 1: if capa[1] in [tb_manzanas,tb_sitios_interes,tb_ejes_viales]: if capa[1] == tb_sitios_interes: x = arcpy.MakeQueryLayer_management(path_conexion, 'capa{}'.format(i), "select * from {} where ({}) AND (CODIGO<91 AND CODIGO<>26) ".format( capa[0], sql)) else: x = arcpy.MakeQueryLayer_management(path_conexion, 'capa{}'.format(i), "select * from {} where {} ".format(capa[0], sql)) else: x = arcpy.MakeQueryLayer_management(path_conexion, 'capa{}'.format(i), "select * from {} where {} ".format(capa[0], sql )) else: x = arcpy.MakeQueryTable_management(capa[0], "capa{}".format(i), "USE_KEY_FIELDS", "objectid", "", sql) if capa[1] in [tb_manzanas]: if capa[2]==1: temp = arcpy.CopyFeatures_management(x, "in_memory\\temp_{}".format(i)) arcpy.AddField_management(temp, 'MANZANA2', 'TEXT', 50) arcpy.CalculateField_management(temp, 'MANZANA2', '!MANZANA!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA']) arcpy.CopyFeatures_management(temp, capa[1]) arcpy.AddField_management(capa[1], 'MANZANA', 'TEXT', 50) arcpy.CalculateField_management(capa[1], 'MANZANA', '!MANZANA2!', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['MANZANA2']) else: temp = arcpy.CopyRows_management(x, "in_memory\\temp_{}".format(i)) arcpy.AddField_management(temp, 'MANZANA2', 'TEXT', 50) arcpy.CalculateField_management(temp, 'MANZANA2', '!MANZANA!', "PYTHON_9.3") arcpy.DeleteField_management(temp, ['MANZANA']) arcpy.CopyRows_management(temp, capa[1]) arcpy.AddField_management(capa[1], 'MANZANA', 'TEXT', 50) arcpy.CalculateField_management(capa[1], 'MANZANA', '!MANZANA2!', "PYTHON_9.3") arcpy.DeleteField_management(capa[1], ['MANZANA2']) else: if capa[2] > 1: arcpy.CopyRows_management(x, capa[1]) else: arcpy.CopyFeatures_management(x, capa[1]) arcpy.env.workspace = path_ini + ""
arcpy.AddField_management(fcRCLdsslv,statsfields[0][0],"TEXT","","",sfieldlen) arcpy.AddField_management(fcRCLdsslv,statsfields[1][0],"TEXT","","",sfieldlen) # Calculate values sfields = [] for i in range(len(statsfields)): sfields.append(statsfields[i][0]) arcpy.AddField_management(fcRCLdsslv,statsfields[i][0],"TEXT","","",sfieldlen) sfield = statsfields[i][1] + "_" + statsfields[i][0] calcexpr = ('!{}!').format(sfield) arcpy.CalculateField_management(fcRCLdsslv,statsfields[i][0],calcexpr,"PYTHON_9.3") ### Connect_Property print('Working on Connect_Property') # Make query table mqtblList = [fcCadPPath,tblPLnkPath] whereClause = tblPropLink + "." + parIDfield + " = " + fcCadP + "." + parIDfield # NOTE: no quotes - known bug arcpy.MakeQueryTable_management(mqtblList,"propQueryTbl","ADD_VIRTUAL_KEY_FIELD","","",whereClause) # Get number of rows numMQrows = int(arcpy.GetCount_management("propQueryTbl").getOutput(0)) # Export print('Exporting...') arcpy.CopyFeatures_management("propQueryTbl",fcPrclPLPath) # Check number of rows numPPLrows = int(arcpy.GetCount_management(fcPrclPLPath).getOutput(0)) if numPPLrows != numMQrows: print('ERROR: Wrong number of rows exported for link FC; {} versus {}').format(numMQrows,numPPLrows) errorLogic = 1 else: print('Correct number of rows exported for link FC.') # Dissolve on ID print('Dissolving on ID...') dfield = tblPropLink + "_" + propIDfield
print "***\nworking With A Many To One Relationship\n***" #todo: declare parameters here fc = "AucklandStravaMetro_Edges_NZTM" fckey = 'AucklandStravaMetro_Edges_NZTM.edge_id' tbl = r'Database Connections\[email protected]\GIS.gisadmin.auckland_edges_ride_data' tblkey = 'edge_id' sqlClause = "(year = 2013 AND hour IN (16,17)) AND day IN (7,14,21,28,35,42,49,56,63,70,77,84,91,98,105,112,119,126,133,140,147,154,161,168,175,182,189,196,203,210,217,224,231,238,245,252,259,266,273,280,287,294,301,308,315,322,329,336,343,350,357,364)" whereClause = "(CycleEvents.edge_id = AucklandStravaMetro_Edges_NZTM.edge_id)" #todo: make query Layer print 'make Query Layer' listTables = [] listTables.append(tbl) arcpy.MakeQueryTable_management(in_table=listTables, out_table="auckland_edges_ride_data", in_key_field_option="ADD_VIRTUAL_KEY_FIELD") #copy Query Table Locally print 'Copy Query Tables Locally to Cycle Events' if arcpy.Exists("CycleEvents"): arcpy.Delete_management("CycleEvents") arcpy.CopyRows_management("auckland_edges_ride_data", "CycleEvents") flds = [] flds.append("edge_id") arcpy.AddIndex_management(in_table="CycleEvents", fields=flds, index_name="EdgeIdIdx") #many To One Join #print "start Many To One Join"