/* contrib/srm_funct/srm_funct--unpackaged--1.0.sql */ -- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION srm_funct FROM unpackaged" to load this file. \quit /* contrib/srm_funct/srm_funct--1.0.sql */ SET search_path TO public, topology, tiger, tiger_data; CREATE OR REPLACE FUNCTION funciones."10_tablas_union_mv"( num_tablas text, esch text, contenido text, nameview text, nametable1 text, nametable2 text, nametable3 text, nametable4 text, nametable5 text, nametable6 text, nametable7 text, nametable8 text, nametable9 text, nametable10 text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare tab text; BEGIN --el numero de tablas marca el número de uniones CASE WHEN num_tablas = '2' THEN EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"'; ELSE tab := '2'; END CASE; CASE WHEN num_tablas = '3' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"'; ELSE tab := '3'; END CASE; CASE WHEN num_tablas = '4' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"'; ELSE tab := '4'; END CASE; CASE WHEN num_tablas = '5' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"'; ELSE tab := '5'; END CASE; CASE WHEN num_tablas = '6' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"'; ELSE tab := '6'; END CASE; CASE WHEN num_tablas = '7' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"'; ELSE tab := '7'; END CASE; CASE WHEN num_tablas = '8' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"'; ELSE tab := '8'; END CASE; CASE WHEN num_tablas = '9' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"'; ELSE tab := '9'; END CASE; CASE WHEN num_tablas = '10' THEN --las geometrias no validas EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable10||'"'; ELSE tab := '10'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones."10_tablas_union_vw"( num_tablas text, esch text, contenido text, nameview text, nametable1 text, nametable2 text, nametable3 text, nametable4 text, nametable5 text, nametable6 text, nametable7 text, nametable8 text, nametable9 text, nametable10 text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare tab text; BEGIN --el numero de tablas marca el número de uniones CASE WHEN num_tablas = '2' THEN EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"'; ELSE tab := '2'; END CASE; CASE WHEN num_tablas = '3' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"'; ELSE tab := '3'; END CASE; CASE WHEN num_tablas = '4' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"'; ELSE tab := '4'; END CASE; CASE WHEN num_tablas = '5' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"'; ELSE tab := '5'; END CASE; CASE WHEN num_tablas = '6' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"'; ELSE tab := '6'; END CASE; CASE WHEN num_tablas = '7' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"'; ELSE tab := '7'; END CASE; CASE WHEN num_tablas = '8' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"'; ELSE tab := '8'; END CASE; CASE WHEN num_tablas = '9' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"'; ELSE tab := '9'; END CASE; CASE WHEN num_tablas = '10' THEN --las geometrias no validas EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'" UNION ALL select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable10||'"'; ELSE tab := '10'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__alter_column_name_mv( esch text, name_tables text, column_name text, new_column_name text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: sirve para renombrar una columna de una tabla, column_name (viejo nombre de la columna), new_column_name (nuevo nombre de la columna) */ EXECUTE 'ALTER MATERIALIZED VIEW "'|| esch ||'"."'|| name_tables ||'" RENAME COLUMN "'|| column_name ||'" TO "'|| new_column_name||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__alter_table_name( esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: sirve para renombrar una tabla, name_tables (viejo nombre de la tabla), new_name (nuevo nombre de la tabla) */ EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__change_epsg( esch text, name_table character varying, epsg numeric, geometria text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: sirve para cambiar el epsg */ EXECUTE 'ALTER TABLE "'|| esch ||'"."'||name_table||'" ALTER COLUMN geom TYPE Geometry('||geometria||',' || epsg||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__contour_lines( geomin geometry[], colin numeric[], breaks numeric[]) RETURNS TABLE(geom geometry, break numeric) LANGUAGE 'plpgsql' COST 100 IMMUTABLE ROWS 1000 AS $BODY$ DECLARE bucketin integer[]; gs geometry[]; g geometry; vertex geometry[]; vv numeric[]; bu integer[]; inter numeric[]; interp12 geometry[]; interp23 geometry[]; interp31 geometry[]; segment geometry[]; running_merge geometry[]; i integer; BEGIN WITH a AS( SELECT width_bucket(t.x, breaks) AS bin FROM unnest(colin) AS t(x) ) SELECT array_agg(bin) INTO bucketin FROM a; WITH a AS (SELECT unnest(geomin) AS e), b AS (SELECT ST_DelaunayTriangles(ST_Collect(a.e)) AS t FROM a), c AS (SELECT (ST_Dump(t)).geom AS v FROM b) SELECT array_agg(v) INTO gs FROM c; i:= 0; FOREACH g IN ARRAY gs LOOP SELECT array_agg(a.v), array_agg(b.c), array_agg(b.bk) INTO vertex, vv, bu FROM ( SELECT (ST_DumpPoints(g)).geom AS v limit 3 ) as a CROSS JOIN LATERAL( SELECT t.* FROM unnest(geomin, colin, bucketin) AS t(geo, c, bk) WHERE ST_Equals(geo, a.v) LIMIT 1 ) AS b; CONTINUE WHEN bu[1] = bu[2] and bu[1] = bu[3]; interp12 := _get_cell_intersects(vertex, vv, bu, breaks,1,2); interp23 := _get_cell_intersects(vertex, vv, bu, breaks,2,3); interp31 := _get_cell_intersects(vertex, vv, bu, breaks,3,1); WITH a AS( SELECT t.* FROM unnest(breaks, interp12, interp23, interp31) AS t(br, p12 , p23, p31) ), b AS( SELECT CASE WHEN (p12 IS NOT NULL AND p23 IS NOT NULL AND ST_equals(p12, p23)=false) OR (p23 IS NOT NULL AND p31 IS NOT NULL AND ST_equals(p23, p31)=false) OR (p31 IS NOT NULL AND p12 IS NOT NULL AND ST_equals(p31, p12)=false) THEN ST_MakeLine(ARRAY[p12, p23, p31]::geometry[]) ELSE null::geometry END AS segm, br FROM a ) SELECT array_agg(b.segm) into segment FROM unnest(breaks) AS c(x) LEFT JOIN b ON b.br = c.x; IF i = 0 THEN running_merge = segment; i := 1; ELSE WITH a AS( SELECT ST_CollectionExtract(x, 2) AS x, y FROM unnest(running_merge,segment) AS t(x,y) ), b AS( SELECT ST_collect(x,y) AS element FROM a ) SELECT array_agg(element) INTO running_merge FROM b; END IF; END LOOP; RETURN QUERY WITH a AS( SELECT br, ST_CollectionExtract(geo, 2) AS geo FROM unnest(running_merge, breaks) AS t(geo, br) ), b AS( SELECT ST_LineMerge(geo) AS geo, br FROM a ) SELECT geo AS geom, br AS break FROM b; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__crear_tesela( geom geometry, x_side double precision, y_side double precision, OUT geometry) RETURNS SETOF geometry LANGUAGE 'plpgsql' COST 100 IMMUTABLE STRICT ROWS 1000 AS $BODY$ DECLARE x_max DECIMAL; y_max DECIMAL; x_min DECIMAL; y_min DECIMAL; srid INTEGER := 4326; input_srid INTEGER; x_series DECIMAL; y_series DECIMAL; geom_cell geometry := ST_GeomFromText(FORMAT('POLYGON((0 0, 0 %s, %s %s, %s 0,0 0))', $3, $2, $3, $2), srid); BEGIN CASE ST_SRID (geom) WHEN 0 THEN geom := ST_SetSRID (geom, srid); RAISE NOTICE'SRID Not Found.'; ELSE RAISE NOTICE'SRID Found.'; END CASE; input_srid := ST_srid ( geom ); geom := ST_Transform ( geom, srid ); x_max := ST_XMax ( geom ); y_max := ST_YMax ( geom ); x_min := ST_XMin ( geom ); y_min := ST_YMin ( geom ); x_series := CEIL ( @( x_max - x_min ) / x_side ); y_series := CEIL ( @( y_max - y_min ) / y_side ); RETURN QUERY With foo AS ( SELECT ST_Translate( geom_cell, j * $2 + x_min, i * $3 + y_min ) AS cell FROM generate_series ( 0, x_series ) AS j, generate_series ( 0, y_series ) AS i ) SELECT ST_CollectionExtract(ST_Collect(ST_Transform ( ST_Intersection(cell, geom), input_srid)), 3) FROM foo where ST_intersects (cell, geom); END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__crear_teselas_en_poligono_mv( esch text, namein text, nameout text, x text, y text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare geoma text; BEGIN SELECT f_geometry_column into geoma FROM geometry_columns WHERE f_table_schema = esch AND f_table_name = namein; EXECUTE 'create materialized view "'|| esch ||'"."'|| nameout ||'" as select row_number ()over() as id_tess,* from (select (st_dump (geom)).geom from (select funciones.__crear_tesela( '|| geoma ||', '|| x ||', '|| y ||') as geom from "'|| esch ||'"."'|| namein ||'")t order by geom desc)t;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__crear_teselas_en_poligono_vw( esch text, namein text, nameout text, x text, y text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare geoma text; BEGIN SELECT f_geometry_column into geoma FROM geometry_columns WHERE f_table_schema = esch AND f_table_name = namein; EXECUTE 'create view "'|| esch ||'"."'|| nameout ||'" as select row_number ()over() as id_tess,* from (select (st_dump (geom)).geom from (select funciones.__crear_tesela( '|| geoma ||', '|| x ||', '|| y ||') as geom from "'|| esch ||'"."'|| namein ||'")t order by geom desc)t;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_id_mv( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; exception when others then EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" ))t'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_id_vw( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_mv( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank;'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; exception when others then EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank)t;'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_vw( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| namemv ||'" AS select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_leftjoin__id_vw( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista desde un leftjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_id_mv( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; exception when others then EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm, * from (SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" ))t'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_mv( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank;'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; exception when others then EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank)t;'; EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_vw( esch text, namemv text, nametablegeom text, nametablegeom_id text, tematic text, tematic_id text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista desde un leftjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| namemv ||'" AS select a.* FRom "'|| esch ||'"."'|| nametablegeom ||'" as B INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'" INNER JOIN ( select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A group by A."'|| nametablegeom_id ||'" ) as NewA ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" AND a.similarity_index = NewA.expertrank;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada */ EXECUTE 'CREATE materialized VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fid)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_mv_contain( esch text, nameview text, nametable text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada, contenido: la selección en bruto de los nombres de las columnas que se quieren llevar a la vista */ EXECUTE 'CREATE materialized VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT "'|| contenido||'" FROM "'|| esch ||'"."'|| nametable||'"'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fid)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_schema( nombre_usuario character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea un esquema, nombre_usuario (nombre del esquema) */ EXECUTE 'CREATE SCHEMA "'|| nombre_usuario ||'" AUTHORIZATION postgres'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_user( nombre_usuario character varying, pw character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea un usuario pw: password */ EXECUTE 'CREATE SCHEMA "'|| nombre_usuario ||'" AUTHORIZATION postgres'; EXECUTE 'CREATE user "'|| nombre_usuario ||'" LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD ''' || pw ||''''; EXECUTE 'GRANT ALL ON SCHEMA "'|| nombre_usuario ||'" TO postgres'; EXECUTE 'GRANT ALL ON SCHEMA "'|| nombre_usuario ||'" TO "'|| nombre_usuario ||'"'; EXECUTE 'ALTER USER "'|| nombre_usuario ||'" SET search_path TO "' || nombre_usuario ||'"'; EXECUTE 'ALTER ROLE "'|| nombre_usuario ||'" IN DATABASE '||current_database()||' SET search_path TO "'|| nombre_usuario ||'"'; EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "' || nombre_usuario ||'"'; EXECUTE 'GRANT all privileges ON ALL TABLES IN SCHEMA "'|| nombre_usuario ||'" TO "'|| nombre_usuario ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_view( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_view_contain( esch text, nameview text, nametable text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista, contenido: la selección en bruto de los nombres de las columnas que se quieren llevar a la vista */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT "'|| contenido||'" FROM "'|| esch ||'"."'|| nametable||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_csv( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos csv sin limite, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER '|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''CSV'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "'|| ser ||'" INTO "'|| esch||'"' ; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_csv( ser text, exc text, name_tables character varying, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos csv con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''CSV'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER ' || ser || ' INTO "' || esch||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_excel( ser text, exc text, names_tables character varying, tipo text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, name_tables: el nombre de los archivos a limitar en el caso del excell el nombre de las hojas, tipo: XLS o XLSX*/ EXECUTE 'CREATE SERVER '|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''' || tipo || ''', config_options ''OGR_XLSX_HEADERS=FORCE'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("'|| names_tables ||'")FROM SERVER '|| ser ||' INTO "'|| esch||'"' ; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_excel( ser text, exc text, tipo text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos excel sin limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''' || tipo || ''', config_options ''OGR_XLSX_HEADERS=FORCE'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_geopackage( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos geopackage sin limite, exc:direccion del archivo acabado con nombre archivo y .gpkg*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GPKG'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_host( ser text, host text, puerto integer, dbase text, usr text, pass character varying, esch1 text, esch2 text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper a otro postgres, usr: usuario del otro postgres, pass: password del otro postgres, esch1:el esquema que se quiere importar, esch2: el esquema propio*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase || ''')'; EXECUTE 'CREATE USER MAPPING FOR "'|| dbase ||'" SERVER '|| ser ||' OPTIONS ("user" ''' || usr || ''', password ''' || pass || ''')'; EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch1 ||'" FROM SERVER '|| ser ||' INTO "'|| esch2||'"' ; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_host( ser text, host text, puerto integer, dbase text, usr text, pass character varying, esch1 text, esch2 text, name_tables character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper a otro postgres con limitacion de tablas , usr: usuario del otro postgres, pass: password del otro postgres, esch1:el esquema que se quiere importar, esch2: el esquema propio name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase || ''')'; EXECUTE 'CREATE USER MAPPING FOR "'|| dbase ||'" SERVER ' || ser || ' OPTIONS ("user" ''' || usr || ''', password ''' || pass || ''')'; EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch1 ||'" LIMIT TO ("'|| name_tables ||'") FROM SERVER '|| ser ||' INTO "'|| esch2||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_kml( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos kml sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''KML'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER '|| ser ||' INTO "'|| esch||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_mdb( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos MDB sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''Geomedia'', config_options ''OGR_SKIP=ODBC GEOMEDIA_DRIVER_TEMPLATE="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s"'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_mdb( ser text, exc text, names_tables character varying, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos MDB con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''Geomedia'', config_options ''OGR_SKIP=ODBC GEOMEDIA_DRIVER_TEMPLATE="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s"'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("'|| names_tables ||'")FROM SERVER ' || ser || ' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_shp( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos shp sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''ESRI Shapefile'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__create_wrapper_shp( ser text, exc text, name_tables character varying, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper local a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''ESRI Shapefile'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER ' || ser || ' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__drop_mv( esch text, name_tables text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: eliminar vista materializada en cascada*/ EXECUTE 'DROP materialized VIEW if exists "'|| esch ||'"."'|| name_tables ||'" cascade'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__drop_server( ser text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: eliminar servidor en cascada*/ EXECUTE 'DROP SERVER if exists "'|| ser ||'" cascade'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__drop_user( nombre_usuario character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: eliminar usuario*/ EXECUTE 'drop schema "'|| nombre_usuario ||'" cascade'; EXECUTE 'REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "'|| nombre_usuario ||'" CASCADE'; EXECUTE 'REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "'|| nombre_usuario ||'" CASCADE'; EXECUTE 'REVOKE select ON all tables in schema public FROM "'|| nombre_usuario ||'"'; EXECUTE 'drop role "'|| nombre_usuario ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_csv( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos csv con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER acsv FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''CSV'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER acsv INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER acsv RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_excel( ser text, exc text, esch text, tipo text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper externo a archivos excel sin limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX*/ EXECUTE 'CREATE SERVER axcl FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER axcl INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER axcl RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_excel_limit( ser text, exc text, esch text, tipo text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper externo a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER axcl FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER axcl INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER axcl RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geojson( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos geojson sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER ageojson FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GeoJSON'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ageojson INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER ageojson RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geojson( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos geojson con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER ageojson FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GeoJSON'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER ageojson INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER ageojson RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geopackage( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos geopackage sin limite de tablas, exc:direccion del archivo con nombre archivo y .gpkg*/ EXECUTE 'CREATE SERVER ageopackage FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''GPKG'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ageopackage INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER ageopackage RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos kml sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER akml INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos kml sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER akml INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml_limit( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos kml con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER akml INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql( ser text, host text, puerto integer, dbase_user text, usr_server text, usr_user text, esch_user text, esch_server text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, esch_user:el esquema que se quiere importar, esch_server: el esquema propio name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')'; EXECUTE 'CREATE USER MAPPING FOR "'|| user_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')'; EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user ||'" LIMIT TO ("'|| name_tables ||'") FROM SERVER apg INTO "'|| esch_server ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch_server ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql( ser text, host text, puerto integer, dbase_user text, usr_server text, usr_user text, pass_user text, esch_user text, esch_server text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, esch_user:el esquema que se quiere importar, esch_server: el esquema propio name_tables: el nombre de los archivos a limitar*/ EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')'; EXECUTE 'CREATE USER MAPPING FOR "'|| usr_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')'; EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user || '" LIMIT TO ("'|| name_tables ||'") FROM SERVER apg INTO "'|| esch_server ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch_server ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql_no_limit( ser text, host text, puerto integer, dbase_user text, usr_server text, usr_user text, pass_user text, esch_user text, esch_server text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, esch_user:el esquema que se quiere importar, esch_server: el esquema propio*/ EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')'; EXECUTE 'CREATE USER MAPPING FOR "'|| usr_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')'; EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user || '" FROM SERVER apg INTO "'|| esch_server ||'"'; EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_shp( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl/'|| exc ||''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_shp_folder( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos shp sin limite de tablas, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl/' || exc || ''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_zip( ser text, exc text, esch text, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare coordx character varying ; BEGIN /*Descripcion: wrapper extertno a archivos zip que tienen un archivo, exc:direccion del archivo, new_name:nuevo nombre de la tabla, coordx:extrae el nombre de la tabla por si no coincide con el zip*/ EXECUTE 'CREATE SERVER zip FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsizip//vsicurl_streaming/'|| exc ||''', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER zip INTO "'|| esch ||'"'; select foreign_table_name into coordx FROM information_schema.foreign_tables where foreign_server_name = 'zip'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| coordx ||'" RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER zip RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__external_wrapper_zip_varios( ser text, exc text, esch text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare coordx character varying ; BEGIN /*Descripcion: wrapper extertno a archivos zip que tienen muchas archivos, exc:direccion del archivo*/ EXECUTE 'CREATE SERVER zip FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsizip//vsicurl/'|| exc ||''', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER zip INTO "'|| esch ||'"'; EXECUTE 'ALTER SERVER zip RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_csv( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''CSV'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_excel( ser text, exc text, esch text, tipo text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper externo a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_kml_limit( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos kml con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||') FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_shp( ser text, exc text, esch text, name_tables character varying, new_name character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/ EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')'; EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER a'|| ser ||' INTO "'|| esch ||'"'; EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"'; EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__list_schemas( ) RETURNS TABLE(schema_name_0 character varying) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ BEGIN RETURN QUERY select schema_name::character varying from information_schema.schemata where not schema_name like 'pg%'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.__list_tbl_view_matview( _schema text) RETURNS TABLE(nombre character varying, tipo character varying) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ BEGIN RETURN query select * 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; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.alter_name_rail2_vw( esch text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion:cambia el nombre de una vista pensado para las de la union */ EXECUTE 'ALTER VIEW "'|| esch ||'"."paraunionrail_'|| nametable ||'" RENAME TO "zvw_'|| nametable ||'";'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.alter_name_vw( esch text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion:cambia el nombre de una vista pensado para las de la union */ EXECUTE 'ALTER VIEW "'|| esch ||'"."paraunion_'|| nametable ||'" RENAME TO "zvw_'|| nametable ||'";'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.cambiar_esquema_multiples_tablas( _schema_salida text, schema_entrada text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE row record; BEGIN FOR row IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = ''||schema_entrada ||'' LOOP EXECUTE 'alter TABLE "'||schema_entrada ||'"."'||row.table_name||'" set schema "'||_schema_salida ||'"'; END LOOP; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_geometrias_mv( validar boolean, esch text, nameview text, nametable text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare tab text; BEGIN --cuando validar es 0 extrae la vista de todas las geometrías, cuando es 1 solo las falsas CASE WHEN validar = '0' THEN --todas geometrías sean validas o no EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')'; ELSE tab := 'falsa'; END CASE; CASE WHEN validar = '1' THEN --las geometrias no validas EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"where st_isvalid (geom)= ''false'' or st_isvalid (geom) is null'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')'; ELSE tab := 'todas'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_geometrias_vw( validar boolean, esch text, nameview text, nametable text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare tab text; BEGIN --cuando validar es 0 extrae la vista de todas las geometrías, cuando es 1 solo las falsas CASE WHEN validar = '0' THEN --todas geometrías sean validas o no EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"'; ELSE tab := 'falsa'; END CASE; CASE WHEN validar = '1' THEN --las geometrias no validas EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"where st_isvalid (geom)= ''false'' or st_isvalid (geom) is null'; ELSE tab := 'todas'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_topologia_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error from( select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.id,c1.geom)as tabla where num = 1 union all select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1 left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2 from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select distinct on (ra.geom) ra.id, ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY id,geomp HAVING COUNT(*)>1 union all select a.id,concat('' duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_topologia_mv( esch text, nameview text, nametable text, pk text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', '||pk||', error from( select gid1 as '||pk||',concat(''geometries with union points'','':'','' pk '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.'||pk||' as gid1, max (c2.'||pk||') as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c2 where c1.'||pk||'<>c2.'||pk||' and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.'||pk||',c1.geom)as tabla where num = 1 union all select '||pk||'1 as '||pk||',''Junctions without union'' as errores, geom from (select r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2, r1.geom from (select a.'||pk||',(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||') r1 left join (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null union all select '||pk||'1 as '||pk||',''Points without union'' as errores, point as geom from(select r1.geom as point, r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2 from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.'||pk||'<>r2.'||pk||' and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null union all select distinct on (ra.geom) ra.'||pk||', ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),'||pk||' FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as '||pk||',''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.'||pk||' as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.'||pk||' as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.'||pk||'<>t2.'||pk||' and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select '||pk||',''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY '||pk||',geomp HAVING COUNT(*)>1 union all select a.'||pk||',concat('' duplicate geometry'','':'','' '||pk||' original '',b.'||pk||') as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.'||pk||'>b.'||pk||' and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.'||pk||',concat(''overlapping geometries'','':'','' pk '',a.'||pk||','' y '',b.'||pk||') as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||')re'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_topologia_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error from( select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.id,c1.geom)as tabla where num = 1 union all select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1 left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2 from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select distinct on (ra.geom) ra.id, ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY id,geomp HAVING COUNT(*)>1 union all select a.id,concat('' duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.chequear_topologia_vw( esch text, nameview text, nametable text, pk text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', '||pk||', error from( select gid1 as '||pk||',concat(''geometries with union points'','':'','' pk '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.'||pk||' as gid1, max (c2.'||pk||') as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c2 where c1.'||pk||'<>c2.'||pk||' and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.'||pk||',c1.geom)as tabla where num = 1 union all select '||pk||'1 as '||pk||',''Junctions without union'' as errores, geom from (select r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2, r1.geom from (select a.'||pk||',(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||') r1 left join (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null union all select '||pk||'1 as '||pk||',''Points without union'' as errores, point as geom from(select r1.geom as point, r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2 from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.'||pk||'<>r2.'||pk||' and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null union all select distinct on (ra.geom) ra.'||pk||', ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),'||pk||' FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as '||pk||',''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.'||pk||' as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.'||pk||' as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.'||pk||'<>t2.'||pk||' and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select '||pk||',''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY '||pk||',geomp HAVING COUNT(*)>1 union all select a.'||pk||',concat('' duplicate geometry'','':'','' '||pk||' original '',b.'||pk||') as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.'||pk||'>b.'||pk||' and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.'||pk||',concat(''overlapping geometries'','':'','' pk '',a.'||pk||','' y '',b.'||pk||') as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||')re'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.comprobacion_cambios_mv( esch text, nameview text, nametableia text, nametableori text, percent_detection double precision, percent_nochange double precision) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*percent_detection limite por el cual la interseccion no sera valida, percent_nochange limite por el cual no habra cambio*/ EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as SELECT re."IA/I", re."ORI/I", re.cambio, re.geom FROM (select ra.* from (SELECT te.geom as geombien, te.cambio FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')te where cambio not like ''No existe %'')re, (SELECT ter."IA/I", ter."ORI/I", ter.cambio, ter.geom FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ter where cambio like ''%IA'')ra where st_intersects(re.geombien,ra.geom) = ''true'' union all SELECT ta.* FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ta)re GROUP BY re."IA/I", re."ORI/I", re.cambio, re.geom HAVING COUNT(*)=1'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.comprobacion_cambios_vw( esch text, nameview text, nametableia text, nametableori text, percent_detection double precision, percent_nochange double precision) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*percent_detection limite por el cual la interseccion no sera valida, percent_nochange limite por el cual no habra cambio*/ EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as SELECT re."IA/I", re."ORI/I", re.cambio, re.geom FROM (select ra.* from (SELECT te.geom as geombien, te.cambio FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')te where cambio not like ''No existe %'')re, (SELECT ter."IA/I", ter."ORI/I", ter.cambio, ter.geom FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ter where cambio like ''%IA'')ra where st_intersects(re.geombien,ra.geom) = ''true'' union all SELECT ta.* FROM (select fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||''' UNION ALL select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I" from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||''' )fil UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe IA'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||''' UNION ALL select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from (select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, "'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re UNION ALL select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I", ''No existe original'' as cambio, re.geom,re.geomia from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, pol3.geom as geomia from "'|| esch ||'"."'|| nametableia||'" pol3 , "'|| esch ||'"."'|| nametableori||'" pol6 WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ta)re GROUP BY re."IA/I", re."ORI/I", re.cambio, re.geom HAVING COUNT(*)=1'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.comprobacion_de_datos_mv( esch text, nameview text, nametable text, contenido character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare namecolumn character varying; BEGIN /*Retorna una vista con una columna en la cual informa si el contenido buscado existe o no. contenido:que se quiere buscar*/ SELECT COLUMN_NAME into namecolumn FROM information_schema.COLUMNS WHERE TABLE_NAME = ''|| nametable ||'' limit 1; EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select *, ''true'' as exists from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||' union select *, ''false'' as exists from "'|| esch ||'"."'|| nametable||'" where '||namecolumn||' not in (select '||namecolumn||' from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.comprobacion_de_datos_vw( esch text, nameview text, nametable text, contenido character varying) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare namecolumn character varying; BEGIN /*Retorna una vista con una columna en la cual informa si el contenido buscado existe o no. contenido:que se quiere buscar*/ SELECT COLUMN_NAME into namecolumn FROM information_schema.COLUMNS WHERE TABLE_NAME = ''|| nametable ||'' limit 1; EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select *, ''true'' as exists from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||' union select *, ''false'' as exists from "'|| esch ||'"."'|| nametable||'" where '||namecolumn||' not in (select '||namecolumn||' from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.crear_geometrias_validas_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada con una columna de geometrías validas */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nameview ||', ra.st_makevalid as geom from (select st_makevalid (geom),* FROM "'|| esch ||'"."'|| nametable||'") ra'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nameview ||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.crear_geometrias_validas_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: crea una vista materializada con una columna de geometrías validas */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nameview ||', ra.st_makevalid as geom from (select st_makevalid (geom),* FROM "'|| esch ||'"."'|| nametable||'") ra'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.create_for_union_rail2_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion:crea una vista para hacer una union posterior */ EXECUTE 'create view "'|| esch ||'"."paraunionrail_'|| nameview ||'" as select ra.* from (select * FROM "'|| esch ||'"."'|| nametable||'") ra'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.create_for_union_vw( esch text, nameview text, nametable text, value text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion:crea una vista para hacer una union posterior */ EXECUTE 'create view "'|| esch ||'"."paraunion_'|| nameview ||'" as select ra.geom, ra.layer_ori from (select geom, '''|| value||''' as layer_ori FROM "'|| esch ||'"."'|| nametable||'") ra'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.cubierta_sin_solapes( esch text, cubierta text, cubiertasalida text, redtransporte text, hidrografia text, epsg numeric) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'drop table if exists "'||esch||'"."'||cubierta||'_valid"; create table "'||esch||'"."'||cubierta||'_valid" as select *, st_makevalid(geom) as geom2 from "'||esch||'"."'||cubierta||'"; alter table "'||esch||'"."'||cubierta||'_valid" drop column geom; alter table "'||esch||'"."'||cubierta||'_valid" rename column geom2 to geom; create index z_red_'||cubierta||'_valid_geom_dif_gidx on "'||esch||'"."'||cubierta||'_valid" using gist(geom); drop table if exists "'||esch||'"."'||cubiertasalida||'"; create table "'||esch||'"."'||cubiertasalida||'" (like "'||esch||'"."'||cubierta||'_valid"); alter table "'||esch||'"."'||cubiertasalida||'" add column geom_dif geometry (Geometry,'||epsg||'); create index z_'||cubierta||'_diferencia_geom_dif_gidx on "'||esch||'"."'||cubiertasalida||'" using gist(geom_dif); drop table if exists "'||esch||'"."'||redtransporte||'_union"; create table "'||esch||'"."'||redtransporte||'_union" as select st_makevalid(st_union(st_makevalid(geom))) as geom from "'||esch||'"."'||redtransporte||'"; create index z_red_clean_union_geom_dif_gidx on "'||esch||'"."'||redtransporte||'_union" using gist(geom); insert into "'||esch||'"."'||cubiertasalida||'" select a.*,(st_dump(st_difference(a.geom,b.geom))).geom from "'||esch||'"."'||cubierta||'_valid" a, "'||esch||'"."'||redtransporte||'_union" b where st_intersects(a.geom,b.geom); insert into "'||esch||'"."'||cubiertasalida||'" select a.*,null from "'||esch||'"."'||cubierta||'_valid" a left join "'||esch||'"."'||redtransporte||'_union" b on st_intersects(a.geom,b.geom) where b.geom is null; update "'||esch||'"."'||cubiertasalida||'" set geom_dif = geom where geom_dif is null; update "'||esch||'"."'||cubiertasalida||'" set geom = geom_dif; drop table if exists "'||esch||'"."'||cubierta||'_diferencia2"; create table "'||esch||'"."'||cubierta||'_diferencia2"(like "'||esch||'"."'||cubiertasalida||'"); alter table "'||esch||'"."'||cubierta||'_diferencia2" add column geom_dif2 geometry (Geometry,'||epsg||'); create index z_'||cubierta||'_diferencia2_geom_dif_gidx on "'||esch||'"."'||cubierta||'_diferencia2" using gist(geom_dif2); create table "'||esch||'"."'||hidrografia||'_union" as select st_makevalid(st_union(st_makevalid(geom))) as geom from "'||esch||'"."'||hidrografia||'"; create index z_hidro_general_generaliced_union_geom_dif_gidx on "'||esch||'"."'||hidrografia||'_union" using gist(geom); insert into "'||esch||'"."'||cubierta||'_diferencia2" select a.*,(st_dump(st_difference(a.geom,b.geom))).geom from "'||esch||'"."'||cubiertasalida||'" a, "'||esch||'"."'||hidrografia||'_union" b where st_intersects(a.geom,b.geom); insert into "'||esch||'"."'||cubierta||'_diferencia2" select a.*,null from "'||esch||'"."'||cubiertasalida||'" a left join "'||esch||'"."'||hidrografia||'_union" b on st_intersects(a.geom,b.geom) where b.geom is null; update "'||esch||'"."'||cubierta||'_diferencia2" set geom_dif2 = geom where geom_dif2 is null; update "'||esch||'"."'||cubierta||'_diferencia2" set geom = geom_dif2; alter table "'||esch||'"."'||cubierta||'_diferencia2" drop column geom_dif2; alter table "'||esch||'"."'||cubierta||'_diferencia2" drop column geom_dif; drop table "'||esch||'"."'||cubiertasalida||'"; drop table "'||esch||'"."'||redtransporte||'_union"; drop table "'||esch||'"."'||hidrografia||'_union"; alter table "'||esch||'"."'||cubierta||'_diferencia2" rename to "'||cubiertasalida||'";'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.dividir_por_tipo_mv( esch text, nameview text, nametable text, campo text, valor text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: divide la tabla por el topo que se quiera tipo */ EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select * from "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||''''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.dividir_por_tipo_vw( esch text, nameview text, nametable text, campo text, valor text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: divide la tabla por el topo que se quiera tipo */ EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select * from "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||''''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.extraer_centroides_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: extrae puntos que siempre estan dentro de la geometria */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS select *, row_number() OVER () as fidid from(SELECT *, ST_PointOnSurface (geom) as centroid FROM "'|| esch ||'"."'|| nametable||'")te'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fidid)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.extraer_centroides_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: extrae puntos que siempre estan dentro de la geometria */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT *, ST_PointOnSurface (geom) as centroid FROM "'|| esch ||'"."'|| nametable||'"'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.extraer_lineas_exteriores_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare colum character varying; BEGIN EXECUTE 'create table "'|| esch ||'"."'|| nameview||'_tabla" as select *,ST_boundary(st_force2d(geom)) as geom2 from "'|| esch ||'"."'||nametable||'"'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" drop column geom;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" rename geom2 to geom;'; EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview||'" as select * from "'|| esch ||'"."'|| nameview||'_tabla";'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.extraer_lineas_exteriores_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare colum character varying; BEGIN EXECUTE 'create table "'|| esch ||'"."'|| nameview||'_tabla" as select *,ST_boundary(st_force2d(geom)) as geom2 from "'|| esch ||'"."'||nametable||'"'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" drop column geom;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" rename geom2 to geom;'; EXECUTE 'create view "'|| esch ||'"."'|| nameview||'" as select * from "'|| esch ||'"."'|| nameview||'_tabla";'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.generalizar_areas( esch text, nametable text, tbl_salida text, tolerance double precision, simply double precision, pk_field character varying, min_area double precision, area_peri double precision, epsg integer) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'create table "'|| esch ||'"."'|| nametable||'_tipo" as select "'|| pk_field ||'", st_union (geom) as geom from "'|| esch ||'"."'|| nametable||'" group by "'|| pk_field ||'"; create table "'|| esch ||'"."'|| nametable||'_dissolve" as select "'|| pk_field ||'", (st_dump (geom)).geom as geom from "'|| esch ||'"."'|| nametable||'_tipo"; CREATE table "'|| esch ||'"."'|| nametable||'_dissolve_simplify" AS SELECT "'|| pk_field ||'", st_simplifypreservetopology(geom, '|| simply ||')as geom FROM "'|| esch ||'"."'|| nametable||'_dissolve"; CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify2" as select fid, "'|| pk_field ||'",geom,st_area (geom) as area from (SELECT row_number() OVER () AS fid, "'|| pk_field ||'", ST_GeomFromText(ST_AsText( (ST_Dump(geom)).geom )) as geom from "'|| esch ||'"."'|| nametable||'_dissolve_simplify")t; CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos" AS SELECT "'|| nametable||'_dissolve_simplify2".fid,"'|| pk_field ||'", (st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom AS geom, st_area((st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom) AS area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos" as SELECT fid , "'|| pk_field ||'", st_force2d(geom) as geom, area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2" UNION SELECT fid , "'|| pk_field ||'", ST_Buffer(geom,'|| tolerance||'), area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"; UPDATE "'|| esch ||'"."'|| nametable||'_union_simply_anillos" SET geom=ST_makevalid(geom) WHERE NOT ST_isValid(geom); create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" as select st_union(geom) as geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos" group by "'|| pk_field ||'" ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" as select (st_dump(geom)).geom as geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" as SELECT row_number() OVER () AS fid,geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" ;create table "'|| esch ||'"."'|| nametable||'_union" as select fid, ST_CollectionExtract(st_union(geom),3) as geom, "'|| pk_field ||'", st_area(ST_CollectionExtract(st_union(geom),3)), ((st_area(ST_CollectionExtract(st_union(geom),3)))/(((ST_Perimeter(ST_CollectionExtract(st_union(geom),3))))^2)) as relation from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" group by fid,"'|| pk_field ||'"; create table "'|| esch ||'"."'|| nametable||'_union_area" as SELECT * FROM "'|| esch ||'"."'|| nametable||'_union"; CREATE INDEX z_'|| nametable||'_union_area_geom_gidx ON "'|| esch ||'"."'|| nametable||'_union_area" USING gist (geom); create table "'|| esch ||'"."'||tbl_salida||'" as select a.*, b.geom as geom2 from "'|| esch ||'"."'|| nametable||'" a join "'|| esch ||'"."'|| nametable||'_union_area" b on a."'|| pk_field ||'" = b."'|| pk_field ||'" and st_intersects (st_setsrid(b.geom,'|| epsg||'),a.geom); alter table "'|| esch ||'"."'||tbl_salida||'" drop geom; alter table "'|| esch ||'"."'||tbl_salida||'" rename geom2 to geom; alter table "'|| esch ||'"."'||tbl_salida||'" add column ida serial; DELETE FROM "'|| esch ||'"."'||tbl_salida||'" WHERE ida IN (SELECT ida FROM (SELECT ida, ROW_NUMBER() OVER( PARTITION BY geom ORDER BY ida ) AS row_num FROM "'|| esch ||'"."'||tbl_salida||'") t WHERE t.row_num > 1 ); CREATE INDEX z_'||tbl_salida||'_generaliced_geom_gidx ON "'|| esch ||'"."'||tbl_salida||'" USING gist (geom); drop table "'|| esch ||'"."'|| nametable||'_union_area"; drop table "'|| esch ||'"."'|| nametable||'_tipo"; drop table "'|| esch ||'"."'|| nametable||'_dissolve"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump"; drop table "'|| esch ||'"."'|| nametable||'_union"; ALTER TABLE "'|| esch ||'"."'||tbl_salida||'" ALTER COLUMN geom TYPE geometry(Geometry,'|| epsg ||') USING ST_SetSRID(geom,'|| epsg||'); alter table "'|| esch ||'"."'||tbl_salida||'" add column area double precision; alter table "'|| esch ||'"."'||tbl_salida||'" add column relation double precision; update "'|| esch ||'"."'||tbl_salida||'" set area = st_area(geom); update "'|| esch ||'"."'||tbl_salida||'" set relation = ((st_area(geom))/((ST_Perimeter(geom))^2))/0.079577471546 ; create table "'|| esch ||'"."'||tbl_salida||'2" as select * from "'|| esch ||'"."'||tbl_salida||'" where area>'''|| min_area ||''' and relation > ('|| area_peri ||')^2; drop table "'|| esch ||'"."'||tbl_salida||'";'; EXECUTE 'create table "'|| esch ||'"."'|| tbl_salida||'" as select row_number ()over() as id_todo,* from "'|| esch ||'"."'||tbl_salida||'2";'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| tbl_salida ||'_geom_gidx ON "'|| esch ||'"."'|| tbl_salida||'" USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union as select st_union (geom) from "'|| esch ||'"."'|| tbl_salida||'";'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union USING gist (st_union);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nametable ||'_union;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nametable ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_boundary_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary USING gist (boundary);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_exterior_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'__union_dump_exterior_poligon_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon USING gist (geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_ddump_boundary_dump_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains as select a.geom as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dddump_contains_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_huecos;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_huecos as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_id as select row_number ()over() as id_hueco,* from "'|| esch ||'".tmp'|| nametable ||'_union_huecos;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_huecos_id_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_huecos_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nametable ||'_huecos_id;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_huecos_buffer_id_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom from (select * from "'|| esch ||'"."'|| tbl_salida||'") a, "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id b where st_intersects (a.geom,b.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_dhuecos_intersection_geom_gidx ON "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max as select id_hueco,max(area) from "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection group by id_hueco;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection a, "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_ori_huecos_id_gidx ON "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo as select b.*,y.id_todo from "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id y join "'|| esch ||'".tmp'|| nametable ||'_huecos_id b on y.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_id_hueco_id_todo_gidx ON "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori as select geom,id_todo from "'|| esch ||'"."'|| tbl_salida||'" union all select geom,id_todo from "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_hueco_geom_ori_gidx ON "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas as select st_union(geom),id_todo from "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori group by id_todo;'; EXECUTE 'update "'|| esch ||'"."'|| tbl_salida||'" a set geom = b.st_union from "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas b where a.id_todo = b.id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'"."'||tbl_salida||'2";'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||';'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_huecos;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.generalizar_areas( esch text, nametable text, tbl_salida text, tolerance double precision, simply double precision, pk_field character varying, min_area double precision, epsg integer) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'create table "'|| esch ||'"."'|| nametable||'_tipo" as select "'|| pk_field ||'", st_union (geom) as geom from "'|| esch ||'"."'|| nametable||'" group by "'|| pk_field ||'"; create table "'|| esch ||'"."'|| nametable||'_dissolve" as select "'|| pk_field ||'", (st_dump (geom)).geom as geom from "'|| esch ||'"."'|| nametable||'_tipo"; CREATE table "'|| esch ||'"."'|| nametable||'_dissolve_simplify" AS SELECT "'|| pk_field ||'", st_simplifypreservetopology(geom, '|| simply ||')as geom FROM "'|| esch ||'"."'|| nametable||'_dissolve"; CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify2" as select fid, "'|| pk_field ||'",geom,st_area (geom) as area from (SELECT row_number() OVER () AS fid, "'|| pk_field ||'", ST_GeomFromText(ST_AsText( (ST_Dump(geom)).geom )) as geom from "'|| esch ||'"."'|| nametable||'_dissolve_simplify")t; CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos" AS SELECT "'|| nametable||'_dissolve_simplify2".fid,"'|| pk_field ||'", (st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom AS geom, st_area((st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom) AS area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos" as SELECT fid , "'|| pk_field ||'", st_force2d(geom) as geom, area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2" UNION SELECT fid , "'|| pk_field ||'", ST_Buffer(geom,'|| tolerance||'), area FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"; UPDATE "'|| esch ||'"."'|| nametable||'_union_simply_anillos" SET geom=ST_makevalid(geom) WHERE NOT ST_isValid(geom); create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" as select st_union(geom) as geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos" group by "'|| pk_field ||'" ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" as select (st_dump(geom)).geom as geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" as SELECT row_number() OVER () AS fid,geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" ;create table "'|| esch ||'"."'|| nametable||'_union" as select fid, ST_CollectionExtract(st_union(geom),3) as geom, "'|| pk_field ||'", st_area(ST_CollectionExtract(st_union(geom),3)) from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" group by fid,"'|| pk_field ||'"; create table "'|| esch ||'"."'|| nametable||'_union_area" as SELECT * FROM "'|| esch ||'"."'|| nametable||'_union" where st_area>'''|| min_area ||'''; CREATE INDEX z_'|| nametable||'_union_area_geom_gidx ON "'|| esch ||'"."'|| nametable||'_union_area" USING gist (geom); create table "'|| esch ||'"."'||tbl_salida||'" as select a.*, b.geom as geom2 from "'|| esch ||'"."'|| nametable||'" a join "'|| esch ||'"."'|| nametable||'_union_area" b on a."'|| pk_field ||'" = b."'|| pk_field ||'" and st_intersects (st_setsrid(b.geom,'|| epsg||'),a.geom); alter table "'|| esch ||'"."'||tbl_salida||'" drop geom; alter table "'|| esch ||'"."'||tbl_salida||'" rename geom2 to geom; alter table "'|| esch ||'"."'||tbl_salida||'" add column ida serial; DELETE FROM "'|| esch ||'"."'||tbl_salida||'" WHERE ida IN (SELECT ida FROM (SELECT ida, ROW_NUMBER() OVER( PARTITION BY geom ORDER BY ida ) AS row_num FROM "'|| esch ||'"."'||tbl_salida||'") t WHERE t.row_num > 1 ); CREATE INDEX z_'||tbl_salida||'_generaliced_geom_gidx ON "'|| esch ||'"."'||tbl_salida||'" USING gist (geom); drop table "'|| esch ||'"."'|| nametable||'_union_area"; drop table "'|| esch ||'"."'|| nametable||'_tipo"; drop table "'|| esch ||'"."'|| nametable||'_dissolve"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"; drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2"; drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump"; drop table "'|| esch ||'"."'|| nametable||'_union"; ALTER TABLE "'|| esch ||'"."'||tbl_salida||'" ALTER COLUMN geom TYPE geometry(Geometry,'|| epsg ||') USING ST_SetSRID(geom,'|| epsg||'); '; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.nineintersection_matrix_mv( validar boolean, esch text, nameview text, name_table1 text, name_table2 text, matriz text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare geome1 character varying; declare geome2 character varying; declare tab text; BEGIN /*Descripcion: crea una vista o vista materializada que comprueba la matriz 9d y extrae si es cierto su intersección si no hay correspondencia retorna vista vacia*/ EXECUTE 'alter table "'|| esch ||'"."'|| name_table1 ||'" rename to __nmtab1'; EXECUTE 'alter table "'|| esch ||'"."'|| name_table2 ||'" rename to __nmtab2'; SELECT f_geometry_column into geome1 FROM geometry_columns WHERE f_table_name = '__nmtab1'; SELECT f_geometry_column into geome2 FROM geometry_columns WHERE f_table_name = '__nmtab2'; EXECUTE 'alter table "'|| esch ||'".__nmtab1 rename to "'|| name_table1 ||'"'; EXECUTE 'alter table "'|| esch ||'".__nmtab2 rename to "'|| name_table2 ||'"'; CASE WHEN validar = '0' THEN EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select ROW_NUMBER() OVER () as fid_'|| name_table1 ||',* from (SELECT st_intersection(a.'|| geome1 ||',b.'|| geome2 ||') as geom from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true)re'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" ( fid_'|| name_table1 ||')'; ELSE tab := 'geom1'; END CASE; CASE WHEN validar = '1' THEN EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select ROW_NUMBER() OVER () as fid_'|| name_table1 ||',* from (SELECT a.'|| geome1 ||',count(a.fid)as numgeomtouch from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true group by a.geom,fid)re'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" ( fid_'|| name_table1 ||')'; ELSE tab := 'intersectiongeom'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.nineintersection_matrix_vw( validar boolean, esch text, nameview text, name_table1 text, name_table2 text, matriz text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare geome1 character varying; declare geome2 character varying; declare tab text; BEGIN /*Descripcion: crea una vista o vista materializada que comprueba la matriz 9d y extrae si es cierto su intersección si no hay correspondencia retorna vista vacia*/ EXECUTE 'alter table "'|| esch ||'"."'|| name_table1 ||'" rename to __nmtab1'; EXECUTE 'alter table "'|| esch ||'"."'|| name_table2 ||'" rename to __nmtab2'; SELECT f_geometry_column into geome1 FROM geometry_columns WHERE f_table_name = '__nmtab1'; SELECT f_geometry_column into geome2 FROM geometry_columns WHERE f_table_name = '__nmtab2'; EXECUTE 'alter table "'|| esch ||'".__nmtab1 rename to "'|| name_table1 ||'"'; EXECUTE 'alter table "'|| esch ||'".__nmtab2 rename to "'|| name_table2 ||'"'; CASE WHEN validar = '0' THEN EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select ROW_NUMBER() OVER (ORDER BY fid_'|| name_table1 ||' ) as fid,* from (SELECT a.fid as fid_'|| name_table1 ||',b.fid as fid_'|| name_table2 ||',st_intersection(a.'|| geome1 ||',b.'|| geome2 ||') from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true)re'; ELSE tab := 'geom1'; END CASE; CASE WHEN validar = '1' THEN EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select ROW_NUMBER() OVER (ORDER BY fid_'|| name_table1 ||' ) as fid,* from (SELECT a.fid as fid_'|| name_table1 ||',a.'|| geome1 ||',count(a.fid)as numgeomtouch from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true group by a.geom,fid_'|| name_table1 ||')re'; ELSE tab := 'intersectiongeom'; END CASE; Return tab; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_mv( esch text, nametable text, nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'||nameview||'" AS SELECT st_union(st_makevalid(pol3.geom)) AS geom, pol3.layer_ori FROM ( SELECT re.fid, re.geom, re.layer_ori FROM ( SELECT pol3_1.bgeom, pol3_1.id AS fid, pol6.layer_ori, pol6.area, pol6.geom FROM ( SELECT ra.bgeom, max(ra.area) AS area, ra.id FROM ( SELECT re_1.bgeom, re_1.layer_ori, re_1.id, re_1.area, re_1.geom FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area, pol6_1.layer_ori, pol3_2.id, pol3_2.bgeom, pol3_2.geom FROM ( SELECT DISTINCT ON ((st_makepolygon(ra_1.geom))) row_number() OVER () AS id, st_buffer(ra_1.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom, st_makepolygon(ra_1.geom) AS geom, ra_1.layer_ori FROM ( SELECT re_2.geom, re_2.contiene, st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches, st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer, a.layer_ori FROM "'|| esch ||'"."'|| nametable||'" a, ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene, (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom FROM "'|| esch ||'"."'|| nametable||'") re_2) ra_1 WHERE ra_1.contiene = ''false'' AND ra_1.st_touches = ''true'') pol3_2, "'|| esch ||'"."'|| nametable||'" pol6_1 WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) ra GROUP BY ra.id, ra.bgeom) pol3_1 LEFT JOIN ( SELECT re_1.bgeom, re_1.layer_ori, re_1.id, re_1.area, re_1.geom FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area, pol6_1.layer_ori, pol3_2.id, pol3_2.bgeom, pol3_2.geom FROM ( SELECT DISTINCT ON ((st_makepolygon(ra.geom))) row_number() OVER () AS id, st_buffer(ra.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom, st_makepolygon(ra.geom) AS geom, ra.layer_ori FROM ( SELECT re_2.geom, re_2.contiene, st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches, st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer, a.layer_ori FROM "'|| esch ||'"."'|| nametable||'" a, ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene, (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom FROM "'|| esch ||'"."'|| nametable||'") re_2) ra WHERE ra.contiene = ''false'' AND ra.st_touches = ''true'') pol3_2, "'|| esch ||'"."'|| nametable||'" pol6_1 WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) pol6 ON pol3_1.area = pol6.area) re UNION ALL SELECT "'|| nametable||'".fid, "'|| nametable||'".geom, "'|| nametable||'".layer_ori FROM "'|| esch ||'"."'|| nametable||'") pol3 GROUP BY pol3.layer_ori;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl2( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||' USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";'; EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx ON "'|| esch ||'"."'|| nametable||'" USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist (st_union);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist (boundary);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist (geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as select a.geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from "'|| esch ||'"."'|| nametable||'" a, "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b where st_intersects (a.geom,b.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a, "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom as select id_hueco,geom from "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id union all select id_hueco,geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ohuecos_ori_geom_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid as select id_todo,id_hueco, st_pointonsurface(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_ori as select id_hueco,st_union(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom group by id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_centroid_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid USING gist (geom);'; EXECUTE 'create INDEX '|| esch ||'tmp'|| nameview ||'_huecos_ori_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_ori USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori as select a.geom,b.id_todo from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid b, "'|| esch ||'".tmp'|| nameview ||'_huecos_ori a where st_intersects (a.geom,b.geom) and a.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'tmp'|| nameview ||'_huecos_id_ori_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union as select st_union (geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori group by id_todo;'; EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union b where a.id_todo = b.id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_ori;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||' USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";'; EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx ON "'|| esch ||'"."'|| nametable||'" USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist (st_union);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist (boundary);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist (geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as select a.geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from (select * from "'|| esch ||'"."'|| nametable||'" where layer_ori = ''cub'') a, "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b where st_intersects (a.geom,b.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a, "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ori_huecos_id_gidx ON "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo as select b.*,y.id_todo from "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id y join "'|| esch ||'".tmp'|| nameview ||'_huecos_id b on y.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_id_hueco_id_todo_gidx ON "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori as select geom,id_todo from "'|| esch ||'"."'|| nametable||'" union all select geom,id_todo from "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_hueco_geom_ori_gidx ON "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas as select st_union(geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori group by id_todo;'; EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas b where a.id_todo = b.id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl_todo( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||' USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";'; EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx ON "'|| esch ||'"."'|| nametable||'" USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist (st_union);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist (boundary);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist (geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as select a.geom as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from "'|| esch ||'"."'|| nametable||'" a, "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b where st_intersects (a.geom,b.geom);'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a, "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ori_huecos_id_gidx ON "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo as select b.*,y.id_todo from "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id y join "'|| esch ||'".tmp'|| nameview ||'_huecos_id b on y.id_hueco = b.id_hueco;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_id_hueco_id_todo_gidx ON "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori as select geom,id_todo from "'|| esch ||'"."'|| nametable||'" union all select geom,id_todo from "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;'; EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_hueco_geom_ori_gidx ON "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori USING gist (geom);'; EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas as select st_union(geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori group by id_todo;'; EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas b where a.id_todo = b.id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori;'; EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_vw( esch text, nametable text, nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN EXECUTE 'CREATE OR REPLACE VIEW "'|| esch ||'"."'||nameview||'" AS SELECT st_union(st_makevalid(pol3.geom)) AS geom, pol3.layer_ori FROM ( SELECT re.fid, re.geom, re.layer_ori FROM ( SELECT pol3_1.bgeom, pol3_1.id AS fid, pol6.layer_ori, pol6.area, pol6.geom FROM ( SELECT ra.bgeom, max(ra.area) AS area, ra.id FROM ( SELECT re_1.bgeom, re_1.layer_ori, re_1.id, re_1.area, re_1.geom FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area, pol6_1.layer_ori, pol3_2.id, pol3_2.bgeom, pol3_2.geom FROM ( SELECT DISTINCT ON ((st_makepolygon(ra_1.geom))) row_number() OVER () AS id, st_buffer(ra_1.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom, st_makepolygon(ra_1.geom) AS geom, ra_1.layer_ori FROM ( SELECT re_2.geom, re_2.contiene, st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches, st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer, a.layer_ori FROM "'|| esch ||'"."'|| nametable||'" a, ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene, (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom FROM "'|| esch ||'"."'|| nametable||'") re_2) ra_1 WHERE ra_1.contiene = ''false'' AND ra_1.st_touches = ''true'') pol3_2, "'|| esch ||'"."'|| nametable||'" pol6_1 WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) ra GROUP BY ra.id, ra.bgeom) pol3_1 LEFT JOIN ( SELECT re_1.bgeom, re_1.layer_ori, re_1.id, re_1.area, re_1.geom FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area, pol6_1.layer_ori, pol3_2.id, pol3_2.bgeom, pol3_2.geom FROM ( SELECT DISTINCT ON ((st_makepolygon(ra.geom))) row_number() OVER () AS id, st_buffer(ra.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom, st_makepolygon(ra.geom) AS geom, ra.layer_ori FROM ( SELECT re_2.geom, re_2.contiene, st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches, st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer, a.layer_ori FROM "'|| esch ||'"."'|| nametable||'" a, ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene, (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom FROM "'|| esch ||'"."'|| nametable||'") re_2) ra WHERE ra.contiene = ''false'' AND ra.st_touches = ''true'') pol3_2, "'|| esch ||'"."'|| nametable||'" pol6_1 WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) pol6 ON pol3_1.area = pol6.area) re UNION ALL SELECT "'|| nametable||'".fid, "'|| nametable||'".geom, "'|| nametable||'".layer_ori FROM "'|| esch ||'"."'|| nametable||'") pol3 GROUP BY pol3.layer_ori;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.renombrar_multiples_tablas( _schema text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE row record; BEGIN FOR row IN SELECT table_name, table_schema FROM information_schema.tables where table_schema = ''||_schema || '' LOOP EXECUTE 'Alter TABLE "'||row.table_schema||'"."'||row.table_name||'" rename to "'||row.table_name||'_ft"'; EXECUTE 'create table TABLE "'||row.table_schema||'"."'||row.table_name||'" as select * from "'||row.table_schema||'"."'||row.table_name||'_ft"'; END LOOP; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_mv2( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as (select distinct on (ra.geom) st_pointonsurface (ra.geom) as geom, ra.fid, ''hollow space'' as errores from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),fid FROM "'|| esch ||'"."'|| nametable||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable||'") re) ra where ra.contiene = ''false'' and ra.st_touches = ''true'') UNION ALL (SELECT re.re as geom, re.fid, ra.ra as errores FROM (SELECT ST_SetSRID(ST_MakePoint (a[2]::double precision, a[4]::double precision),4326) as re, fid from (select regexp_split_to_array( to_tsvector(ST_IsValidReason(st_force2d(geom)))::text,''''''''), fid FROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom))as dt(a)) re, (select ST_IsValidReason(geom) as ra, fid fROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom)) ra where ra.fid = re.fid)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_mv( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error,geom from( select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.id,c1.geom)as tabla where num = 1 union all select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1 left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2 from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select distinct on (ra.geom) ra.id, ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY id,geomp HAVING COUNT(*)>1 union all select a.id,concat('' duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re'; EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_vw2( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as (select distinct on (ra.geom) st_pointonsurface (ra.geom) as geom, ra.fid, ''hollow space'' as errores from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),fid FROM "'|| esch ||'"."'|| nametable||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable||'") re) ra where ra.contiene = ''false'' and ra.st_touches = ''true'') UNION ALL (SELECT re.re as geom, re.fid, ra.ra as errores FROM (SELECT ST_SetSRID(ST_MakePoint (a[2]::double precision, a[4]::double precision),4326) as re, fid from (select regexp_split_to_array( to_tsvector(ST_IsValidReason(st_force2d(geom)))::text,''''''''), fid FROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom))as dt(a)) re, (select ST_IsValidReason(geom) as ra, fid fROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom)) ra where ra.fid = re.fid)'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_vw( esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN /*Descripcion: Saca errores topológicos */ Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error,geom from( select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from (select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom, count (*) as num from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 group by c1.id,c1.geom)as tabla where num = 1 union all select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1 left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2 on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2 from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 left join "'|| esch ||'"."'|| nametable ||'" r2 on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null union all select distinct on (ra.geom) ra.id, ''Hollow space'' as errores,st_centroid (ra.geom) as geom from (select re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom), ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, ((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom) AS geom FROM "'|| esch ||'"."'|| nametable ||'") re) ra where ra.contiene is null and ra.st_touches = ''true'' union all select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2 on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null union all select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r GROUP BY id,geomp HAVING COUNT(*)>1 union all select a.id,concat('' duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005)) union all select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.seleccion_total_vw( esch text, nameview text, nametable text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: hace una busqueda por el contenido que se añada en el where */ EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as select * from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.seleccion_total_mv( esch text, nameview text, nametable text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: hace una busqueda por el contenido que se añada en el where */ EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select * from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.topotoleranceloop( esquema_dest text, destino text, anedge integer, maxtolerance double precision) RETURNS double precision LANGUAGE 'plpgsql' COST 100 STABLE STRICT AS $BODY$DECLARE tol float8; sql varchar; BEGIN tol := maxtolerance; LOOP sql := 'select topology.toTopoGeom(geom,''topo_'||destino||''', 1,' || tol || ') from "'||esquema_dest||'"."'||destino||'_topo"'; BEGIN RAISE DEBUG 'Running %', sql; EXECUTE sql; RETURN tol; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'Simplification of edge % with tolerance % failed: %', anedge, tol, SQLERRM; tol := round( (tol/1.25) * 1e8 ) / 1e8; -- round to get to zero quicker IF tol = 0 THEN RAISE EXCEPTION '%', SQLERRM; END IF; END; END LOOP; END $BODY$; CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_mv( esch text, nameview text, nametable_geom text, tabla_centroides text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare colum character varying; BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as SELECT '|| contenido ||',a.geom FROM (SELECT '|| contenido ||', b.geom, st_contains(b.geom, a.centroid) AS st_contains FROM "'|| esch ||'"."'|| nametable_geom||'" b, "'|| esch ||'"."'||tabla_centroides||'" a ) a WHERE a.st_contains = ''true'''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_tbl( esch text, nameview text, nametable_geom text, tabla_centroides text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare colum character varying; BEGIN EXECUTE 'drop table if exists "'|| esch ||'".centroides;'; EXECUTE 'create table "'|| esch ||'".centroides as SELECT * FROM "'|| esch ||'"."'||tabla_centroides||'";'; EXECUTE 'alter table "'|| esch ||'".centroides drop column geom;'; EXECUTE 'alter table "'|| esch ||'".centroides rename centroid to geom;'; EXECUTE 'create INDEX ZxasCabW7iWwiBeL8_centroides_geom_gidx ON "'|| esch ||'".centroides USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta;'; EXECUTE 'create table "'|| esch ||'".cubierta as select geom ,layer_ori,row_number ()over() as id_srm from "'|| esch ||'"."'|| nametable_geom||'" group by layer_ori, geom;'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta_centroides;'; EXECUTE 'create table "'|| esch ||'".cubierta_centroides as select layer_ori,st_pointonsurface(geom) as geom, id_srm from "'|| esch ||'".cubierta;'; EXECUTE 'create INDEX ZxasCabW7iWwiBeL8_cubierta_centroides_geom_gidx ON "'|| esch ||'".cubierta_centroides USING gist (geom);'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta_centroides_valores;'; EXECUTE 'create table "'|| esch ||'".cubierta_centroides_valores as SELECT DISTINCT ON (h.geom) s.*, h.id_srm, h.geom as geom_cubier FROM "'|| esch ||'".centroides s LEFT JOIN "'|| esch ||'".cubierta_centroides h ON st_dwithin(h.geom, s.geom, 200::double precision) ORDER BY h.geom, (st_distance(h.geom, s.geom));'; EXECUTE 'alter table "'|| esch ||'".cubierta_centroides_valores drop column geom;'; EXECUTE 'alter table "'|| esch ||'".cubierta_centroides_valores rename geom_cubier to geom;'; EXECUTE 'drop table if exists "'|| esch ||'"."'|| nameview ||'";'; EXECUTE 'create table "'|| esch ||'"."'|| nameview ||'" as select a.*,b.geom as geom2 from "'|| esch ||'".cubierta b, "'|| esch ||'".cubierta_centroides_valores a where a.id_srm = b.id_srm;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview ||'" drop column geom;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview ||'" rename geom2 to geom;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview ||'" drop column if exists ogc_fid;'; EXECUTE 'alter table "'|| esch ||'"."'|| nameview ||'" drop column if exists id_srm;'; EXECUTE 'update "'|| esch ||'"."'|| nameview ||'" set shape_leng = ST_Perimeter(geom);'; EXECUTE 'update "'|| esch ||'"."'|| nameview ||'" set shape_area = ST_area(geom);'; EXECUTE 'drop table if exists "'|| esch ||'".centroides;'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta;'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta_centroides;'; EXECUTE 'drop table if exists "'|| esch ||'".cubierta_centroides_valores;'; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_vw( esch text, nameview text, nametable_geom text, tabla_centroides text, contenido text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare colum character varying; BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as SELECT '|| contenido ||',a.geom FROM (SELECT '|| contenido ||', b.geom, st_contains(b.geom, a.centroid) AS st_contains FROM "'|| esch ||'"."'|| nametable_geom||'" b, "'|| esch ||'"."'||tabla_centroides||'" a ) a WHERE a.st_contains = ''true'''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.union_multiple_mw( _schema text, _nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview); BEGIN EXECUTE ( SELECT 'CREATE materialized view ' || _safe_target || ' AS TABLE ' || string_agg(quote_ident(table_schema) || '.' || quote_ident(table_name), ' UNION ALL TABLE ') FROM information_schema.views WHERE table_schema = _schema AND table_name LIKE ('paraunion%') ); END $BODY$; CREATE OR REPLACE FUNCTION funciones.union_multiple_rail2_mw( _schema text, _nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview); BEGIN EXECUTE ( SELECT 'CREATE materialized view ' || _safe_target || ' AS TABLE ' || string_agg(quote_ident(table_schema) || '.' || quote_ident(table_name), ' UNION ALL TABLE ') FROM information_schema.views WHERE table_schema = _schema AND table_name LIKE ('paraunionrail%') ); END $BODY$; CREATE OR REPLACE FUNCTION funciones.union_multiple_rail2_vw( _schema text, _nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview); BEGIN EXECUTE ( SELECT 'CREATE view ' || _safe_target || ' AS TABLE ' || string_agg(quote_ident(table_schema) || '.' || quote_ident(table_name), ' UNION ALL TABLE ') FROM information_schema.views WHERE table_schema = _schema AND table_name LIKE ('paraunionrail%') ); END $BODY$; CREATE OR REPLACE FUNCTION funciones.union_multiple_vw( _schema text, _nameview text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview); BEGIN EXECUTE ( SELECT 'CREATE view ' || _safe_target || ' AS TABLE ' || string_agg(quote_ident(table_schema) || '.' || quote_ident(table_name), ' UNION ALL TABLE ') FROM information_schema.views WHERE table_schema = _schema AND table_name LIKE ('paraunion%') ); END $BODY$; CREATE OR REPLACE FUNCTION funciones.verficar_datos_mv( campo text, valor text, esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: verifica datos */ EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||''''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.verficar_datos_vw( campo text, valor text, esch text, nameview text, nametable text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$BEGIN /*Descripcion: verifica datos */ EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||''''; END; $BODY$; CREATE OR REPLACE FUNCTION funciones.zzloop( ) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE r geometry; BEGIN FOR r IN SELECT geom FROM "ZxasCabW7iWwiBeL8".edipbpruebaarea LOOP BEGIN -- may be unnecessary UPDATE "ZxasCabW7iWwiBeL8".llenar SET geom = st_union (geom,r); EXCEPTION when others THEN UPDATE "ZxasCabW7iWwiBeL8".llenar SET geom = st_union (geom,r); END; END LOOP; END $BODY$;