pggeodb.nancy.inrae.fr/db_arboretum

View: v_arb_circonference

Schema

public

Owner

arboretum

Descriptions

carte des arbres par circonference

Columns

Rules

Indices

There are no indices for table v_arb_circonference

Definition

CREATE VIEW public.v_arb_circonference (
   zon_nom,
   pla_nom,
   col_nom,
   arb_etiquette,
   arb_id,
   tax_libelle,
   circonference,
   classement,
   geom)
AS
SELECT sreq.zon_nom,
   sreq.pla_nom,
   sreq.col_nom,
   sreq.arb_etiquette,
   sreq.arb_id,
   sreq.tax_libelle,
   sreq.circonference,
   sreq.classement,
   sreq.geom
FROM (
   SELECT DISTINCT subpath(tr_zone_etude_zon.zon_tree, 0, 1) AS zon_nom,
           subpath(tr_zone_etude_zon.zon_tree, 1, 1) AS pla_nom,
           subpath(tr_zone_etude_zon.zon_tree, 2, 1) AS col_nom,
           tr_zone_etude_zon.zon_id,
           t_arbre_arb.arb_id,
           t_arbre_arb.arb_etiquette,
           COALESCE(t_arbre_arb.arb_geom_theo, t_arbre_arb.arb_geom_gps,
               t_arbre_arb.arb_geom_plan) AS geom,
           tr_taxon_tax.tax_id,
           tr_taxon_tax.tax_libelle,
           t_mesure_marb.marb_valeur_num AS circonference,
           rank() OVER (PARTITION BY tr_zone_etude_zon.zon_id, tr_taxon_tax.tax_id
   ORDER BY t_mesure_marb.marb_valeur_num DESC) AS classement
   FROM t_arbre_arb
            JOIN tr_zone_etude_zon ON tr_zone_etude_zon.zon_id = t_arbre_arb.arb_zon_id
            JOIN tr_taxon_tax ON t_arbre_arb.arb_tax_id = tr_taxon_tax.tax_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 t_arbre_arb.arb_mort = false AND tr_variable_var.var_code::text =
       'c'::text AND t_mesure_marb.marb_valeur_num > 0::double precision
   ) sreq
ORDER BY sreq.zon_nom, sreq.pla_nom, sreq.col_nom;

COMMENT ON VIEW public.v_arb_circonference
IS 'carte des arbres par circonference';

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