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

Function: ps_extraction_source

Schema

public

Owner

albenard

Descriptions

Fonction d'extraction des informations d'une source (id_maille) pour la création de fichier param en entrée du modèle biljou

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_extraction_source (
 mode_emprise text,
 source text,
 categorie_source text,
 table_result text,
 epsg_emprise integer,
 nom_geometrie_emprise text,
 colonne_filtre text,
 tolerance integer = 5
)
RETURNS text LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
AS
$span$
-- Auteur : Alain Benard
-- Date de mise à jour : 09/07/2022 - prise en compte du mode vectoriel avec ajout du parametre tolerance
-- Cette fonction s'appuie sur une table préalalablement remplie et complétée par la procédure ps_initialise_emprise. Elle effectue des vérifications
-- (existence source/géométrie, catégorie correcte de la source, existence de la table de données) récupère diverses informations découlant de la source
-- (geometrie, srid de la géométrie) et alimente la colonne adéquate (passée en paramètre) de la table de résultat. Elle est appelée par une autre procédure
-- stockée telle que ps_extraction_meteo ou ps_extraction_complete chargée d'effectuer d'autres vérifications.
-- Au 07/09 : ajout du param_ètre 'colonne_filtre' pour optimiser le nombre de lignes analysés (par exemple si beaucoup moins de maille peuplement rensiegnées que de mailles sol)
-- Ajout d'une clause 'limit 100000000' seule astuce trouvée pour que l'optimiseur de requête utilise les index disponibles

-- 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
--  probablement obsolete au 24/07/2019
-- source : Nom de la source à analyser (champ src_nom de la table t_source_src)
--    categorie_source : Catégorie attendue de la source.
-- table_result : nom de la table qui contiendra les résultats
-- epsg_emprise : Code epsg des coordonnées fournies par l'opérateur si l'utilisateur a fourni un fichier X;Y
-- nom_geometrie_emprise : 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)
-- colonne_filtre : nom de la table à analyser (table_result) qui fera l'objet d'un filtre : where noncolonne is not null
-- tolerance : distance (entier) exprimée dans la même unité que la couche de donnée traitée (identique pour toutes les couches si besoin)
--  en dessous de laquelle on considère que le vecteur coïncide avec le point X,Y. (distance max pour des points)


-- déclaration des variables de traitements
DECLARE
   requete text; -- Variable permettant de stocker le code SQL de requêtes à exécuter.
status boolean; -- Booléen permettant de tester si une exécution a retourné vrai ou faux
--curs_table refcursor; -- Curseur recevant les informations de la table / requête à analyser
   --rec_table   record; -- Enregistrement contenant la ligne en cours de traitement
--raster_emprise text; -- Nom de la table contenant le raster de l'emprise en mode maillage.
--compteur  integer; -- VAriable permettant de stocker le résultat d'une opération de comptage.
geometrie_source text; -- Nom de la table contenant la géométrie (raster ou vectoriel) associée à la source (via sa géométrie)
type_geometrie text; -- type de géométrie contenu dans la table représentée par la variable geometrie_source. (raster, point ou polygone)
epsg_source integer; -- Code epsg du raster associé à la source.
categorie_base text; -- code catégorie de la source récupéré en base et utilisé pour sélectionner la colonne à alimenter dans la table de résultats.
table_data text; -- Variable permettant de stocker le nom de la table de données associée à la source.
BEGIN

--  Récupération du nom de la table contenant la géométrie et du type de géométrie (raster, point ...)
requete = 'SELECT
tr_geometrie_geo.geo_table, tr_type_typ.typ_nom
FROM
t_source_src
INNER JOIN tr_geometrie_geo ON (t_source_src.src_geo_id = tr_geometrie_geo.geo_id)
INNER JOIN tr_type_typ on (tr_type_typ.typ_id = tr_geometrie_geo.geo_typ_id)
WHERE t_source_src.src_nom = ' || quote_literal(source)|| ';' ;
execute requete into geometrie_source,type_geometrie;
-- Vérification existence de la table géomtérie associée à la source
select into status ps_existe_table('public',geometrie_source);
if status = false then
return 'Erreur : la table ' || geometrie_source || ' est inexistante. (Géométrie de la source ' || source || ')';
end if;
-- Vérification valeurs attendue du type de géométrie
CASE
WHEN (type_geometrie != 'point') and (type_geometrie != 'raster') and (type_geometrie != 'polygone') THEN
return 'Erreur : le type de géométrie ne correspond pas aux valeurs définies dans ps_extraction_source :' ||  source || ' -- ' || type_geometrie;
else
END CASE;
-- Vérification catégorie
requete = 'SELECT
tr_categorie_cat.cat_code
FROM
t_source_src
INNER JOIN tr_categorie_cat ON (tr_categorie_cat.cat_id = t_source_src.src_cat_id)
WHERE t_source_src.src_nom = ' || quote_literal(source)|| ';' ;
execute requete into categorie_base;
if categorie_source <> categorie_base then
return 'Erreur : la catégorie de la source est  ' || categorie_base || ' alors qu''elle est déclarée en ' || categorie_source || ';';
end if;
-- Vérification table de donnees
-- Récupération nom de la table data
requete = 'SELECT
src_table_valeur
FROM
t_source_src
WHERE t_source_src.src_nom = ' || quote_literal(source)|| ';' ;
execute requete into table_data;
--existence de la table data
select into status ps_existe_table('public',table_data);
if status = false then
return 'Erreur : la table ' || table_data || ' référencée comme table de données de la source ' || source || ' est inexistante.';
end if;


-- Traitement spécifique au type de géométrie
CASE
WHEN (type_geometrie = 'raster') THEN
-- Récupération code EPSG du raster source.
requete = 'select srid from raster_columns where r_table_name = ' || quote_literal(geometrie_source) || ';';
execute requete into epsg_source;

CASE -- Traitement spécifique à chaque mode de traitement
WHEN mode_emprise = 'XY' THEN
-- Alimentation table resultat
if colonne_filtre = '' THEN
requete = 'with point_emprise as
(select cle,x as x_donnee,y as y_donnee, st_transform(ST_SetSRID(st_makepoint(x,y),' || epsg_emprise || '),' || epsg_source || ') as geom from ' || table_result || ' limit 100000000),
 donnee_source as
(select cle,x_donnee,y_donnee, st_value(rast,geom)as valeur from ' || geometrie_source || ',point_emprise
where st_intersects(rast,geom))
 update ' || table_result || '
 set id_maille_' || categorie_source || '=  valeur
 from donnee_source
 where x = x_donnee
 and y = y_donnee;';
ELSE
requete = 'with point_emprise as
(select cle,x as x_donnee,y as y_donnee, st_transform(ST_SetSRID(st_makepoint(x,y),' || epsg_emprise || '),' || epsg_source || ') as geom from ' || table_result ||
' where '|| colonne_filtre || ' is not null limit 100000000),
donnee_source as
(select cle,x_donnee,y_donnee, st_value(rast,geom)as valeur from ' || geometrie_source || ',point_emprise
where st_intersects(rast,geom))
update ' || table_result || '
set id_maille_' || categorie_source || '=  valeur
from donnee_source
where x = x_donnee
and y = y_donnee;';
end if;
--raise notice ' requete : %',requete;
execute requete;
--requete = 'delete from ' || table_result || '  where id_maille_' || categorie_source || ' is null;';
--execute requete;
--raise notice ' menage : %',requete;

WHEN (mode_emprise = 'MAILLE_COMPLETE') or (mode_emprise = 'MAILLE_PARTIELLE' )  THEN
return 'Le mode maille (complete ou partielle) n''est pas implémenté actuellement dans ce traitement';

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

WHEN (type_geometrie = 'point') THEN
-- Récupération code EPSG du vecteur source.
requete = 'select srid from geometry_columns where f_table_name = ' || quote_literal(geometrie_source) || ';';
execute requete into epsg_source;

CASE -- Traitement spécifique à chaque mode de traitement
WHEN mode_emprise = 'XY' THEN
-- Alimentation table resultat
if colonne_filtre = '' THEN
requete = 'with point_emprise as
(select cle,x as x_donnee,y as y_donnee, st_transform(ST_SetSRID(st_makepoint(x,y),' || epsg_emprise || '),' || epsg_source || ') as geom_donnee from ' || table_result || '),
 donnee_source as
(select cle,x_donnee,y_donnee, id_geom as valeur from ' || geometrie_source || ',point_emprise
where st_dwithin(geom,geom_donnee,' || tolerance || '))
 update ' || table_result || '
 set id_maille_' || categorie_source || '=  valeur
 from donnee_source
 where x = x_donnee
 and y = y_donnee;';
ELSE
requete = 'with point_emprise as
(select cle,x as x_donnee,y as y_donnee, st_transform(ST_SetSRID(st_makepoint(x,y),' || epsg_emprise || '),' || epsg_source || ') as geom_donnee from ' || table_result ||
' where '|| colonne_filtre || ' is not null limit 100000000),
 donnee_source as
(select cle,x_donnee,y_donnee, id_geom as valeur from ' || geometrie_source || ',point_emprise
where st_dwithin(geom,geom_donnee,' || tolerance || '))
 update ' || table_result || '
 set id_maille_' || categorie_source || '=  valeur
 from donnee_source
 where x = x_donnee
 and y = y_donnee;';
end if;
--raise notice ' requete : %',requete;
execute requete;

WHEN (mode_emprise = 'MAILLE_COMPLETE') or (mode_emprise = 'MAILLE_PARTIELLE' )  THEN
return 'Le mode maille (complete ou partielle) n''est pas implémenté actuellement dans ce traitement';

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


else
return 'Erreur : Implémentation de la procédure ps_extraction_source non fournie pour le type de géométrie ' || type_geometrie;
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_extraction_source(mode_emprise text, source text, categorie_source text, table_result text, epsg_emprise integer, nom_geometrie_emprise text, colonne_filtre text, tolerance integer)
IS 'Fonction d''extraction des informations d''une source (id_maille) pour la création de fichier param en entrée du modèle biljou';

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