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 openfoncier.pos AS 
 SELECT sb_data_338.type_ AS pos,
    (sb_data_338.id_sect || ' '::text) || "substring"(sb_data_338.nom_zone::text, 0, 30) AS libelle,
    sb_data_338.rowid, 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 openfoncier.parcelle AS 
 SELECT "substring"(sb_data_170_copy.idu_id::text, 7, 6) AS parcelle,
       sb_data_170_copy.supf_id AS surface,
       sb_geom_170_copy.g AS geom,
       0 AS proprietaire, '' AS debut,
       '' AS rivoli, "substring"(sb_data_170_copy.idu_id::text, 7, 2) AS section,
       'arles' AS commune, 0 AS pos,
       sb_data_170_copy.rowid as rowid 
   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;


-- openadresse
CREATE OR REPLACE VIEW openfoncier.rivoli AS 
 SELECT rivoli AS rivoli,
        (type_voie||' ' ||nom_complet) as libelle        
   FROM openadresse.rivoli; 


-- ------------------------------------------------------
-- 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 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	331	CREATE OR REPLACE VIEW openfoncier.pos AS
59	fraynaud	249	SELECT sb_data_338.type_ AS pos,
60	fraynaud	328	(sb_data_338.id_sect \|\| ' '::text) \|\| "substring"(sb_data_338.nom_zone::text, 0, 30) AS libelle,
61			sb_data_338.rowid, sb_data_338.coment AS observation,
62			sb_geom_338.g AS geom
63	fraynaud	249	FROM accm_sig.sb_data_338
64	fraynaud	328	JOIN accm_sig.sb_geom_338 ON sb_data_338.rowid = sb_geom_338.rowid;
65	fraynaud	329	CREATE OR REPLACE VIEW openfoncier.parcelle AS
66			SELECT "substring"(sb_data_170_copy.idu_id::text, 7, 6) AS parcelle,
67			sb_data_170_copy.supf_id AS surface,
68			sb_geom_170_copy.g AS geom,
69			0 AS proprietaire, '' AS debut,
70			'' AS rivoli, "substring"(sb_data_170_copy.idu_id::text, 7, 2) AS section,
71			'arles' AS commune, 0 AS pos,
72			sb_data_170_copy.rowid as rowid
73	fraynaud	249	FROM accm_sig.sb_data_170_copy
74	fraynaud	329	JOIN accm_sig.sb_geom_170_copy ON sb_data_170_copy.rowid = sb_geom_170_copy.rowid;
75	fraynaud	249
76	fraynaud	268	CREATE OR REPLACE VIEW parcelle_lot AS
77	fraynaud	249	SELECT sb_data_499 rowid AS parcelle,
78			num as numero,
79			surface AS surface,
80			lotissemen as lotissement,
81			g AS geom
82			FROM accm_sig.sb_data_499
83			JOIN accm_sig.sb_geom_499
84			ON sb_data_499.rowid = sb_geom_499.rowid;
85
86
87	fraynaud	292	-- openadresse
88			CREATE OR REPLACE VIEW openfoncier.rivoli AS
89			SELECT rivoli AS rivoli,
90			(type_voie\|\|' ' \|\|nom_complet) as libelle
91			FROM openadresse.rivoli;
92	fraynaud	249
93	fraynaud	292
94	fraynaud	249	-- ------------------------------------------------------
95			-- vues sur dynmap avec dblink dans une base openfoncier
96			-- ------------------------------------------------------
97	fraynaud	40	CREATE OR REPLACE VIEW pos AS
98			SELECT *
99			FROM dblink('dbname=arles_dynmap'::text,
100			'SELECT type_, (id_sect\|\|'' ''\|\|substring(nom_zone,0,30)) , g FROM
101			sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid')
102			as (pos varchar(10), libelle varchar(40), geom geometry);
103	fraynaud	138
104	fraynaud	40	CREATE OR REPLACE VIEW parcelle AS
105			SELECT *
106			FROM dblink('dbname=arles_dynmap'::text,
107			'SELECT substring(idu_id,7,6), supf_id , g ,null, '''', '''', null, null FROM
108			sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid')
109			as (parcelle varchar(6), surface numeric(10,3), geom geometry, proprietaire varchar(6),
110			debut varchar(4), rivoli varchar(4), section varchar(7), pos varchar(10));
111	fraynaud	89
112	fraynaud	126	CREATE OR REPLACE VIEW servitude_surfacique AS
113			SELECT *
114			FROM dblink('dbname=arles_dynmap'::text,
115	fraynaud	152	'SELECT sb_data_90.rowid,type_, coment, 0, g FROM
116	fraynaud	126	sb_data_90 inner join sb_geom_90 on sb_data_90.rowid=sb_geom_90.rowid')
117	fraynaud	152	as (servitude_surfacique integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
118	fraynaud	126
119	fraynaud	128	CREATE OR REPLACE VIEW servitude_ligne AS
120			SELECT *
121			FROM dblink('dbname=arles_dynmap'::text,
122	fraynaud	152	'SELECT sb_data_331.rowid,type_, coment, 100, g FROM
123	fraynaud	128	sb_data_331 inner join sb_geom_331 on sb_data_331.rowid=sb_geom_331.rowid')
124	fraynaud	152	as (servitude_ligne integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
125	fraynaud	128
126	fraynaud	130	CREATE OR REPLACE VIEW servitude_point AS
127			SELECT *
128			FROM dblink('dbname=arles_dynmap'::text,
129	fraynaud	152	'SELECT sb_data_89.rowid,type_, coment, 500, g FROM
130	fraynaud	130	sb_data_89 inner join sb_geom_89 on sb_data_89.rowid=sb_geom_89.rowid')
131	fraynaud	152	as (servitude_point integer, libelle varchar(16), observation varchar(80), perimetre integer, geom geometry);
132	fraynaud	128
133	fraynaud	130
134	fraynaud	142	CREATE OR REPLACE VIEW parcelle_lot AS
135			SELECT *
136			FROM dblink('dbname=arles_dynmap'::text,
137			'SELECT rowid, lotissemen , num , surface, the_geom FROM lot')
138			as (parcelle_lot integer, lotissement varchar(50), numero varchar(16), surface numeric(10,3), geom geometry);
139
140	fraynaud	292