test_database_set_up.sql 8.98 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 TYPE IF EXISTS occupancycase;
8
DROP TYPE IF EXISTS settlement;
9
10
11
12
13
DROP EXTENSION IF EXISTS postgis;

CREATE EXTENSION postgis;

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

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');
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

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)
);
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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))'));
                    
62
63
64
65
66
67
68
69
INSERT INTO data_units(data_unit_id,
                       occupancy_case,
                       aggregated_source_id,
                       exposure_entity,
                       buildings_total,
                       dwellings_total,
                       people_census,
                       cost_total)
70
VALUES ('DEF_00000', 'residential', 2, 'DEF', 0.0, 0.0, 0.0, 0.0);
71
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,
    occupancy_case occupancycase,
    quadkey CHAR(18),
    geometry GEOMETRY (GEOMETRY, 4326),

    PRIMARY KEY (osm_id)
);

85
-- Residential building with number of storeys, not part of relation
86
87
88
INSERT INTO obm_buildings(osm_id, storeys, occupancy, occupancy_case, quadkey, geometry)
VALUES (
    11223344, 4, 'RES2', 'residential', '122010321033023130',
89
90
91
92
    ST_GeomFromText('POLYGON((15.0487 37.4812,15.0489 37.4810,15.0486 37.4808,15.0484 37.4810,15.0487 37.4812))')),
(
    99001122, 15, 'RES2', 'residential', '122010321033023130',
    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
95
96
97
98
99
100
101
INSERT INTO obm_buildings(osm_id, occupancy, occupancy_case, quadkey, geometry)
VALUES (
    22334455, 'RES1', 'residential', '122010321033023130',
    ST_GeomFromText('POLYGON((15.0492 37.4808,15.0492 37.4806,15.0490 37.4806,15.0490 37.4808,15.0492 37.4808))')),
(
    55667788, 'RES3', 'commercial', '122010321033023130',
    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', 'residential', '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
106
107
108
109
110
INSERT INTO obm_buildings(osm_id, storeys, relation_id, occupancy, occupancy_case, quadkey, geometry)
VALUES (
    33445566, 2, -101010, 'COM3', 'commercial', '122010321033023130',
    ST_GeomFromText('POLYGON((15.0491 37.4811,15.0494 37.4814,15.0495 37.4813,15.0492 37.4810,15.0491 37.4811))')),
(
    44556677, 3, -101010, 'COM3', 'commercial', '122010321033023130',
111
112
113
114
115
116
117
118
119
    ST_GeomFromText('POLYGON((15.0495 37.4813,15.0497 37.4812,15.0495 37.4811,15.0494 37.4812,15.0495 37.4813))')),
(
    66778899, 4, -202020, 'COM2', 'commercial', '122010321033023132',
    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
INSERT INTO obm_buildings(osm_id, relation_id, occupancy, occupancy_case, quadkey, geometry)                    
VALUES (
    77889900, -202020, 'COM2', 'commercial', '122010321033023130',
    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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200

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,

    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),
('122010321033023132', 2, 'commercial', 'ABC', 'ABC_10269', 0.0, 0.0, 0.0, 0.0, 11.5);
201
202
203
204
205
206
207
208
209
210
211

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),
212
213
214
('122010321033023132', 1, 1),
('122010321033023121', 1, 0),
('122010321033023123', 1, 1);