/*
 Création de la table à partir du raster pg
 MAJ 2020/10 v02:
- optimisation de la durée du traitement (<> 1 heure pour vie et jaunay)

 */
set work_mem ='32GB';
SET client_min_messages = ERROR;

/*
alti temp
*/
drop table if exists r020_territoire_physique.t_mnt_rge_r1_alti_temp  ;

Create table r020_territoire_physique.t_mnt_rge_r1_alti_temp  as  (
 with 
 gv as (
   SELECT r.rid, ST_PixelAsPolygons(r.rast,1) gv  
   FROM "r020_territoire_physique"."r_topo_alti_aspect_slope_percent_grass" r -- A modifier
 )
 select 
    rid
  , (gv).x
  , (gv).y 
  , (gv).val::real as alti

  , (gv).geom::geometry(polygon,2154) as geom
  from gv 
 )
 ; 

/*
aspect temp
*/
  
set work_mem ='32GB';
drop table if exists r020_territoire_physique.t_mnt_rge_r2_aspect_temp;
Create table r020_territoire_physique.t_mnt_rge_r2_aspect_temp  as  (
 with gv as (
   SELECT r.rid, ST_PixelAsPolygons(r.rast,2) gv  
   FROM "r020_territoire_physique"."r_topo_alti_aspect_slope_percent_grass" r -- A modifier
  
  )
 select rid,(gv).val::real as aspect, (gv).x, (gv).y 
  from gv 
 )
 ; 

set work_mem ='32GB';
  CREATE INDEX idx_t_mnt_rge_r2_aspect_temp
  ON r020_territoire_physique.t_mnt_rge_r2_aspect_temp  
  USING btree
  (rid, x, y);

/*
slope temp
*/
  
set work_mem ='32GB';
DROP  table IF EXISTS r020_territoire_physique.t_mnt_rge_r3_slope_temp ;
Create table r020_territoire_physique.t_mnt_rge_r3_slope_temp  as  (
 with gv as (
   SELECT r.rid, ST_PixelAsPolygons(r.rast,3) gv  
   FROM "r020_territoire_physique"."r_topo_alti_aspect_slope_percent_grass" r -- A modifier
  
  )
 select rid,(gv).val::real as slope_percent, (gv).x, (gv).y  from gv 
 )
 ; 

set work_mem ='32GB';
  CREATE INDEX idx_t_mnt_rge_r3_slope_temp
  ON r020_territoire_physique.t_mnt_rge_r3_slope_temp  
  USING btree
  (rid, x, y);

/*
Assemblage :
*/
set work_mem ='32GB';
drop table if exists r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass  ;

Create table r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass  as  (
   select row_number() over() ::bigint as gid
   , r1.alti::real 
   , r2.aspect::real
   , r3.slope_percent::real
   , r1.geom::geometry(polygon,2154) as geom
   FROM r020_territoire_physique.t_mnt_rge_r1_alti_temp r1
   LEFT JOIN r020_territoire_physique.t_mnt_rge_r2_aspect_temp r2 on (r1.rid=r2.rid and r1.x=r2.x AND r1.y=r2.y)
   LEFT JOIN r020_territoire_physique.t_mnt_rge_r3_slope_temp r3 on (r1.rid=r3.rid and r1.x=r3.x AND r1.y=r3.y)
);

/*
AJOUT des commentaires
*/

COMMENT ON TABLE r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass
  IS 'Données du RGE alti de l''IGN (5mx5m)
date de création : 07/2022

Méthode de calcul et de création de la couche :

http://wiki.sevre-nantaise.com/index.php/Localisation_et_caract%C3%A9risation_des_t%C3%AAtes_de_bassin_versant#Pr.C3.A9paration_des_donn.C3.A9es_rasters_n.C3.A9cessaires_aux_calculs

Référence grass :
https://grass.osgeo.org/grass70/manuals/r.slope.aspect.html

';
COMMENT ON COLUMN r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass.alti IS 'altitude en m';
COMMENT ON COLUMN r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass.slope_percent IS 'pente en poucentage (calcul grass https://grass.osgeo.org/grass70/manuals/r.slope.aspect.html)';
COMMENT ON COLUMN r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass.aspect IS 'Direction de pente  = orientation (calcul grass : https://grass.osgeo.org/grass70/manuals/r.slope.aspect.html)

Description des valeurs :
The aspect output raster map indicates the direction that slopes are facing. The aspect categories represent the number degrees of east. Category and color table files are also generated for the aspect raster map. The aspect categories represent the number degrees of east and they increase counterclockwise: 90 degrees is North, 180 is West, 270 is South 360 is East.
  90 : Nord
180 : Ouest
270 : Sud
360 : Est';



/*
AJOUT pk et index geom
*/
set work_mem ='32GB';


ALTER TABLE r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass  
  ADD CONSTRAINT t_mnt_alti_aspect_slope_percent_grass_pkey PRIMARY KEY(gid);

CREATE INDEX sidx_t_mnt_alti_aspect_slope_percent_grass_geom
  ON r020_territoire_physique.t_mnt_alti_aspect_slope_percent_grass  
  USING gist
  (geom);


/*
Supression des tables temporaires
*/
drop table if exists r020_territoire_physique.t_mnt_rge_r1_alti_temp  ;
drop table if exists r020_territoire_physique.t_mnt_rge_r2_aspect_temp;
DROP table IF EXISTS r020_territoire_physique.t_mnt_rge_r3_slope_temp ;

ALTER TABLE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_4000 ADD COLUMN flow_accum_km2 numeric;
UPDATE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_4000 
SET flow_accum_km2 =flow_accum*25./1000000. ;
COMMENT ON COLUMN m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_4000.flow_accum_km2 IS 'champ additionnel flow_accum en km2 : flow_accum*25. / 1000 000.';


ALTER TABLE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_500 ADD COLUMN flow_accum_km2 numeric;
UPDATE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_500 
SET flow_accum_km2 =flow_accum*25./1000000. ;
COMMENT ON COLUMN m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_500.flow_accum_km2 IS 'champ additionnel flow_accum en km2 : flow_accum*25. / 1000 000.';


ALTER TABLE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_50 ADD COLUMN flow_accum_km2 numeric;
UPDATE m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_50 
SET flow_accum_km2 =flow_accum*25./1000000. ;
COMMENT ON COLUMN m060_milieux_continuite_tetes_de_bv.t_mnt_sc_sk_stream_50.flow_accum_km2 IS 'champ additionnel flow_accum en km2 : flow_accum*25. / 1000 000.';