test_database_set_up.sql 4.55 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
5
6
7
8
9
DROP TYPE IF EXISTS occupancycase;
DROP EXTENSION IF EXISTS postgis;

CREATE EXTENSION postgis;

CREATE TYPE occupancycase AS ENUM ('residential', 'commercial', 'industrial');
10
11
12
13
14
15
16
17
18
19
20
21

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');
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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)
);
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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))'));
                    
57
58
59
60
61
62
63
64
INSERT INTO data_units(data_unit_id,
                       occupancy_case,
                       aggregated_source_id,
                       exposure_entity,
                       buildings_total,
                       dwellings_total,
                       people_census,
                       cost_total)
65
VALUES ('DEF_00000', 'residential', 2, 'DEF', 0.0, 0.0, 0.0, 0.0);
66
67
68
69
70
71
72
73
74
75
76
77
78
79

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)
);

80
-- Residential building with number of storeys, not part of relation
81
82
83
84
INSERT INTO obm_buildings(osm_id, storeys, occupancy, occupancy_case, quadkey, geometry)
VALUES (
    11223344, 4, 'RES2', 'residential', '122010321033023130',
    ST_GeomFromText('POLYGON((15.0487 37.4812,15.0489 37.4810,15.0486 37.4808,15.0484 37.4810,15.0487 37.4812))'));
85
-- Buildings that are not part of a relation and do not have number of storeys
86
87
88
89
90
91
92
93
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))')),
(
94
    88990011, 'RES', 'residential', '122010321033023120',
95
    ST_GeomFromText('POLYGON((15.0463 37.4809,15.0463 37.4808,15.0461 37.4808,15.0461 37.4809,15.0463 37.4809))'));
96
-- Commercial buildings that are part of a relation, with number of storeys
97
98
99
100
101
102
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',
103
104
105
106
107
108
109
110
111
    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))'));