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 InsertarRegistros(self):
arcpy.env.workspace = "Database Connections/PruebaSegmentacion.sde"
arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(4326)
if arcpy.Exists("GEODATABASE.sde") == False:
arcpy.CreateDatabaseConnection_management(
"Database Connections", "GEODATABASE.sde", "SQL_SERVER",
self.server, "DATABASE_AUTH", "sde", "$deDEs4Rr0lLo", "#",
"GEODB_CPV_SEGM", "#", "#", "#", "#")
arcpy.env.workspace = "Database Connections/GEODATABASE.sde"
path_conexion2 = "Database Connections/GEODATABASE.sde"
segm_ruta_prueba = path_conexion2 + "/GEODB_CPV_SEGM.SDE.SEGM_RUTA_PRUEBA"
segm_aeu_prueba = path_conexion2 + "/GEODB_CPV_SEGM.SDE.SEGM_AEU_PRUEBA"
list_capas = [[self.tb_rutas, segm_ruta_prueba],
[self.tb_aeus, segm_aeu_prueba]]
i = 0
data = []
for x in arcpy.da.SearchCursor(self.tb_zonas,
["UBIGEO", "ZONA", "ID_ESTRATO"]):
if int(x[2]) == 1:
data.append([
x[0], x[1], self.cant_viv_techo_gra_ciud,
self.techo_segunda_pasada_gra_ciud, self.uso_falso_cod
])
else:
data.append([
x[0], x[1], self.cant_viv_techo_peq_ciud,
self.techo_segunda_pasada_peq_ciud, self.uso_falso_cod
])
where_final = expresiones_consulta_arcpy.Expresion_2(
data, [['UBIGEO', 'TEXT'], ['ZONA', 'TEXT'], ['TECHO', 'SHORT'],
['TECHO_S_P', 'SHORT'], ['FALSO_COD', 'SHORT']])
print where_final
for el in list_capas:
i = i + 1
#a = arcpy.MakeTableView_management(el[1], "a{}".format(i), "({}) and TECHO={}".format(self.where_expression,self.cant_viv_techo_gra_ciud))
a = arcpy.MakeTableView_management(el[1], "a{}".format(i),
where_final)
#print int(arcpy.GetCount_management(a).getOutput(0))
if (int(arcpy.GetCount_management(a).getOutput(0)) > 0):
arcpy.DeleteRows_management(a)
#b = arcpy.MakeTableView_management(el[0], "b{}".format(i), "({}) and TECHO={}".format(self.where_expression,self.cant_viv_techo_gra_ciud) )
b = arcpy.MakeTableView_management(el[0], "b{}".format(i),
where_final)
arcpy.Append_management(b, el[1], "NO_TEST")
def actualizar_monitoreo():
distrito = ""
conn = conx.Conexion()
cursor = conn.cursor()
sql_query = """
select distinct a.ubigeo from
(
select distinct ubigeo from dbo.TB_VIVIENDA_U
union all
select distinct ubigeo from dbo.TB_VIVIENDA_r) a
where ubigeo>'050506'
order by a.ubigeo
"""
cursor.execute(sql_query)
for row in cursor.fetchall():
distrito = "{}".format(row[0])
where = expresion.Expresion_2([[distrito]], [["UBIGEO", "TEXT"]])
print where
importar_tablas(where)
insertar_registros([distrito])
continue
#sql_query2="""UPDATE A
# SET A.AREA=2
# FROM sde.CARTO_ccpp A
#
# UPDATE A
# SET A.AREA=1
# FROM sde.CARTO_ccpp A
# INNER JOIN
# (SELECT DISTINCT UBIGEO ,CODCCPP FROM [LKN_93_SEGM].[CPV_SEGMENTACION].DBO.MARCO_ZONA) B ON A.UBIGEO=B.UBIGEO AND A.CODCCPP=B.CODCCPP"""
#
#cursor.execute(sql_query2)
conn.commit()
conn.close()
def exportar_croquis_urbano_zona_subzona(where_expression):
arcpy.env.workspace =path_ini+""
with arcpy.da.UpdateCursor(tb_subzonas,
['UBIGEO', 'ZONA', 'SUBZONA', 'CODCCPP', 'CANT_VIV', 'CANT_PAG', 'COD_CROQ',
'RUTA_CROQ', 'RUTA_WEB'], where_expression) as cursor:
for row in cursor:
ubigeo=row[0]
ccdd=ubigeo[0:2]
ccpp = ubigeo[2:4]
ccdi = ubigeo[4:6]
zona=row[1]
subzona=row[2]
codccpp = row[3]
cant_viv = row[4]
#######################################Creamos los filtros de la zona############################################################
where_zona = "UBIGEO='{}' AND ZONA='{}'".format(ubigeo,zona)
if subzona>0:
where_subzona = "UBIGEO='{}' AND ZONA='{}' AND SUBZONA={}".format(ubigeo, zona,subzona)
list_secciones=[[x[0],x[1],x[2]] for x in arcpy.da.SearchCursor(tb_secciones,['UBIGEO','ZONA','SECCION'],where_subzona)]
else:
where_subzona = "UBIGEO='{}' AND ZONA='{}'".format(ubigeo, zona)
list_secciones = [[x[0], x[1], x[2]] for x in
arcpy.da.SearchCursor(tb_secciones, ['UBIGEO', 'ZONA', 'SECCION'], where_zona)]
where_secciones=expresion.Expresion_2(list_secciones, [["UBIGEO", "TEXT"], ["ZONA", "TEXT"], ["SECCION", "TEXT"]])
list_aeus=[[x[0],x[1],x[2]] for x in arcpy.da.SearchCursor(tb_aeus,['UBIGEO','ZONA','AEU'],where_secciones)]
where_aeus=expresion.Expresion_2(list_aeus, [["UBIGEO", "TEXT"], ["ZONA", "TEXT"], ["AEU", "TEXT"]])
list_manzanas = [[x[0], x[1], x[2]] for x in arcpy.da.SearchCursor(tb_rutas, ['UBIGEO', 'ZONA', 'MANZANA'], where_aeus)]
where_manzanas = expresion.Expresion_2(list_manzanas, [["UBIGEO", "TEXT"], ["ZONA", "TEXT"], ["MANZANA", "TEXT"]])
for row4 in arcpy.da.SearchCursor(tb_zonas, ['DEPARTAMEN', 'PROVINCIA', 'DISTRITO', 'NOMCCPP'], where_zona):
dep = row4[0]
prov = row4[1]
dist = row4[2]
nomccpp = row4[3]
#########################################Listamos los layers del mxd #####################################
mxd = arcpy.mapping.MapDocument(path_plantillas_croquis + "/CroquisUrbanoZonaCorregido.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
zona_mfl = arcpy.mapping.ListLayers(mxd, "TB_ZONAS")[0]
zona_mfl.definitionQuery=where_zona
mzs_mfl = arcpy.mapping.ListLayers(mxd, "TB_MZS_ORD")[0]
if subzona==0:
mzs_mfl.definitionQuery =" MANZANA='0' "
else:
mzs_mfl.definitionQuery=where_manzanas
list_manzanas_2 = list(set((x[0], x[1], x[2]) for x in arcpy.da.SearchCursor(tb_rutas, ['UBIGEO', 'ZONA', 'MANZANA'], where_aeus)))
cant_secc = int(len(list_secciones))
cant_mzs=int(len(list_manzanas_2))
cant_aeus = int(len(list_aeus))
seccion_inicial_temp = list_secciones[0][2]
seccion_final_temp = list_secciones[-1][2]
aeu_inicial_temp = list_aeus[0][2]
aeu_final_temp = list_aeus[-1][2]
mzs_inicial = list_manzanas[0][2]
mzs_final = list_manzanas[-1][2]
aeu_inicial = str(aeu_inicial_temp).zfill(3)
aeu_final = str(aeu_final_temp).zfill(3)
seccion_inicial = str(seccion_inicial_temp).zfill(3)
seccion_final = str(seccion_final_temp).zfill(3)
codigo = '{}{}{}'.format(ubigeo,zona,subzona)
zona_etiqueta = expresion.EtiquetaZona(zona)
frase = u'<BOL>OBSERVACIONES: </BOL>La Zona Nº {} se inicia en la manzana Nº {} y termina en la manzana Nº {}'.format(zona_etiqueta,mzs_inicial,mzs_final)
#########################################Asignado los valores de las variables de los croquis ################################################
list_text_el=[["COD_BARRA","*{}*".format(codigo)],["TEXT_COD_BARRA","*{}*".format(codigo)],["CCDD",ccdd],["CCPP",ccpp],["CCDI",ccdi],["CODCCPP",codccpp],
["DEPARTAMENTO",dep],["PROVINCIA",prov],["DISTRITO",dist],["NOMCCPP",nomccpp],["ZONA",zona_etiqueta],["SUBZONA",subzona],["SECCION_INICIAL",seccion_inicial],
["SECCION_FINAL",seccion_final],["AEU_INICIAL",aeu_inicial],["AEU_FINAL",aeu_final],["CANT_VIV",cant_viv],["FRASE",frase]]
for text_el in list_text_el:
el=arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT", text_el[0])[0]
el.text=text_el[1]
#######################################Asignamos la escala del dibujo#################################################
df.extent = zona_mfl.getSelectedExtent()
################################################Obtemos la informacion del listado####################################################
cabecera=[ccdd,dep,ccpp,prov,ccdi,dist,codccpp,nomccpp,'CIUDAD',zona_etiqueta,subzona, u'DEL {} AL {}'.format(seccion_inicial, seccion_final),u'DEL {} AL {} '.format(aeu_inicial,aeu_final),cant_viv]
data=[]
for x in arcpy.da.SearchCursor(tb_aeus,['UBIGEO','ZONA','SECCION','AEU','CANT_VIV'],where_secciones):
mzs=""
i=0
for y in arcpy.da.SearchCursor(tb_rutas,['MANZANA'],"UBIGEO='{}' AND ZONA='{}' AND AEU='{}'".format(x[0],x[1],x[3])):
i=i+1
if (i==1):
mzs=u"{}".format(y[0])
else:
mzs = u"{}-{}".format(mzs,y[0])
data.append(['{}'.format(x[2]).zfill(3),'{}'.format(x[3]).zfill(3),mzs,x[4] ] )
resumen=[cant_secc,cant_mzs,cant_aeus]
informacion=[cabecera,data,resumen]
########################################Exportamos los croquis y listados####################################################
out_croquis = '{}\\{}\\{}\\{}.pdf'.format(path_urbano_croquis,ubigeo,zona,codigo)
out_listado = '{}\\{}\\{}\\{}.pdf'.format(path_urbano_listados, ubigeo, zona, codigo)
out_final= '{}\\{}\\{}\\{}.pdf'.format(path_urbano_croquis_listado, ubigeo, zona, codigo)
arcpy.mapping.ExportToPDF(mxd, out_croquis, "PAGE_LAYOUT")
error_export_pdf(path_urbano_croquis, ubigeo, zona, codigo, out_croquis, mxd)
listado.ListadoZona(informacion, out_listado)
pdfDoc = arcpy.mapping.PDFDocumentCreate(out_final)
pdfDoc.appendPages(out_croquis)
pdfDoc.appendPages(out_listado)
pdfDoc.saveAndClose()
[cant_pag,nomb_web]=obtener_datos_pdf(out_final)
row[5]=cant_pag
row[6]=codigo
row[7]=out_final
row[8]=nomb_web
cursor.updateRow(row)
del mxd
del df
del pdfDoc
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 exportar_croquis_emp_especial(tipo,informacion,out_croquis):
list_manzanas = []
cab = informacion[0]
ccdd = cab[0]
dep = cab[1]
ccpp = cab[2]
prov = cab[3]
ccdi = cab[4]
dist = cab[5]
codccpp = cab[6]
nomccpp = cab[7]
etiq_zona = cab[8]
subzona=cab[9]
zona = cab[10]
cant_viv=cab[11]
ubigeo=ccdd+ccpp+ccdi
for fila in informacion[1]:
manzana=fila[2]
list_manzanas.append([ubigeo,zona,manzana])
where_zona=expresion.Expresion_2([[ubigeo,zona] ],[ ["UBIGEO","TEXT"],["ZONA","TEXT"]])
where_manzanas=expresion.Expresion_2(list_manzanas,[["UBIGEO","TEXT"],["ZONA","TEXT"],['MANZANA','TEXT']])
#########################################Listamos los layers del mxd #####################################
mxd = arcpy.mapping.MapDocument(path_plantillas_croquis + "/CroquisUrbanoZonaEmpEspecial.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
zona_mfl = arcpy.mapping.ListLayers(mxd, "TB_ZONAS")[0]
mzs_mfl = arcpy.mapping.ListLayers(mxd, "TB_MZS_ORD")[0]
print where_zona
print where_manzanas
mzs_mfl.definitionQuery = where_manzanas
zona_mfl.definitionQuery=where_zona
#########################################Asignado los valores de las variables de los croquis ################################################
if tipo==1:
titulo_croquis=u"CROQUIS DE VIVIENDAS COLECTIVAS INSTITUCIONALES DE LA ZONA CENSAL"
cod_documento=u"Doc. CPV.03.34"
etiq_total_viv=u"TOTAL DE VIVIENDAS COLECTIVAS"
elif tipo==2:
titulo_croquis = u"CROQUIS DE VIVIENDAS COLECTIVAS NO INSTITUCIONALES DE LA ZONA CENSAL"
cod_documento=u"Doc. CPV.03.34A"
etiq_total_viv = u"TOTAL DE VIVIENDAS COLECTIVAS"
else:
titulo_croquis = u"CROQUIS DE EMPRESAS E INSTITUCIONES DE LA ZONA CENSAL"
cod_documento = u"Doc. CPV.03.34B"
etiq_total_viv = u"TOTAL DE EMPRESAS E INSTITUCIONES"
list_text_el = [["titulo_croquis",titulo_croquis],["cod_documento",cod_documento],
["CCDD", ccdd],["CCPP", ccpp], ["CCDI", ccdi], ["CODCCPP", codccpp],
["DEPARTAMENTO", dep], ["PROVINCIA", prov], ["DISTRITO", dist], ["NOMCCPP", nomccpp],
["ZONA", etiq_zona], ["SUBZONA", subzona],["CANT_VIV", cant_viv],["etiq_total_viv",etiq_total_viv] ]
for text_el in list_text_el:
el = arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT", text_el[0])[0]
el.text = text_el[1]
#######################################Asignamos la escala del dibujo#################################################
df.extent = zona_mfl.getSelectedExtent()
arcpy.mapping.ExportToPDF(mxd, out_croquis, "PAGE_LAYOUT")
u.error_export_pdf(out_croquis, mxd)
del mxd
del df
def procesar_calidad(cant_zonas=0, data=[], campos=['UBIGEO', 'ZONA']):
if len(data) == 0:
data = conex.obtener_lista_zonas_calidad(cant_zonas)[:]
importar_tablas_trabajo(data, campos)
where = expresiones_consulta_arcpy.Expresion(data, campos)
arcpy.AddField_management(tb_viviendas_ordenadas, 'IDMANZANA', 'TEXT')
arcpy.CalculateField_management(tb_viviendas_ordenadas, 'IDMANZANA',
'!UBIGEO!+!ZONA!+!MANZANA!', 'PYTHON_9.3')
# print "Importar"
list_zonas = [(x[0], x[1])
for x in arcpy.da.SearchCursor(tb_zonas, ["UBIGEO", "ZONA"])]
######################################################CALIDAD PUERTAS MULTIFAMILIAR AFUERA DEL FRENTE DE MANZANA############################################################
arcpy.AddField_management(tb_viviendas_ordenadas, 'IDMANZANA', 'TEXT')
arcpy.CalculateField_management(tb_viviendas_ordenadas, 'IDMANZANA',
'!UBIGEO!+!ZONA!+!MANZANA!', 'PYTHON_9.3')
manzanas_mfl = arcpy.MakeFeatureLayer_management(tb_manzanas,
"manzanas_mfl", where)
viviendas_mfl = arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas,
"viviendas_mfl", where)
frentes_mfl = arcpy.MakeFeatureLayer_management(tb_frentes, "frentes_mfl",
where)
mzs_line = arcpy.FeatureToLine_management(manzanas_mfl,
"in_memory/mzs_line")
puertas_multifamiliar = arcpy.MakeFeatureLayer_management(
tb_viviendas_ordenadas, "puertas_multifamiliar", "p29=6")
puertas_multifamiliar_afuera = arcpy.SelectLayerByLocation_management(
puertas_multifamiliar, "INTERSECT", mzs_line, '', "NEW_SELECTION",
"INVERT")
viviendas_selecc_frentes_mfl = arcpy.SelectLayerByLocation_management(
viviendas_mfl, "INTERSECT", mzs_line)
viviendas_selecc_frentes = arcpy.CopyFeatures_management(
viviendas_selecc_frentes_mfl, "in_memory/viv_selecc_frentes")
arcpy.CopyFeatures_management(puertas_multifamiliar_afuera, error_1)
########################################LISTA ZONAS CON ERROR PUERTA MULTIFAMILIAR###############################
list_1 = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_1, ["UBIGEO", "ZONA"])]))
zonas_error_puertas_multi = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_1, ["UBIGEO", "ZONA"])]))
# print zonas_error_puertas_multi
#####################################################CALIDAD EXISTENCIA DE EJES VIALES POR ZONA#######################################################################
# tb_ejes_viales
ejes_viales_mfl = arcpy.MakeFeatureLayer_management(
tb_ejes_viales, "ejes_viales_mfl")
manzanas_sin_vias = arcpy.SelectLayerByLocation_management(
manzanas_mfl, "INTERSECT", ejes_viales_mfl, "20 METERS",
"NEW_SELECTION", "INVERT")
arcpy.CopyFeatures_management(manzanas_sin_vias, error_2)
######################################LISTA DE ZONAS SIN EJES VIALES#############################################
#list_2 = []
#for x in arcpy.da.SearchCursor(tb_zonas, ["UBIGEO", "ZONA"]):
# where = " UBIGEO='{}' AND ZONA='{}'".format(x[0], x[1])
# manzanas_mfl = arcpy.MakeFeatureLayer_management(tb_manzanas, "manzanas_mfl", where)
# manzanas_sin_vias_mfl = arcpy.MakeFeatureLayer_management(error_2, "manzanas_sin_vias_mfl", where)
# a = int(arcpy.GetCount_management(manzanas_mfl).getOutput(0))
# b = int(arcpy.GetCount_management(manzanas_sin_vias_mfl).getOutput(0))
# if a != 0:
# porcentaje = b / float(a) * 100
#
# else:
# porcentaje = 100
#
# if porcentaje > 10:
# list_2.append((x[0], x[1]))
##################################################CALIDAD MANZANAS INTERSECTADO CON VIAS########################################
line_mzs = arcpy.FeatureToLine_management(tb_manzanas_ordenadas,
"in_memory/line_mzs")
buffer_line = arcpy.Buffer_analysis(line_mzs, "in_memory/buffer_line",
"0.50 meters")
mzs_cortadas = arcpy.Erase_analysis(tb_manzanas_ordenadas, buffer_line,
"in_memory/erase_mzs")
#manzanas_ordenadas_mfl = arcpy.MakeFeatureLayer_management(tb_manzanas_ordenadas, "manzanas_ordenadas_mfl")
manzanas_cortadas_mfl = arcpy.MakeFeatureLayer_management(
mzs_cortadas, "mzs_cortadas_mfl")
#vias_dentro_manzana = arcpy.SelectLayerByLocation_management(manzanas_ordenadas_mfl, "INTERSECT", tb_ejes_viales,'', "NEW_SELECTION")
vias_dentro_manzana = arcpy.SelectLayerByLocation_management(
manzanas_cortadas_mfl, "INTERSECT", tb_ejes_viales, '',
"NEW_SELECTION")
arcpy.CopyFeatures_management(vias_dentro_manzana, error_3)
#########################################LISTA DE ZONAS CON VIAS DENTRO DE MANZANAS###################################
list_3 = []
if (int(arcpy.GetCount_management(error_3).getOutput(0)) > 0):
list_3 = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_3, ["UBIGEO", "ZONA"])]))
#################Calidad Viviendas afuera de la manzana#################################################
viviendas_mfl = arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas,
"viviendas_mfl", where)
viviendas_afuera_manzana = arcpy.SelectLayerByLocation_management(
viviendas_mfl, "INTERSECT", tb_manzanas_ordenadas, '0.2 meters',
"NEW_SELECTION", "INVERT")
arcpy.CopyFeatures_management(viviendas_afuera_manzana, error_5)
##########################################LISTA DE ZONAS CON VIVIENDAS FUERA DE MANZANA#################
list_4 = []
if (int(arcpy.GetCount_management(error_5).getOutput(0)) > 0):
list_4 = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_5, ["UBIGEO", "ZONA"])]))
#################################################CALIDAD PUNTOS DE INICIO#######################################################################
lineas_viviendas = arcpy.PointsToLine_management(
viviendas_selecc_frentes, 'in_memory/lineas_viviendas', "IDMANZANA",
"ID_REG_OR")
puntos_extremos = arcpy.FeatureVerticesToPoints_management(
lineas_viviendas, 'in_memory/puntos_extremos', "BOTH_ENDS")
puntos_extremos_buffer = arcpy.Buffer_analysis(
puntos_extremos, 'in_memory/puntos_extremos_buffer', "0.2 meters")
erase_lineas = arcpy.Erase_analysis(mzs_line, puntos_extremos_buffer,
'in_memory/erase_lineas')
split = arcpy.SplitLine_management(erase_lineas, "in_memory/split")
dissolve = arcpy.Dissolve_management(split, "in_memory/dissolve",
"UBIGEO;CODCCPP;ZONA;MANZANA", "",
"MULTI_PART", "DISSOLVE_LINES")
dissolve_multi = arcpy.MultipartToSinglepart_management(
dissolve, "in_memory/dissolve_multi")
dissolve_mfl = arcpy.MakeFeatureLayer_management(dissolve_multi,
'dissolve_mfl')
puntos_inicio_mfl = arcpy.MakeFeatureLayer_management(
tb_puntos_inicio, 'puntos_inicio_mfl')
segmentos_selec = arcpy.SelectLayerByLocation_management(
dissolve_mfl, "INTERSECT", tb_viviendas_ordenadas, '', "NEW_SELECTION",
"INVERT")
tb_segmentos_selec = arcpy.CopyFeatures_management(
segmentos_selec, "{}/tb_segmentos_selec.shp".format(path_ini))
puntos_inici_selec = arcpy.SelectLayerByLocation_management(
puntos_inicio_mfl, "INTERSECT", tb_segmentos_selec, '',
"NEW_SELECTION", "INVERT")
arcpy.CopyFeatures_management(puntos_inici_selec, error_4)
################################################LISTA DE ZONAS CON PROBLEMAS DE PUNTO DE INICIO##################################################
list_5 = []
if (int(arcpy.GetCount_management(error_4).getOutput(0)) > 0):
list_5 = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_4, ["UBIGEO", "ZONA"])]))
############################ Cantidad de frentes############################################################
'''
resumen_frentes_viv=arcpy.Statistics_analysis(tb_viviendas_ordenadas,'in_memory/resumen_frentes_viv',[["FRENTE_ORD","MAX"]],["UBIGEO","ZONA","MANZANA"])
arcpy.AddField_management(resumen_frentes_viv,"ID_MANZANA","text")
with arcpy.da.UpdateCursor(resumen_frentes_viv, ["UBIGEO","ZONA","MANZANA","ID_MANZANA"]) as cursor:
for x in cursor:
x[4]=u'{}{}{}'.format(x[0],x[1],x[2])
cursor.updateRow(x)
#arcpy.CalculateField_management(resumen_frentes_viv,"ID_MANZANA","!UBIGEO!+!ZONA!+!MANZANA!","PYTHON_9.3")
resumen_frentes = arcpy.Statistics_analysis(tb_frentes_dissolve, 'in_memory/resumen_frentes',[["FRENTE_ORD", "MAX"],["FRENTE_ORD", "COUNT"]], ["UBIGEO", "ZONA", "MANZANA"])
arcpy.AddField_management(resumen_frentes, "ID_MANZANA", "text")
with arcpy.da.UpdateCursor(resumen_frentes, ["UBIGEO","ZONA","MANZANA","ID_MANZANA"]) as cursor:
for x in cursor:
x[4]=u'{}{}{}'.format(x[0],x[1],x[2])
cursor.updateRow(x)
arcpy.CalculateField_management(resumen_frentes, "ID_MANZANA", "!UBIGEO!+!ZONA!+!MANZANA!", "PYTHON_9.3")
arcpy.JoinField_management(resumen_frentes,"ID_MANZANA",resumen_frentes_viv,"ID_MANZANA",["MAX_FRENTE_ORD"])
mzs_dif_cant_frent=arcpy.TableSelect_analysis(resumen_frentes, error_6, " (MAX_FRENTE_ORD<>MAX_FRENTE_ORD_1)")
arcpy.AddField_management(error_6, "CANT_FR_V", "SHORT")
arcpy.CalculateField_management(error_6, "CANT_FR_V", "!MAX_FRENTE_ORD!")
arcpy.AddField_management(error_6, "CANT_FR_F", "text")
arcpy.CalculateField_management(error_6, "CANT_FR_F", "!MAX_FRENTE_ORD_1!")
arcpy.DeleteField_management(error_6,["MAX_FRENTE_ORD","MAX_FRENTE_ORD_1"])
list_6=[]
if (int(arcpy.GetCount_management(error_6).getOutput(0)) > 0):
list_6 = list(set([(x[0], x[1]) for x in arcpy.da.SearchCursor(error_6, ["UBIGEO", "ZONA"])]))
#mzs_dif_cant_frent_1 = arcpy.TableSelect_analysis(resumen_frentes, error_7_cant_frentes_dif, " CapVivNFr<>COUNT_FRENTE_ORD")
#list_7 = []
#if (int(arcpy.GetCount_management(error_7_cant_frentes_dif).getOutput(0)) > 0):
# list_7 = list(set([(x[0], x[1]) for x in arcpy.da.SearchCursor(error_7_cant_frentes_dif, ["UBIGEO", "ZONA"])]))
#arcpy.SelectLayerByLocation_management
'''
#####################################################ERROR DE FRENTE DE VIVIENDAS#########################################################
resultado = arcpy.Intersect_analysis([tb_viviendas_ordenadas, tb_frentes],
'in_memory/results')
arcpy.Select_analysis(resultado, error_7, 'FRENTE_ORD<>FRENTE_ORD_1')
fields = arcpy.ListFields(error_7)
list_campos_validos = [
'FID', 'Shape', 'UBIGEO', 'CODCCPP', 'ZONA', 'MANZANA', 'ID_REG_OR',
'FRENTE_ORD'
]
delete_fields = []
for el in fields:
if el.name not in list_campos_validos:
delete_fields.append(el.name)
arcpy.DeleteField_management(error_7, delete_fields)
#####################################################ERROR FRENTES DE MANZANAS NO COINCIDEN CON LA MANZANA EN FORMA#################################
temp_frentes = arcpy.SelectLayerByLocation_management(
frentes_mfl, "WITHIN", mzs_line, '', "NEW_SELECTION", "INVERT")
arcpy.CopyFeatures_management(temp_frentes, error_8)
list_8 = []
if (int(arcpy.GetCount_management(error_8).getOutput(0)) > 0):
list_8 = list(
set([(x[0], x[1])
for x in arcpy.da.SearchCursor(error_8, ["UBIGEO", "ZONA"])]))
####################################################ERROR NUMERACION DE VIVIENDAS#############################################################
lineas_viviendas = arcpy.PointsToLine_management(
viviendas_selecc_frentes, 'in_memory/lineas_viviendas', "IDMANZANA",
"ID_REG_OR")
viviendas_selecc_frentes_buffer = arcpy.Buffer_analysis(
viviendas_selecc_frentes, "in_memory/puntos_extremos_buffer",
"0.2 meters")
erase_lineas = arcpy.Erase_analysis(lineas_viviendas,
viviendas_selecc_frentes_buffer,
'in_memory/erase_lineas')
split = arcpy.SplitLine_management(erase_lineas, path_ini + "/split.shp")
mz_line_erase = arcpy.Erase_analysis(mzs_line,
viviendas_selecc_frentes_buffer,
"in_memory\mz_line_erase")
mz_line_erase_multi = arcpy.MultipartToSinglepart_management(
mz_line_erase, 'in_memory\m_l_e_m')
result = arcpy.Statistics_analysis(mz_line_erase_multi, 'in_memory/result',
[['FID', "MAX"]], ["Shape"])
maxids = [[
x[0]
] for x in arcpy.da.SearchCursor(result, ["MAX_FID"], 'FREQUENCY>1')]
if len(maxids) == 0:
where_ids = expresiones_consulta_arcpy.Expresion_2([["-1"]],
[["FID", "SHORT"]])
else:
where_ids = expresiones_consulta_arcpy.Expresion_2(
maxids, [["FID", "SHORT"]])
arcpy.Select_analysis(mz_line_erase_multi, error_9, where_ids)
'''
intersect=arcpy.Intersect_analysis([mz_line_erase_multi, split], path_ini+"/intersect.shp", "ALL", "", "")
list_id_buffer_mzs_line_erase_multi=list(set( [x[0] for x in arcpy.da.SearchCursor(intersect,["FID_m_l_e_"])]))
list_intersect= [x[0] for x in arcpy.da.SearchCursor(intersect,["FID_m_l_e_"])]
errores_numeracion=[]
print list_id_buffer_mzs_line_erase_multi
print list_intersect
for x in list_id_buffer_mzs_line_erase_multi:
cont = 0
for y in list_intersect:
if (x==y):
cont=cont+1
#print cont
if (cont>1):
errores_numeracion.append([x])
print errores_numeracion
where_exp=UBIGEO.Expresion_2(errores_numeracion,[["FID","SHORT"]])
b_m_l_e_m_selecc = arcpy.Select_analysis(mz_line_erase_multi, error_9, where_exp)
list_9=[]
if (int(arcpy.GetCount_management(error_9).getOutput(0)) > 0):
list_9 = list(set([(x[0], x[1]) for x in arcpy.da.SearchCursor(error_9, ["UBIGEO", "ZONA"])]))
#dissolve = arcpy.Dissolve_management(split, "in_memory/dissolve", "UBIGEO;CODCCPP;ZONA;MANZANA", "","MULTI_PART","DISSOLVE_LINES")
#dissolve_multi=arcpy.MultipartToSinglepart_management(dissolve, "in_memory/dissolve_multi")
#arcpy.SelectLayerByLocation_management (dissolve_multi, "INTERSECT",dissolve_multi)
#arcpy.MultipartToSinglepart_management("intersect", "in_memory/intersect2")
'''
#################################################VIVIENDAS Y VIAS#####################################################
# list_zonas_error=list(set(list_1+list_2+list_3+list_4+list_5+list_6+list_8+list_9))
# print list_zonas_error
#nombre_ejes_viales()
################################puertas hijos multifamiliar en el frente##########################
puertas_hijos_multifamilar = arcpy.MakeFeatureLayer_management(
tb_viviendas_ordenadas, "puertas_multifamiliar",
"(p29=1 or p29=3) and ID_REG_PAD<>0 ")
error_11_mfl = arcpy.SelectLayerByLocation_management(
puertas_hijos_multifamilar, "INTERSECT", mzs_line, '', "NEW_SELECTION")
arcpy.CopyFeatures_management(error_11_mfl, error_11)
# puertas_hijos_multifamilar=arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas, "puertas_multifamiliar", "(p29=1 or p29=3) and ID_REG_PAD<>0 ")
# error_11_mfl=arcpy.SelectLayerByLocation_management(puertas_hijos_multifamilar, "INTERSECT",mzs_line ,'' , "NEW_SELECTION")
# arcpy.CopyFeatures_management(error_11_mfl, error_11)
###############################################ERROR HIJOS SIN PADRES#########################################################################
'''
puertas_hijos_multifamilar = arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas, "puertas_multifamiliar",
"(p29=1 or p29=3) and (ID_REG_PAD<>0)" )
list_puertas_hijos_multifamilar=[[x[0],x[1],x[2],x[3],x[4]] for x in arcpy.da.SearchCursor(puertas_hijos_multifamilar,["UBIGEO","ZONA","MANZANA","ID_REG_OR","ID_REG_PAD"])]
list_puertas_multifamiliar=[ '{}{}{}{}'.format(x[0],x[1],x[2],x[3]) for x in arcpy.da.SearchCursor(puertas_multifamiliar,["UBIGEO","ZONA","MANZANA","ID_REG"])]
where_error_12=""
i=0
for el in list_puertas_hijos_multifamilar:
i=i+1
id_padre='{}{}{}{}'.format(el[0],el[1],el[2],el[4])
if id_padre not in list_puertas_multifamiliar:
if i==1:
where_error_12=" (UBIGEO='{}' AND ZONA='{}' AND MANZANA='{}' AND ID_REG_OR={})".format(el[0],el[1],el[2],el[3])
else:
where_error_12 = "{} OR (UBIGEO='{}' AND ZONA='{}' AND MANZANA='{}' AND ID_REG_OR={})".format(where_error_12,el[0], el[1],
el[2], el[3])
error_12_mfl=arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas, "error_12",where_error_12 )
arcpy.CopyFeatures_management(error_12_mfl, error_12)
#############################ERROR PUERTAS MULTIFAMILIAR CON MAS DE 2 GEOMETRIAS#########################################
set_puertas_multi=set(list_puertas_multifamiliar)
where_error_13=""
j=0
for el in set_puertas_multi:
i=0
if el in list_puertas_multifamiliar:
i=i+1
if i>1:
j=j+1
if (j==1):
where_error_13 = " (UBIGEO='{}' AND ZONA='{}' AND MANZANA='{}' AND ID_REG_OR={})".format(el[0], el[1],el[2], el[3])
else:
where_error_13 = "{} OR (UBIGEO='{}' AND ZONA='{}' AND MANZANA='{}' AND ID_REG_OR={})".format(where_error_13,el[0], el[1],
el[2], el[3])
error_13_mfl = arcpy.MakeFeatureLayer_management(tb_viviendas_ordenadas, "error_13", where_error_13)
arcpy.CopyFeatures_management(error_13_mfl, error_13)
'''
################################Insercion de data###########################################
arcpy.env.workspace = "Database Connections/PruebaSegmentacion.sde"
arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(4326)
if arcpy.Exists("GEODATABASE.sde") == False:
arcpy.CreateDatabaseConnection_management(
"Database Connections", "GEODATABASE.sde", "SQL_SERVER", ip_server,
"DATABASE_AUTH", "sde", "$deDEs4Rr0lLo", "#", "GEODB_CPV_SEGM",
"#", "#", "#", "#")
arcpy.env.workspace = "Database Connections/GEODATABASE.sde"
path_conexion2 = "Database Connections/GEODATABASE.sde"
path_calidad = path_conexion2 + "/GEODB_CPV_SEGM.SDE.CALIDAD_URBANO"
calidad_error_1_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_1_INPUT_PUERTA_MULTIFAMILIAR_DENTRO_MZ'
calidad_error_2_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_2_INPUT_MANZANAS_SIN_VIAS'
calidad_error_3_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_3_INPUT_MANZANAS_VIAS_DENTRO'
calidad_error_4_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_4_INPUT_PUNTOS_INICIO'
calidad_error_5_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_5_INPUT_VIVIENDAS_AFUERA_MZ'
calidad_error_7_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_7_INPUT_VIVIENDAS_ERROR_FRENTE'
calidad_error_8_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_8_INPUT_FRENTES_MANZANAS_FORMA'
calidad_error_9_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_9_INPUT_ENUMERACION_VIV_POR_FRENTE'
calidad_error_10_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_10_INPUT_VIV_ERROR_NOMBRE_VIA'
calidad_error_11_input = path_calidad + '/GEODB_CPV_SEGM.SDE.ERROR_11_INPUT_PUERTAS_HIJOS_MULTI_EN_FRENTE_MZ'
#error_7 = path_calidad + "/error_7_viviendas_error_frente.shp"
list_errores = [
[error_1, calidad_error_1_input, 1],
[error_2, calidad_error_2_input, 1],
[error_3, calidad_error_3_input, 1],
[error_4, calidad_error_4_input, 1],
[error_5, calidad_error_5_input, 1],
[error_8, calidad_error_8_input, 1],
[error_9, calidad_error_9_input, 1],
# [error_10, calidad_error_10_input, 1],
[error_11, calidad_error_11_input, 1],
[error_7, calidad_error_7_input, 1],
]
conn = conex.Conexion2()
cursor = conn.cursor()
for el in data:
ubigeo = el[0]
zona = el[1]
sql_query = """
DELETE GEODB_CPV_SEGM.SDE.ERROR_1_INPUT_PUERTA_MULTIFAMILIAR_DENTRO_MZ where ubigeo='{ubigeo}' and zona='{zona}'
DELETE GEODB_CPV_SEGM.SDE.ERROR_2_INPUT_MANZANAS_SIN_VIAS where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_3_INPUT_MANZANAS_VIAS_DENTRO where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_4_INPUT_PUNTOS_INICIO where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_5_INPUT_VIVIENDAS_AFUERA_MZ where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_7_INPUT_VIVIENDAS_ERROR_FRENTE where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_8_INPUT_FRENTES_MANZANAS_FORMA where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_9_INPUT_ENUMERACION_VIV_POR_FRENTE where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_10_INPUT_VIV_ERROR_NOMBRE_VIA where ubigeo='{ubigeo}' and zona='{zona}'
delete GEODB_CPV_SEGM.SDE.ERROR_11_INPUT_PUERTAS_HIJOS_MULTI_EN_FRENTE_MZ where ubigeo='{ubigeo}' and zona='{zona}'
""".format(ubigeo=ubigeo, zona=zona)
cursor.execute(sql_query)
conn.commit()
conn.close()
i = 0
for el in list_errores:
i = i + 1
print el[0]
if (int(el[2]) > 1):
a = arcpy.MakeTableView_management(
el[0],
"a{}".format(i),
)
else:
a = arcpy.MakeFeatureLayer_management(el[0], "a{}".format(i))
arcpy.Append_management(a, el[1], "NO_TEST")
#for el in list_errores:
# i = i + 1
#
# print where
#
# if el[2] == 1:
# a = arcpy.arcpy.MakeFeatureLayer_management(el[1], "a{}".format(i), where)
# else:
# a = arcpy.MakeTableView_management(el[1], "a{}".format(i), where)
#
# if (int(arcpy.GetCount_management(a).getOutput(0)) > 0):
# arcpy.DeleteRows_management(a)
#
# print 'borro'
# if el[2] == 1:
# b = arcpy.arcpy.MakeFeatureLayer_management(el[0], "b{}".format(i), where)
# else:
# b = arcpy.MakeTableView_management(el[0], "b{}".format(i), where)
#
# if (int(arcpy.GetCount_management(b).getOutput(0)) > 0):
# arcpy.Append_management(b, el[1], "NO_TEST")
# print 'inserto'
for el in data:
conex.actualizar_errores_input_adicionales(ubigeo=el[0], zona=el[1])