data/pgsql/init_metier_vue.sql

-- version pgsql

-- -------------------------------------------
-- les vues proposées ici sont celles qui sont
-- specifiques à la ville d arles
-- avec la base postgresql - postgis DYNMAP
-- elles sont présentées ici à titre indicatif
-- d'exemple de croisement avec les données SIG
-- --------------------------------------------

-- -------------------------------------------------------------------------------------
-- 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
-- -------------------------------------------------------------------------------------


-- -----------------------------------------------------
-- vues sur dynmap sans dblink dans un shema openfoncier
-- -----------------------------------------------------

-- schema
-- SET search_path =  pg_catalog;


CREATE OR REPLACE VIEW servitude_point AS 
 SELECT sb_data_89.rowid AS servitude_point,
        sb_data_89.type_ AS libelle,
        sb_data_89.coment AS observation,
        500 AS perimetre,
        sb_geom_89.g AS geom
   FROM accm_sig.sb_data_89 
   JOIN accm_sig.sb_geom_89 
   ON sb_data_89.rowid = sb_geom_89.rowid;

CREATE OR REPLACE VIEW servitude_ligne AS 
 SELECT sb_data_331.rowid AS servitude_ligne,
        sb_data_331.type_ AS libelle,
        sb_data_331.coment AS observation,
        100 AS perimetre,
        sb_geom_331.g AS geom
   FROM accm_sig.sb_data_331
   JOIN accm_sig.sb_geom_331 
   ON sb_data_331.rowid = sb_geom_331.rowid;

CREATE OR REPLACE VIEW servitude_surfacique AS 
 SELECT sb_data_90.rowid AS servitude_surfacique,
        sb_data_90.type_ AS libelle,
        sb_data_90.coment AS observation,
        0 AS perimetre,
        sb_geom_90.g AS geom
   FROM accm_sig.sb_data_90
   JOIN accm_sig.sb_geom_90 
   ON sb_data_90.rowid = sb_geom_90.rowid;

CREATE OR REPLACE VIEW pos AS 
 SELECT sb_data_338.type_ AS pos,
        (id_sect||' '||substring(nom_zone,0,30)) AS libelle,
        sb_data_338.coment AS observation,
        sb_geom_338.g AS geom
   FROM accm_sig.sb_data_338
   JOIN accm_sig.sb_geom_338 
   ON sb_data_338.rowid = sb_geom_338.rowid;

CREATE OR REPLACE VIEW parcelle AS 
 SELECT (substring(idu_id,7,6)) AS parcelle,
        supf_id AS surface,
        sb_geom_170_copy.g AS geom,
        null as proprietaire,
        '' as debut,
        '' as rivoli,
        substring(idu_id,7,2) as section,
        'arles' as commune,
        null as pos
   FROM accm_sig.sb_data_170_copy
   JOIN accm_sig.sb_geom_170_copy 
   ON sb_data_170_copy.rowid = sb_geom_170_copy.rowid;

CREATE OR REPLACE VIEW parcelle_lot AS 
 SELECT sb_data_499 rowid AS parcelle,
        num as numero,        
        surface AS surface,
        lotissemen as lotissement,
        g AS geom
   FROM accm_sig.sb_data_499
   JOIN accm_sig.sb_geom_499 
   ON sb_data_499.rowid = sb_geom_499.rowid;

CREATE OR REPLACE VIEW rivoli AS 
 SELECT covoie AS rivoli,
        (natvoie||' ' ||libvoie) as libelle        
   FROM openvoie.rivoli_dgi;


-- ------------------------------------------------------
-- vues sur dynmap avec dblink dans une base 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);
            
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));

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));

CREATE OR REPLACE VIEW servitude_surfacique AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT sb_data_90.rowid,type_, coment, 0, 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), perimetre integer, geom geometry);

CREATE OR REPLACE VIEW servitude_ligne AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT sb_data_331.rowid,type_, coment, 100, g FROM
            sb_data_331 inner join sb_geom_331 on sb_data_331.rowid=sb_geom_331.rowid') 
            as (servitude_ligne integer,  libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);

CREATE OR REPLACE VIEW servitude_point AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT sb_data_89.rowid,type_, coment, 500, g FROM
            sb_data_89 inner join sb_geom_89 on sb_data_89.rowid=sb_geom_89.rowid') 
            as (servitude_point integer,  libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);


CREATE OR REPLACE VIEW parcelle_lot AS 
 SELECT *
   FROM dblink('dbname=arles_dynmap'::text,
            'SELECT rowid, lotissemen , num , surface, the_geom FROM lot') 
            as (parcelle_lot integer, lotissement varchar(50), numero varchar(16), surface numeric(10,3), geom geometry);

    
1	fraynaud	40	-- version pgsql
2
3	fraynaud	138	-- -------------------------------------------
4			-- les vues proposées ici sont celles qui sont
5			-- specifiques à la ville d arles
6			-- avec la base postgresql - postgis DYNMAP
7			-- elles sont présentées ici à titre indicatif
8			-- d'exemple de croisement avec les données SIG
9			-- --------------------------------------------
10	fraynaud	40
11	fraynaud	89	-- -------------------------------------------------------------------------------------
12			-- dans openFoncier faire des vues dans dynmap pour pos, parcelle et rivoli
13			-- creation de vue et destruction des tables coorespondantes
14			-- dans var.inc : $vue_pos = 1 , $vue_parcelle =1 et vue_rivoli = 1 pour empecher la maj
15			-- -------------------------------------------------------------------------------------
16	fraynaud	40
17	fraynaud	249
18	fraynaud	268
19
20	fraynaud	249	-- -----------------------------------------------------
21			-- vues sur dynmap sans dblink dans un shema openfoncier
22			-- -----------------------------------------------------
23
24	fraynaud	268	-- schema
25			-- SET search_path = pg_catalog;
26	fraynaud	249
27	fraynaud	268
28			CREATE OR REPLACE VIEW servitude_point AS
29	fraynaud	249	SELECT sb_data_89.rowid AS servitude_point,
30			sb_data_89.type_ AS libelle,
31			sb_data_89.coment AS observation,
32			500 AS perimetre,
33			sb_geom_89.g AS geom
34			FROM accm_sig.sb_data_89
35			JOIN accm_sig.sb_geom_89
36			ON sb_data_89.rowid = sb_geom_89.rowid;
37
38	fraynaud	268	CREATE OR REPLACE VIEW servitude_ligne AS
39	fraynaud	249	SELECT sb_data_331.rowid AS servitude_ligne,
40			sb_data_331.type_ AS libelle,
41			sb_data_331.coment AS observation,
42			100 AS perimetre,
43			sb_geom_331.g AS geom
44			FROM accm_sig.sb_data_331
45			JOIN accm_sig.sb_geom_331
46			ON sb_data_331.rowid = sb_geom_331.rowid;
47
48	fraynaud	268	CREATE OR REPLACE VIEW servitude_surfacique AS
49	fraynaud	249	SELECT sb_data_90.rowid AS servitude_surfacique,
50			sb_data_90.type_ AS libelle,
51			sb_data_90.coment AS observation,
52			0 AS perimetre,
53			sb_geom_90.g AS geom
54			FROM accm_sig.sb_data_90
55			JOIN accm_sig.sb_geom_90
56			ON sb_data_90.rowid = sb_geom_90.rowid;
57
58	fraynaud	268	CREATE OR REPLACE VIEW pos AS
59	fraynaud	249	SELECT sb_data_338.type_ AS pos,
60			(id_sect\|\|' '\|\|substring(nom_zone,0,30)) AS libelle,
61			sb_data_338.coment AS observation,
62			sb_geom_338.g AS geom
63			FROM accm_sig.sb_data_338
64			JOIN accm_sig.sb_geom_338
65			ON sb_data_338.rowid = sb_geom_338.rowid;
66
67	fraynaud	268	CREATE OR REPLACE VIEW parcelle AS
68	fraynaud	249	SELECT (substring(idu_id,7,6)) AS parcelle,
69			supf_id AS surface,
70			sb_geom_170_copy.g AS geom,
71			null as proprietaire,
72			'' as debut,
73			'' as rivoli,
74			substring(idu_id,7,2) as section,
75			'arles' as commune,
76			null as pos
77			FROM accm_sig.sb_data_170_copy
78			JOIN accm_sig.sb_geom_170_copy
79			ON sb_data_170_copy.rowid = sb_geom_170_copy.rowid;
80
81	fraynaud	268	CREATE OR REPLACE VIEW parcelle_lot AS
82	fraynaud	249	SELECT sb_data_499 rowid AS parcelle,
83			num as numero,
84			surface AS surface,
85			lotissemen as lotissement,
86			g AS geom
87			FROM accm_sig.sb_data_499
88			JOIN accm_sig.sb_geom_499
89			ON sb_data_499.rowid = sb_geom_499.rowid;
90
91	fraynaud	268	CREATE OR REPLACE VIEW rivoli AS
92	fraynaud	249	SELECT covoie AS rivoli,
93			(natvoie\|\|' ' \|\|libvoie) as libelle
94			FROM openvoie.rivoli_dgi;
95
96
97			-- ------------------------------------------------------
98			-- vues sur dynmap avec dblink dans une base openfoncier
99			-- ------------------------------------------------------
100	fraynaud	40	CREATE OR REPLACE VIEW pos AS
101			SELECT *
102			FROM dblink('dbname=arles_dynmap'::text,
103			'SELECT type_, (id_sect\|\|'' ''\|\|substring(nom_zone,0,30)) , g FROM
104			sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid')
105			as (pos varchar(10), libelle varchar(40), geom geometry);
106	fraynaud	138
107	fraynaud	40	CREATE OR REPLACE VIEW parcelle AS
108			SELECT *
109			FROM dblink('dbname=arles_dynmap'::text,
110			'SELECT substring(idu_id,7,6), supf_id , g ,null, '''', '''', null, null FROM
111			sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid')
112			as (parcelle varchar(6), surface numeric(10,3), geom geometry, proprietaire varchar(6),
113			debut varchar(4), rivoli varchar(4), section varchar(7), pos varchar(10));
114	fraynaud	89
115			CREATE OR REPLACE VIEW rivoli AS
116			SELECT *
117			FROM dblink('dbname=arles_dynmap'::text,
118			'select distinct rivoli_id, substr((typevoie\|\|' '\|\|nomvoie),0,40) as libelle
119			from sb_data_254 where rivoli_id != ''''')
120			as (rivoli varchar(4), libelle varchar(40));
121	fraynaud	126
122			CREATE OR REPLACE VIEW servitude_surfacique AS
123			SELECT *
124			FROM dblink('dbname=arles_dynmap'::text,
125	fraynaud	152	'SELECT sb_data_90.rowid,type_, coment, 0, g FROM
126	fraynaud	126	sb_data_90 inner join sb_geom_90 on sb_data_90.rowid=sb_geom_90.rowid')
127	fraynaud	152	as (servitude_surfacique integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
128	fraynaud	126
129	fraynaud	128	CREATE OR REPLACE VIEW servitude_ligne AS
130			SELECT *
131			FROM dblink('dbname=arles_dynmap'::text,
132	fraynaud	152	'SELECT sb_data_331.rowid,type_, coment, 100, g FROM
133	fraynaud	128	sb_data_331 inner join sb_geom_331 on sb_data_331.rowid=sb_geom_331.rowid')
134	fraynaud	152	as (servitude_ligne integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
135	fraynaud	128
136	fraynaud	130	CREATE OR REPLACE VIEW servitude_point AS
137			SELECT *
138			FROM dblink('dbname=arles_dynmap'::text,
139	fraynaud	152	'SELECT sb_data_89.rowid,type_, coment, 500, g FROM
140	fraynaud	130	sb_data_89 inner join sb_geom_89 on sb_data_89.rowid=sb_geom_89.rowid')
141	fraynaud	152	as (servitude_point integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
142	fraynaud	128
143	fraynaud	130
144	fraynaud	142	CREATE OR REPLACE VIEW parcelle_lot AS
145			SELECT *
146			FROM dblink('dbname=arles_dynmap'::text,
147			'SELECT rowid, lotissemen , num , surface, the_geom FROM lot')
148			as (parcelle_lot integer, lotissement varchar(50), numero varchar(16), surface numeric(10,3), geom geometry);
149
150	fraynaud	138