pggeodb.nancy.inra.fr/db_cefs - db_cefs on pggeodb.nancy.inra.fr

Function: ps_distance_os

Schema

analyse

Owner

albenard

Descriptions

Cette fonction calcule la distance à certains éléments (route, bati ...) et met à jour les champs de la table t_positionnement_pos dédiés au stockage de cette information.

Options

Definition

CREATE OR REPLACE FUNCTION "analyse".ps_distance_os (
 annee_traitement integer = 2005,
 tailleoffset integer = 2000
)
RETURNS boolean AS
$span$
DECLARE
nb_point_a_traiter INTEGER;
   nb_boucle INTEGER;
i INTEGER;
   valeur_offset INTEGER;

BEGIN

  SELECT count(*)
       FROM
         public.t_campagne_pos_cpos
         INNER JOIN public.t_positionnement_pos ON (public.t_campagne_pos_cpos.cpos_id = public.t_positionnement_pos.pos_cpos_id)
       WHERE t_campagne_pos_cpos.cpos_annee_suivi = annee_traitement into nb_point_a_traiter;
   nb_boucle = nb_point_a_traiter / tailleoffset;
raise notice 'Nb points a traiter = % - Nb boucle = %', nb_point_a_traiter, nb_boucle;
   
   FOR i IN 0..nb_boucle LOOP
     raise notice 'Boucle % - horodatage : % ', i, now();
    valeur_offset = i * tailleoffset;
    with point_gps_annee as (
                   SELECT
                     public.t_campagne_pos_cpos.cpos_annee_suivi as annee_suivi,
                     public.t_positionnement_pos.pos_id,
                     public.t_positionnement_pos.the_geom
                   FROM
                     public.t_campagne_pos_cpos
                     INNER JOIN public.t_positionnement_pos ON (public.t_campagne_pos_cpos.cpos_id = public.t_positionnement_pos.pos_cpos_id)
                   where t_campagne_pos_cpos.cpos_annee_suivi = annee_traitement
                   order by t_positionnement_pos.pos_id
                   limit tailleoffset OFFSET valeur_offset),
           distance_diverses as (
                   select pointgps.pos_id,
                           poly_route.par_id as pos_route_par_id,
                           poly_route.dist as pos_distance_route,
                           poly_bois.par_id as pos_bois_par_id,
                           poly_bois.dist as pos_distance_bois,
                           poly_bati.par_id as pos_bati_par_id,
                           poly_bati.dist as pos_distance_bati,
                           poly_haie.par_id as pos_haie_par_id,
                           poly_haie.dist as pos_distance_haie
                       from point_gps_annee as pointgps ,
                       lateral (
                           select tr_parcellaire_par.par_id, tr_parcellaire_par.par_annee, tr_parcellaire_par.par_grd_cat, st_distance(tr_parcellaire_par.geom , pointgps.the_geom) as dist
                           from tr_parcellaire_par
                           -- critère
                           where tr_parcellaire_par.par_grd_cat = 'route' and pointgps.annee_suivi = tr_parcellaire_par.par_annee
                           order by tr_parcellaire_par.geom <-> pointgps.the_geom limit 1
                           ) as poly_route,
                       lateral (
                           select tr_parcellaire_par.par_id, tr_parcellaire_par.par_annee, tr_parcellaire_par.par_grd_cat, st_distance(tr_parcellaire_par.geom , pointgps.the_geom) as dist
                           from tr_parcellaire_par
                           -- critère
                           where tr_parcellaire_par.par_grd_cat = 'bois' and pointgps.annee_suivi = tr_parcellaire_par.par_annee
                           order by tr_parcellaire_par.geom <-> pointgps.the_geom limit 1
                           ) as poly_bois,
                       lateral (
                           select tr_parcellaire_par.par_id, tr_parcellaire_par.par_annee, tr_parcellaire_par.par_grd_cat, st_distance(tr_parcellaire_par.geom , pointgps.the_geom) as dist
                           from tr_parcellaire_par
                           -- critère
                           where tr_parcellaire_par.par_grd_cat = 'bati' and pointgps.annee_suivi = tr_parcellaire_par.par_annee
                           order by tr_parcellaire_par.geom <-> pointgps.the_geom limit 1
                           ) as poly_bati,
                       lateral (
                           select tr_parcellaire_par.par_id, tr_parcellaire_par.par_annee, tr_parcellaire_par.par_grd_cat, st_distance(tr_parcellaire_par.geom , pointgps.the_geom) as dist
                           from tr_parcellaire_par
                           -- critère
                           where tr_parcellaire_par.par_grd_cat = 'bati' and pointgps.annee_suivi = tr_parcellaire_par.par_annee
                           order by tr_parcellaire_par.geom <-> pointgps.the_geom limit 1
                           ) as poly_haie
                   )
          update t_positionnement_pos
             set pos_distance_route = distance_diverses.pos_distance_route ,
                 pos_route_par_id = distance_diverses.pos_route_par_id,
                 pos_distance_bois = distance_diverses.pos_distance_bois,
                 pos_bois_par_id = distance_diverses.pos_bois_par_id,
                 pos_distance_bati = distance_diverses.pos_distance_bati,
                 pos_bati_par_id = distance_diverses.pos_bati_par_id,
                 pos_distance_haie = distance_diverses.pos_distance_haie,
                 pos_haie_par_id = distance_diverses.pos_haie_par_id
           from distance_diverses
             where distance_diverses.pos_id = t_positionnement_pos.pos_id;
   END LOOP;
   return TRUE;
EXCEPTION
WHEN OTHERS THEN
 raise notice 'erreur inconnue';
 return false;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION "analyse".ps_distance_os(annee_traitement integer, tailleoffset integer)
IS 'Cette fonction calcule la distance à certains éléments (route, bati ...) et met à jour les champs de la table t_positionnement_pos dédiés au stockage de cette information.';

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