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")
