pggeodb.nancy.inrae.fr/db_arboretum

View: v_collection_sans_etiquette

Schema

application

Owner

arboretum

Descriptions

Vue pemettant de lister les collections pour lesquelles il reste au moins un arbre vivant mais qui n'on aucun arbre etiqueté. La zone 'Borudre' est exclue de cette vue.

Columns

Rules

Indices

There are no indices for table v_collection_sans_etiquette

Definition

CREATE VIEW application.v_collection_sans_etiquette (
   collection,
   nb_arb_non_mort)
AS
WITH arb_non_mort AS (
SELECT tr_zone_etude_zon.zon_id,
           ltree2text(tr_zone_etude_zon.zon_tree) AS coll,
           t_arbre_arb.arb_id
FROM t_arbre_arb
            JOIN tr_zone_etude_zon ON t_arbre_arb.arb_zon_id = tr_zone_etude_zon.zon_id
WHERE t_arbre_arb.arb_mort IS FALSE OR t_arbre_arb.arb_mort IS NULL
       ), collection_arb_non_mort AS (
   SELECT arb_non_mort.zon_id,
           arb_non_mort.coll,
           count(arb_non_mort.arb_id) AS non_mort
   FROM arb_non_mort
   WHERE arb_non_mort.coll !~~ 'Bordure%'::text
   GROUP BY arb_non_mort.zon_id, arb_non_mort.coll
   ), arbre_etiquete AS (
   SELECT tr_zone_etude_zon.zon_id,
           t_arbre_arb.arb_etiquette,
           tr_variable_var.var_code,
           t_mesure_marb.marb_date_mesure
   FROM t_arbre_arb
            JOIN tr_zone_etude_zon ON t_arbre_arb.arb_zon_id = tr_zone_etude_zon.zon_id
            JOIN t_mesure_marb ON t_arbre_arb.arb_id = t_mesure_marb.marb_arb_id
            JOIN tr_variable_var ON t_mesure_marb.marb_var_id = tr_variable_var.var_id
   WHERE tr_variable_var.var_code::text = 'etiquetage'::text
   )
   SELECT collection_arb_non_mort.coll AS collection,
   collection_arb_non_mort.non_mort AS nb_arb_non_mort
   FROM collection_arb_non_mort
    LEFT JOIN arbre_etiquete ON collection_arb_non_mort.zon_id = arbre_etiquete.zon_id
   WHERE arbre_etiquete.zon_id IS NULL
   ORDER BY collection_arb_non_mort.coll;

COMMENT ON VIEW application.v_collection_sans_etiquette
IS 'Vue pemettant de lister les collections pour lesquelles il reste au moins un arbre vivant mais qui n''on aucun arbre etiqueté. La zone ''Borudre'' est exclue de cette vue.';

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