data/pgsql/init_metier_vue.sql

-- version pgsql


-- -------------------------------------------------------------------------------------
-- dans openFoncier faire des vues dans dynmap pour pos, parcelle et rivoli
-- creation de vue et destruction des tables coorespondantes
-- dans var.inc : $vue_pos = 1 , $vue_parcelle =1 et vue_rivoli = 1 pour empecher la maj
-- -------------------------------------------------------------------------------------

-- ---------------------------------------
-- vu sur le pos dynmap depuis openFoncier
-- ---------------------------------------

CREATE OR REPLACE VIEW pos AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT type_, (id_sect||'' ''||substring(nom_zone,0,30)) , g FROM
            sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid') 
            as (pos varchar(10), libelle varchar(40), geom geometry);

-- -----------------------------------------------
-- vue sur les parcelles dynmap depuis openFoncier
-- -----------------------------------------------

CREATE OR REPLACE VIEW parcelle AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT substring(idu_id,7,6), supf_id , g ,null, '''', '''', null, null FROM
            sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid') 
            as (parcelle varchar(6), surface numeric(10,3), geom geometry, proprietaire varchar(6),
            debut varchar(4), rivoli varchar(4), section varchar(7), pos varchar(10));

-- -------------------------------------------------
-- vue sur les "rivoli" de dynmap depuis openFoncier
-- -------------------------------------------------

CREATE OR REPLACE VIEW rivoli AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'select distinct rivoli_id, substr((typevoie||' '||nomvoie),0,40) as libelle
             from sb_data_254 where rivoli_id != ''''') 
            as (rivoli varchar(4), libelle varchar(40));
            
            
-- -----------------------------------------------
-- vue sur les servitudes surfaciques
--------------------------------------------------
CREATE OR REPLACE VIEW servitude_surfacique AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT sb_data_90.rowid,type_, coment, g FROM
            sb_data_90 inner join sb_geom_90 on sb_data_90.rowid=sb_geom_90.rowid') 
            as (servitude_surfacique integer,  libelle varchar(16), observation varchar(80), geom geometry);

-- ------------------------------------------------
-- vue de dynmap sur dossier d'openFoncier
-- utilisation de l'oid comme identifiant numerique
---------------------------------------------------

CREATE OR REPLACE VIEW sb_data_dossier AS 
 SELECT *
   FROM dblink('dbname=openfoncier'::text,
            'select oid,dossier,nature,annee,etat,demandeur_nom
             from dossier') 
            as (rowid integer, dossier varchar(12), 
                nature char(2), anne char(2), 
                etat varchar(20), 
                demandeur_nom varchar(80));

CREATE OR REPLACE VIEW sb_geom_dossier AS 
 SELECT *
   FROM dblink('dbname=openfoncier'::text, 'SELECT oid,geom FROM dossier ')
        as (rowid integer, g geometry);
        
        
-- -------------------------------------
-- vue cadastre et pos dynmap format ogc
-- jointure data + geom 
-- -------------------------------------

CREATE OR REPLACE VIEW parcelle AS
    SELECT sb_data_170.rowid, substring(idu_id,7,6) as parcelle, supf_id , g
    FROM sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid;

CREATE OR REPLACE VIEW pos AS
    SELECT sb_data_338.rowid,type_, (id_sect||' '||substring(nom_zone,0,30)) as libelle, g
    FROM sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid;
1	fraynaud	40	-- version pgsql
2
3
4	fraynaud	89	-- -------------------------------------------------------------------------------------
5			-- dans openFoncier faire des vues dans dynmap pour pos, parcelle et rivoli
6			-- creation de vue et destruction des tables coorespondantes
7			-- dans var.inc : $vue_pos = 1 , $vue_parcelle =1 et vue_rivoli = 1 pour empecher la maj
8			-- -------------------------------------------------------------------------------------
9	fraynaud	40
10	fraynaud	89	-- ---------------------------------------
11			-- vu sur le pos dynmap depuis openFoncier
12			-- ---------------------------------------
13	fraynaud	40
14			CREATE OR REPLACE VIEW pos AS
15			SELECT *
16			FROM dblink('dbname=arles_dynmap'::text,
17			'SELECT type_, (id_sect\|\|'' ''\|\|substring(nom_zone,0,30)) , g FROM
18			sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid')
19			as (pos varchar(10), libelle varchar(40), geom geometry);
20
21	fraynaud	89	-- -----------------------------------------------
22			-- vue sur les parcelles dynmap depuis openFoncier
23			-- -----------------------------------------------
24	fraynaud	40
25			CREATE OR REPLACE VIEW parcelle AS
26			SELECT *
27			FROM dblink('dbname=arles_dynmap'::text,
28			'SELECT substring(idu_id,7,6), supf_id , g ,null, '''', '''', null, null FROM
29			sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid')
30			as (parcelle varchar(6), surface numeric(10,3), geom geometry, proprietaire varchar(6),
31			debut varchar(4), rivoli varchar(4), section varchar(7), pos varchar(10));
32	fraynaud	89
33			-- -------------------------------------------------
34			-- vue sur les "rivoli" de dynmap depuis openFoncier
35			-- -------------------------------------------------
36
37			CREATE OR REPLACE VIEW rivoli AS
38			SELECT *
39			FROM dblink('dbname=arles_dynmap'::text,
40			'select distinct rivoli_id, substr((typevoie\|\|' '\|\|nomvoie),0,40) as libelle
41			from sb_data_254 where rivoli_id != ''''')
42			as (rivoli varchar(4), libelle varchar(40));
43
44	fraynaud	126
45
46			-- -----------------------------------------------
47			-- vue sur les servitudes surfaciques
48			--------------------------------------------------
49			CREATE OR REPLACE VIEW servitude_surfacique AS
50			SELECT *
51			FROM dblink('dbname=arles_dynmap'::text,
52			'SELECT sb_data_90.rowid,type_, coment, g FROM
53			sb_data_90 inner join sb_geom_90 on sb_data_90.rowid=sb_geom_90.rowid')
54			as (servitude_surfacique integer, libelle varchar(16), observation varchar(80), geom geometry);
55
56	fraynaud	89	-- ------------------------------------------------
57			-- vue de dynmap sur dossier d'openFoncier
58			-- utilisation de l'oid comme identifiant numerique
59			---------------------------------------------------
60
61			CREATE OR REPLACE VIEW sb_data_dossier AS
62			SELECT *
63			FROM dblink('dbname=openfoncier'::text,
64			'select oid,dossier,nature,annee,etat,demandeur_nom
65			from dossier')
66			as (rowid integer, dossier varchar(12),
67			nature char(2), anne char(2),
68			etat varchar(20),
69			demandeur_nom varchar(80));
70
71			CREATE OR REPLACE VIEW sb_geom_dossier AS
72			SELECT *
73			FROM dblink('dbname=openfoncier'::text, 'SELECT oid,geom FROM dossier ')
74			as (rowid integer, g geometry);
75
76
77			-- -------------------------------------
78			-- vue cadastre et pos dynmap format ogc
79			-- jointure data + geom
80			-- -------------------------------------
81
82			CREATE OR REPLACE VIEW parcelle AS
83			SELECT sb_data_170.rowid, substring(idu_id,7,6) as parcelle, supf_id , g
84			FROM sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid;
85
86			CREATE OR REPLACE VIEW pos AS
87			SELECT sb_data_338.rowid,type_, (id_sect\|\|' '\|\|substring(nom_zone,0,30)) as libelle, g
88			FROM sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid;