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 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			-- ------------------------------------------------
45			-- vue de dynmap sur dossier d'openFoncier
46			-- utilisation de l'oid comme identifiant numerique
47			---------------------------------------------------
48
49			CREATE OR REPLACE VIEW sb_data_dossier AS
50			SELECT *
51			FROM dblink('dbname=openfoncier'::text,
52			'select oid,dossier,nature,annee,etat,demandeur_nom
53			from dossier')
54			as (rowid integer, dossier varchar(12),
55			nature char(2), anne char(2),
56			etat varchar(20),
57			demandeur_nom varchar(80));
58
59			CREATE OR REPLACE VIEW sb_geom_dossier AS
60			SELECT *
61			FROM dblink('dbname=openfoncier'::text, 'SELECT oid,geom FROM dossier ')
62			as (rowid integer, g geometry);
63
64
65			-- -------------------------------------
66			-- vue cadastre et pos dynmap format ogc
67			-- jointure data + geom
68			-- -------------------------------------
69
70			CREATE OR REPLACE VIEW parcelle AS
71			SELECT sb_data_170.rowid, substring(idu_id,7,6) as parcelle, supf_id , g
72			FROM sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid;
73
74			CREATE OR REPLACE VIEW pos AS
75			SELECT sb_data_338.rowid,type_, (id_sect\|\|' '\|\|substring(nom_zone,0,30)) as libelle, g
76			FROM sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid;