-
Notifications
You must be signed in to change notification settings - Fork 3
/
init_bd_pei_dependencies.sql
315 lines (252 loc) · 13.9 KB
/
init_bd_pei_dependencies.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
/*
objet : Script d'initialisation des dépendances au script init_bd_pei.sql
Historique des versions :
date | auteur | description
28/03/2018 | Florent VANHOUTTE | version initiale
Attention, hormis pour le domaine de valeur du référentiel géographique source, ce script ne contient
que le modèle des données (sans les entités) nécessaire à l'exécution du script d'initialisation de la base métier init_bd_pei.sql
*/
-- Schema: r_objet
-- DROP SCHEMA r_objet;
CREATE SCHEMA r_objet
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA r_objet TO postgres;
COMMENT ON SCHEMA r_objet
IS 'Schéma contenant les objets géographiques virtuels métiers (zonages, lots, entités administratives, ...). Les données métiers (alphanumériques) sont stockées dans le schéma correspondant, et le lien s''effectue via la référence géographique. Une donnée géographique spécifique à un seul métier, reste dans le schéma du métier.';
-- ################################################################# Domaine valeur ouvert - id_contrat ###############################################
-- Table: r_objet.lt_contrat
-- DROP TABLE r_objet.lt_contrat;
CREATE TABLE r_objet.lt_contrat
(
code character varying(2) NOT NULL DEFAULT to_char(nextval('m_reseau_sec.lt_ecl_id_contrat_seq'::regclass), 'FM00'::text), -- Code de la liste énumérée relative au numéro de contrat pour l'entretien et/ou le contrôle de réseau public par la ville ou l'ARC
valeur character varying(80) NOT NULL, -- Valeur de la référence du marché du contrat pour l'entretien et/ou le contrôle de réseau public par la ville ou l'ARC
presta character varying(254), -- Nom du prestataire retenu par le contrat pour l'entretien et/ou le contrôle de réseau public par la ville ou l'ARC
ddebut timestamp without time zone, -- Date de début du contrat
dfin timestamp without time zone, -- Date de fin du contrat
definition character varying(254), -- Definition du contrat pour l'entretien et/ou le contrôle de réseau public par la ville ou l'ARC
CONSTRAINT lt_contrat_pkey PRIMARY KEY (code)
)
WITH (
OIDS=FALSE
);
ALTER TABLE r_objet.lt_contrat
OWNER TO sig_create;
GRANT ALL ON TABLE r_objet.lt_contrat TO sig_create;
GRANT ALL ON TABLE r_objet.lt_contrat TO create_sig;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE r_objet.lt_contrat TO edit_sig;
GRANT SELECT ON TABLE r_objet.lt_contrat TO read_sig;
COMMENT ON TABLE r_objet.lt_contrat
IS 'Liste des contrats pour l''entretien et/ou le contrôle de réseau public par la ville ou l''ARC';
COMMENT ON COLUMN r_objet.lt_contrat.code IS 'Code de la liste énumérée relative au numéro de contrat pour l''entretien et/ou le contrôle de réseau public par la ville ou l''ARC';
COMMENT ON COLUMN r_objet.lt_contrat.valeur IS 'Valeur de la référence du marché du contrat pour l''entretien et/ou le contrôle de réseau public par la ville ou l''ARC';
COMMENT ON COLUMN r_objet.lt_contrat.presta IS 'Nom du prestataire retenu par le contrat pour l''entretien et/ou le contrôle de réseau public par la ville ou l''ARC';
COMMENT ON COLUMN r_objet.lt_contrat.ddebut IS 'Date de début du contrat';
COMMENT ON COLUMN r_objet.lt_contrat.dfin IS 'Date de fin du contrat';
COMMENT ON COLUMN r_objet.lt_contrat.definition IS 'Definition du contrat pour l''entretien et/ou le contrôle de réseau public par la ville ou l''ARC';
-- cette table de contrat est commune à l'ensemble des données gérées par contrat. Cette table est donc non supprimables mais le code est
-- mis à disposition ici. Les valeurs intégrant cette liste sont confidentielles.
-- ################################################################# Domaine valeur - source du référentiel geom ###############################################
-- Table: r_objet.lt_src_geom
-- DROP TABLE r_objet.lt_src_geom;
CREATE TABLE r_objet.lt_src_geom
(
code character varying(2) NOT NULL, -- Code de la liste énumérée relative au type de référentiel géométrique
valeur character varying(254) NOT NULL, -- Valeur de la liste énumérée relative au type de référentiel géométrique
CONSTRAINT lt_src_geom_pkey PRIMARY KEY (code)
)
WITH (
OIDS=FALSE
);
ALTER TABLE r_objet.lt_src_geom
OWNER TO postgres;
GRANT ALL ON TABLE r_objet.lt_src_geom TO postgres;
COMMENT ON TABLE r_objet.lt_src_geom
IS 'Code permettant de décrire le type de référentiel géométrique';
COMMENT ON COLUMN r_objet.lt_src_geom.code IS 'Code de la liste énumérée relative au type de référentiel géométrique';
COMMENT ON COLUMN r_objet.lt_src_geom.valeur IS 'Valeur de la liste énumérée relative au type de référentiel géométrique';
INSERT INTO r_objet.lt_src_geom(
code, valeur)
VALUES
('10', 'Cadastre'),
('11', 'PCI vecteur'),
('12', 'BD Parcellaire'),
('13', 'RPCU'),
('20', 'Ortho-images'),
('21', 'Orthophotoplan IGN'),
('22', 'Orthophotoplan partenaire'),
('23', 'Orthophotoplan local'),
('30', 'Filaire voirie'),
('31', 'Route BDTopo'),
('32', 'Route OSM'),
('40', 'Cartes'),
('41', 'Scan25'),
('50', 'Lever'),
('51', 'Plan topographique'),
('52', 'PCRS'),
('53', 'Trace GPS'),
('60', 'Geocodage'),
('61', 'Base Adresse Locale'),
('70', 'Plan masse'),
('71', 'Plan masse vectoriel'),
('72', 'Plan masse redessiné'),
('80', 'Thématique'),
('81', 'Document d''urbanisme'),
('82', 'Occupation du Sol'),
('83', 'Thèmes BDTopo'),
('99', 'Autre'),
('00', 'Non renseigné');
-- ################################################################# Administratif ###############################################
-- Schema: r_administratif
-- DROP SCHEMA r_administratif;
CREATE SCHEMA r_administratif
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA r_administratif TO postgres;
COMMENT ON SCHEMA r_administratif
IS 'Référentiels géographiques administratifs (en attente de voir comment ce schéma doit évoluer ==> à supprimer par rapport aux schémas contenant les référentiels administratifs)';
-- Table: r_administratif.an_geo
-- DROP TABLE r_administratif.an_geo;
CREATE TABLE r_administratif.an_geo
(
insee character varying(5) NOT NULL, -- Code géographique de la commune (code insee)
libgeo character varying(255), -- Libellé géographique de la commune
dep smallint, -- Code géographique du département
reg smallint, -- Code géographique de la région jusqu'au 31 décembre 2015
reg2016 smallint, -- Code géographique de la région 2016
epci character varying(9), -- Code géographique de l'établissement public à fiscalité propre (EPCI)
nature_epci character varying(2), -- Nature d'établissement public
arr character varying(4), -- Code géographique de l'arrondissement
cv character varying(5), -- Code géographique du canton ville
ze2010 smallint, -- Code géographique de la zone d'emploi 2010
uu2010 character varying(5), -- Code géographique de l'unité urbaine 2010
tuu2010 smallint, -- Tranche d'unité urbaine 2010
tduu2010 smallint, -- Tranche détaillée d'unité urbaine 2010
au2010 character varying(3), -- Code géographique de l'aire urbaine 2010
tau2010 smallint, -- Tranche d'aire urbaine 2010
cataeu2010 smallint, -- Catégorie de la commune dans le zonage en aires urbaines 2010
bv2012 character varying(5), -- Code géographique du bassin de vie 2012
lib_epci character varying(255), -- Libellé des EPCI
nomreg2016 character varying(100), -- Nom des régions au 1er janvier 2016
dept character varying(2), -- Code du département
CONSTRAINT an_geo_pkey PRIMARY KEY (insee)
)
WITH (
OIDS=FALSE
);
ALTER TABLE r_administratif.an_geo
OWNER TO postgres;
GRANT ALL ON TABLE r_administratif.an_geo TO postgres;
COMMENT ON TABLE r_administratif.an_geo
IS 'Table attributaire contenant les codes adminsitratifs de découpage des différents périmètres officiels de l''Insee (commune, EPCI, zone d''emploi, ...)';
COMMENT ON COLUMN r_administratif.an_geo.insee IS 'Code géographique de la commune (code insee)';
COMMENT ON COLUMN r_administratif.an_geo.libgeo IS 'Libellé géographique de la commune';
COMMENT ON COLUMN r_administratif.an_geo.dep IS 'Code géographique du département';
COMMENT ON COLUMN r_administratif.an_geo.reg IS 'Code géographique de la région jusqu''au 31 décembre 2015';
COMMENT ON COLUMN r_administratif.an_geo.reg2016 IS 'Code géographique de la région 2016';
COMMENT ON COLUMN r_administratif.an_geo.epci IS 'Code géographique de l''établissement public à fiscalité propre (EPCI)';
COMMENT ON COLUMN r_administratif.an_geo.nature_epci IS 'Nature d''établissement public';
COMMENT ON COLUMN r_administratif.an_geo.arr IS 'Code géographique de l''arrondissement';
COMMENT ON COLUMN r_administratif.an_geo.cv IS 'Code géographique du canton ville';
COMMENT ON COLUMN r_administratif.an_geo.ze2010 IS 'Code géographique de la zone d''emploi 2010';
COMMENT ON COLUMN r_administratif.an_geo.uu2010 IS 'Code géographique de l''unité urbaine 2010';
COMMENT ON COLUMN r_administratif.an_geo.tuu2010 IS 'Tranche d''unité urbaine 2010';
COMMENT ON COLUMN r_administratif.an_geo.tduu2010 IS 'Tranche détaillée d''unité urbaine 2010';
COMMENT ON COLUMN r_administratif.an_geo.au2010 IS 'Code géographique de l''aire urbaine 2010';
COMMENT ON COLUMN r_administratif.an_geo.tau2010 IS 'Tranche d''aire urbaine 2010';
COMMENT ON COLUMN r_administratif.an_geo.cataeu2010 IS 'Catégorie de la commune dans le zonage en aires urbaines 2010';
COMMENT ON COLUMN r_administratif.an_geo.bv2012 IS 'Code géographique du bassin de vie 2012';
COMMENT ON COLUMN r_administratif.an_geo.lib_epci IS 'Libellé des EPCI';
COMMENT ON COLUMN r_administratif.an_geo.nomreg2016 IS 'Nom des régions au 1er janvier 2016';
COMMENT ON COLUMN r_administratif.an_geo.dept IS 'Code du département';
-- ################################################################# OSM ###############################################
-- Schema: r_osm
-- DROP SCHEMA r_osm;
CREATE SCHEMA r_osm
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA r_osm TO postgres;
COMMENT ON SCHEMA r_osm
IS 'Référentiel Géographique Open Street Map';
-- Table: r_osm.geo_osm_commune
-- DROP TABLE r_osm.geo_osm_commune;
CREATE TABLE r_osm.geo_osm_commune
(
commune_m character varying(255), -- Libellé des communes (en majuscule)
insee character varying(5) NOT NULL, -- Code Insee de la commune
gid serial NOT NULL, -- Identifiant incrémenté de 1 à n
geom geometry(MultiPolygon,2154), -- Champ contenant la géométrie des communes (polygone)
geom1 geometry(Point,2154), -- Champ contenant le centroïd des communes (point forcé dans le polygone)
commune character varying(80), -- Libellé des communes en minusucle (avec la 1ere lettre en Majuscule)
CONSTRAINT geo_osm_commune_pkey PRIMARY KEY (insee)
)
WITH (
OIDS=FALSE
);
ALTER TABLE r_osm.geo_osm_commune
OWNER TO postgres;
GRANT ALL ON TABLE r_osm.geo_osm_commune TO groupe_sig WITH GRANT OPTION;
GRANT ALL ON TABLE r_osm.geo_osm_commune TO postgres;
GRANT SELECT ON TABLE r_osm.geo_osm_commune TO groupe_apc;
GRANT SELECT ON TABLE r_osm.geo_osm_commune TO groupe_sig_stage WITH GRANT OPTION;
GRANT SELECT ON TABLE r_osm.geo_osm_commune TO groupe_eco;
GRANT SELECT ON TABLE r_osm.geo_osm_commune TO groupe_be;
COMMENT ON TABLE r_osm.geo_osm_commune
IS 'Limites des communes de la Région Picardie , Haute-Normandie et Ile-de-France issues d''Open Street Map';
COMMENT ON COLUMN r_osm.geo_osm_commune.commune_m IS 'Libellé des communes (en majuscule)';
COMMENT ON COLUMN r_osm.geo_osm_commune.insee IS 'Code Insee de la commune';
COMMENT ON COLUMN r_osm.geo_osm_commune.gid IS 'Identifiant incrémenté de 1 à n';
COMMENT ON COLUMN r_osm.geo_osm_commune.geom IS 'Champ contenant la géométrie des communes (polygone)';
COMMENT ON COLUMN r_osm.geo_osm_commune.geom1 IS 'Champ contenant le centroïd des communes (point forcé dans le polygone)';
COMMENT ON COLUMN r_osm.geo_osm_commune.commune IS 'Libellé des communes en minusucle (avec la 1ere lettre en Majuscule)';
-- Index: r_osm.geo_osm_commune_geom_idx
-- DROP INDEX r_osm.geo_osm_commune_geom_idx;
CREATE INDEX geo_osm_commune_geom_idx
ON r_osm.geo_osm_commune
USING gist
(geom);
-- Index: r_osm.geo_osm_commune_insee_idx
-- DROP INDEX r_osm.geo_osm_commune_insee_idx;
CREATE INDEX geo_osm_commune_insee_idx
ON r_osm.geo_osm_commune
USING btree
(insee COLLATE pg_catalog."default");
-- Table: r_osm.geo_osm_epci
-- DROP TABLE r_osm.geo_osm_epci;
CREATE TABLE r_osm.geo_osm_epci
(
cepci character varying(9) NOT NULL,
lib_epci character varying(255),
geom geometry(MultiPolygon,2154),
CONSTRAINT geo_osm_epci_pkey PRIMARY KEY (cepci)
)
WITH (
OIDS=FALSE
);
ALTER TABLE r_osm.geo_osm_epci
OWNER TO postgres;
GRANT ALL ON TABLE r_osm.geo_osm_epci TO postgres;
GRANT ALL ON TABLE r_osm.geo_osm_epci TO groupe_sig WITH GRANT OPTION;
GRANT SELECT ON TABLE r_osm.geo_osm_epci TO groupe_sig_stage WITH GRANT OPTION;
COMMENT ON TABLE r_osm.geo_osm_epci
IS 'Table des EPCI de l''Oise au 1er janvier 2018 issu de la vue matérialisée geo_vm_osm_epci';
-- Index: r_osm.geo_osm_epci_geom_idx
-- DROP INDEX r_osm.geo_osm_epci_geom_idx;
CREATE INDEX geo_osm_epci_geom_idx
ON r_osm.geo_osm_epci
USING gist
(geom);
-- View: r_osm.geo_v_osm_commune_apc
-- DROP VIEW r_osm.geo_v_osm_commune_apc;
CREATE OR REPLACE VIEW r_osm.geo_v_osm_commune_apc AS
SELECT geo_osm_commune.commune_m,
geo_osm_commune.commune,
geo_vm_osm_epci.lib_epci,
geo_osm_commune.insee,
geo_osm_commune.gid,
st_multi(geo_osm_commune.geom)::geometry(MultiPolygon,2154) AS geom
FROM r_osm.geo_osm_commune,
r_osm.geo_vm_osm_epci
WHERE (geo_vm_osm_epci.cepci::text = '200067965'::text OR geo_vm_osm_epci.cepci::text = '246000897'::text OR geo_vm_osm_epci.cepci::text = '246000749'::text) AND st_intersects(st_centroid(geo_osm_commune.geom), geo_vm_osm_epci.geom);
ALTER TABLE r_osm.geo_v_osm_commune_apc
OWNER TO postgres;
GRANT ALL ON TABLE r_osm.geo_v_osm_commune_apc TO postgres;
COMMENT ON VIEW r_osm.geo_v_osm_commune_apc
IS 'Limite communale des communes du Pays Compiégnois';