Newer
Older
-- ------------------------
-- POSTGIS
-- ------------------------
CREATE EXTENSION IF NOT EXISTS postgis;
-- ------------------------
-- POSTGIS
-- ------------------------
CREATE ROLE adm;
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
-- ------------------------
-- SCHEMA
-- ------------------------
DROP SCHEMA IF EXISTS base CASCADE;
CREATE SCHEMA base AUTHORIZATION adm;
-- AUTHORIZATION adm;
-- ------------------------
-- TABLES
-- ------------------------
CREATE TABLE base.tiles (
id serial PRIMARY KEY,
geom_poly geometry(POLYGON) NOT NULL,
insee int NULL,
indice real NULL DEFAULT 0
);
CREATE TABLE base.factors (
id serial PRIMARY KEY,
"name" character varying(100) NULL,
ponderation smallint NULL
);
CREATE TABLE base.metadatas (
id serial PRIMARY KEY,
"name" character varying(100) NOT NULL,
date_produceur timestamp without time zone NULL,
date_integration timestamp without time zone NULL,
"version" character varying(100) NULL,
"type" character varying(100) NULL,
quality character varying(100) NULL,
source_url character varying(255) NULL,
source_name character varying(255) NULL,
temp_file_path character varying(255) NULL,
script_path character varying(255)[] NULL,
factors_list int[] NULL
);
CREATE TABLE base.datas (
id serial PRIMARY KEY,
geom_poly geometry(POLYGON) NOT NULL,
custom_field1 character varying(100) NULL,
custom_field2 character varying(100) NULL,
id_metadata INT NOT NULL,
id_factor INT NOT NULL,
FOREIGN KEY (id_metadata)
REFERENCES base.metadatas (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
FOREIGN KEY (id_factor)
REFERENCES base.factors (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE base.communes (
id serial PRIMARY KEY,
libelle character varying(255) NULL,
insee character varying(10) NULL,
geom_poly geometry(POLYGON) NOT NULL
);
CREATE TABLE base.users (
id serial PRIMARY KEY,
firstname character varying(255) NULL,
lastname character varying(255) NULL,
email character varying(255) NOT NULL,
passwd character varying(255) NOT NULL,
user_role character varying(50) NOT NULL
);
CREATE TABLE base.history (
id serial PRIMARY KEY,
search_name character varying(255) NOT NULL,
search_location character varying(255) NULL,
id_user INT NOT NULL,
FOREIGN KEY (id_user)
REFERENCES base.users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
-- ------------------------
-- TABLES LIAISON
-- ------------------------
CREATE TABLE base.tiles_factors (
id serial PRIMARY KEY,
id_tile INT NOT NULL,
id_factor INT NOT NULL,
area smallint NULL,
FOREIGN KEY (id_tile)
REFERENCES base.tiles (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
FOREIGN KEY (id_factor)
REFERENCES base.factors (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
-- ------------------------
-- INDEX
-- ------------------------
CREATE INDEX CONCURRENTLY tiles_geom_index ON base.tiles (geom_poly);
CREATE INDEX CONCURRENTLY datas_geom_index ON base.datas USING GIST (geom_poly);