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

Function: ps_create_raster

Schema

public

Owner

albenard

Descriptions

Fonction de création de raster à partir d'une table comportant les x ,y et valeur de pixel

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_create_raster (
 nom_table_xy text,
 nom_table_finale text,
 x_y_centre_pixel boolean,
 system_geo integer,
 tuilage integer,
 type_donnees text,
 commentaire text = 'raster créé par ps_create_raster'::text,
 schema_travail text = 'public'::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 : 14/11/2019
-- Prérequis : cette procédure nécessite la disponibilité de ps_existe_table

-- Cette fonction sert à créer une table raster postgis à partir d'une table comportant 3 colonnes : x, y pour les coordonnées pixel et valeur
-- pour la valeur à attribuer au pixel. Les colonnes x et y doivent être de type bigint, integer , real ou double precision et la casse des noms de colonnes est minuscule.
-- La méthode utilisée pour fabriquer le raster (union de raster de 1 pixel) pourrait ne pas fonctionner dans le cas d'une table des x,y très volumineuse (mémoire insuffisante)
-- et il faudra dans ce cas se tourner vers d'autres solutions ou augmenter la mémoire disponible.
--
-- paramètres :
-- nom_table_xy : nom de la table préalablement créée par l'appelant et contenant les données pour fabriquer le raster (colonnes x,y,valeur)
-- nom_table_finale : nom de la table qui contiendra le raster
-- x_y_centre_pixel : booléen indiquant si les données x et y représentent le centre du pixel. Si cette valeur est à false alors les coordonnées
--  x et y doivent représenter le coin supérieur gauche des pixel.
-- system_geo : Système de géoréférencement des x, y présents dans la table représentée par nom_table_xy.
--  Il s'agit d'un code EPSG (par exemple 27572 pour du Lambert 2 étendu)
-- tuilage : valeur entière indiquant la taille du fenêtrage (tuilage). Une valeur de 50 indiquera que le raster sera tuilé sous
--  forme de lignes de 50 * 50 (2500) pixel.
-- type_donnees : Taille des données de chaque pixel. Les valeurs suivantes sont accpetées :
-- 1BB - 1-bit boolean
-- 2BUI - 2-bit unsigned integer
-- 4BUI - 4-bit unsigned integer
-- 8BSI - 8-bit signed integer
-- 8BUI - 8-bit unsigned integer
-- 16BSI - 16-bit signed integer
-- 16BUI - 16-bit unsigned integer
-- 32BSI - 32-bit signed integer
-- 32BUI - 32-bit unsigned integer
-- 32BF - 32-bit float
-- 64BF - 64-bit float
-- Le nombre de pixel du raster et ce paramètre sont les 2 déterminants de la taille occupée par le raster. Il faut donc
-- veiller à ne pas surdimensionner le type de donnée (par exemple pour stocker des entiers identifiant moins de 10 000 mailles
-- la valeur 16BUI sera suffisante.
-- commentaire : commentaire permettant la description du raster final.
-- schema_travail : nom du schema où se trouve la table représentée par nom_table_xy et où sera créé la table représentée par nom_table_finale (pas de mixage)
--
-- valeur de retour : la chaine 'ok' est retournée si le traitement peut aboutir ou un message sur la nature de l'erreur dans le cas contraire.

-- Les éléments suivants pourraient être paramétrés mais il est décidé de les déterminer au cours du traitement pour ne pas complexifier l'appel de cette procédure :
-- - Largeur / hauteur de pixel

-- déclaration des variables de traitements
DECLARE
   requete text;
status boolean;
table_xy text; -- Variable contenant la concaténation schema_travail + nom_table_xy
table_finale text; -- Variable contenant la concaténation schema_travail + nom_table_finale
nb_ligne integer; -- Variable contenant le comptage de lignes à l'issue d'une requête
pixel_x float; -- Variable stockant la taille de pixel déterminée (en x)
pixel_y float; -- Variable stockant la taille de pixel déterminée (en y)
decalage_x float; -- Décalage des coordonnées x par rapport au coin supérieur gauche de chaque pixel (positionné à 0
-- ou à la moitié de pixel_x selon la valeur du paramètre x_y_centre_pixel.
decalage_y float; -- Décalage des coordonnées y par rapport au coin supérieur gauche de chaque pixel (positionné à 0
-- ou à la moitié de pixel_y selon la valeur du paramètre x_y_centre_pixel.

BEGIN

-- Vérification existence nom_table_xy et conformité des colonnes attendues
 select into status ps_existe_table(schema_travail,nom_table_xy);
 if status = false then
return 'Erreur : la table  ' || schema_travail || '.' ||  nom_table_xy ||' est inexistante.';
else
table_xy = schema_travail || '.' ||  nom_table_xy ;
table_finale = schema_travail || '.' ||  nom_table_finale ;
-- la requete ci-dessous permet de vérifier que les colonnes attendues sont présentes
requete = 'select count(*) from
(SELECT column_name,data_type FROM information_schema.columns
where
(table_schema=' || quote_literal(schema_travail) || ' and table_name = ' || quote_literal(nom_table_xy) || ')
and
(
(column_name = ' || quote_literal('x')  || ' and data_type in (' || quote_literal('bigint') || ','|| quote_literal('integer') || ','|| quote_literal('real') || ',' || quote_literal('double precision') || ')) or  
(column_name = ' || quote_literal('y')  || ' and data_type in (' || quote_literal('bigint') || ','|| quote_literal('integer') || ','|| quote_literal('real') || ',' || quote_literal('double precision') || ')) or      
(column_name = ' || quote_literal('valeur') || ')
)
)as sous_req;';
execute requete into nb_ligne;
if nb_ligne <> 3 then
return 'Erreur : la table  ' || table_xy ||' n''est pas conforme (x [long], y [long], valeur).';
end if;
 end if;

-- Vérification du type de données avec une mise en relation du paramètre type_donnees et le data_type.
-- cette vérification n'est pas implémentée ici et devrait faire l'objet d'une procédure dédiée.

-- Calcul taille de pixel en x
requete = '
with x_distinct as
(select distinct x from ' || table_xy || '),
calcul_delta as
(select (x - lag(x,1)over (order by x) )as delta from x_distinct),
compte_delta as
(select delta, count(delta) as occurrence from calcul_delta group by delta),
classement_delta as
(select delta, rank() over (order by occurrence desc) as classement from compte_delta)
select delta from classement_delta where classement = 1 limit 1;';
execute requete into pixel_x;

-- Calcul taille de pixel en y
requete = '
with x_distinct as
(select distinct y from ' || table_xy || '),
calcul_delta as
(select (y - lag(y,1)over (order by y) )as delta from x_distinct),
compte_delta as
(select delta, count(delta) as occurrence from calcul_delta group by delta),
classement_delta as
(select delta, rank() over (order by occurrence desc) as classement from compte_delta)
select delta from classement_delta where classement = 1 limit 1;';
execute requete into pixel_y;

-- Vérification espace entre les pixels. Maintenant que les tailles du pixel (en x et en y) sont définies il faut vérifier que tous les points
-- de la table sont bien alignés par exemple en x : (valeur_x - valeur_minimale_des_x) / pixel_y est une valeur entière
-- Vérifications alignement des x
requete = 'with x_distinct as
(select distinct x from ' || table_xy || '),
x_distance_grille as
(select x, mod((x - first_value(x) over(order by x)),' || pixel_x || ') as distance_grille from x_distinct)
select count(*) from (select * from x_distance_grille where distance_grille <>0)as hors_grille;';
execute requete into nb_ligne;
if nb_ligne <> 0 then
return 'Erreur : la table  ' || table_xy ||' comporte ' || nb_ligne || 'valeurs de x en dehors du maillage pour une taille de pixel détectée de ' || pixel_x;
end if;

-- Vérifications alignement des y
requete = 'with y_distinct as
(select distinct y from ' || table_xy || '),
y_distance_grille as
(select y, mod((y - first_value(y) over(order by y)),' || pixel_y || ') as distance_grille from y_distinct)
select count(*) from (select * from y_distance_grille where distance_grille <>0)as hors_grille;';
execute requete into nb_ligne;
if nb_ligne <> 0 then
return 'Erreur : la table  ' || table_xy ||' comporte ' || nb_ligne || 'valeurs de y en dehors du maillage pour une taille de pixel détectée de ' || pixel_x;
end if;

-- Calcul du décalage éventuel (si les coordonnées x,y sont celles du milieu du pixel)
if x_y_centre_pixel = true then
decalage_x = pixel_x / 2;
decalage_y = pixel_y /2;
else
decalage_x = 0;
decalage_y = 0;
end if;

-- Suppression de la table raster si celle-ci existe déjà puis création de la structure vide
requete = 'drop table if exists ' || table_finale || ';' ;
execute requete;
requete = 'create table ' || table_finale || '(id SERIAL PRIMARY KEY, rast RASTER, comment TEXT)';
execute requete;

-- Execution de la requete de création du raster à partir des points. Le principe consiste à créer des raster de 1 seul pixel (1 par point) à l'aide des fonctions st_addband et st_makeemptyraster (pour
-- la création du raster vide), de la fonction st_setvalue pour positionner la valeur du pixel avec le contenu de la colonne valeur. L'ensemble de ces raster de 1 pixel est ensuite aggrégé en un seul
-- gros raster à l'aide la la focntion st_union. Cette agrégation est sensible et pourrait échouer dans le cas de très gros raster sur une configuration qui ne dispose pas de suffisamment de mémoire vive.
-- Le gros raster est ensuite tuilé dans la table destination finale à l'aide de la fonction st_tile avant la finalisation qui s'appuie sur la fonction AddRasterConstraints.

requete = 'with raster_test as
(
select st_union( st_setvalue(st_addband(st_makeemptyraster(1,1,x - '|| decalage_x || ',y + '|| decalage_y || ','|| pixel_x ||',-'|| pixel_y ||',0,0,'|| system_geo ||'),
' || quote_literal(type_donnees) ||'::text,0,null),1,1,valeur))as rast2 from '|| table_xy ||'
)
insert into ' || table_finale || '(rast,COMMENT)
select st_tile(rast2, '|| tuilage ||',' || tuilage || ',true),' || quote_literal(commentaire) || ' from raster_test;';
execute requete;

requete = 'SELECT AddRasterConstraints('|| quote_literal(schema_travail) ||'::name, '|| quote_literal(nom_table_finale) ||'::name, '|| quote_literal('rast') ||'::name);';
execute requete;

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

COMMENT ON FUNCTION public.ps_create_raster(nom_table_xy text, nom_table_finale text, x_y_centre_pixel boolean, system_geo integer, tuilage integer, type_donnees text, commentaire text, schema_travail text)
IS 'Fonction de création de raster à partir d''une table comportant les x ,y et valeur de pixel';

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