import pandas as pd from geopy.geocoders import Nominatim from sqlalchemy import create_engine, text from sqlalchemy.exc import SQLAlchemyError from starlette.config import Config import pathlib #import config import pycountry import requests import json import time import h3 from shapely.geometry import Polygon, Point import math from app.core.database.connections import get_session,get_engine from app.core.database.connections import get_db,get_db_new from app.apis.apiV1.Controllers.TaskController import update from datetime import datetime, timedelta from sqlalchemy import inspect from psycopg2 import sql from sqlalchemy.orm import Session from sqlalchemy import create_engine #import LoadService as ls GEO_TYPE_POINT = 'point' GEO_TYPE_POLYGON = 'polygon' GEO_TYPE_POINT = ("apple", "banana", "cherry") geolocator = Nominatim(user_agent="geoapi") created_tables = set() def obtener_codigo_iso(lat, lon): try: location = geolocator.reverse((lat, lon), language='en') if location: address = location.raw.get('address', {}) country_code_v2 = address.get('country_code', '').upper() # Obtener el código ISO de 2 caracteres country_code_v3 = address.get('ISO3166-1-Alpha-3', '').upper() # Obtener el código ISO de 3 caracteres si existe # Verificar si country_code_v2 es válido if not country_code_v3: # Si el código de 3 caracteres no está disponible, podemos usar una biblioteca adicional para convertirlo from pycountry import countries try: country_code_v3 = countries.get(alpha_2=country_code_v2).alpha_3 except AttributeError: print(f"No se pudo encontrar el código ISO de 3 caracteres para el país con código de 2 caracteres '{country_code_v2}'") country_code_v3 = None return country_code_v2, country_code_v3 return None, None except Exception as e: print(f"Error al obtener códigos ISO para ({lat}, {lon}): {e}") return None, None def create_polygon_table_if_not_exists(engine, iso_code_v2): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas table_name = f"polygon.polygon._{iso_code_v2_lower}" # Crear la tabla create_table_query = f""" CREATE TABLE IF NOT EXISTS polygon._{iso_code_v2_lower} ( "index" numeric NULL, geometry varchar(512) NULL, un varchar NULL, lat float8 NULL, lon float8 NULL, area numeric NULL, fips varchar NULL, iso2 varchar NULL, iso3 varchar NULL, "name" varchar NULL, region varchar NULL, pop2005 varchar NULL, subregion varchar NULL, id_local varchar NULL, color varchar NULL, radius numeric NULL, user_id varchar NULL, polygon polygon.geometry NULL, centro polygon.geometry NULL, h3_polyfill varchar NULL, "data" jsonb NULL, id int4 NOT NULL, CONSTRAINT polygon_{iso_code_v2_lower}_pk PRIMARY KEY (id) ); """ try: with engine.connection() as conn: # Ejecutar la consulta para crear la tabla conn.execute(text(create_table_query)) conn.commit() created_tables.add(table_name) except SQLAlchemyError as e: print(f"Error al crear la tabla o el índice: {e}") def crear_tabla_si_no_existe(engine, iso_code_v2): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas table_name = f"polygon.point_{iso_code_v2_lower}" # Crear la tabla create_table_query = f""" CREATE TABLE IF NOT EXISTS polygon.point_{iso_code_v2_lower} ( id serial4 NOT NULL, iso_code_v2_country varchar(2) NULL, iso_code_v3_country varchar(3) NULL, rel_h_m float4 NULL, point polygon.geometry NOT NULL, data jsonb NULL, created_at timestamptz NULL, updated_at timestamptz NULL, deleted_at timestamptz NULL, code varchar NULL, muni_id varchar NULL, CONSTRAINT point_{iso_code_v2_lower}_pk PRIMARY KEY (id) ); """ try: with engine.connection() as conn: # Ejecutar la consulta para crear la tabla conn.execute(text(create_table_query)) conn.commit() created_tables.add(table_name) except SQLAlchemyError as e: print(f"Error al crear la tabla o el índice: {e}") def insert_data_polygon(engine, iso_code_v2, iso_code_v3, lat, lon,data,polygon): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas insert_query = f""" INSERT INTO polygon.polygon_{iso_code_v2_lower} (iso2, iso3, polygon, centro, "data") VALUES (:iso_code_v2, :iso_code_v3,:polygon, polygon.ST_Point(:lon, :lat),:data); """ try: engine.execute(text(insert_query), {"iso_code_v2": iso_code_v2, "iso_code_v3": iso_code_v3, "polygon":polygon,"lat": lat, "lon": lon,"data": json.dumps(data) }) engine.commit() return True # Indicador de que no se insertó except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") def update_data_polygon(engine, iso_code_v2,data,id): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas insert_query = f""" UPDATE polygon.polygon_{iso_code_v2_lower} SET "data"=:data WHERE id=:id; """ try: db = next(get_db()) db.execute(text(insert_query), {"data": json.dumps(data), "id": id}) #db.execute(text(insert_query), {"iso_code_v2": iso_code_v2, "iso_code_v3": iso_code_v3, "lat": lat, "lon": lon,"data":data }) db.commit() db.close() return True # Indicador de que no se insertó except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") def update_data_point(engine, iso_code_v2,data,id): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas insert_query = f""" UPDATE polygon.point_{iso_code_v2_lower} SET "data"=:data WHERE id=:id; """ try: db = next(get_db()) db.execute(text(insert_query), {"data": json.dumps(data), "id": id}) #db.execute(text(insert_query), {"iso_code_v2": iso_code_v2, "iso_code_v3": iso_code_v3, "lat": lat, "lon": lon,"data":data }) db.commit() db.close() return True # Indicador de que no se insertó except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") # Insertar datos en la tabla def insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,data): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas insert_query = f""" INSERT INTO polygon.point_{iso_code_v2_lower} (iso_code_v2_country, iso_code_v3_country, point, data, created_at, updated_at) VALUES (:iso_code_v2, :iso_code_v3, polygon.ST_Point(:lon, :lat), :data, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); """ check_exists_query = f""" SELECT COUNT(*) FROM polygon.point_{iso_code_v2_lower} WHERE polygon.ST_Equals(point, polygon.ST_Point(:lon, :lat)); """ data =json.dumps(data) print(565) try: # db=get_db() db = next(get_db()) db.execute(text(insert_query), {"iso_code_v2": iso_code_v2, "iso_code_v3": iso_code_v3, "lat": lat, "lon": lon,"data":data }) db.commit() db.close() return True # Indicador de que no se insertó #print("Datos insertados con éxito.") except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") def crear_indice_si_no_existe(engine, iso_code_v2): iso_code_v2_lower = iso_code_v2.lower() check_index_query = f""" SELECT 1 FROM pg_indexes WHERE tablename = 'point_{iso_code_v2_lower}' AND indexname = 'point_{iso_code_v2_lower}_point_gist'; """ create_index_query = f""" CREATE INDEX point_{iso_code_v2_lower}_point_gist ON polygon.point_{iso_code_v2_lower} USING gist (point); """ try: with engine.connection() as conn: # Verificar si el índice ya existe result = conn.execute(text(check_index_query)) if result.fetchone() is None: # Crear el índice si no existe conn.execute(text(create_index_query)) print(f"Índice GiST en 'point_{iso_code_v2_lower}' creado con éxito.") else: print(f"Índice GiST en 'point_{iso_code_v2_lower}' ya existe.") except SQLAlchemyError as e: print(f"Error al verificar o crear el índice: {e}") # Función para obtener datos de la API def get_data_worldpop(response_send_data): print(63,response_send_data["taskid"]) url = f'https://www.api.worldpop.org/v1/tasks/{response_send_data["taskid"]}' response_data = requests.get(url) if response_data.status_code == 200: data_resultado = response_data.json() return data_resultado else: print(f"Error en la segunda petición. Código de estado: {response_data.status_code}") return None # Función para crear datos y obtener el resultado def create_data_worldpop(geometry): api_url = "https://api.worldpop.org/v1/services/stats" payload = { "dataset": "wpgpas", "year": 2020, "geojson": geometry } response_send = requests.get(api_url, params=payload) if response_send.status_code == 200: response_send_data = response_send.json() url = f'https://www.api.worldpop.org/v1/tasks/{response_send_data["taskid"]}' response_data = requests.get(url) if response_data.status_code == 200: data_resultado = response_data.json() return data_resultado else: print(f"Error en la segunda petición. Código de estado: {response_data.status_code}") return None else: print(f"Error: {response_send.status_code}") return None def actualizar_dato(engine, id_barrio, data): print(9,data) update_query = """ UPDATE polygon.point_{iso_code_v2_lower} SET data = :data WHERE id = :id_barrio; """ try: with engine.connection() as conn: conn.execute(text(update_query), {"id_barrio": id_barrio, "data": json.dumps(data)}) print(f"Datos actualizados para id_barrio {id_barrio}.") except SQLAlchemyError as e: print(f"Error al actualizar los datos: {e}") def get_polygon_by_lat_lon(engine, lat, lon, iso_code): query = f""" SELECT id, data,polygon.ST_AsText(polygon) FROM polygon.polygon_{iso_code.lower()} WHERE polygon.ST_Contains( polygon.ST_SetSRID(polygon, 4326), polygon.ST_SetSRID(polygon.ST_MakePoint(:lon, :lat), 4326)); """ try: result = engine.execute(text(query), {"lat": lat, "lon": lon}) row = result.fetchone() engine.close() #print(888,row) if row: return {"id": row[0], "data": row[1],"polygon":row[2]} else: return row #json.loads(row[1], indent=2) print(f"No se encontró ningún polígono que contenga el punto ({lat}, {lon}).") return None except SQLAlchemyError as e: print(f"Error al buscar el polígono: {e}") return None def create_polygon(lat, lon,engine): sql_query = f""" WITH original_polygon AS ( SELECT 'POLYGON ((-79.89963915792427 -2.1391402943773703, -79.90389516811103 -2.142055282302423, -79.9034452271258 -2.1471943481565536, -79.89873914667754 -2.1494183517632406, -79.89448313951581 -2.1465032276773215, -79.89493320976997 -2.141364236156891, -79.89963915792427 -2.1391402943773703))'::polygon.geometry AS geom ), new_center AS ( SELECT polygon.ST_SetSRID(polygon.ST_MakePoint({lon}, {lat}), 4326) AS new_center_point ), moved_polygon AS ( SELECT polygon.ST_Translate(geom, polygon.ST_X(new_center_point) - polygon.ST_X(polygon.ST_Centroid(geom)), polygon.ST_Y(new_center_point) - polygon.ST_Y(polygon.ST_Centroid(geom))) AS moved_geom FROM original_polygon, new_center ) SELECT polygon.ST_AsText(moved_geom) AS new_polygon FROM moved_polygon; """ try: # with engine.connection() as conn: #conn = conn.connection.cursor() hexa=engine.execute(text(sql_query)) #print(f"Hexágono ajustado e insertado con éxito para {iso_code_v2}, {iso_code_v3}.") engine.close() return hexa.fetchall()[0][0] except SQLAlchemyError as e: print(f"Error al ajustar e insertar el hexágono: {e}") def save_polygon_data(engine, iso_code_v2, iso_code_v3,hexagono, lat, lon ,data): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas insert_query = f""" INSERT INTO polygon.point_{iso_code_v2_lower} (iso_code_v2_country, iso_code_v3_country, point, data, created_at, updated_at) VALUES (:iso2, :iso3, polygon.ST_Point(:lon, :lat), :data, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); """ check_exists_query = f""" SELECT COUNT(*) FROM polygon.point_{iso_code_v2_lower} WHERE polygon.ST_Equals(point, polygon.ST_Point(:lon, :lat)); """ data = { "geography": { "geometry": f"POINT({lon} {lat})", "latitude": lat, "longitude": lon } } data_json = json.dumps(data) try: with engine.connection() as conn: conn = conn.connection.cursor() result = conn.execute(text(check_exists_query), {"lat": lat, "lon": lon}) count = result.scalar() if count == 0: conn = conn.connection.cursor() conn.execute(text(insert_query), {"iso_code_v2": iso_code_v2, "iso_code_v3": iso_code_v3, "lat": lat, "lon": lon,"data": data_json }) conn.commit() #return True # Indicador de éxito return True # Indicador de que no se insertó #print("Datos insertados con éxito.") except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") def data_hexagonos_check(data): if not data['age_range']: print(1) else: print(2) if not data['socioeconomic_level']: print(1) else: print(2) def get_socioeconomic_level(data): return 1 def get_age_range(data): return 2 def generate_data_worldpop(geometry): #print(geometry,type(geometry)) coordinates_text = geometry.replace("POLYGON((", "").replace("))", "") coordinate_pairs = coordinates_text.split(",") # Convertir las coordenadas a una lista de listas de floats coordinates = [[float(coord) for coord in pair.split()] for pair in coordinate_pairs] # Crear el objeto GeoJSON geojson_polygon = { "type": "Feature", "properties": {}, "geometry": { "type": "Polygon", "coordinates": [coordinates] } } geojson_string = json.dumps(geojson_polygon, indent=2) api_data = create_data_worldpop(geojson_string) if api_data: time.sleep(3) data_app = get_data_worldpop(api_data) if data_app: if "data" in data_app: return {'data': data_app['data']} else: return {'data': {}} return False def format_data_worldpop(data): agesexpyramid_updated = [] total_male=0 total_female=0 for item in data['data']['agesexpyramid']: # Calcular los nuevos valores de male y female male_new_value = item['male'] * 1.78 # Factor de multiplicación 1.78 female_new_value = item['female'] * 2.05 # Factor de multiplicación 2.05 # Sumar los valores al total total_male += male_new_value total_female += female_new_value # Crear un nuevo diccionario para la lista actualizada agesexpyramid_updated.append({ 'age': item['age'], 'male': round(male_new_value, 2), 'class': item['class'], 'female': round(female_new_value, 2) }) # Crear la estructura de datos de salida output = { "age_range": { "total": round(total_male + total_female, 2), "values": { "total": round(total_male + total_female, 2), "totals": { "total_male": round(total_male, 2), "total_female": round(total_female, 2) }, "total_male": f"{round(total_male, 2)}", "total_female": f"{round(total_female, 2)}", "agesexpyramid": agesexpyramid_updated }, "total_male": f"{round(total_male, 2)}", "total_female": f"{round(total_female, 2)}" } } #print(output) #output_json = json.dumps(output, indent=4) return output """ for index, row in df.iterrows(): geometry = row[geometry_column] diccionario_original = json.loads(geometry) coordenadas = diccionario_original['coordinates'][0] poligono_ccv = [list(coordenada) for coordenada in coordenadas] poligono = Polygon(poligono_ccv) tolerancia = 0.003 poligono_simplificado = poligono.simplify(tolerancia) x = loads(str(poligono_simplificado)) coordenadas_ext = list(x.exterior.coords) lista_coordenadas = [list(map(str, coord)) for coord in coordenadas_ext] nuevo_formato = { "type": "FeatureCollection", "features": [ { "type": "Feature", "properties": {}, "geometry": { "coordinates": [lista_coordenadas], "type": "Polygon" } } ] } cadena_json = json.dumps(nuevo_formato, separators=(',', ':')) datos = json.loads(cadena_json) if row['nombre'] in ['']: print('data_json_updated') print(row['nombre']) else: for feature in datos["features"]: if 'geometry' in feature and 'coordinates' in feature['geometry']: for polygon in feature['geometry']['coordinates']: for i, coord in enumerate(polygon): polygon[i] = [float(coord[0]), float(coord[1])] data_json_updated = json.dumps(datos) api_data = create_data(data_json_updated) if api_data: time.sleep(3) data_app = get_data(api_data) if data_app: actualizar_dato(engine, row['id_barrio'], data_app['data']) nuevo_dato = {'data': data_app['data'], 'id': row['id_barrio'], 'nombre': row['nombre']} datos_acumulados.append(nuevo_dato) """ def get_point(lat,lon,iso_code_v2,engine): iso_code_v2_lower = iso_code_v2.lower() # Convertir a minúsculas check_exists_query = f""" SELECT id,data,point FROM polygon.point_{iso_code_v2_lower} WHERE polygon.ST_Equals(point, polygon.ST_Point(:lon, :lat)); """ try: result = engine.execute(text(check_exists_query), {"lat": lat, "lon": lon}) engine.close() return result.fetchall() except SQLAlchemyError as e: print(f"Error al insertar datos: {e}") """ data = { "geography": { "geometry": f"POINT({lon} {lat})", "latitude": lat, "longitude": lon } } data_json = json.dumps(data) """ def has_age_range(data): if "age_range" in data: #if not data['age_range']: return True else: return False def has_age_range2(data): if "age_range" in data: #if not data['age_range']: return True else: return False def has_socioeconomic_levele(data): if "socioeconomic_level" in data: return True else: return False def add_geo_data(data,geometry , geo_type): if geo_type == GEO_TYPE_POINT: # Agregar datos al diccionario coordinates_text = geometry.replace("POINT(", "").replace(")", "") lon, lat = map(float, coordinates_text.split()) data["geography"] = { "geometry": geometry, "latitude": lat, "longitude": lon } if geo_type == GEO_TYPE_POLYGON: # Agregar datos al diccionario data["geography"] = { "geometry": geometry, } return data def add_new_data(data,new_data ,key): # print(565,data,new_data) data[key]= new_data #print(852,data) return data def get_state(data,new_data ,key): data[key]= new_data # print(852,data) return data def get_state_by_lat_lon(engine, lat, lon, iso_code): query = f""" SELECT id, data,polygon.ST_AsText(polygon) FROM polygon.states_{iso_code.lower()} WHERE polygon.ST_Contains( polygon.ST_SetSRID(polygon, 4326), polygon.ST_SetSRID(polygon.ST_MakePoint(:lon, :lat), 4326)); """ try: result = engine.execute(text(query), {"lat": lat, "lon": lon}) row = result.fetchone() engine.close() #print(888,row) if row: return {"id": row[0], "data": row[1],"polygon":row[2]} else: return row #json.loads(row[1], indent=2) print(f"No se encontró ningún polígono que contenga el punto ({lat}, {lon}).") return None except SQLAlchemyError as e: print(f"Error al buscar el polígono: {e}") return None def update_socioeconomic_level_data(socioeconomic_level_data,age_range_level_data): nuevo_total = age_range_level_data['age_range']['total'] # Recalcular los valores en json1 aplicando los porcentajes al nuevo total for item in socioeconomic_level_data['socioeconomic_level']['values']: porcentaje = item['percent'] if isinstance(porcentaje, str): porcentaje = porcentaje.replace(',', '.') porcentaje = float(porcentaje) # Convierte porcentaje a un número flotante nuevo_valor = (porcentaje / 100) * nuevo_total item['value'] = round(nuevo_valor, 2) # Actualizamos el valor con el nuevo cálculo # Actualizamos el total en el primer JSON socioeconomic_level_data['socioeconomic_level']['total'] = nuevo_total # Mostrar el resultado modificado return socioeconomic_level_data def load(data,engine,task): #return 1 total = len(data) message={} for index, row in enumerate(data): message={} info ='' lat = row['lat'] # Utilizar el nombre literal de la columna con las comillas dobles lon = row['lon'] # También el nombre literal para "LONGITUD*" # iso_code_v2, iso_code_v3 = obtener_codigo_iso(lat, lon) country= getCountry(longitude=lon ,latitude=lat) iso_code_v2=country[0] iso_code_v3=country[1] polygon_data={} if iso_code_v2 and iso_code_v3: # crear_tabla_si_no_existe(engine, iso_code_v2) # Crear la tabla si no existe point = get_point(lat,lon,iso_code_v2,engine) #print(f" {lat}, {lon} - Iniciando") if not point: info=info+'No existe el punto -' point_data={} geometry_point= f"POINT({lon} {lat})" point_data = add_geo_data(point_data,geometry_point, GEO_TYPE_POINT) polygon= get_polygon_by_lat_lon(engine, lat, lon, iso_code_v2) if not polygon: # No tiene poligono info=info+'No existe el poligono para este punto -' polygon_geo = create_polygon(lat, lon,engine) polygon_data={} polygon_data = add_geo_data(polygon_data,polygon_geo, GEO_TYPE_POLYGON) data_worldpop = generate_data_worldpop(polygon_geo) if data_worldpop['data'] == {}: info=info+'No existe data para age_range' else: data_worldpop = format_data_worldpop(data_worldpop) polygon_data = add_new_data(polygon_data,data_worldpop['age_range'],'age_range') point_data = add_new_data(point_data,data_worldpop['age_range'],'age_range') #engine=get_db socioeconomic_level_data=get_socioeconomic_level(engine,iso_code_v2,'socioeconomic_level',lat, lon) if socioeconomic_level_data: #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) info=info+'Se agrego socioeconomic_level al punto -' info=info+'Se agrego socioeconomic_level al poligono -' socioeconomic_level_data = update_socioeconomic_level_data(socioeconomic_level_data,polygon_data) point_data = add_new_data(point_data,data_worldpop,'socioeconomic_level') polygon_data = add_new_data(polygon_data,data_worldpop,'socioeconomic_level') else: info=info+'No se encontro valor valido para socioeconomic_level' # engine=get_db insert_data_polygon(engine, iso_code_v2, iso_code_v3, lat, lon,polygon_data,polygon) #engine=get_db info=info+'Se creo el poligono correspondiente al punto -' insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) info=info+'Se creo el punto -' else: # Tiene poligono if has_age_range(polygon['data']): info=info+'Existe el poligono para este punto y cuenta con age_range -' if has_age_range(polygon['data']['age_range']): point_data = add_new_data(point_data,polygon['data']['age_range']['age_range'],'age_range') else: point_data = add_new_data(point_data,polygon['data']['age_range'],'age_range') socioeconomic_level_data=get_socioeconomic_level(engine,iso_code_v2,'socioeconomic_level',lat, lon) if socioeconomic_level_data: #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) info=info+'Se agrego socioeconomic_level al punto -' socioeconomic_level_data = update_socioeconomic_level_data(socioeconomic_level_data,point_data) point_data = add_new_data(point_data,socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') else: info=info+'No se encontro valor valido para socioeconomic_level' #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) #socioeconomic_level_data = update_socioeconomic_level_data(state['data'],point_data) #point_data = add_new_data(point_data,socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') #engine=get_db insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) info=info+'Se creo el punto -' else: info=info+'Existe el poligono para este punto y no cuenta con age_range -' data_worldpop = generate_data_worldpop(polygon['polygon']) if data_worldpop['data'] == {}: info=info+'No existe data para age_range' else: data_worldpop = format_data_worldpop(data_worldpop) #polygon_data = add_new_data(polygon_data,data_worldpop['age_range'],'age_range') #point_data = add_new_data(point_data,data_worldpop['age_range'],'age_range') #data_worldpop = format_data_worldpop(data_worldpop) polygon_data = add_new_data(polygon['data'],data_worldpop['age_range'],'age_range') point_data = add_new_data(point_data,data_worldpop['age_range'],'age_range') #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) socioeconomic_level_data=get_socioeconomic_level(engine,iso_code_v2,'socioeconomic_level',lat, lon) if socioeconomic_level_data: info=info+'Se agrego socioeconomic_level al punto -' socioeconomic_level_data = update_socioeconomic_level_data(socioeconomic_level_data,polygon_data) point_data = add_new_data(point_data,socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') else: info=info+'No se encontro valor valido para socioeconomic_level' #socioeconomic_level_data = update_socioeconomic_level_data(state['data'],point_data) #point_data = add_new_data(point_data,socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') update_data_polygon(engine, iso_code_v2,polygon_data,polygon['id']) info=info+'Se actualizo el poligono correspondiente al punto con la data age_range -' insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) info=info+'Se creo el punto con la data age_range y socioeconomic_level' """ if not has_socioeconomic_levele(polygon['data']): state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) socioeconomic_level_data = update_socioeconomic_level_data(state['data'],point_data) polygon_data = add_new_data(polygon['data'],socioeconomic_level_data,'socioeconomic_level') update_data_polygon(engine, iso_code_v2,polygon_data,polygon['id']) """ else: #print(point[0][1]['socioeconomic_level']) if not has_socioeconomic_levele(point[0][1]): socioeconomic_level_data=get_socioeconomic_level(engine,iso_code_v2,'socioeconomic_level',lat, lon) if socioeconomic_level_data: #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) info=info+'Se agrego socioeconomic_level al punto -' socioeconomic_level_data = update_socioeconomic_level_data(socioeconomic_level_data,point[0][1]) point_data = add_new_data(point[0][1],socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') update_data_point(engine, iso_code_v2,point_data,point[0][0]) else: if "age_range" in point[0][1]['socioeconomic_level']: socioeconomic_level_data=get_socioeconomic_level(engine,iso_code_v2,'socioeconomic_level',lat, lon) if socioeconomic_level_data: #state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) info=info+'Se agrego socioeconomic_level al punto -' socioeconomic_level_data = update_socioeconomic_level_data(socioeconomic_level_data,point[0][1]) point_data = add_new_data({},socioeconomic_level_data['socioeconomic_level'],'socioeconomic_level') point_data = add_new_data(point_data,point[0][1]['age_range'],'age_range') point_data = add_new_data(point_data,point[0][1]['geography'],'geography') update_data_point(engine, iso_code_v2,point_data,point[0][0]) info=info+'El punto ya existe con a data requerida' """ if not has_age_range(point['data']): polygon= get_polygon_by_lat_lon(engine, lat, lon, iso_code_v2) if not polygon: # No tiene poligono print(5) polygon_geo = create_polygon(lat, lon) polygon_data={} polygon_data = add_geo_data(polygon_data,polygon_geo, GEO_TYPE_POLYGON) data_worldpop = generate_data_worldpop(polygon_geo) data_worldpop = format_data_worldpop(data_worldpop) polygon_data = add_new_data(polygon_data,data_worldpop,'age_range') point_data = add_new_data(point['data'],data_worldpop,'age_range') create_polygon_table_if_not_exists(engine, iso_code_v2) insert_data_polygon(engine, iso_code_v2, iso_code_v3, lat, lon,polygon_data,polygon) insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) print('si') else: # Tiene poligono if has_age_range(polygon['data']): point_data = add_new_data(point_data,polygon['age_range'],'age_range') insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) else: data_worldpop = generate_data_worldpop(polygon['polygon']) data_worldpop = format_data_worldpop(data_worldpop) polygon_data = add_new_data(polygon,data_worldpop,'age_range') point_data = add_new_data(point_data,data_worldpop,'age_range') update_data_polygon(engine, iso_code_v2,polygon_data,polygon['id']) insert_data_point(engine, iso_code_v2, iso_code_v3, lat, lon,point_data) if not has_socioeconomic_levele(point['data']): state= get_state_by_lat_lon(engine, lat, lon, iso_code_v2) socioeconomic_level_data = update_socioeconomic_level_data(state['data'],point_data) polygon_data = add_new_data(polygon['data'],socioeconomic_level_data,'socioeconomic_level') update_data_polygon(engine, iso_code_v2,polygon_data,polygon['id']) """ else: print(f"Coordenadas no válidas o no se pudo encontrar el país para la fila {index}.") log = {"time": datetime.now().strftime('%Y-%m-%d %H:%M:%S'), "info": info,'point':f"POINT({lon} {lat})"} status=f"{index + 1}/{total}" update(task['code'],log,status,next(get_db())) print(f"{index + 1}/{total} {lat}, {lon} - Datos insertados con éxito.") #return 1 return 1 def table_exists(table_name: str, engine) -> bool: inspector = inspect(engine) return inspector.has_table(table_name) def get_socioeconomic_level(engine,iso_code_v2,key,lat, lon) -> bool: db =get_engine() inspector = inspect(db) if inspector.has_table(f"municipalities_{iso_code_v2.lower()}",schema='polygon'): polygon= get_polygon_data_by_lat_lon(engine, lat, lon,f"polygon.municipalities_{iso_code_v2.lower()}") if polygon: if has_socioeconomic_levele(polygon['data']): return polygon['data'] if inspector.has_table(f"states_{iso_code_v2.lower()}",schema='polygon'): polygon= get_polygon_data_by_lat_lon(engine, lat, lon,f"polygon.states_{iso_code_v2.lower()}") if polygon: if has_socioeconomic_levele(polygon['data']): return polygon['data'] #db.close() #engine.close() return False def get_polygon_data_by_lat_lon(engine, lat, lon,table): query = f""" SELECT id, data,polygon.ST_AsText(polygon) FROM {table} WHERE polygon.ST_Contains( polygon.ST_SetSRID(polygon, 4326), polygon.ST_SetSRID(polygon.ST_MakePoint(:lon, :lat), 4326)); """ try: result = engine.execute(text(query), {"lat": lat, "lon": lon}) row = result.fetchone() #print(888,row) if row: return {"id": row[0], "data": row[1],"polygon":row[2]} else: return row #json.loads(row[1], indent=2) print(f"No se encontró ningún polígono que contenga el punto ({lat}, {lon}).") return None except SQLAlchemyError as e: print(f"Error al buscar el polígono: {e}") return None def getCountry1(session: Session,longitude: float ,latitude:float,skip: int = 0, limit: int = 100): connection = get_db() cursor = connection.connection.cursor() cursor.execute(sql.SQL("SELECT iso2,iso3 FROM polygon.countries WHERE polygon.ST_Contains(polygon , polygon.ST_MakePoint({}::double precision,{}::double precision )) and enable = true ").format( sql.Literal(longitude), sql.Literal(latitude) )) data = cursor.fetchone() if data is None: return None return data def getCountry(longitude: float, latitude: float): with get_db_new() as db: # Query para encontrar el país basado en las coordenadas query = text(""" SELECT iso2, iso3 FROM polygon.countries WHERE polygon.ST_Contains(polygon, polygon.ST_MakePoint(:lon, :lat)) AND enable = true """) # Ejecutar la consulta usando la sesión de SQLAlchemy result = db.execute(query, {"lon": longitude, "lat": latitude}).fetchone() if result is None: return None return result