/[openfoncier]/trunk/data/pgsql/init_metier_vue.sql
ViewVC logotype

Annotation of /trunk/data/pgsql/init_metier_vue.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 130 - (hide annotations)
Wed Sep 21 10:33:41 2011 UTC (13 years, 4 months ago) by fraynaud
File size: 4695 byte(s)
servitudes gen *.inc + menu var vues


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 128 -- -----------------------------------------------
57     -- vue sur les servitudes ligne
58     --------------------------------------------------
59     CREATE OR REPLACE VIEW servitude_ligne AS
60     SELECT *
61     FROM dblink('dbname=arles_dynmap'::text,
62     'SELECT sb_data_331.rowid,type_, coment, g FROM
63     sb_data_331 inner join sb_geom_331 on sb_data_331.rowid=sb_geom_331.rowid')
64 fraynaud 130 as (servitude_ligne integer, libelle varchar(16), observation varchar(80), geom geometry);
65 fraynaud 128
66 fraynaud 130 -- -----------------------------------------------
67     -- vue sur les servitudes point
68     --------------------------------------------------
69     CREATE OR REPLACE VIEW servitude_point AS
70     SELECT *
71     FROM dblink('dbname=arles_dynmap'::text,
72     'SELECT sb_data_89.rowid,type_, coment, g FROM
73     sb_data_89 inner join sb_geom_89 on sb_data_89.rowid=sb_geom_89.rowid')
74     as (servitude_point integer, libelle varchar(16), observation varchar(80), geom geometry);
75 fraynaud 128
76 fraynaud 130
77    
78    
79 fraynaud 89 -- ------------------------------------------------
80     -- vue de dynmap sur dossier d'openFoncier
81     -- utilisation de l'oid comme identifiant numerique
82     ---------------------------------------------------
83    
84     CREATE OR REPLACE VIEW sb_data_dossier AS
85     SELECT *
86     FROM dblink('dbname=openfoncier'::text,
87     'select oid,dossier,nature,annee,etat,demandeur_nom
88     from dossier')
89     as (rowid integer, dossier varchar(12),
90     nature char(2), anne char(2),
91     etat varchar(20),
92     demandeur_nom varchar(80));
93    
94     CREATE OR REPLACE VIEW sb_geom_dossier AS
95     SELECT *
96     FROM dblink('dbname=openfoncier'::text, 'SELECT oid,geom FROM dossier ')
97     as (rowid integer, g geometry);
98    
99    
100     -- -------------------------------------
101     -- vue cadastre et pos dynmap format ogc
102     -- jointure data + geom
103     -- -------------------------------------
104    
105     CREATE OR REPLACE VIEW parcelle AS
106     SELECT sb_data_170.rowid, substring(idu_id,7,6) as parcelle, supf_id , g
107     FROM sb_data_170 inner join sb_geom_170 on sb_data_170.rowid=sb_geom_170.rowid;
108    
109     CREATE OR REPLACE VIEW pos AS
110     SELECT sb_data_338.rowid,type_, (id_sect||' '||substring(nom_zone,0,30)) as libelle, g
111     FROM sb_data_338 inner join sb_geom_338 on sb_data_338.rowid=sb_geom_338.rowid;

[email protected]
ViewVC Help
Powered by ViewVC 1.1.26