db.silva.inrae.fr/db_biljou_param - db_biljou_param on db.silva.inrae.fr

Function: ps_initialise_emprise

Schema

public

Owner

albenard

Descriptions

Fonction de vérification des données d'emprise pour le logiciel de fabrication de fichiers d'entrée pour le modèle biljou

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_initialise_emprise (
 mode_emprise text,
 table_tmp text,
 system_geo text = NULL::text,
 nom_geometrie text = NULL::text
)
RETURNS text LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS
$span$
-- Auteur : Alain Benard
-- Date de mise à jour : 09/07/2019
-- Cette fonction sert à compléter l'initialisation d'une table temporaire créée par le logiciel chargé de fournir les fichiers de paramétrage (météo, sol, peuplement) nécessaire au fonctionnement du modèle biljou.
-- Pour le mode XY : Elle vérifie le système de géoréférencement, la validité des X, Y fournis ainsi que l'unicité du champ cle (identifiant de chaque ligne du fichier d'entrée).
-- Elle crée et alimente les colonnes x_srid et y_srid () à partir de x et y) ou srid représente le code epsg du système de géoréférencement tel qu'on peut le trouver dans le champ sri_code_epsg de la table tr_srid_sri.     
-- Pour le mode MAILLE_COMPLETE : elle alimente les colonnes x_srid, y_srid et id_maille_emprise avec les informations issues du raster associé à la géométrie.(pas de traitement vecteur pour l'instant)
-- Pour le mode MAILLE_PARTIELLE : elle vérifie l'existence des id_maille_emprise dans le raster associé à la géométrie, alimente également x_srid et y_srid et vérifie l'unicité du champ cle (identifiant de chaque ligne du fichier d'entrée)..
--
--
-- paramètres :
-- mode_emprise : 3 modes de choix pour sélectionner l'emmprise :
-- L'opérateur fournit un fichier avec des X et Y en indiquant le système de géoréférencement. (XY)
-- L'opérateur indique une géométrie (voir table tr_geometrie_geo de la base db_biljou_param) avec 2 possibilités :
-- L'ensemble des coordonnées de la géométrie (id_maille) est à prendre en compte (MAILLE_COMPLETE)
-- L'utilisateur fournit en plus un fichier comportant uniquement le id_maille concernés (MAILLE_PARTIELLE)login de l'utilisateur à créer
-- table_tmp : nom de la table préalablement créée par l'appelant et contenant les données des fichiers utilisateurs le cas échéant  (X;Y ou id_maille)
-- system_geo : Système de géoréférencement si l'utilisateur a fourni un fichier X;Y
-- nom_geometrie : Dans les 2 derniers cas d'usage la fonction doit connaître la géométrie choisie (champ geo_nom de la table tr_geometrie_geo)

-- déclaration des variables de traitements
DECLARE
   requete text;
status boolean;
epsg integer;
curs_table refcursor; -- Curseur recevant les informations de la table / requête à analyser
   rec_table   record; -- Enregistrement contenant la ligne en cours de traitement
table_raster text;
compteur  integer;
BEGIN

--Vérification existence table_tmp
 select into status ps_existe_table_temporaire(table_tmp);
 if status = false then
return 'Erreur : la table temporaire ' || table_tmp || ' est inexistante.';
 end if;
 
 CASE -- Traitement spécifique à chaque mode de traitement
     WHEN mode_emprise = 'XY' THEN
-- Vérification existence systeme géoréférencement.
select sri_code_epsg into epsg from tr_srid_sri where sri_nom = system_geo;
if epsg is null then
return 'Erreur : le système de géoréférencement ' || system_geo || ' est inexistant dans la base de données.';
end if;
-- vérification absence doublon et null
requete = 'select count(*) -(select count(*) from (select distinct(x,y) from ' ||table_tmp || ' where x is not null and y is not null) ssreq) from ' || table_tmp|| ';';
execute requete into compteur;
if compteur > 0 then
return 'Erreur : il existe au moins ' || compteur || ' valeur(s) d'' X,Y en doublon ou non renseignée(s)';
end if;

-- vérification des valeurs x et y par rapport aux bornes autorisées du système de géoréférencement.
requete = 'select count(*) as nb_xy_errone from ' || table_tmp || ',tr_srid_sri
where (((x not between sri_xmin and sri_xmax) or (y not between sri_ymin and sri_ymax)) and sri_code_epsg =' || epsg || ') or (x is null) or (y is null);' ;
execute requete into compteur;
if compteur > 0 then
return 'Erreur : il existe des valeurs de X ou Y qui ne correspondent pas aux valeurs autorisées pour le système de géoréférencement choisi';
else -- recopie des colonnes x et y d'origine dans des colonnes précisant leur srid
requete = 'alter table ' || table_tmp || ' add column x_' || epsg || ' double precision, add column y_' || epsg || ' double precision;';
execute requete;
requete = 'update ' || table_tmp || ' set x_' || epsg || '=x, y_' || epsg || '=y;';
execute requete;
end if;

     WHEN (mode_emprise = 'MAILLE_COMPLETE') or (mode_emprise = 'MAILLE_PARTIELLE' )  THEN
--Vérification de l'existence de la géométrie
select geo_table into table_raster from tr_geometrie_geo where geo_nom = nom_geometrie;
if table_raster is null then
return 'Erreur : la géométrie ' || nom_geometrie || ' est inexistante dans la base de données.';
end if;
--Vérification existence table_raster
select into status ps_existe_table('public',table_raster);
if status = false then
return 'Erreur : la table ' || table_raster || ' référencée dans la géométrie ' || nom_geometrie || ' est inexistante.';
end if;
-- Récupération code epsg du raster.
        requete = 'select st_srid(rast) from ' || table_raster || ';';
           execute requete into epsg ;
-- Création des colonnes x_srid et y_srid
requete = 'alter table ' || table_tmp || ' add column x_' || epsg || ' double precision, add column y_' || epsg || ' double precision;';
execute requete;
-- Dans le cas du mode 'MAILLE_PARTIELLE' il y a vérification de l'existence des ID de maille fournis alors que dans le cas 'MAILLE_COMPLETE' il y a seulement récupération de tous les id de maille du raster
if mode_emprise= 'MAILLE_PARTIELLE' then
--Vérification des id maille emprise fournis par l'utilisateur (pas de doublons, pas de null et existence dans le raster concerné)

--requete = 'select count(*) from (select count(id_maille_emprise) from ' || table_tmp || ' group by id_maille_emprise having count(id_maille_emprise) >1)ssreq;';
--execute requete into compteur;
--if compteur > 0 then
-- return 'Erreur : il existe au moins ' || compteur || ' valeur(s) d''id maille utilisateur en doublon';
--end if;

--requete = 'select count(*) from (select id_maille_emprise from ' || table_tmp || ' where id_maille_emprise is null)ssreq;';
--execute requete into compteur;
--if compteur > 0 then
-- return 'Erreur : il existe au moins ' || compteur || ' valeur(s) d''id maille utilisateur non renseignée(s)';
--end if;
-- vérification absence doublon et null sur id_maille_emprise
requete = 'select count(*) -(select count(*) from (select distinct(id_maille_emprise) from ' ||table_tmp || ' where id_maille_emprise is not null) ssreq) from ' || table_tmp|| ';';
execute requete into compteur;
if compteur > 0 then
return 'Erreur : il existe au moins ' || compteur || ' valeur(s) d'' id maille utilisateur en doublon ou non renseignée(s)';
end if;

requete = 'with
tuilage as (select ST_Tile(rast, 50, 50, TRUE)as rast from ' || table_raster ||' ),
valeur_pixel as (select st_value(rast,x,y,true) as valeur, st_x(st_pixelascentroid(rast,x,y)) as x_raster, st_y(st_pixelascentroid(rast,x,y))as y_raster from tuilage cross join generate_series(1,st_width(rast)) as x cross join generate_series(1,st_height(rast))as y where st_value(rast,x,y,true) is not null)
update ' || table_tmp || ' set x_' || epsg || '= x_raster, y_' || epsg ||' = y_raster from valeur_pixel where id_maille_emprise = valeur;';
execute requete;

requete = 'select count(*) from ' || table_tmp || ' where x_' || epsg || ' is null;';
execute requete into compteur;
if compteur <> 0 then -- Il y a au moins une maille pour laquelle il n'a pas été possible d'extraire les informations du pixel (x et y), signifiant que l'id_maille fourni ne correspond pas à un pixel avec des données dans le raster concerné.
return 'Erreur : il existe au moins ' || compteur|| ' valeur(s) d''id maille utilisateur inexistante(s) au sein du raster concerné';
end if;

-- vérification absence doublon et null sur champ cle
requete = 'select count(*) -(select count(*) from (select distinct(cle) from ' ||table_tmp || ' where cle is not null) ssreq) from ' || table_tmp|| ';';
execute requete into compteur;
if compteur > 0 then
return 'Erreur : il existe au moins ' || compteur || ' valeur(s) de cle en doublon ou non renseignée(s)';
end if;

else -- Cas MAILLE_COMPLETE
--Alimentation de la colonne id_maille_emprise avec les informations des pixels du raster concerné (x_srid,y_srid et id_maille)
requete = 'with
tuilage as (select ST_Tile(rast, 50, 50, TRUE)as rast from ' || table_raster ||' ),
valeur_pixel as (select st_value(rast,x,y,true) as valeur, st_x(st_pixelascentroid(rast,x,y)) as x_raster, st_y(st_pixelascentroid(rast,x,y))as y_raster from tuilage cross join generate_series(1,st_width(rast)) as x cross join generate_series(1,st_height(rast))as y where st_value(rast,x,y,true) is not null)
insert into ' || table_tmp || '(id_maille_emprise, x_' || epsg || ',y_' || epsg || ')
select valeur, x_raster,y_raster from valeur_pixel;';

--select valeur,x_raster,y_raster from (select st_value(rast,x,y,true) as valeur , st_x(st_pixelascentroid(rast,x,y)) as x_raster,st_y(st_pixelascentroid(rast,x,y))as y_raster  from ' || table_raster || ' cross join generate_series(1,st_width(rast)) as x cross join generate_series(1,st_height(rast))as y)ssreq
--where valeur is not null;';
execute requete;
end if;
--requete = 'update ' || table_tmp || ' set x_' || epsg || '=x, y_' || epsg || '=y;';
--execute requete;

     
 else
         return 'Erreur : le paramètre mode_emprise doit prendre une des 3 valeurs XY, MAILLE_COMPLETE ou MAILLE_PARTIELLE';
 END CASE;

--  RAISE NOTICE 'Creation du compte - % ',req;
 return 'ok' ;

EXCEPTION
WHEN OTHERS THEN
raise notice '% % - %', sqlstate, sqlerrm,requete;
return 'erreur ' || requete || ' - ' || sqlerrm;
END;
$span$;

COMMENT ON FUNCTION public.ps_initialise_emprise(mode_emprise text, table_tmp text, system_geo text, nom_geometrie text)
IS 'Fonction de vérification des données d''emprise pour le logiciel de fabrication de fichiers d''entrée pour le modèle biljou';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 07/12/2018 13:23