from os import * import re import random import string import psycopg2 from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT import psycopg2.errors from django.conf import settings # Fix gdal 2.40 and 3.3 integration problems try: import ogr except ModuleNotFoundError: from osgeo import ogr from tools import const from tools.envConf.def_conf import * class PostGis(object): SHP_KEY = const.SHAPE_KEY GJSON_KEY = const.GJSON_KEY KML_KEY = const.KML_KEY PG_KEY = const.POSTGRESQL_KEY CSV_KEY = const.CSV_KEY EXCEL_KEY = const.EXCEL_KEY TABLE_NAME = 'BASE TABLE' VIEW_NAME = 'VIEW' MW_NAME = 'MATERIALIZED VIEW' FT_NAME = 'FOREIGN TABLE' TABLE_ELEMENTS = [TABLE_NAME, VIEW_NAME, MW_NAME, FT_NAME] NotAValidElement = type('NotAValidElement', (Exception, ), {}) TableNotFound = type('TableNotFound', (Exception, ), {}) def __init__(self, schema): env_conf = settings.ENV_CONF self.ip = env_conf[PG_HOST] self.port = int(env_conf[PG_PORT]) self.user = env_conf[PG_USER] self.passw = env_conf[PG_PASSW] self.schema = schema self.schema_funciones = env_conf[PG_FUNC_SCHEMA] self.schema_metadata = env_conf[PG_META_SCHEMA] self.dbname = env_conf[PG_META_DATABASE] self.dbname_colaborative = env_conf[PG_COLABORATIVE_DATABASE] self.schema_colaborative = env_conf[PG_COLABORATIVE_SCHEMA] self.dbname_catalog_layerserver = env_conf[ PG_CATALOG_LAYERSERVER_DATABASE] self.schema_semilla = env_conf[PG_SEMILLA_SCHEMA] self.table_semilla = env_conf[PG_SEMILLA_TABLE] self.connexion = self.connect() def connect(self): try: connexion = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) self.connexion = connexion return connexion except Exception as err: # 'error '+str(err) raise err def _execute(self, sql_sentence, verbose=False): try: if verbose: print(sql_sentence) cur = self.connexion.cursor() cur.execute(sql_sentence) self.connexion.commit() except Exception as err: raise err return cur ### Funciones genéricas base de datos ### def get_unique_name(self, schema, prefix='', suffix=''): """ Get a unique name in the schema's tables Parameters ---------- schema: str Schema prefix: str Some string to put as prefix suffix: str Some string to put as suffix Returns ------- unique_table: str """ tables = self.list_table_elements(schema, self.TABLE_NAME) k = 7 random_string = [random.choice(string.ascii_lowercase) for _ in range(k)] random_string = ''.join(random_string) unique_table = '{}{}{}'.format(prefix, random_string, suffix) while unique_table in tables: random_string = [random.choice(string.ascii_lowercase) for _ in range(k)] random_string = ''.join(random_string) unique_table = '{}{}{}'.format(prefix, random_string, suffix) return unique_table def test_conect(self): try: psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) return 'conexión aceptada' except Exception as err: return 'error '+str(err) def list_schemas(self): try: cur = self.connexion.cursor() cmd = """select nspname from pg_catalog.pg_namespace;""" cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error '+str(err) def list_views(self, schema): try: cur = self.connexion.cursor() cmd = """ SELECT table_schema as schema_name, table_name as view_name from information_schema.views where table_schema = '{schema}'; """.format(schema=schema) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise 'error '+str(err) def list_all_by_schema(self, schema): try: cur = self.connexion.cursor() cmd = """ SELECT nombre from (SELECT table_name::character varying as nombre, table_type as tipo FROM information_schema.tables WHERE table_schema = '{schema}' union all select matviewname::character varying as nombre, 'MATVIEW' as tipo from pg_matviews where schemaname = '{schema}')t ; """.format(schema=schema) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise 'error '+str(err) def list_foreign_tables(self): try: cur = self.connexion.cursor() cmd = """ select foreign_table_name from information_schema.foreign_tables """ cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def list_foreing_server(self): try: cur = self.connexion.cursor() cmd = """select srvname as name from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw;""" cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error '+str(err) def list_columns(self, schema, table): try: cur = self.connexion.cursor() cmd = """ SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = '{}' AND table_name = '{}'; """.format(schema, table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error '+str(err) def list_columns_from_MaterializedView(self, schema, table): try: cur = self.connexion.cursor() cmd = """ select attr.attname as column_name, trim(leading '_' from tp.typname) as datatype from pg_catalog.pg_attribute as attr join pg_catalog.pg_class as cls on cls.oid = attr.attrelid join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace join pg_catalog.pg_type as tp on tp.typelem = attr.atttypid where ns.nspname = '{}' and cls.relname = '{}' and not attr.attisdropped and cast(tp.typanalyze as text) = 'array_typanalyze' and attr.attnum > 0 order by attr.attnum; """.format(schema, table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error '+str(err) def list_table_elements(self, schema, element_type): if element_type not in self.TABLE_ELEMENTS: raise self.NotAValidElement('{} is not a valid element' .format(element_type)) sql_command = """ SELECT * FROM information_schema.tables where table_schema = '{}' AND table_type = '{}' """.format(schema, element_type) cur = self._execute(sql_command) for element in cur: yield element[2] return cur def list_attributes_each_item(self, schema, table): cur = self.connexion.cursor() cmd = """SELECT a.attname FROM pg_attribute a JOIN pg_class t on a.attrelid = t.oid JOIN pg_namespace s on t.relnamespace = s.oid WHERE a.attnum > 0 AND NOT a.attisdropped AND t.relname = '{table}' AND s.nspname = '{schema}'; """.format(schema=schema, table=table) cur.execute(cmd) self.connexion.commit() return cur def select_by_attribute(self, schema, table, attribute): try: cur = self.connexion.cursor() cmd = """SELECT {} FROM "{}"."{}";""".format(attribute, schema, table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise 'error '+str(err) def create_schema(self, new_schema): try: cur = self.connexion.cursor() cmd = """CREATE SCHEMA "{}";""".format(new_schema) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def change_name_table(self, user, table, new_table): try: cur = self.connexion.cursor() cmd = """ALTER TABLE "{}"."{}" RENAME TO "{}";""".format(user, table, new_table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def create_table(self, user, table, ori_user, ori_table): try: cur = self.connexion.cursor() cmd = """create table "{}"."{}" as select * from "{}"."{}"; """.format(user, table, ori_user, ori_table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def delete_materialized_view(self, name_mv): cur = self.connexion.cursor() cmd = """DROP MATERIALIZED VIEW \"{}\".\"{}\" CASCADE;""".format( self.schema, name_mv) cur.execute(cmd) self.connexion.commit() def create_view_by_clauses(self, user, view, list_att, user_ori, table, where, group_by, having, order_by, limit): try: cur = self.connexion.cursor() cmd = """create view "{schema}"."{view}" as select {list} from "{schema_ori}"."{table}" {where} {group_by} {having} {order_by} {limit}; """.format(schema=user, view=view, list=list_att, schema_ori=user_ori, table=table, where=where, group_by=group_by, having=having, order_by=order_by, limit=limit) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def delete_view(self, name_vw): cur = self.connexion.cursor() cmd = """DROP VIEW \"{}\".\"{}\" CASCADE;""".format(self.schema, name_vw) cur.execute(cmd) self.connexion.commit() def delete_schema(self, schema): try: cur = self.connexion.cursor() cmd = """DROP SCHEMA "{}" CASCADE;""".format(schema) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def delete_server_data_Wraper(self, server): try: cur = self.connexion.cursor() cmd = """DROP SERVER IF EXISTS "{}" CASCADE;""".format(server) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def _del_table(self, schema, table, verbose): cmd = """DROP TABLE IF EXISTS "{}"."{}" CASCADE;""".format(schema, table) cur = self._execute(cmd, verbose=verbose) return cur def _del_mv(self, schema, table, verbose): cmd = """DROP MATERIALIZED VIEW IF EXISTS "{}"."{}" CASCADE;"""\ .format(schema, table) cur = self._execute(cmd, verbose=verbose) return cur def _del_vw(self, schema, table, verbose): cmd = """DROP VIEW IF EXISTS "{}"."{}" CASCADE;""".format(schema, table) cur = self._execute(cmd, verbose=verbose) return cur def _del_ft(self, schema, ft, verbose): servers_to_remove = PostGis(schema).get_foreign_server_from_ft(schema, ft) for server in servers_to_remove: cur = PostGis(self.user).delete_server_data_Wraper(server) return cur def get_table_type(self, schema, table): sql_command = """SELECT table_type FROM information_schema.tables where table_schema = '{}' AND table_name = '{}' """.format(schema, table) cur = list(self._execute(sql_command)) if len(cur) == 0: return [self.MW_NAME, 0] return cur[0] def delete_tables(self, schema, *tables, verbose=False): for table in tables: if isinstance(table, (float, str, set, dict)): if len(table) != 0: table_type = self.get_table_type(schema, table)[0] if table_type == self.TABLE_NAME: cur = self._del_table(schema, table, verbose=verbose) elif table_type == self.VIEW_NAME: cur = self._del_vw(schema, table, verbose=verbose) elif table_type == self.MW_NAME: cur = self._del_mv(schema, table, verbose=verbose) elif table_type == self.FT_NAME: cur = self._del_ft(schema, table, verbose=verbose) def get_foreign_server_from_ft(self, schema, *fts): for ft in fts: cur = self.connexion.cursor() cmd = """SELECT foreign_server_name from information_schema.foreign_tables WHERE foreign_table_name = '{}' AND foreign_table_schema = '{}' """.format(ft, schema) cur.execute(cmd) yield list(cur)[0][0] def metadata_select(self, atrib_name, table_name, contain): try: cur = self.connexion.cursor() cmd = """select {} from {} where {} ;""".format(atrib_name, table_name, contain) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise err def insert_data(self, user, table_name, atrib_name, contain): try: cur = self.connexion.cursor() string_val = "(" for dat in atrib_name: string_val += """ "{dat}" """.format(dat=dat) + "," string_val = string_val[:-1] string_val += ")" cmd = """INSERT into "{}"."{}" {} VALUES {}"""\ .format(user, str(table_name), string_val, tuple(contain)) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise err def send_sql_command(self, command): self.connexion.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = self.connexion.cursor() cmd = """{}""".format(command) cur.execute(cmd) self.connexion.commit() return cur ### Funciones ### def list_tables_views_materialized_foreing(self, schema): self.connexion.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = self.connexion.cursor() cmd = """ select * from {}.__list_tbl_view_matview('{}')"""\ .format(self.schema_funciones, schema) cur.execute(cmd) self.connexion.commit() return cur def join_pg(self, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain, type_join, sincoronized): if type_join == 'innerjoin': try: if sincoronized: cur = self.connexion.cursor() cmd = """SELECT {}.__create_innerjoin_vw( '{}','{}','{}','{}','{}','{}','{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' else: cur = self.connexion.cursor() cmd = """SELECT {}.__create_innerjoin_mv( '{}','{}','{}','{}','{}','{}','{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' except Exception as err: raise err elif type_join == 'leftjoin': try: if sincoronized: cur = self.connexion.cursor() cmd = """SELECT {}.__create_leftjoin_vw( '{}','{}','{}','{}','{}','{}', '{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' else: cur = self.connexion.cursor() cmd = """SELECT {}.__create_leftjoin_mv( '{}','{}','{}','{}', '{}','{}','{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' except Exception as err: raise err else: return 'especifica tipo de salida válido' def join_pg_id(self, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain, type_join, sincoronized): if type_join == 'innerjoin': try: if sincoronized: cur = self.connexion.cursor() cmd = """SELECT {}.__create_innerjoin_id_vw( '{}', '{}', '{}', '{}', '{}', '{}', '{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' else: cur = self.connexion.cursor() cmd = """SELECT {}.__create_innerjoin_id_mv( '{}', '{}', '{}', '{}', '{}', '{}', '{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' except Exception as err: raise err elif type_join == 'leftjoin': try: if sincoronized: cur = self.connexion.cursor() cmd = """SELECT {}.__create_leftjoin__id_vw( '{}', '{}', '{}', '{}', '{}', '{}', '{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' else: cur = self.connexion.cursor() cmd = """SELECT {}.__create_leftjoin_id_mv( '{}', '{}', '{}', '{}', '{}', '{}', '{}') """.format(self.schema_funciones, schema, new_table, table_vectorial_1, id_vectorial_1, table_thematic_2, Id_thematic_2, contain) cur.execute(cmd) self.connexion.commit() return 'Join creado' except Exception as err: raise err else: return 'especifica tipo de salida válido' def interseccion_create_table(self, schema, output_layer_name, layer_vec1, geom1, layer_vec2, geom2, relation, sql_filter, tolerancia, validate): cur = self.connexion.cursor() cmd = """SELECT {schema_qc}.op_interseccion( '{schema}', '{viewname}', '{nametable1}', '{sgeom1}', '{nametable2}', '{sgeom2}', '{srelespacial}', '{swhere}', '{ntolerancia}', '{bvalidar}') """.format( schema_qc='qc', schema=schema, viewname=output_layer_name, nametable1=layer_vec1, sgeom1=geom1, nametable2=layer_vec2, sgeom2=geom2, srelespacial='', swhere=sql_filter, ntolerancia=tolerancia, bvalidar=validate ) cur.execute(cmd) self.connexion.commit() return 'Tabla intersección creada' def check_valid_geometries(self, validate, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.chequear_geometrias_vw( '{valid}','{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, valid=validate, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'OK' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.chequear_geometrias_mv( '{valid}','{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, valid=validate, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'OK' def fix_invalid_geometries(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.crear_geometrias_validas_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Valid Geometries' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.crear_geometrias_validas_mv( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Valid Geometries Materialized View' def extract_centroids(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.extraer_centroides_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Get Centroids' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.extraer_centroides_mv( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Get Centroids' def extrac_lines_E(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.extraer_lineas_exteriores_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Get Outdoor Lines' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.extraer_lineas_exteriores_mv( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Get Outdoor Lines' def sub_type(self, schema, nameoutput, nameinput, field, value, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.dividir_por_tipo_vw( '{schema}','{viewname}','{nametable}','{ownfield}','{ownvalue}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, ownfield=field, ownvalue=value) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.dividir_por_tipo_mv( '{schema}','{viewname}','{nametable}','{ownfield}','{ownvalue}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, ownfield=field, ownvalue=value) cur.execute(cmd) self.connexion.commit() return 'ok' def fill_holes(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.rellenar_huecos_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Fill Holes' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.rellenar_huecos_mv( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Fill Holes' def fill_holes_tierra2(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.rellenar_huecos_tbl( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Fill Holes' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.rellenar_huecos_tbl( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Fill Holes' def error_Points(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.sacar_puntos_erroneos_vw2( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Error Points' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.sacar_puntos_erroneos_mv2( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Error Points' def get_all(self, schema, nameoutput, nameinput, content, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.seleccion_total_vw( '{schema}','{viewname}','{nametable}','{owncontent}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, owncontent=content) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.seleccion_total_mv( '{schema}','{viewname}','{nametable}','{owncontent}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, owncontent=content) cur.execute(cmd) self.connexion.commit() return 'ok' def join_by_centroids(self, schema, nameoutput, nameinput2, nameinput1, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_de_atributos_por_centrodies_tbl( '{schema}','{viewname}','{nametable_geom}','{nametable_cen}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable_geom=nameinput2, nametable_cen=nameinput1) cur.execute(cmd) self.connexion.commit() return 'Join with centroids' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_de_atributos_por_centrodies_tbl( '{schema}','{viewname}','{nametable_geom}','{nametable_cen}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable_geom=nameinput2, nametable_cen=nameinput1) cur.execute(cmd) self.connexion.commit() return 'Join with centroids' def Cverify_data(self, schema, nameoutput, nameinput, containt, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.comprobacion_de_datos_vw( '{schema}','{viewname}','{nametable}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, content=containt) cur.execute(cmd) self.connexion.commit() return 'Verify Data' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.comprobacion_de_datos_mv( '{schema}','{viewname}','{nametable}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, content=containt) cur.execute(cmd) self.connexion.commit() return 'Verify Data' def join_tables(self, schema, nameoutput, nameinput, value=None): value = nameinput[:-3] if not value else value cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.create_for_union_vw( '{schema}','{viewname}','{nametable}' ,'{value}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, value=value) cur.execute(cmd) self.connexion.commit() return 'Join tables' def Union(self, schema, nameoutput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_vw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_mw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables' def RemoveOverlapsTierra2(self, schema, nameinput, nameoutput, nameRT, nameHidro, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.cubierta_sin_solapes( '{schema}','{viewname}','{viewnameOut}','{viewnameRT}','{viewnameHidro}','{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, viewnameOut=nameoutput, viewnameRT=nameRT, viewnameHidro=nameHidro, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'solapes' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.cubierta_sin_solapes( '{schema}','{viewname}','{viewnameOut}','{viewnameRT}','{viewnameHidro}','{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, viewnameOut=nameoutput, viewnameRT=nameRT, viewnameHidro=nameHidro, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'solapes' def join_tables_rail2(self, schema, nameoutput, nameinput): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.create_for_union_rail2_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' def Union_rail2(self, schema, nameoutput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_rail2_vw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_rail2_mw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' def Union_Name_Rail2(self, schema, nameinput): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.alter_name_rail2_vw( '{schema}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Ok alter name rail 2' def join_tables_hidro2(self, schema, nameoutput, nameinput): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.create_for_union_hidro2_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' def Union_hidro2(self, schema, nameoutput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_hidro2_vw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.union_multiple_hidro2_mw( '{schema}','{viewname}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput) cur.execute(cmd) self.connexion.commit() return 'Join tables Rail Transport' def Union_Name_hidro2(self, schema, nameinput): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.alter_name_hidro2_vw( '{schema}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Ok alter name rail 2' def Union_Name(self, schema, nameinput): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.alter_name_vw( '{schema}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Ok alter name' def change_detection(self, schema, nameoutput, nameinput1, nameinput2, detection, nochange, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.extraer_centroides_mv( '{schema}','{viewname}','{nametable1}','{nametable2}','{perDetect}','{perNoChan}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, nametable2=nameinput2, perDetect=detection, perNoChan=nochange) cur.execute(cmd) self.connexion.commit() return 'Changes Detected' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.comprobacion_cambios_mv( '{schema}','{viewname}','{nametable1}','{nametable2}','{perDetect}','{perNoChan}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, nametable2=nameinput2, perDetect=detection, perNoChan=nochange) cur.execute(cmd) self.connexion.commit() return 'Changes detected' def nineIntersections(self, schema, nameoutput, nameinput1, nameinput2, matrix, sincronized, validar): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.nineintersection_matrix_vw( '{validar}','{schema}','{viewname}','{nametable_1}','{nametable_2}','{matrix}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable_2=nameinput2, nametable_1=nameinput1, matrix=matrix, validar=validar) cur.execute(cmd) self.connexion.commit() return 'Matrix Nine Intersection' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.nineintersection_matrix_mv( '{validar}','{schema}','{viewname}','{nametable_1}','{nametable_2}','{matrix}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable_2=nameinput2, nametable_1=nameinput1, matrix=matrix, validar=validar) cur.execute(cmd) self.connexion.commit() return 'Matrix Nine Intersection' def topology_check(self, schema, nameoutput, nameinput, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.chequear_topologia_vw( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Topology Errors' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.chequear_topologia_mv( '{schema}','{viewname}','{nametable}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput) cur.execute(cmd) self.connexion.commit() return 'Topology Errors' def createTilesFromPolygon(self, schema, nameinput, nameoutput, incrX, incrY, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.__crear_teselas_en_poligono_vw( '{schema}','{viewname}','{nametable}', '{incrX}','{incrY}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput, incrX=incrX, incrY=incrY) cur.execute(cmd) self.connexion.commit() return 'status: ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.__crear_teselas_en_poligono_mv( '{schema}','{viewname}','{nametable}','{incrX}','{incrY}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameinput, nametable=nameoutput, incrX=incrX, incrY=incrY) cur.execute(cmd) self.connexion.commit() return 'status: ok' def SelectAtributesandFilter(self, schema, nameoutput, nameinput, content, fields, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.seleccion_eligiendo_campos_mv( '{schema}','{viewname}','{nametable}','{owncontent}','{fields}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, owncontent=content, fields=fields) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.seleccion_eligiendo_campos_mv( '{schema}','{viewname}','{nametable}','{owncontent}','{fields}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, owncontent=content, fields=fields) cur.execute(cmd) self.connexion.commit() return 'ok' def SpatialJoin(self, schema, nameoutput, nameinput1, nameinput2, content, joinType, fieldsOut, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.spatial_join_vw( '{esquema}','{tabla1}','{tabla2}','{nombre_salida}','{tipo_de_interseccion}','{contenido}', '{tipo_join}') """.format( schemafuncions=self.schema_funciones, esquema=schema, tabla1=nameinput1, tabla2=nameinput2, nombre_salida=nameoutput, contenido=content, tipo_de_interseccion=joinType, tipo_join=fieldsOut) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.spatial_join_mv( '{esquema}','{tabla1}','{tabla2}','{nombre_salida}','{tipo_de_interseccion}','{contenido}', '{tipo_join}') """.format( schemafuncions=self.schema_funciones, esquema=schema, tabla1=nameinput1, tabla2=nameinput2, nombre_salida=nameoutput, contenido=content, tipo_de_interseccion=joinType, tipo_join=fieldsOut) cur.execute(cmd) self.connexion.commit() return 'ok' def html_table(self, schema, nameoutput, nameinput, color, size, img_title, own_title, contait, colums, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.crear_html_vw( '{schema}','{nametable}','{viewname}','{color}','{size}','{img_title}','{own_title}','{contait}','{colums}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, color=color, size=size, img_title=img_title, own_title=own_title, contait=contait, colums=colums) cur.execute(cmd) self.connexion.commit() return 'HTML table created' def H3_count(self, schema, nameoutput, nameinput, h3_zoom, content_where, epsg, scale, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_conteo_vw( '{schema}','{viewname}','{nametable}', '{h3_zoom}','{content_where}', '{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_conteo_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_where}', '{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_count_index(self, schema, nameoutput, nameinput, h3_zoom, content_where, epsg, scale, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_conteo_index_vw( '{schema}','{viewname}','{nametable}', '{h3_zoom}','{content_where}', '{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_conteo_index_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_where}', '{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_count_values(self, schema, nameoutput, nameinput, h3_zoom, content_field, content_not_group, content_where, epsg, scale, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_mixto_vw( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}', '{content_not_group}', '{content_where}', '{epsg}' , '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_not_group=content_not_group, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_mixto_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}', '{content_not_group}', '{content_where}', '{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_not_group=content_not_group, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_count_values_index(self, schema, nameoutput, nameinput, h3_zoom, content_field, content_not_group, content_where, epsg, scale, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_mixto_index_vw( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}','{content_not_group}','{content_where}','{epsg}' , '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_not_group=content_not_group, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_mixto_index_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}', '{content_not_group}','{content_where}','{epsg}', '{scale}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_not_group=content_not_group, content_where=content_where, epsg=epsg, scale=scale) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_point_count(self, schema, nameoutput, nameinput, h3_zoom, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_conteo_vw( '{schema}','{viewname}','{nametable}','{h3_zoom}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_conteo_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_point_count_index(self, schema, nameoutput, nameinput, h3_zoom, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_conteo_index_vw( '{schema}','{viewname}','{nametable}','{h3_zoom}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_conteo_index_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_point_count_values(self, schema, nameoutput, nameinput, h3_zoom, content_field, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_mixto_vw( '{schema}','{viewname}','{nametable}', '{h3_zoom}','{content_field}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_mixto_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_point_count_values_index(self, schema, nameoutput, nameinput, h3_zoom, content_field, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_mixto_index_vw( '{schema}','{viewname}','{nametable}', '{h3_zoom}','{content_field}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_punto_mixto_index_mv( '{schema}','{viewname}','{nametable}','{h3_zoom}','{content_field}','{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable=nameinput, h3_zoom=h3_zoom, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def H3_spatialJoin(self, schema, nameoutput, nameinput1, nameinput2, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_join_vw( '{schema}','{viewname}','{nametable1}','{nametable2}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, nametable2=nameinput2) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_join_mv( '{schema}','{viewname}','{nametable1}','{nametable2}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, nametable2=nameinput2) cur.execute(cmd) self.connexion.commit() return 'ok' def triangulation_Delonay(self, schema, nameoutput, nameinput1, tolerance, flag, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.delaunytriangles_vw( '{schema}', '{nametable1}','{viewname}','{tolerance}','{flag}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable1=nameinput1, viewname=nameoutput, tolerance=tolerance, flag=flag) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.delaunytriangles_mv( '{schema}','{nametable1}','{viewname}','{tolerance}','{flag}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable1=nameinput1, viewname=nameoutput, tolerance=tolerance, flag=flag) cur.execute(cmd) self.connexion.commit() return 'ok' def poligonization_Voronoi(self, schema, nameoutput, nameinput1, tolerance, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.voronoypoligons_vw( '{schema}','{viewname}','{nametable1}','{tolerance}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, tolerance=tolerance) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.voronoypoligons_mv( '{schema}','{viewname}','{nametable1}','{tolerance}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametable1=nameinput1, tolerance=tolerance) cur.execute(cmd) self.connexion.commit() return 'ok' def Convex_Hull_Simple(self, schema, nameoutput, nameinput, content, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.convexhull_vw( '{schema}','{nametable}','{viewname}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.convexhull_mv( '{schema}','{nametable}','{viewname}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' def Convex_Hull(self, schema, nameoutput, nameinput, simplify, buffer, area, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.convexhull_union_vw( '{schema}','{nametable}', '{viewname}', '{simplify}','{buffer}','{area}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, simplify=simplify, buffer=buffer, area=area) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.convexhull_union_mv( '{schema}','{nametable}', '{viewname}', '{simplify}','{buffer}','{area}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, simplify=simplify, buffer=buffer, area=area) cur.execute(cmd) self.connexion.commit() return 'oki' def Concave_Hull_Simple(self, schema, nameoutput, nameinput, content, porcentage, holes, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.concavehull_vw( '{schema}','{nametable}','{viewname}','{content}', '{porcentage}','{holes}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content, porcentage=porcentage, holes=holes) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.concavehull_mv( '{schema}','{nametable}','{viewname}','{content}', '{porcentage}', '{holes}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content, porcentage=porcentage, holes=holes) cur.execute(cmd) self.connexion.commit() return 'ok' def Concave_Hull(self, schema, nameoutput, nameinput, porcentage, holes, simplify, buffer, area, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.concavehull_union_vw( '{schema}','{nametable}','{viewname}','{porcentage}','{holes}','{simplify}','{buffer}','{area}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, porcentage=porcentage, holes=holes, simplify=simplify, buffer=buffer, area=area) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.concavehull_union_mv( '{schema}','{nametable}','{viewname}','{porcentage}', '{holes}','{simplify}','{buffer}','{area}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, porcentage=porcentage, holes=holes, simplify=simplify, buffer=buffer, area=area) cur.execute(cmd) self.connexion.commit() return 'oki' def Medial_Axis(self, schema, nameoutput, nameinput, content, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.medialaxis_vw( '{schema}','{nametable}','{viewname}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.medialaxis_mv( '{schema}','{nametable}','{viewname}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' def Medial_Axis_With_Simpli(self, schema, nameoutput, nameinput, content, metre_buff, simplify, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.medialaxis_con_limpieza_vw( '{schema}','{nametable}','{viewname}', '{content}', '{metre_buff}', '{simplify}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content, metre_buff=metre_buff, simplify=simplify) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.medialaxis_con_limpieza_mv( '{schema}','{nametable}','{viewname}', '{content}', '{metre_buff}', '{simplify}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content=content, metre_buff=metre_buff, simplify=simplify) cur.execute(cmd) self.connexion.commit() return 'ok' def Difference(self, schema, nameoutput, nameinput, nameinput1, content, srid1, srid2, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.difference_vw( '{schema}','{nametable}','{nametableclip}', '{viewname}', '{content}', '{srid1}', '{srid2}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, nametableclip=nameinput1, viewname=nameoutput, content=content, srid1=srid1, srid2=srid2) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.difference_mv( '{schema}','{nametable}','{nametableclip}', '{viewname}', '{content}', '{srid1}', '{srid2}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, nametableclip=nameinput1, viewname=nameoutput, content=content, srid1=srid1, srid2=srid2) cur.execute(cmd) self.connexion.commit() return 'ok' def Difference_Dump(self, schema, nameoutput, nameinput, nameinput1, content, srid1, srid2, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.difference_dump_vw( '{schema}','{nametable}','{nametableclip}', '{viewname}','{content}', '{srid1}', '{srid2}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, nametableclip=nameinput1, viewname=nameoutput, content=content, srid1=srid1, srid2=srid2) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.difference_dump_mv( '{schema}','{nametable}','{nametableclip}', '{viewname}', '{content}', '{srid1}', '{srid2}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, nametableclip=nameinput1, viewname=nameoutput, content=content, srid1=srid1, srid2=srid2) cur.execute(cmd) self.connexion.commit() return 'ok' def SQL_sentence_byClauses(self, schema, nameoutput, content, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.generica_vw( '{schema}','{viewname}','{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.generica_mv( '{schema}','{viewname}', '{content}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, content=content) cur.execute(cmd) self.connexion.commit() return 'ok' def padre_hijo_limites_admin_mixto(self, schema, nameoutput, nameinput, content_field, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.padre_hijo_limites_administrativos_mixto_vw( '{schema}','{nametable}', '{viewname}', '{content_field}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.padre_hijo_limites_administrativos_mixto_mv( '{schema}','{nametable}', '{viewname}', '{content_field}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def padre_hijo_limites_admin(self, schema, nameoutput, nameinput, type, column, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.padre_hijo_limites_administrativos_vw( '{schema}','{nametable}', '{viewname}', '{type}', '{column}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, type=type, column=column, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.padre_hijo_limites_administrativos_mv( '{schema}','{nametable}', '{viewname}', '{type}', '{column}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, type=type, column=column, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def Spatial_group(self, schema, nameoutput, nameinput, nameinput1, content_field, content_where, epsg1, epsg2, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.agrupacion_espacial_mixto_vw( '{schema}','{viewname}','{nametablegeom}', '{nametablefield}', '{content_field}', '{content_where}', '{epsg1}', '{epsg2}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametablegeom=nameinput, nametablefield=nameinput1, content_field=content_field, content_where=content_where, epsg1=epsg1, epsg2=epsg2) cur.execute(cmd) self.connexion.commit() return 'oki' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.agrupacion_espacial_mixto_mv( '{schema}','{viewname}','{nametablegeom}', '{nametablefield}', '{content_field}', '{content_where}', '{epsg1}','{epsg2}') """.format( schemafuncions=self.schema_funciones, schema=schema, viewname=nameoutput, nametablegeom=nameinput, nametablefield=nameinput1, content_field=content_field, content_where=content_where, epsg1=epsg1, epsg2=epsg2) cur.execute(cmd) self.connexion.commit() return 'oki' def h3_padre_hijo(self, schema, nameoutput, nameinput, content_field, content_where, epsg, sincronized): if sincronized: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_padre_hijo_vw( '{schema}','{viewname}', '{nametable}', '{content_field}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'oki' else: cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.h3_padre_hijo_mv( '{schema}','{viewname}', '{nametable}', '{content_field}', '{content_where}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, viewname=nameoutput, content_field=content_field, content_where=content_where, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'ok' def get_axil_vials_ai(self, schema, nameoutput, nameoutput_1, nameinput, umbral_score, distance_buffer, simplify, epsg): cur = self.connexion.cursor() cmd = """SELECT {schemafuncions}.areas_ejes_viales_ia( '{schema}','{nametable}', '{tableareasname}', '{tableaxisname}', '{ratioscore}', '{buffer}', '{simply}', '{epsg}') """.format( schemafuncions=self.schema_funciones, schema=schema, nametable=nameinput, tableareasname=nameoutput, tableaxisname=nameoutput_1, ratioscore=umbral_score, buffer=distance_buffer, simply=simplify, epsg=epsg) cur.execute(cmd) self.connexion.commit() return 'oki' ### Wrappers ### def wrapper_postgresql_pg(self, user, server_name, ip_pg, port_pg, user_pg, passw_pg, database_pg, schema_pg, foreign_table_name): list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}')""".format( self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreign_table_name} else: flag_server = 0 if flag_server == 0: table_pg = str(foreign_table_name).replace("PG_", "") table_pg = table_pg.replace("_ft", "") try: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_postgresql( '{}', '{}', '{}','{}', '{}','{}','{}','{}','{}','{}','{}')""" \ .format(self.schema_funciones, server_name, ip_pg, port_pg, database_pg, self.user, user_pg, passw_pg, schema_pg, user, table_pg, foreign_table_name) cur1.execute(cmd1) self.connexion.commit() except psycopg2.errors.DuplicateTable: # TODO Quitar este try-except cuando hagamos la base de datos # de metadatos bien. # Esta BBDD NO puede tener tablas físicas self.delete_tables(user, table_pg) cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_postgresql('{}', '{}', '{}', '{}','{}','{}','{}','{}','{}','{}','{}') """.format(self.schema_funciones, server_name, ip_pg, port_pg, database_pg, self.user, user_pg, passw_pg, schema_pg, user, table_pg, foreign_table_name) cur1.execute(cmd1) self.connexion.commit() except psycopg2.errors.UndefinedTable: cur1_1 = self.connexion.cursor() cmd1_1 = """ROLLBACK;""" cur1_1.execute(cmd1_1) self.connexion.commit() cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_postgresql('{}', '{}', '{}','{}','{}','{}', '{}','{}','{}','{}','{}') """.format(self.schema_funciones, server_name, ip_pg, port_pg, database_pg, self.user, user_pg, passw_pg, schema_pg, user, table_pg.lower(), foreign_table_name) cur1.execute(cmd1) self.connexion.commit() except psycopg2.errors.SqlclientUnableToEstablishSqlconnection \ as err: raise err # from PG try: conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' \ % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreign_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreign_table_name) spatial_ref = layer.GetSpatialRef() except AttributeError: spatial_ref = 'EPSG:4326' if spatial_ref is None: # from Layer conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' % ( ip_pg, database_pg, user_pg, passw_pg, port_pg, schema_pg, table_pg) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(table_pg) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: return {'status': 'Capa subida', 'layer': foreign_table_name} epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + environ["PATH"] ogrinfo = popen('ogrinfo {}'.format(conn_string).read()) if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}')""" \ .format(self.schema_funciones, epsg_number, 'GeometryZ', user+'.'+foreign_table_name) cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}')""" \ .format(self.schema_funciones, epsg_number, 'Geometry', foreign_table_name) cur2.execute(cmd2) self.connexion.commit() return {'status': 'Capa subida', 'layer': foreign_table_name} def wrapper_shp_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}')""".format( self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: if '/vsicurl_streaming/' in url_file: url_file = url_file[19:] file = url_file.split('/')[-1] if '.shp' not in file: right_url = url_file.rsplit('/', 1)[0] else: right_url = url_file dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(right_url), 0) layer = dataset.GetLayer() layer_name_ogr = layer.GetName() cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_shp_limit( '{}', '{}', '{}','{}','{}')""".format( self.schema_funciones, server_name, right_url, schema, layer_name_ogr, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s port=%s ' \ 'schemas=%s tables=%s' % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(right_url)) layer = dataset.GetLayer() feature = layer.GetNextFeature() geom = feature.GetGeometryRef() spatial_ref = geom.GetSpatialReference() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) ogrinfo = popen('ogrinfo /vsicurl_streaming/{}' .format(right_url)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} def wrapper_geopackage_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(url_file)) layer = dataset.GetLayer() layer_name_ogr = layer.GetName() cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_geopackage_limit( '{}','{}','{}','{}', '{}') """.format(self.schema_funciones, server_name, url_file, schema, layer_name_ogr, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' % ( self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}' .format(url_file)) layer = dataset.GetLayer() feature = layer.GetNextFeature() geom = feature.GetGeometryRef() spatial_ref = geom.GetSpatialReference() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo /vsicurl_streaming/{}' .format(url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: raise err def wrapper_gml_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(url_file)) layer = dataset.GetLayer() layer_name_ogr = layer.GetName() cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_gml_limit( '{}','{}','{}','{}', '{}') """.format(self.schema_funciones, server_name, url_file, schema, layer_name_ogr, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' % ( self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}' .format(url_file)) layer = dataset.GetLayer() feature = layer.GetNextFeature() geom = feature.GetGeometryRef() spatial_ref = geom.GetSpatialReference() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo /vsicurl_streaming/{}' .format(url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: raise err def wrapper_kml_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}')""".format( self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 1 if server_name[:63] in [i[0] for i in list_foreing_server] else 0 if flag_server == 1: # from pg layer_list = [] cur1 = self.connexion.cursor() cmd1 = """SELECT foreign_Table_name FROM information_schema.foreign_tables WHERE foreign_server_name LIKE '%{}%' """.format(server_name[:63]) cur1.execute(cmd1) for i in cur1: layer_list.append(i[0][:-3]) return {'status': 'Ya existe la capa', 'layer': layer_list}, layer_list if flag_server == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_kml('{}', '{}', '{}') """.format(self.schema_funciones, server_name, url_file, schema) cur1.execute(cmd1) self.connexion.commit() # from layer dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(url_file)) layer_list = [] for i in dataset: try: cur1_1 = self.connexion.cursor() rename_table = '{}_{}_ft'.format(foreing_table_name, str(i.GetName()).replace( " ", "_")) cmd1_1 = """SELECT {}.__alter_table_name('{}', '{}', '{}') """.format(self.schema_funciones, schema, str(i.GetName()).replace(" ", "_"), rename_table) cur1_1.execute(cmd1_1) self.connexion.commit() layer_list.append(rename_table[:-3]) # from PG conn_string = 'PG: host=%s dbname=%s user=%s ' \ 'password=%s port=%s schemas=%s tables=%s' \ % (self.ip, self.dbname, self.user, self.passw, self.port, user, rename_table) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(rename_table) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}' .format(url_file)) layer = dataset.GetLayer() spatial_ref = layer.GetSpatialRef() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo /vsicurl_streaming/{}' .format(url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}') """.format( self.schema_funciones, epsg_number, 'GeometryZ', user + '.' + rename_table) cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}') """.format(self.schema_funciones, epsg_number, 'Geometry', rename_table) cur2.execute(cmd2) self.connexion.commit() except Exception as err: cur1_1 = self.connexion.cursor() cmd1_1 = """ROLLBACK;""" cur1_1.execute(cmd1_1) self.connexion.commit() return {'status': 'capa creada', 'layer': layer_list}, layer_list def wrapper_kml_limit_pg(self, user, server_name, url_file, schema, file_name, layer_name, foreing_table_name): list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}')""".format( self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: layer_name_ogr = layer_name cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_kml_limit( '{}', '{}', '{}','{}','{}')""".format( self.schema_funciones, server_name, url_file, schema, layer_name_ogr, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s port=%s' \ ' schemas=%s tables=%s' % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}'.format(url_file)) layer = dataset.GetLayer() spatial_ref = layer.GetSpatialRef() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + environ["PATH"] ogrinfo = popen('ogrinfo /vsicurl_streaming/{}'.format( url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg('{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} def wrapper_gjson_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): dataset = ogr.Open(r'{}'.format(url_file)) layer = dataset.GetLayer() layer_name_ogr = layer.GetName() layer_name_ogr = layer_name_ogr.replace('.', '_') layer_name_ogr = layer_name_ogr.replace('-', '_') try: int(layer_name_ogr[0]) layer_name_ogr = 'n' + layer_name_ogr except: pass spatial_ref = layer.GetSpatialRef() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}')""".format( self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_geojson_limit( '{}','{}','{}','{}','{}') """.format(self.schema_funciones, server_name, url_file, schema, layer_name_ogr, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' \ % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'{}'.format(url_file)) layer = dataset.GetLayer() spatial_ref = layer.GetSpatialRef() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo {}'.format(url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except psycopg2.errors.DuplicateTable: return {'status': 'capa ya existe', 'layer': foreing_table_name} def wrapper_csv_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name): try: list_schemas = PostGis('public').list_schemas() flag_schema = 0 for i in list_schemas: if user in i[0]: flag_schema = 1 break else: flag_schema = 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_csv_limit( '{}', '{}', '{}','{}','{}')""".format( self.schema_funciones, server_name, url_file, schema, file_name, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%'""".format( foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: return 'error ' + str(err) def wrapper_excel_limit_pg(self, user, server_name, url_file, schema, file_name, layer_name, foreing_table_name): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_excel_limit( '{}','{}','{}','{}', '{}','{}') """.format(self.schema_funciones, server_name, url_file, schema, 'XLSX', layer_name, foreing_table_name) cur1.execute(cmd1) self.connexion.commit() if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: return 'error ' + str(err) def wrapper_shp_zip_limit_pg(self, user, server_name, url_file, schema, layer_name, foreing_table_name): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 0 for i in list_foreing_server: if server_name[:63] in i[0]: flag_server = 1 return {'status': 'Ya existe la capa', 'layer': foreing_table_name} else: flag_server = 0 if flag_server == 0: ds = ogr.Open('/vsizip//vsicurl/{}'.format(str(url_file))) layer_to_remove = [] for layer in ds: name_to_remove = layer.GetName().replace(' ', '_').lower() if name_to_remove != layer_name: layer_to_remove.append(name_to_remove) cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_zip_varios( '{}','{}','{}') """.format(self.schema_funciones, server_name, url_file, schema) cur1.execute(cmd1) self.connexion.commit() # Removing other tables command = 'DROP FOREIGN TABLE "{scheme}"."{table}" CASCADE;' for lay in layer_to_remove: self.send_sql_command(command.format(scheme=user, table=lay)) # Renaming foreign table command = 'ALTER FOREIGN TABLE "{scheme}"."{old_name}" ' \ 'RENAME TO "{new_name}"' self.send_sql_command(command.format( scheme=user, old_name=layer_name, new_name=foreing_table_name)) if len(foreing_table_name) > 62: cur2 = self.connexion.cursor() cmd2 = """SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_name LIKE '%{}%' """.format(foreing_table_name[:63]) cur2.execute(cmd2) foreing_table_name = list(cur2)[0][0] self.connexion.commit() # from PG conn_string = 'PG: host=%s dbname=%s user=%s password=%s ' \ 'port=%s schemas=%s tables=%s' \ % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer dataset = ogr.Open(r'/vsicurl_streaming/{}' .format(url_file)) layer = dataset.GetLayer() feature = layer.GetNextFeature() geom = feature.GetGeometryRef() spatial_ref = geom.GetSpatialReference() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo /vsicurl_streaming/{}' .format(url_file)).read() if ogrinfo.split('(')[-1][:2] == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name, epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: raise err def wrapper_shp_zip_pg(self, user, server_name, url_file, schema, file_name, foreing_table_name, dataset): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() list_foreing_server = PostGis('public').list_foreing_server() flag_server = 1 if server_name[:63] in [i[0] for i in list_foreing_server] else 0 if flag_server == 1: # from pg layer_list = [] cur1 = self.connexion.cursor() cmd1 = """SELECT foreign_Table_name FROM information_schema.foreign_tables WHERE foreign_server_name LIKE '%{}%' """.format(server_name[:63]) cur1.execute(cmd1) for i in cur1: layer_list.append(i[0]) return {'status': 'Ya existe la capa', 'layer': layer_list}, layer_list if flag_server == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__external_wrapper_zip_varios( '{}', '{}', '{}') """.format(self.schema_funciones, server_name, url_file, schema) cur1.execute(cmd1) self.connexion.commit() # from layer layer_list = [] cur1 = self.connexion.cursor() cmd1 = """SELECT foreign_Table_name FROM information_schema.foreign_tables WHERE foreign_server_name LIKE '%{}%' """.format(server_name[:63]) cur1.execute(cmd1) for i in cur1: try: cur1_1 = self.connexion.cursor() cmd1_1 = """SELECT {}.__alter_table_name( '{}', '{}', '{}') """.format(self.schema_funciones, schema, str(i[0]).replace(" ", "_"), foreing_table_name+'_'+str(i[0]). replace(" ", "_")+'_ft') cur1_1.execute(cmd1_1) self.connexion.commit() layer_list.append(foreing_table_name+'_'+str(i[0]). replace(" ", "_")) # from PG conn_string = 'PG: host=%s dbname=%s user=%s ' \ 'password=%s port=%s schemas=%s ' \ 'tables=%s' % (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name+'_' + str(i[0]). replace(" ", "_")+'_ft') conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name+'_' + str(i[0]). replace(" ", "_")+'_ft') spatial_ref = layer.GetSpatialRef() if spatial_ref is None: # from Layer flag_n = '' if i[0][0] == 'n' and i[0][1].isdigit is True: layerr = dataset.GetLayerByName(i[0][1:]) flag_n = i[0][1:] else: layerr = dataset.GetLayerByName(i[0]) flag_n = i[0] spatial_ref = layerr.GetSpatialRef() epsg_number = spatial_ref.GetAttrValue("AUTHORITY", 1) environ["PATH"] = "/usr/local/gdal2_4_0/bin:" + \ environ["PATH"] ogrinfo = popen('ogrinfo -so /vsizip//vsicurl/{} {}' .format(url_file, flag_n)).read() m3d_flag = re.search('3D', ogrinfo) if m3d_flag.group(0) == "3D": cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}')""".format( self.schema_funciones, user, foreing_table_name+'_' + flag_n+'_ft', epsg_number, 'GeometryZ') cur2.execute(cmd2) self.connexion.commit() else: cur2 = self.connexion.cursor() cmd2 = """SELECT {}.__change_epsg( '{}', '{}', '{}', '{}') """.format(self.schema_funciones, user, foreing_table_name+'_' + flag_n+'_ft', epsg_number, 'Geometry') cur2.execute(cmd2) self.connexion.commit() else: pass except Exception as err: cur1_1 = self.connexion.cursor() cmd1_1 = """ROLLBACK;""" cur1_1.execute(cmd1_1) self.connexion.commit() return {'status': 'capa creada', 'layer': layer_list}, layer_list except Exception as err: return 'error ' + str(err) def wrapper_fiware_pg(self, user, foreing_table_name, version_token, url_token, url_cb, application_id, application_secret, fiware_user, fiware_password, service, subservice, entity_name, georref_type, georref_attr, mat): try: list_schemas = PostGis('public').list_schemas() flag_schema = 1 if user in [i[0] for i in list_schemas] else 0 if flag_schema == 0: cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__create_schema('{}') """.format(self.schema_funciones, user) cur1.execute(cmd1) self.connexion.commit() views = list(PostGis('public').list_views(user)) view_list = [] for i in views: view_list.append(i[1]) if foreing_table_name in view_list: try: PostGis('public').send_sql_command( """ DROP VIEW "{}"."{}" CASCADE; """.format(user, foreing_table_name)) except psycopg2.errors.DependentObjectsStillExist: raise TypeError("No ha sido posible eliminar " "la vista existente") cur1 = self.connexion.cursor() cmd1 = """SELECT {}.__wrapper_fiware( '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')""".format( self.schema_funciones, user, foreing_table_name, version_token, url_token, url_cb, application_id, application_secret, fiware_user, fiware_password, service, subservice, entity_name, georref_type, georref_attr, mat) cur1.execute(cmd1) self.connexion.commit() conn_string = 'PG: host=%s dbname=%s user=%s password=%s port=%s ' \ 'schemas=%s tables=%s' % \ (self.ip, self.dbname, self.user, self.passw, self.port, user, foreing_table_name) conn = ogr.Open(conn_string) layer = conn.GetLayerByName(foreing_table_name) spatial_ref = layer.GetSpatialRef() if spatial_ref is None: raise TypeError("Los valores para la " "georreferenciación no son válidos ") return {'status': 'capa subida', 'layer': foreing_table_name} except Exception as err: raise err ### Creación Vistas ### def create_view_pg(self, user, name): try: cur = self.connexion.cursor() cmd = """select {}.__create_view('{}','{}', '{}'); """.format(self.schema_funciones, user, name, name+'_ft') cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error '+str(err) def create_materialized_view_pg(self, user, name): try: cur = self.connexion.cursor() cmd = """CREATE MATERIALIZED VIEW "{}"."{}" AS SELECT *, row_number() OVER (PARTITION BY true) as ID_index FROM "{}"."{}"; """.format(user, name, user, name+'_ft') cur.execute(cmd) self.connexion.commit() cur2 = self.connexion.cursor() cmd2 = """CREATE UNIQUE INDEX "index_{}_{}" ON "{}"."{}" (ID_index); """.format(user, name, user, name) cur2.execute(cmd2) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def create_view_with_contain_pg(self, esquema, view, nametable, contain): try: cur = self.connexion.cursor() cmd = """SELECT {}.__alter_column_name_vw('{}', '{}', '{}', '{}') """.format(self.schema_funciones, esquema, view, nametable, contain) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def qwer(self): pass class External_PostGis(object): PG_CONNECTION_ERROR = type('PG_CONNECTION_ERROR', (Exception, ), {}) def __init__(self, ip_pg, port_pg, user_pg, passw_pg, dbname_pg, schema_pg, table_pg): self.ip = ip_pg self.port = port_pg self.user = user_pg self.passw = passw_pg self.dbname = dbname_pg self.schema = schema_pg self.table = table_pg self.test_connection() def test_connection(self): try: self.connexion = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname, connect_timeout=2 ) except psycopg2.OperationalError: return(self.PG_CONNECTION_ERROR("Could not connect to {}:{} DB: {}" .format(self.ip, self.port, self.dbname))) def test_table(self): cur = self.connexion.cursor() cmd = """SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = '{schema}' AND table_name = '{table}' ); """.format(schema=self.schema, table=self.table) cur.execute(cmd) result = cur.fetchone() self.connexion.commit() results = ''.join(str(result)) return results def list_tables(self, attribute, attribute_value): cur = self.connexion.cursor() cmd = """SELECT {attribute} FROM "pg_catalog"."pg_tables" where {attribute}='{attributeValue}'; """.format(attribute=attribute, attributeValue=attribute_value) cur.execute(cmd) self.connexion.commit() return cur def list_all_by_schema(self, schema): cur = self.connexion.cursor() cmd = """ SELECT nombre FROM (SELECT table_name::character varying as nombre, table_type as tipo FROM information_schema.tables WHERE table_schema = '{schema}' union all select matviewname::character varying as nombre, 'MATVIEW' as tipo from pg_matviews where schemaname = '{schema}')t ; """.format(schema=schema) cur.execute(cmd) self.connexion.commit() return cur def list_attributes_each_item(self, schema, table): cur = self.connexion.cursor() cmd = """SELECT a.attname FROM pg_attribute a JOIN pg_class t on a.attrelid = t.oid JOIN pg_namespace s on t.relnamespace = s.oid WHERE a.attnum > 0 AND NOT a.attisdropped AND t.relname = '{table}' AND s.nspname = '{schema}'; """.format(schema=schema, table=table) cur.execute(cmd) self.connexion.commit() return cur def select_by_attribute(self, schema, table, attribute, limit=0): cur = iter([]) if attribute: cur = self.connexion.cursor() cmd = """SELECT {} FROM "{}"."{}" {}; """.format(attribute, schema, table, "LIMIT {}".format(limit)*bool(limit)) cur.execute(cmd) self.connexion.commit() return cur def create_table(self, user, table, ori_user, ori_table): try: cur = self.connexion.cursor() cmd = """CREATE table "{}"."{}" AS SELECT * FROM "{}"."{}"; """.format(user, table, ori_user, ori_table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def delete_materialized_view(self, name_mv): cur = self.connexion.cursor() cmd = """DROP MATERIALIZED VIEW \"{}\".\"{}\" CASCADE; """.format(self.schema, name_mv) cur.execute(cmd) self.connexion.commit() def delete_view(self, name_vw): cur = self.connexion.cursor() cmd = """DROP VIEW \"{}\".\"{}\" CASCADE; """.format(self.schema, name_vw) cur.execute(cmd) self.connexion.commit() def create_materialized_view_pg(self, new_name): try: con = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) cur = con.cursor() cmd = """CREATE MATERIALIZED VIEW "{}"."{}" AS SELECT *, row_number() OVER (PARTITION BY true) as ID_index FROM "{}"."{}"; """.format(self.schema, new_name, self.schema, self.table) cur.execute(cmd) cur2 = con.cursor() cmd2 = """CREATE UNIQUE INDEX "index_{}_{}" ON "{}"."{}" (ID_index); """.format(self.schema, new_name, self.schema, new_name) cur2.execute(cmd2) con.commit() except psycopg2.errors.DuplicateTable: self.delete_materialized_view(new_name) cur = self.create_materialized_view_pg(new_name) return cur def create_view_pg(self, new_name): try: con = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) cur = con.cursor() cmd = """CREATE VIEW "{}"."{}" AS SELECT * FROM "{}"."{}"; """.format(self.schema, new_name, self.schema, self.table) cur.execute(cmd) con.commit() except psycopg2.errors.DuplicateTable as e: self.delete_view(new_name) cur = self.create_view_pg(new_name) return cur def create_view_by_clauses(self, user, view, list_att, user_ori, table, where, group_by, having, order_by, limit): cur = self.connexion.cursor() cmd = """CREATE VIEW "{schema}"."{view}" as select {list} FROM "{schema_ori}"."{table}" {where} {group_by} {having} {order_by} {limit}; """.format(schema=user, view=view, list=list_att, schema_ori=user_ori, table=table, where=where, group_by=group_by, having=having, order_by=order_by, limit=limit) cur.execute(cmd) self.connexion.commit() return cur def refresh_materialized_view_pg(self): con = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) cur = con.cursor() cmd = """REFRESH materialized view CONCURRENTLY "{}"."{}"; """.format(self.schema, self.table) cur.execute(cmd) con.commit() return cur def list_columns(self, schema, table): try: cur = self.connexion.cursor() cmd = """SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = '{}' AND table_name = '{}'; """.format(schema, table) cur.execute(cmd) self.connexion.commit() return cur except Exception as err: return 'error ' + str(err) def insert_data(self, schema, table_name, atrib_name, contain): cur = self.connexion.cursor() values_string = "(" for dat in atrib_name: values_string += dat + "," values_string = values_string[:-1] values_string += ")" cmd = """Insert into "{}"."{}" {} VALUES {} """.format(schema, str(table_name), values_string, tuple(contain)) cmd = cmd.replace("\"___", "") cmd = cmd.replace("___\"", "") cur.execute(cmd) self.connexion.commit() return cur def update_data(self, table_name, atrib_name, contain): try: cur = self.connexion.cursor() string = "(" for dat in atrib_name: string += dat + "," string = string[:-1] string += ")" cmd = """update "{schema}"."{table}" set columna = 'valor' where id = 'x' """ % () cur.execute(cmd) self.connexion.commit() return cur except Exception as err: raise err def send_sql_command(self, command): con = psycopg2.connect( user=self.user, host=self.ip, port=self.port, password=self.passw, dbname=self.dbname) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = con.cursor() cmd = """{}""".format(command) cur.execute(cmd) con.commit() return cur