pggeodb-preprod.nancy.inra.fr/db_phyto - db_phyto on pggeodb-preprod.nancy.inra.fr

View: v_parcelles_x_y

Schema

public

Owner

albenard

Descriptions

There is no description for view v_parcelles_x_y

Fields

Rules

Indices

There are no indices for table v_parcelles_x_y

Definition

CREATE VIEW public.v_parcelles_x_y (
   pau_id,
   x,
   y,
   xy_systeme,
   geom2154)
AS
WITH loc_pau AS (
SELECT zon.zon_tree,
           subltree(zon.zon_tree, 0, nlevel(zon.zon_tree) - 1) AS parent
FROM tr_zone_etude_zon zon
            JOIN tr_type_zone_etude_typz typz ON zon.zon_typz_id = typz.typz_id
WHERE typz.typz_code::text = 'parcelle unitaire'::text
       ), loc_pau_id AS (
   SELECT loc_pau.zon_tree,
           zon_pau.zon_id AS id_pau,
           loc_pau.parent,
           zon_loc.zon_id AS id_loc
   FROM loc_pau
            JOIN tr_zone_etude_zon zon_pau ON loc_pau.zon_tree = zon_pau.zon_tree
            JOIN tr_zone_etude_zon zon_loc ON loc_pau.parent = zon_loc.zon_tree
   ), topo_loc_pau AS (
   SELECT loc_pau_id.zon_tree,
           loc_pau_id.id_pau,
           loc_pau_id.parent,
           loc_pau_id.id_loc,
           topo_pau.topo_x_centre AS x_centre_pau,
           topo_pau.topo_y_centre AS y_centre_pau,
           topo_pau.topo_xy_syst_geo AS xy_systeme_pau,
           topo_pau.topo_geom2154 AS geom2154_pau,
           topo_loc.topo_x_centre AS x_centre_loc,
           topo_loc.topo_y_centre AS y_centre_loc,
           topo_loc.topo_xy_syst_geo AS xy_systeme_loc,
           topo_loc.topo_geom2154 AS geom2154_loc
   FROM loc_pau_id
            LEFT JOIN t_information_topographique_topo topo_pau ON
                loc_pau_id.id_pau = topo_pau.topo_zon_id
            LEFT JOIN t_information_topographique_topo topo_loc ON
                loc_pau_id.id_loc = topo_loc.topo_zon_id
   )
   SELECT topo_loc_pau.id_pau AS pau_id,
   COALESCE(topo_loc_pau.x_centre_pau, topo_loc_pau.x_centre_loc) AS x,
   COALESCE(topo_loc_pau.y_centre_pau, topo_loc_pau.y_centre_loc) AS y,
   COALESCE(topo_loc_pau.xy_systeme_pau, topo_loc_pau.xy_systeme_loc) AS xy_systeme,
   COALESCE(topo_loc_pau.geom2154_pau, topo_loc_pau.geom2154_loc) AS geom2154
   FROM topo_loc_pau;

This file was generated with SQL Manager 2011 for PostgreSQL (www.pgsqlmanager.com) at 28/03/2012 14:37