def aer_E1(expresion, conexionBD=conexionDB): informacion = [] aer_mql = arcpy.MakeQueryLayer_management( conexionBD, "AERS_mql", "SELECT*FROM TB_AER WHERE {}".format(expresion), 'UBIGEO;AER_INI;AER_FIN', 'POLYGON', '4326', arcpy.SpatialReference(4326)) dist_mql = arcpy.MakeQueryLayer_management( conexionBD, "dist_mql", "SELECT*FROM VW_DISTRITO WHERE {}".format(expresion), 'UBIGEO', 'POLYGON', '4326', arcpy.SpatialReference(4326)) aer = arcpy.CopyFeatures_management(aer_mql, "in_memory\AER_mfl") dist = arcpy.CopyFeatures_management(dist_mql, "in_memory\dist_mfl") for m in arcpy.da.SearchCursor(dist, ["SHAPE@", "UBIGEO"]): mfl = arcpy.MakeFeatureLayer_management(aer, "mfl", "UBIGEO = '{}'".format(m[1])) seleccion = arcpy.SelectLayerByLocation_management( mfl, "BOUNDARY_TOUCHES", m[0], "#", "NEW_SELECTION", "NOT_INVERT") if (arcpy.GetCount_management(seleccion))[0] == 1: pass else: aerError = [[x[0], 1] for x in arcpy.da.SearchCursor(mfl, ["IDAER"])] informacion.extend(aerError) return informacion
def copy_to_memory_with_query(self, input_database, in_memory_output_table_name, query): #TODO: test in_memory_table = self.workspace + "\\" + in_memory_output_table_name arcpy.MakeQueryLayer_management(input_database, in_memory_table, query, "", "", "") pass
def asignarAer(ubigeos, conexion=conexionDB, conn=conn): asignarIDAER() aers = r'{}\CPV_SEGMENTACION.dbo.TB_AER'.format(conexion) for ubigeo in ubigeos: print ubigeo ccppsmfl = arcpy.MakeQueryLayer_management( conexion, 'CCPP', "SELECT*FROM TB_CCPP WHERE UBIGEO = '{}' AND AREA = 2".format( ubigeo), 'LLAVE_CCPP', 'POINT', '4326', arcpy.SpatialReference(4326)) ccpps_copy = arcpy.CopyFeatures_management(ccppsmfl, "in_memory\ccpps") ccpps = arcpy.MakeFeatureLayer_management(ccpps_copy, "ccpp") for aer in arcpy.da.SearchCursor( aers, ["SHAPE@", "AER_INI", "AER_FIN", "IDAER"], "UBIGEO = '{}'".format(ubigeo)): seleccion = arcpy.SelectLayerByLocation_management( ccpps, "INTERSECT", aer[0], "#", "NEW_SELECTION", "NOT_INVERT") listaCcpps = [ x[0] for x in arcpy.da.SearchCursor(seleccion, ["LLAVE_CCPP"]) ] if len(listaCcpps) > 0: expresion = funcionesGenerales.Expresion( listaCcpps, "#", "LLAVE_CCPP") cursor = conn.cursor() cursor.execute( "UPDATE TB_CCPP SET AER_INI = '{}', AER_FIN = '{}', IDAER = '{}' WHERE {}" .format(aer[1], aer[2], aer[3], expresion)) conn.commit() cursor.close() else: pass
def importar_capas_segmentacion(self): arcpy.env.overwriteOutput = True path_conexion = cnx.connect_arcpy() arcpy.env.workspace = path_conexion data = [] for zona in self.zonas: data.append([zona['UBIGEO'], zona['ZONA']]) where_zona = expresion.expresion(data, ['UBIGEO', 'ZONA']) where_ubigeo = expresion.expresion(data, ['UBIGEO']) list_capas = [ [ "{}.sde.TB_OPER_MANZANA".format(config.DB_NAME), "tb_manzana_procesar", 2 ], ] for i, capa in enumerate(list_capas): if (capa[2] == 1): where = where_zona else: where = where_ubigeo where = "({}) ".format(where) x = arcpy.MakeQueryLayer_management( path_conexion, 'capa{}'.format(i), "select * from {} where ({}) AND (COD_OPER = '{}') ".format( capa[0], where, self.cod_oper)) temp = arcpy.CopyFeatures_management( x, '{}/{}'.format(self.path_trabajo, capa[1]))
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 full_landowner_enrich(): start_date = "2002-02-01 12:00:00" stop_date = "2002-03-01 12:00:00" ending = "2004-06-01 12:00:00" start = dt.strptime(start_date, "%Y-%m-%d %H:%M:%S") stop = dt.strptime(stop_date, "%Y-%m-%d %H:%M:%S") end = dt.strptime(ending, "%Y-%m-%d %H:%M:%S") while start < end: print('Start', start, 'End', stop) #where clause for the time range where_clause = "select * from Collar.COLLARADMIN.Collars where DateYearAndJulian >=" + \ "'{}'".format(start) + " AND " + \ "DateYearAndJulian <=" + "'{}'".format(stop) #query layer created from the clause arcpy.MakeQueryLayer_management( r"enrichedPoints\collar.agrc.utah.gov.sde", "date_query_result", where_clause) #defining features for the spatial join join_features = r"H:\enrichedPoints.gdb\SGID10_Landownership" target_features = r"date_query_result" #field map to determine which fields to keep fieldmappings = arcpy.FieldMappings() # Add all fields from inputs. fieldmappings.addTable(join_features) fieldmappings.addTable(target_features) fields_sequence = ["OWNER", "ADMIN", "COUNTY", "GlobalID"] for field in fieldmappings.fields: if field.name not in fields_sequence: fieldmappings.removeFieldMap( fieldmappings.findFieldMapIndex(field.name)) #joining the query layer with landownership and writing to in_memory arcpy.SpatialJoin_analysis(target_features, join_features, r"in_memory\spatial_join", "JOIN_ONE_TO_ONE", "KEEP_ALL", fieldmappings) #removing uneeded fields created from join arcpy.DeleteField_management( r"spatial_join", ["Join_Count", "TARGET_FID"]) #appending the spatial join output to the master table of enriched points arcpy.Append_management(r"spatial_join", r"H:\enrichedPoints.gdb\enrichedPoints", "NO_TEST") arcpy.Delete_management(r"in_memory\spatial_join") #adding time to the start and stop date to pickup where it left off start = stop + timedelta(minutes=1) stop = stop + timedelta(days=30)
def importar_capas_insumos(self): arcpy.env.overwriteOutput = True path_conexion = cnx.connect_arcpy() arcpy.env.workspace = path_conexion data = [] for distrito in self.distritos: data.append([distrito['UBIGEO']]) where_ubigeo = expresion.expresion(data, ['UBIGEO']) list_capas = [ [ "{}.DBO.TB_MANZANA".format(config.DB_NAME), "tb_manzana_procesar", 1 ], [ "{}.DBO.TB_EJE_VIAL".format(config.DB_NAME), "tb_eje_vial_procesar", 2 ], ["{}.DBO.TB_ZONA".format(config.DB_NAME), "tb_zona_procesar", 2], ] where_ubigeo = "({}) and FASE=0 ".format(where_ubigeo) for i, capa in enumerate(list_capas): print capa[1] if capa[2] == 2: x = arcpy.MakeQueryLayer_management( path_conexion, 'capa{}'.format(i), "select * from {} where {} ".format( capa[0], where_ubigeo)) else: x = arcpy.MakeQueryLayer_management( path_conexion, 'capa{}'.format(i), "select * from {} where {} ".format( capa[0], where_ubigeo)) temp = arcpy.CopyFeatures_management( x, '{}/{}'.format(self.path_trabajo, capa[1]))
def segmentacionmain(ubigeos): #### PROCESAMIENTO DE INFORMACION # UBIGEOS sql = funcionesGenerales.Expresion(ubigeos, "#", 'UBIGEO') Distritos_tmp = arcpy.MakeQueryLayer_management( conexionDB, 'DistritosTMP', "SELECT*FROM CPV_SEGMENTACION_GDB.sde.TB_LIMITE_DIS where {}".format( sql), 'UBIGEO', 'POLYGON', '4326', arcpy.SpatialReference(4326)) # SEGMENTACION print "SEGMENTACION" arcpy.AddMessage("SEGMENTACION") Segmentacion_2.SegmentacionRural(WorkSpaceSegmentacion, Distritos_tmp, conexionDB, conexionGDB, SEGM_R_AER, SEGM_R_CCPP, SEGM_R_VIV, SEGM_R_AER_POST)
def get_querylayer_for_yesterday(workspace, table_name, guid_field, date_field, today=None): """Create a query layer that includes only data for the previous day.""" if today is None: yesterday = dt.now() - timedelta(days=1) else: yesterday = today - timedelta(days=1) start_of_day = dt(yesterday.year, yesterday.month, yesterday.day) start_day_string = dt.strftime(start_of_day, "%Y-%m-%d %H:%M:%S") end_of_day = start_of_day + timedelta(days=1) end_day_string = dt.strftime(end_of_day, "%Y-%m-%d %H:%M:%S") logger.multi_log('Making query layer for {}. Date range: {} to {}'.format( table_name, start_day_string, end_day_string), 'INFO') #where clause for the time range where_clause = \ """ select {fields_list} from {table} where {field} >= '{start}' AND {field} < '{end}' """.format( fields_list=guid_field + ', SHAPE', table=table_name, field=date_field, start=start_day_string, end=end_day_string) ql_name = "date_query_result" ql_start_time = time.time() arcpy.MakeQueryLayer_management( workspace, ql_name, where_clause) ql_time = round(time.time() - ql_start_time, 4) logger.multi_log('Query layer creation time: {} seconds'.format(ql_time), 'INFO', CLOUD_LOGGING, {'action': 'ql creation', 'feature': table_name, 'time': ql_time}) return ql_name
def cargarTabla(errores, expresion, CALIDAD_AER_PRESEGM, conexion=conexionDB, conn=conn): mfl = arcpy.MakeFeatureLayer_management(CALIDAD_AER_PRESEGM, "mfl", expresion) arcpy.DeleteRows_management(mfl) aer_mql = arcpy.MakeQueryLayer_management( conexion, "AERS", "SELECT*FROM TB_AER WHERE {}".format(expresion), "IDAER", 'POLYGON', '4326', arcpy.SpatialReference(4326)) arcpy.Append_management(aer_mql, CALIDAD_AER_PRESEGM, "NO_TEST") for error in errores: cursor = conn.cursor() cursor.execute( "UPDATE GEODB_CPV_SEGM.SDE.CALIDAD_AER_PRESEGM SET E{} = 1 WHERE IDAER = '{}'" .format(error[1], error[0])) conn.commit() cursor.close()
def makeMxd(conFile, mxd, df, fc, mxdpath): fieldNames = [f.name for f in arcpy.ListFields(fc)] arcpy.AddMessage("---------FIELD NAMES------------") arcpy.AddMessage(fieldNames) fcsql = "SELECT * FROM SDE.huaweitestpolygon" addFCData(fc) result = arcpy.MakeQueryLayer_management(conFile, "querylayer", fcsql, "OBJECTID", "POLYGON", "4326") addLayer = result.getOutput(0) addLayer.showLabel = True for myLabel in addLayer.labelClasses: myLabel.showClassLabels = True myLabel.expression = '[OBJECTID]' arcpy.mapping.AddLayer(df, addLayer, "BOTTOM") removeFCData(fc) result2 = arcpy.MakeFeatureLayer_management(fc, "newfeaturelayer") addLayer2 = result2.getOutput(0) arcpy.mapping.AddLayer(df, addLayer2, "BOTTOM") newMxd = mxdpath + r"\new\new.mxd" print "newMxd" + newMxd mxd.saveACopy(newMxd) arcpy.AddMessage("------------MAKE MXD FINISH---------") del mxd
def cargarTabla(errores, expresion, CALIDAD_VIV_PRESEGM, conexion=conexionDB, conn=conn): mfl = arcpy.MakeFeatureLayer_management(CALIDAD_VIV_PRESEGM, "mfl", expresion) arcpy.DeleteRows_management(mfl) viv_mql = arcpy.MakeQueryLayer_management( conexion, "CCPPS", "SELECT*FROM TB_VIVIENDA_R WHERE {}".format(expresion), "IDVIV", 'POINT', '4326', arcpy.SpatialReference(4326)) arcpy.Append_management(viv_mql, CALIDAD_VIV_PRESEGM, "NO_TEST") for error in errores: cursor = conn.cursor() cursor.execute( "UPDATE CPV_SEGMENTACION_GDB.SDE.CALIDAD_VIV_PRESEGM SET E{} = 1 WHERE IDVIV= '{}'" .format(error[1], error[0])) conn.commit() cursor.close()
def get_querylayer_for_yesterday(workspace, table_name, date_field, today=None): """Create a query layer that includes only data for the previous day.""" if today is None: yesterday = dt.now() - timedelta(days=1) else: yesterday = today - timedelta(days=1) start_of_day = dt(yesterday.year, yesterday.month, yesterday.day) start_day_string = dt.strftime(start_of_day, "%Y-%m-%d %H:%M:%S") end_of_day = start_of_day + timedelta(days=1) end_day_string = dt.strftime(end_of_day, "%Y-%m-%d %H:%M:%S") log.info('Making query layer for {}. Date range: {} to {}'.format( table_name, start_day_string, end_day_string)) #where clause for the time range where_clause = \ """ select * from {table} where {field} >= '{start}' AND {field} < '{end}' """.format( table=table_name, field=date_field, start=start_day_string, end=end_day_string) ql_name = "date_query_result" ql_start_time = time.time() arcpy.MakeQueryLayer_management( workspace, ql_name, where_clause) log.info('Query Layer creation time: {} seconds'.format(round(time.time() - ql_start_time, 4))) return ql_name
def indicator_to_excel(OutputXLSX, IndicatorID, GeographicLevelID, Language, DGUIDs): geoID = GeographicLevelID.strip().upper()[0:5] lng = Language.upper() # prepare DGUIDs if present dguid_str = prepare_dguids(DGUIDs) # select and rename fields to be exported based on language selection # note special characters are automatically changed to _ by arcpy if lng == "EN": d = { "DGUID": "DGUID", "Loc": "Location", "Prov": "Province_Territory", "Value": "Value", "Desc": "Data_Comment" } elif lng == "FR": d = { "DGUID": "IDUGD", "Loc": "Endroit", "Prov": "Province_Territoire", "Value": "Valeur", "Desc": "Commentaire" } # Build query string qry = "SELECT grfi.GeographyReferenceId AS " + d["DGUID"] + ", " \ "g.DisplayNameLong_" + lng + " AS " + d["Loc"] + ", " \ "g.ProvTerrName_" + lng + " AS " + d["Prov"] + ", " \ "iv.value AS " + d["Value"] + ", " \ "nr.Description_" + lng + " AS " + d["Desc"] + ", g.Shape " \ "FROM gis.geographyreference AS g INNER JOIN gis.geographyreferenceforindicator AS grfi " \ "ON g.geographyreferenceid = grfi.geographyreferenceid INNER JOIN " \ "(select * from gis.indicator where indicatorId = " + IndicatorID + ") AS i " \ "ON grfi.indicatorid = i.indicatorid INNER JOIN gis.geographiclevel AS gl " \ "ON g.geographiclevelid = gl.geographiclevelid INNER JOIN " \ "gis.geographiclevelforindicator AS glfi ON i.indicatorid = glfi.indicatorid " \ "AND gl.geographiclevelid = glfi.geographiclevelid INNER JOIN " \ "gis.indicatorvalues AS iv ON iv.indicatorvalueid = grfi.indicatorvalueid " \ "INNER JOIN gis.indicatortheme AS it ON i.indicatorthemeid = it.indicatorthemeid " \ "LEFT OUTER JOIN gis.indicatornullreason AS nr ON iv.nullreasonid = nr.nullreasonid " \ "WHERE g.GeographicLevelID = '" + geoID + "' " if DGUIDs != "": qry += "AND grfi.GeographyReferenceID IN (" + dguid_str + ") " # Make Query Layer from result of primary query - results in feature layer sr = arcpy.SpatialReference(3857) # WGS_1984_Web_Mercator_Auxiliary_Sphere all_polys = arcpy.MakeQueryLayer_management(input_database=csge_sde, out_layer_name=os.path.join( scratch_path, "all_polys.shp"), query=qry, oid_fields=d["DGUID"], shape_type="POLYGON", spatial_reference=sr) # Hide any automatically added geo fields that are not needed for output desc = arcpy.Describe(all_polys) fi = desc.fieldInfo for i in range(0, fi.count): if fi.getFieldName(i) == "ESRI_OID": fi.setVisible(i, "HIDDEN") # make table view to allow export with custom field visibility tb_out = arcpy.management.MakeTableView(all_polys, "tableout", "", "", fi) # export to excel file # output filename also becomes worksheet name, which has 31 char limit. # ignoring OutputXLSX arg allows consistent filename when service is published arcpy.conversion.TableToExcel(tb_out, "Export.xlsx")
output_vista_data_table = 'SLCO_Vista_Addresses' csv_folder = os.path.join(output_folder, 'job_uploads') if not arcpy.Exists(output_workspace): arcpy.CreateFileGDB_management(output_folder, output_gdb) else: print('output_gdb exists') if not os.path.exists(csv_folder): os.mkdir(csv_folder) else: print('csv_folder exists') vist_ql = arcpy.MakeQueryLayer_management( input_database=vista_database_connection, out_layer_name="VDATA", query=query, oid_fields="UNIQUE_ID", shape_type="", srid="", spatial_reference="") vista_tableview = arcpy.mapping.TableView(vist_ql[0]) row_count = int(arcpy.GetCount_management(vista_tableview).getOutput(0)) print('total rows:', row_count) address_table = arcpy.TableToTable_conversion(vista_tableview, output_workspace, output_vista_data_table)[0] partition_table(address_table, 3, [18]) print('Data created:', address_table) separate_partitions(address_table, 3, output_workspace, csv_folder)
arcpy.AddMessage("Cannot connect to database. Aborting") exit(0) ## Firstly run the stored procedure. arcpy.AddMessage("Selecting all data from stored procedure") try: theQuery = "dbo.HLCreateTempTable" theCursor = cnnDB.execute(theQuery) except Exception as err: arcpy.AddMessage(err) arcpy.AddMessage("Selection failed") ## Now put the result into a query layer. arcpy.AddMessage("Creating query layer") try: arcpy.MakeQueryLayer_management(DBSDE, "Temp", "select * from dbo.HLTemp") arcpy.CopyFeatures_management("Temp", OutFC) arcpy.MakeFeatureLayer_management(OutFC, "SpeciesSelection") except Exception as err: arcpy.AddMessage(err) arcpy.AddMessage("Could not make query layer") ## Add resulting layer to view arcpy.AddMessage("Adding query layer to view") mxd = arcpy.mapping.MapDocument("CURRENT") df = arcpy.mapping.ListDataFrames(mxd, "*")[0] try: addlayer = arcpy.mapping.Layer("SpeciesSelection") arcpy.mapping.AddLayer(df, addlayer) except Exception as err:
start = datetime.strptime(start_date, "%Y-%m-%d %H:%M:%S") stop = datetime.strptime(stop_date, "%Y-%m-%d %H:%M:%S") end = datetime.strptime(ending, "%Y-%m-%d %H:%M:%S") while start < end: print(start, stop) #where clause for the time range where_caluse = "select * from Collar.COLLARADMIN.Collars where DateYearAndJulian >=" + \ "'{}'".format(start) + " AND " + \ "DateYearAndJulian <=" + "'{}'".format(stop) #query layer created from the clause arcpy.MakeQueryLayer_management( r"enrichedPoints\collar.agrc.utah.gov.sde", "date_query_result", where_caluse) #defining features for the spatial join joinFeatures = r"H:\enrichedPoints.gdb\SGID10_Landownership" targetFeatures = r"date_query_result" #field map to determine which fields to keep fieldmappings = arcpy.FieldMappings() # Add all fields from inputs. fieldmappings.addTable(joinFeatures) fieldmappings.addTable(targetFeatures) fields_sequence = ["OWNER", "ADMIN", "COUNTY", "GlobalID"] for field in fieldmappings.fields: if field.name not in fields_sequence:
def indicator_to_shapefile(OutputZip, IndicatorID, GeographicLevelID, Language, DGUIDs): geoID = GeographicLevelID.strip().upper()[0:5] lng = Language.upper() # prepare DGUIDs if present dguid_str = prepare_dguids(DGUIDs) # select and rename fields to be exported based on language selection # note special characters are automatically changed to _ by arcpy if lng == "EN": d = { "DGUID": "DGUID", "Loc": "Location", "Prov": "ProvTerr", "Value": "Value", "Desc": "Comment" } else: d = { "DGUID": "IDUGD", "Loc": "Endroit", "Prov": "ProvTerr", "Value": "Valeur", "Desc": "Commentaire" } # Build query string qry = "SELECT grfi.GeographyReferenceId AS " + d["DGUID"] + ", " \ "g.DisplayNameLong_" + lng + " AS " + d["Loc"] + ", " \ "g.ProvTerrName_" + lng + " AS " + d["Prov"] + ", " \ "iv.value AS " + d["Value"] + ", " \ "nr.Description_" + lng + " AS " + d["Desc"] + ", g.Shape " \ "FROM gis.geographyreference AS g INNER JOIN gis.geographyreferenceforindicator AS grfi " \ "ON g.geographyreferenceid = grfi.geographyreferenceid INNER JOIN " \ "(select * from gis.indicator where indicatorId = " + IndicatorID + ") AS i " \ "ON grfi.indicatorid = i.indicatorid INNER JOIN gis.geographiclevel AS gl " \ "ON g.geographiclevelid = gl.geographiclevelid INNER JOIN " \ "gis.geographiclevelforindicator AS glfi ON i.indicatorid = glfi.indicatorid " \ "AND gl.geographiclevelid = glfi.geographiclevelid INNER JOIN " \ "gis.indicatorvalues AS iv ON iv.indicatorvalueid = grfi.indicatorvalueid " \ "INNER JOIN gis.indicatortheme AS it ON i.indicatorthemeid = it.indicatorthemeid " \ "LEFT OUTER JOIN gis.indicatornullreason AS nr ON iv.nullreasonid = nr.nullreasonid " \ "WHERE g.GeographicLevelID = '" + geoID + "' " if DGUIDs != "": qry += "AND grfi.GeographyReferenceID IN (" + dguid_str + ") " # Make Query Layer from result of primary query sr = arcpy.SpatialReference(3857) # WGS_1984_Web_Mercator_Auxiliary_Sphere all_polys = arcpy.MakeQueryLayer_management(input_database=csge_sde, out_layer_name=os.path.join( scratch_path_db, "all_polys.shp"), query=qry, oid_fields=d["DGUID"], shape_type="POLYGON", spatial_reference=sr) # Note - must save query layer to scratch folder or published service will fail copied_lyr = arcpy.Select_analysis(all_polys, os.path.join(scratch_folder, "Export")) # zip shapefile files_to_zip = build_file_list(scratch_folder, "Export") with zipfile.ZipFile(OutputZip, "w", zipfile.ZIP_DEFLATED) as myZip: for file in files_to_zip: myZip.write(file, os.path.basename(file))
def importar_tablas(where_list): arcpy.env.overwriteOutput = True if arcpy.Exists("CPV_SEGMENTACION.sde") == False: arcpy.CreateDatabaseConnection_management( "Database Connections", "CPV_SEGMENTACION.sde", "SQL_SERVER", "172.18.1.93", "DATABASE_AUTH", "us_arcgis_seg_2", "MBs0p0rt301", "#", "CPV_SEGMENTACION", "#", "#", "#", "#") arcpy.env.workspace = "Database Connections/CPV_SEGMENTACION.sde" path_conexion = "Database Connections/CPV_SEGMENTACION.sde" 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 {} " .format(where_list)) zonas_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_ZONA', "SELECT * FROM CPV_SEGMENTACION.dbo.TB_ZONA WHERE {} ".format( where_list)) eje_vial_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_EJE_VIAL', "SELECT * FROM CPV_SEGMENTACION.dbo.TB_EJE_VIAL WHERE {} ".format( where_list)) viviendas_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_VIVIENDAS_U', "SELECT * FROM CPV_SEGMENTACION.dbo.VW_VIVIENDAS_NACIONAL WHERE {} ". format(where_list)) ccpp_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_CCPP', " SELECT ID,UBIGEO,CODCCPP,NOMCCPP,CAST(AREA AS INT)AREA,VIV_CCPP,CATEGORIA,CATEGORIA_O,LLAVE_CCPP,geom,COMUNIDAD,AER_INI,AER_FIN,IDSCR,IDAER,IDRUTA,ESTADO FROM TB_CCPP WHERE {} " .format(where_list)) 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") viviendas_mfl = arcpy.MakeFeatureLayer_management(viviendas_Layer, "viviendas_mfl") ccpp_mfl = arcpy.MakeFeatureLayer_management(ccpp_Layer, "ccpp_mfl") list_mfl = [[viviendas_mfl, tb_viviendas], [manzanas_mfl, tb_manzanas]] 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_eje_vial) arcpy.CopyFeatures_management(ccpp_mfl, tb_ccpp)
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 + ""
def importar_tablas_trabajo(data, campos): arcpy.env.overwriteOutput = True if arcpy.Exists("CPV_SEGMENTACION.sde") == False: arcpy.CreateDatabaseConnection_management( "Database Connections", "CPV_SEGMENTACION.sde", "SQL_SERVER", ip_server, "DATABASE_AUTH", "us_arcgis_seg_2", "MBs0p0rt301", "#", "CPV_SEGMENTACION", "#", "#", "#", "#") arcpy.env.workspace = "Database Connections/CPV_SEGMENTACION.sde" path_conexion = "Database Connections/CPV_SEGMENTACION.sde" where_expression = expresiones_consulta_arcpy.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 = expresiones_consulta_arcpy.Expresion(data, campos) print sql 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 ({}) " .format(sql)) print 'importo manzanas' sitios_interes_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_SITIOS_INT', "SELECT * FROM TB_SITIO_INTERES WHERE UBIGEO IN ({}) AND (CODIGO<91 AND CODIGO<>26) " .format(temp_ubigeos)) print 'importo sitios interes' puntos_inicio_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'TB_PUNTO_INICIO', "SELECT * FROM TB_PUNTO_INICIO WHERE {} ".format(sql)) print 'importo puntos inicio' zonas_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'CPV_SEGMENTACION.dbo.VW_ZONA_CENSAL', "SELECT * FROM CPV_SEGMENTACION.dbo.VW_ZONA_CENSAL WHERE {} ".format( sql)) print 'importo zonas' 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)) print 'importo ejes viales' viviendas_Layer = arcpy.MakeQueryLayer_management( path_conexion, 'CPV_SEGMENTACION.dbo.VW_VIVIENDAS_U', "SELECT * FROM CPV_SEGMENTACION.dbo.VW_VIVIENDAS_U WHERE {} ".format( sql)) print 'importo viviendas' frentes_mfl = arcpy.MakeQueryLayer_management( path_conexion, 'CPV_SEGMENTACION.dbo.TB_FRENTES', "SELECT * FROM CPV_SEGMENTACION.dbo.TB_FRENTES WHERE {} ".format(sql)) print 'importo frentes' manzanas_mfl = arcpy.MakeFeatureLayer_management(manzanas_Layer, "manzanas_mfl") sitios_interes_mfl = arcpy.MakeFeatureLayer_management( sitios_interes_Layer, "sitios_interes_mfl") puntos_inicio_mfl = arcpy.MakeFeatureLayer_management( puntos_inicio_Layer, "puntos_inicio_mfl") zonas_mfl = arcpy.MakeFeatureLayer_management(zonas_Layer, "zonas_mfl") eje_vial_mfl = arcpy.MakeFeatureLayer_management(eje_vial_Layer, "eje_vial_mfl") viviendas_mfl = arcpy.MakeFeatureLayer_management(viviendas_Layer, "viviendas_mfl") list_mfl = [[viviendas_mfl, tb_viviendas], [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(puntos_inicio_mfl, tb_puntos_inicio) arcpy.CopyFeatures_management(zonas_mfl, tb_zonas) arcpy.CopyFeatures_management(eje_vial_mfl, tb_ejes_viales) arcpy.CopyFeatures_management(sitios_interes_mfl, tb_sitios_interes) arcpy.TableToTable_conversion( path_conexion + '/CPV_SEGMENTACION.dbo.VW_MZS_CONDOMINIOS', path_calidad, "tb_mzs_condominios.dbf") arcpy.env.workspace = path_calidad + "" 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'])
def indicator_to_csv(OutputCSVFile, IndicatorID, GeographicLevelID, Language, DGUIDs): geoID = GeographicLevelID.strip().upper()[0:5] lng = Language.upper() # prepare DGUIDs if present dguid_str = prepare_dguids(DGUIDs) # select and rename fields to be exported based on language selection # note special characters are automatically changed to _ by arcpy if lng == "EN": d = { "DGUID" : "DGUID", "Loc" : "Location", "Prov" : "Province_Territory", "Value" : "Value", "Desc" : "Data_Comment" } elif lng == "FR": d = { "DGUID" : "IDUGD", "Loc" : "Endroit", "Prov" : "Province_Territoire", "Value" : "Valeur", "Desc" : "Commentaire" } # Build query string qry = "SELECT grfi.GeographyReferenceId AS " + d["DGUID"] + ", " \ "g.DisplayNameLong_" + lng + " AS " + d["Loc"] + ", " \ "g.ProvTerrName_" + lng + " AS " + d["Prov"] + ", " \ "iv.value AS " + d["Value"] + ", " \ "nr.Description_" + lng + " AS " + d["Desc"] + ", g.Shape " \ "FROM gis.geographyreference AS g INNER JOIN gis.geographyreferenceforindicator AS grfi " \ "ON g.geographyreferenceid = grfi.geographyreferenceid INNER JOIN " \ "(select * from gis.indicator where indicatorId = " + IndicatorID + ") AS i " \ "ON grfi.indicatorid = i.indicatorid INNER JOIN gis.geographiclevel AS gl " \ "ON g.geographiclevelid = gl.geographiclevelid INNER JOIN " \ "gis.geographiclevelforindicator AS glfi ON i.indicatorid = glfi.indicatorid " \ "AND gl.geographiclevelid = glfi.geographiclevelid INNER JOIN " \ "gis.indicatorvalues AS iv ON iv.indicatorvalueid = grfi.indicatorvalueid " \ "INNER JOIN gis.indicatortheme AS it ON i.indicatorthemeid = it.indicatorthemeid " \ "LEFT OUTER JOIN gis.indicatornullreason AS nr ON iv.nullreasonid = nr.nullreasonid " \ "WHERE g.GeographicLevelID = '" + geoID + "' " if DGUIDs != "": qry += "AND grfi.GeographyReferenceID IN (" + dguid_str + ") " arcpy.AddMessage(qry) # Make Query Layer from result of primary query - results in feature layer sr = arcpy.SpatialReference(3857) # WGS_1984_Web_Mercator_Auxiliary_Sphere all_polys = arcpy.MakeQueryLayer_management(input_database=csge_sde, out_layer_name=os.path.join(scratch_path, "all_polys.shp"), query=qry, oid_fields=d["DGUID"], shape_type="POLYGON", spatial_reference=sr) # Export CSV - semi colon delimiter is used b/c many values contain commas. # Note: table to table tool does not support custom delimiters as of pro version 2.8. # Ignoring OutputCSVFile arg allows consistent filename when service is published arcpy.ExportXYv_stats(Input_Feature_Class=all_polys, Value_Field=list(d.values()), Delimiter="SEMI-COLON", Output_ASCII_File="Export.txt", Add_Field_Names_to_Output="ADD_FIELD_NAMES")
def importarFeatureClass(sede, subsede, rutaFD): conexionDB = conectionDB_arcpy() ccpp_urbano = arcpy.MakeQueryLayer_management( conexionDB, "CCPP_URBANO_{}_{}".format(sede, subsede), "SELECT * FROM {}.SDE.TB_CCPP_URBANO where CODSEDE = '{}' AND CODSUBSEDE = {} AND PRIORIDAD='01' " .format(DB_NAME, sede, subsede), 'OBJECTID', 'POINT', '4326', arcpy.SpatialReference(4326)) departamento = arcpy.MakeQueryLayer_management( conexionDB, "DEPARTAMENTO", "SELECT * FROM {db}.SDE.TB_DEPARTAMENTO WHERE CODDPTO IN (SELECT DISTINCT CODDPTO FROM {db}.SDE.TB_DISTRITO B WHERE b.CODSEDE ='{sede}' AND B.CODSUBSEDE ={subsede} ) " .format(db=DB_NAME, sede=sede, subsede=subsede), 'OBJECTID', 'POLYGON', '4326', arcpy.SpatialReference(4326)) provincia = arcpy.MakeQueryLayer_management( conexionDB, "PROVINCIA", "SELECT * FROM {db}.SDE.TB_PROVINCIA WHERE CODDPTO + CODPROV IN (SELECT DISTINCT CODDPTO+CODPROV FROM {db}.SDE.TB_DISTRITO B WHERE B.CODSEDE ='{sede}' AND B.CODSUBSEDE ={subsede} ) " .format(db=DB_NAME, sede=sede, subsede=subsede), 'OBJECTID', 'POLYGON', '4326', arcpy.SpatialReference(4326)) sede_operativa = arcpy.MakeQueryLayer_management( conexionDB, "SEDE_OPERATIVA", " SELECT * FROM {db}.SDE.TB_SEDE_OPERATIVA WHERE CODSEDE ='{sede}' ". format(db=DB_NAME, sede=sede), 'OBJECTID', 'POLYGON', '4326', arcpy.SpatialReference(4326)) #hidro = arcpy.MakeQueryLayer_management(conexionDB, "HIDRO_{}_{}".format(sede, subsede),"SELECT * FROM {}.SDE.TB_HIDRO where CODSEDE = '{}' ".format(DB_NAME, sede), 'OBJECTID_1') track = arcpy.MakeQueryLayer_management( conexionDB, "TRACK_{}_{}".format(sede, subsede), "SELECT * FROM {}.SDE.TB_TRACK where CODSEDE = '{}' ".format( DB_NAME, sede), 'OBJECTID', 'POLYLINE', '4326', arcpy.SpatialReference(4326)) distritos = arcpy.MakeQueryLayer_management( conexionDB, "DISTRITO_{}_{}".format(sede, subsede), "SELECT * FROM {}.SDE.TB_DISTRITO where CODSEDE = '{}' AND CODSUBSEDE = {} AND COD_OPER='01' " .format(DB_NAME, sede, subsede), 'OBJECTID', 'POLYLINE', '4326', arcpy.SpatialReference(4326)) zonas = arcpy.MakeQueryLayer_management( conexionDB, "ZONA_{}_{}".format(sede, subsede), "SELECT * FROM {}.SDE.TB_ZONA where CODSEDE = '{}' AND CODSUBSEDE = {} AND COD_OPER='01' " .format(DB_NAME, sede, subsede), 'OBJECTID', 'POLYLINE', '4326', arcpy.SpatialReference(4326)) aes = arcpy.MakeQueryLayer_management( conexionDB, "AES_{}_{}".format(sede, subsede), "SELECT * FROM {}.SDE.SEGM_U_AEU where CODSEDE = '{}' AND CODSUBSEDE = {} " .format(DB_NAME, sede, subsede), 'OBJECTID', 'POLYGON', '4326', arcpy.SpatialReference(4326)) #arcpy.Append_management(hidro, r'{}\HIDRO_{}_{}'.format(rutaFD, sede, subsede),"NO_TEST") arcpy.Append_management(track, r'{}\TRACK_{}_{}'.format(rutaFD, sede, subsede), "NO_TEST") arcpy.Append_management(distritos, r'{}\DISTRITO_{}_{}'.format(rutaFD, sede, subsede), "NO_TEST") arcpy.Append_management(zonas, r'{}\ZONAS_{}_{}'.format(rutaFD, sede, subsede), "NO_TEST") arcpy.Append_management(aes, r'{}\AES_{}_{}'.format(rutaFD, sede, subsede), "NO_TEST") arcpy.Append_management( ccpp_urbano, r'{}\CCPP_URBANO_{}_{}'.format(rutaFD, sede, subsede), "NO_TEST") arcpy.Append_management(departamento, r'{}\DEPARTAMENTO'.format(rutaFD), "NO_TEST") arcpy.Append_management(provincia, r'{}\PROVINCIA'.format(rutaFD), "NO_TEST") arcpy.Append_management(sede_operativa, r'{}\SEDE_OPERATIVA'.format(rutaFD), "NO_TEST")
if minDate: #startDate = datetime.strptime(minDate, '%m/%d/%Y') c2 = ''.join([" AND timevalue > '", minDate, "'"]) else: c2 = '' if combine == 'true': tagList = ','.join([str(v) for v in devDict.values()]) c1 = ''.join(['tag_id IN (', tagList, ')']) sqlSelect = '{0} AND {1}{2}'.format(c0,c1,c2) layerName = 'q_tags' # View uses fid as a "sacrificial" key for Arcmap to ingest q_layer = arcpy.MakeQueryLayer_management("Database Connections/wtg_gdb.sde", layerName, sqlSelect, "fid", "POINT", "4326", sr) # Persist to feature class out_name = 'a'+layerName[1:7] f_layer = arcpy.FeatureToPoint_management (layerName, out_name) else: for ptt, devID in devDict.iteritems(): c1 = ''.join(['tag_id = ', str(devID)]) layerName = 'q_' + str(ptt).zfill(5) sqlSelect = '{0} AND {1}{2}'.format(c0,c1,c2) q_layer = arcpy.MakeQueryLayer_management("Database Connections/wtg_gdb.sde", layerName, sqlSelect, "fid",
connect = arcpy.GetParameterAsText(0) connection = r"'" + connect + "'" queryname = arcpy.GetParameterAsText(1) queryname1 = "'" + queryname + "'" projcode = arcpy.GetParameterAsText(2) projectcode = "'" + projcode + "'" print projectcode fc = 'CL_Vector.OWD.QRY_ACQ_GEOCHRONOLOGY' print queryname print "{0} + {1}".format(fc, projectcode) query = '"select * from {0} where PROJECTCODE = {1}"'.format(fc, projectcode) print query # Run the tool try: arcpy.MakeQueryLayer_management( connection, queryname1, query, oid_fields="OBJECTID", shape_type="POINT", srid="32719", spatial_reference= "PROJCS['WGS_1984_UTM_Zone_19S',GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['False_Easting',500000.0],PARAMETER['False_Northing',10000000.0],PARAMETER['Central_Meridian',-69.0],PARAMETER['Scale_Factor',0.9996],PARAMETER['Latitude_Of_Origin',0.0],UNIT['Meter',1.0]];-5120900 1900 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision" ) except: print connection print queryname1 print query
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 importarFeatureClass(ubigeo, rutaFD): conexionDB = conectionDB_arcpy(ip, nombre) ccpp = arcpy.MakeQueryLayer_management( conexionDB, "CCPP_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.TB_CCPP where UBIGEO = '{}' ". format(ubigeo), 'LLAVE_CCPP', 'POINT', '4326', arcpy.SpatialReference(4326)) aer = arcpy.MakeQueryLayer_management( conexionDB, "AER_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.TB_AER where UBIGEO = '{}'". format(ubigeo), 'UBIGEO;AER_INI;AER_FIN', 'POLYGON', '4326', arcpy.SpatialReference(4326)) distrito = arcpy.MakeQueryLayer_management( conexionDB, "DIST_{}".format(ubigeo), "SELECT*FROM CPV_SEGMENTACION_GDB.sde.TB_LIMITE_DIS WHERE UBIGEO = '{}'" .format(ubigeo), "UBIGEO", "POLYGON", '4326', arcpy.SpatialReference(4326)) track = arcpy.MakeQueryLayer_management( conexionDB, "TRACK_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.TB_TRACK where UBIGEO = '{}'". format(ubigeo), 'ID', 'POLYLINE', '4326', arcpy.SpatialReference(4326)) curvasnivel = arcpy.MakeQueryLayer_management( conexionDB, "CN_{}".format(ubigeo), "SELECT*FROM CPV_SEGMENTACION_GDB.sde.TB_CN WHERE UBIGEO = '{}'". format(ubigeo), "UBIGEO", "POLYLINE", '4326', arcpy.SpatialReference(4326)) hidrografia = arcpy.MakeQueryLayer_management( conexionDB, "HIDRO_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.TB_HIDRO where UBIGEO = '{}'". format(ubigeo), 'UBIGEO', 'POLYLINE', '4326', arcpy.SpatialReference(4326)) ccpp_n = arcpy.MakeQueryLayer_management( conexionDB, "CCPP_N_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.TB_CCPP where UBIGEO = '{}' AND ESTADO = '3'" .format(ubigeo), 'LLAVE_CCPP', 'POINT', '4326', arcpy.SpatialReference(4326)) ruta = arcpy.MakeQueryLayer_management( conexionDB, "RUTA_OLD_{}".format(ubigeo), "SELECT * FROM CPV_SEGMENTACION_GDB.sde.SEGM_R_RUTA where UBIGEO = '{}'" .format(ubigeo), 'IDRUTA', 'POLYGON', '4326', arcpy.SpatialReference(4326)) arcpy.Append_management(ccpp, r'{}\CCPP_{}'.format(rutaFD, ubigeo), "NO_TEST") arcpy.Append_management(aer, r'{}\AER_{}'.format(rutaFD, ubigeo), "NO_TEST") arcpy.Append_management(distrito, r'{}\DIST_{}'.format(rutaFD, ubigeo), "NO_TEST") arcpy.Append_management(track, r'{}\TRACK_{}'.format(rutaFD, ubigeo), "NO_TEST") # **************************************************** arcpy.Append_management(ccpp_n, r'{}\CCPP_N_{}'.format(rutaFD, ubigeo), "NO_TEST") arcpy.Append_management(ruta, r'{}\RUTA_OLD_{}'.format(rutaFD, ubigeo), "NO_TEST") # **************************************************** arcpy.Append_management(curvasnivel, r'{}\CN_{}'.format(rutaFD, ubigeo), "NO_TEST") arcpy.Append_management(hidrografia, r'{}\HIDRO_{}'.format(rutaFD, ubigeo), "NO_TEST")