pggeodb.nancy.inrae.fr/db_arboretum

View: vm_arb_proche_lisiere

Schema

public

Owner

arboretum_ecriture

Descriptions

Vue matérialisée ...

Columns

Rules

There are no rules for view vm_arb_proche_lisiere

Indices

There are no indices for table vm_arb_proche_lisiere

Definition

CREATE MATERIALIZED VIEW public.vm_arb_proche_lisiere (
   id,
   zon_nom,
   pla_nom,
   col_nom,
   col_id,
   arb_id,
   tax_nom_simple,
   geom_arbre,
   distance,
   rang2)
AS
WITH arbre_geom AS (
SELECT t_arbre_arb.arb_id,
           t_arbre_arb.arb_zon_id,
           t_arbre_arb.arb_tax_id,
           t_arbre_arb.arb_geom_plan AS geom_arbre
FROM t_arbre_arb
WHERE t_arbre_arb.arb_mort = false
       ), distance_arb_pla AS (
   SELECT arbre_geom.arb_id,
           arbre_geom.arb_zon_id,
           arbre_geom.arb_tax_id,
           st_distance(arbre_geom.geom_arbre, sig_placeau_lisieres.geom) AS distance,
           arbre_geom.geom_arbre,
           rank() OVER (PARTITION BY arbre_geom.arb_id
   ORDER BY (st_distance(arbre_geom.geom_arbre, sig_placeau_lisieres.geom))) AS rang
   FROM arbre_geom,
           sig_placeau_lisieres
   ), plus_proche_placeau AS (
   SELECT distance_arb_pla.arb_id,
           distance_arb_pla.arb_zon_id,
           distance_arb_pla.arb_tax_id,
           distance_arb_pla.distance,
           distance_arb_pla.geom_arbre,
           distance_arb_pla.rang
   FROM distance_arb_pla
   WHERE distance_arb_pla.rang = 1
   ), tri_dist_col AS (
   SELECT plus_proche_placeau.arb_id,
           plus_proche_placeau.arb_zon_id,
           plus_proche_placeau.arb_tax_id,
           plus_proche_placeau.distance,
           plus_proche_placeau.geom_arbre,
           plus_proche_placeau.rang,
           rank() OVER (PARTITION BY plus_proche_placeau.arb_zon_id
   ORDER BY plus_proche_placeau.distance) AS rang2
   FROM plus_proche_placeau
   )
   SELECT row_number() OVER () AS id,
   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 AS col_id,
   tri_dist_col.arb_id,
   tr_taxon_tax.tax_nom_simple,
   tri_dist_col.geom_arbre,
   tri_dist_col.distance,
   tri_dist_col.rang2
   FROM tri_dist_col
    JOIN tr_zone_etude_zon ON tri_dist_col.arb_zon_id = tr_zone_etude_zon.zon_id
    JOIN tr_taxon_tax ON tri_dist_col.arb_tax_id = tr_taxon_tax.tax_id
   WHERE tri_dist_col.geom_arbre IS NOT NULL AND tri_dist_col.rang2 <= 2
   ORDER BY tr_zone_etude_zon.zon_id, tri_dist_col.rang2;

COMMENT ON MATERIALIZED VIEW public.vm_arb_proche_lisiere
IS 'Vue matérialisée ...';

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