test_database_set_up.sql 10.1 KB
Newer Older
1
DROP TABLE IF EXISTS aggregated_sources;
2
DROP TABLE IF EXISTS data_units;
3
DROP TABLE IF EXISTS obm_buildings;
4
DROP TABLE IF EXISTS data_units_buildings;
5
DROP TABLE IF EXISTS data_unit_tiles;
6
DROP TABLE IF EXISTS obm_built_area_assessments;
7
DROP TABLE IF EXISTS gde_buildings;
8
DROP TYPE IF EXISTS occupancycase;
9
DROP TYPE IF EXISTS settlement;
10
11
12
13
14
DROP EXTENSION IF EXISTS postgis;

CREATE EXTENSION postgis;

CREATE TYPE occupancycase AS ENUM ('residential', 'commercial', 'industrial');
15
CREATE TYPE settlement AS ENUM ('urban', 'rural', 'big_city', 'all');
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE aggregated_sources
(
    aggregated_source_id SERIAL PRIMARY KEY,
    name varchar,
    format varchar
);
INSERT INTO aggregated_sources(name, format)
VALUES ('esrm20', 'esrm20'),
('second_source', 'bbb'),
('third_source', 'ccc'),
('first_source', 'ddd');
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE data_units
(
    data_unit_id VARCHAR,
    occupancy_case occupancycase,
    aggregated_source_id SMALLINT,
    exposure_entity CHAR(3),
    buildings_total FLOAT,
    dwellings_total FLOAT,
    people_census FLOAT,
    cost_total FLOAT,
    geometry GEOMETRY,

    PRIMARY KEY (data_unit_id, occupancy_case, aggregated_source_id)
);
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
INSERT INTO data_units(data_unit_id,
                       occupancy_case,
                       aggregated_source_id,
                       exposure_entity,
                       buildings_total,
                       dwellings_total,
                       people_census,
                       cost_total,
                       geometry)
VALUES (
    'ABC_10269', 'residential', 2, 'ABC', 0.0, 0.0, 0.0, 0.0,
    ST_GeomFromText(
        'POLYGON((15.04625 37.48424,15.05455 37.48424,15.05455 37.475893,15.04625 37.475893,15.04625 37.48424))')),
(
    'ABC_10278', 'residential', 2, 'ABC', 0.0, 0.0, 0.0, 0.0,
    ST_GeomFromText('POLYGON((15.05455 37.48424,15.0629 37.48424,15.0629 37.475893,15.05455 37.475893,15.05455 37.48424))')),
(
    'ABC_10277', 'residential', 2, 'ABC', 0.0, 0.0, 0.0, 0.0,
    ST_GeomFromText('POLYGON((15.05455 37.475893,15.0629 37.475893,15.0629 37.4675485,15.05455 37.4675485,15.05455 37.475893))'));
                    
63
64
65
66
67
68
69
70
INSERT INTO data_units(data_unit_id,
                       occupancy_case,
                       aggregated_source_id,
                       exposure_entity,
                       buildings_total,
                       dwellings_total,
                       people_census,
                       cost_total)
71
VALUES ('DEF_00000', 'residential', 2, 'DEF', 0.0, 0.0, 0.0, 0.0);
72
73
74
75
76
77
78
79
80
81
82
83
84

CREATE TABLE obm_buildings
(
    osm_id INTEGER,
    storeys INTEGER,
    relation_id INTEGER,
    occupancy VARCHAR,
    quadkey CHAR(18),
    geometry GEOMETRY (GEOMETRY, 4326),

    PRIMARY KEY (osm_id)
);

85
-- Residential building with number of storeys, not part of relation
86
INSERT INTO obm_buildings(osm_id, storeys, occupancy, quadkey, geometry)
87
VALUES (
88
    11223344, 4, 'RES2', '122010321033023130',
89
90
    ST_GeomFromText('POLYGON((15.0487 37.4812,15.0489 37.4810,15.0486 37.4808,15.0484 37.4810,15.0487 37.4812))')),
(
91
    99001122, 15, 'RES2', '122010321033023130',
92
    ST_GeomFromText('POLYGON((15.0490 37.4813,15.0490 37.4812,15.0488 37.4812,15.0488 37.4813,15.0490 37.4813))'));
93
-- Buildings that are not part of a relation and do not have number of storeys
94
INSERT INTO obm_buildings(osm_id, occupancy, quadkey, geometry)
95
VALUES (
96
    22334455, 'RES1', '122010321033023130',
97
98
    ST_GeomFromText('POLYGON((15.0492 37.4808,15.0492 37.4806,15.0490 37.4806,15.0490 37.4808,15.0492 37.4808))')),
(
99
    55667788, 'RES3', '122010321033023130',
100
101
    ST_GeomFromText('POLYGON((15.0495 37.4810,15.0498 37.4810,15.0498 37.4808,15.0495 37.4808,15.0495 37.4810))')),
(
102
    88990011, 'RES', '122010321033023120',
103
    ST_GeomFromText('POLYGON((15.0463 37.4809,15.0463 37.4808,15.0461 37.4808,15.0461 37.4809,15.0463 37.4809))'));
104
-- Commercial buildings that are part of a relation, with number of storeys
105
INSERT INTO obm_buildings(osm_id, storeys, relation_id, occupancy, quadkey, geometry)
106
VALUES (
107
    33445566, 2, -101010, 'COM3', '122010321033023130',
108
109
    ST_GeomFromText('POLYGON((15.0491 37.4811,15.0494 37.4814,15.0495 37.4813,15.0492 37.4810,15.0491 37.4811))')),
(
110
    44556677, 3, -101010, 'COM3', '122010321033023130',
111
112
    ST_GeomFromText('POLYGON((15.0495 37.4813,15.0497 37.4812,15.0495 37.4811,15.0494 37.4812,15.0495 37.4813))')),
(
113
    66778899, 4, -202020, 'COM2', '122010321033023132',
114
115
    ST_GeomFromText('POLYGON((15.0490 37.4802,15.0493 37.4804,15.0494 37.4802,15.0491 37.4800,15.0490 37.4802))'));
-- Commercial buildings that are part of a relation, without number of storeys
116
INSERT INTO obm_buildings(osm_id, relation_id, occupancy, quadkey, geometry)                    
117
VALUES (
118
    77889900, -202020, 'COM2', '122010321033023130',
119
    ST_GeomFromText('POLYGON((15.0494 37.4805,15.0496 37.4803,15.0494 37.4802,15.0492 37.4804,15.0494 37.4805))'));
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

CREATE TABLE data_units_buildings
(
    building_class_name VARCHAR,
    settlement_type settlement,
    occupancy_subtype VARCHAR,
    aggregated_source_id SMALLINT,
    exposure_entity CHAR(3),
    occupancy_case occupancycase,
    data_unit_id VARCHAR,
    proportions FLOAT,
    census_people_per_building FLOAT,
    total_cost_per_building FLOAT,
	storeys_min SMALLINT,
	storeys_max SMALLINT,

    PRIMARY KEY (
        data_unit_id,
        occupancy_case,
        aggregated_source_id,
        building_class_name,
        settlement_type,
        occupancy_subtype
    )

);
INSERT INTO data_units_buildings(building_class_name,
                                 settlement_type,
                                 occupancy_subtype,
                                 aggregated_source_id,
                                 exposure_entity,
                                 occupancy_case,
                                 data_unit_id,
                                 proportions,
                                 census_people_per_building,
                                 total_cost_per_building,
                                 storeys_min,
                                 storeys_max)
VALUES ('A1/HBET:1-3', 'urban', 'all', 2, 'ABC', 'residential', 'ABC_10269', 0.20, 0.0, 0.0, 1, 3),
('A2/HBET:4-6', 'urban', 'all', 2, 'ABC', 'residential', 'ABC_10269', 0.30, 0.0, 0.0, 4, 6),
160
('A3/HBET:7-12', 'urban', 'all', 2, 'ABC', 'residential', 'ABC_10269', 0.10, 0.0, 0.0, 7, 12),
161
162
163
164
165
166
167
168
('B1/HBET:1-3', 'rural', 'all', 2, 'ABC', 'residential', 'ABC_10269', 0.25, 0.0, 0.0, 1, 3),
('B2/H:4', 'rural', 'all', 2, 'ABC', 'residential', 'ABC_10269', 0.15, 0.0, 0.0, 4, 4),
('C1/HBET:1-2', 'urban', 'Hotels', 2, 'ABC', 'commercial', 'ABC_10269', 0.10, 0.0, 0.0, 1, 2),
('C2/HBET:3-', 'urban', 'Hotels', 2, 'ABC', 'commercial', 'ABC_10269', 0.25, 0.0, 0.0, 3, 9999),
('C3/H:1', 'urban', 'Trade', 2, 'ABC', 'commercial', 'ABC_10269', 0.05, 0.0, 0.0, 1, 1),                   
('C4/HBET:2-3', 'urban', 'Trade', 2, 'ABC', 'commercial', 'ABC_10269', 0.10, 0.0, 0.0, 2, 3),
('C5/HBET:1-2', 'urban', 'Offices', 2, 'ABC', 'commercial', 'ABC_10269', 0.20, 0.0, 0.0, 1, 2),
('C6/HBET:3-5', 'urban', 'Offices', 2, 'ABC', 'commercial', 'ABC_10269', 0.30, 0.0, 0.0, 3, 5);
169
170
171
172
173
174
175
176
177
178
179
180
181

CREATE TABLE data_unit_tiles
(
    quadkey char(18),
    aggregated_source_id SMALLINT,
    occupancy_case occupancycase,
    exposure_entity char(3),
    data_unit_id varchar,
    size_data_unit_tile_area FLOAT,
    size_data_unit_tile_built_up_area FLOAT,
    fraction_data_unit_area FLOAT,
    fraction_data_unit_built_up_area FLOAT,
    aggregated_buildings FLOAT,
182
183
    obm_buildings SMALLINT,
    remainder_buildings FLOAT,
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201

    PRIMARY KEY (quadkey, aggregated_source_id, occupancy_case, data_unit_id)
);
INSERT INTO data_unit_tiles(quadkey,
                            aggregated_source_id,
                            occupancy_case,
                            exposure_entity,
                            data_unit_id,
                            size_data_unit_tile_area,
                            size_data_unit_tile_built_up_area,
                            fraction_data_unit_area,
                            fraction_data_unit_built_up_area,
                            aggregated_buildings)
VALUES ('122010321033023130', 2, 'residential', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 15.7),
('122010321033023130', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 23.4),
('122010321033023120', 2, 'residential', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 39.1),
('122010321033023120', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 17.6),
('122010321033023132', 2, 'residential', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 34.4),
202
203
204
205
206
('122010321033023132', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 11.5),
('122010321033023121', 2, 'residential', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 26.2),
('122010321033023121', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 0.0),
('122010321033023123', 2, 'residential', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 16.5),
('122010321033023123', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 0.0);
207
208
209
210
211
212
213
214
215
216
217

CREATE TABLE obm_built_area_assessments
(
    quadkey char(18),
    source_id SMALLINT,
    completeness SMALLINT,
    PRIMARY KEY (quadkey, source_id)
);
INSERT INTO obm_built_area_assessments(quadkey, source_id, completeness)
VALUES ('122010321033023130', 1, 0),
('122010321033023120', 1, 0),
218
219
220
('122010321033023132', 1, 1),
('122010321033023121', 1, 0),
('122010321033023123', 1, 1);
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

CREATE TABLE gde_buildings
(
    osm_id integer,
    aggregated_source_id SMALLINT,
    occupancy_case occupancycase,
    data_unit_id VARCHAR,    
    building_class_names VARCHAR[],
    settlement_types settlement[],
    occupancy_subtypes VARCHAR[],
    probabilities FLOAT[],
    
    PRIMARY KEY (osm_id, aggregated_source_id)
);
INSERT INTO gde_buildings(osm_id,
                          aggregated_source_id,
                          occupancy_case,
                          data_unit_id,    
                          building_class_names,
                          settlement_types,
                          occupancy_subtypes,
                          probabilities)
VALUES (-101010, 2, 'industrial', 'ABC_10269',
        '{"CLASS/X/params/H:1", "CLASS/Y/params/H:2"}',
        '{"rural", "rural"}',
        '{"all", "all"}',
        '{0.723, 0.277}'
       );