README.md 11.6 KB
Newer Older
Felix Delattre's avatar
Felix Delattre committed
1
2
3
# `GDE Tiles` Database Management

This repository handles the structure and migrations in the `gde_tiles` database.
Felix Delattre's avatar
Felix Delattre committed
4
5
6
7
8
9
10
11
12
13
14

The database is described through SQL files in the `migrations/` directory.

## Database tables

####  `gde_tiles` - Main `GDE Tiles` information

| column name  | data type          |
|--------------|--------------------|
| quadkey      | text (primary key) |

15
16
17
18
19
20
21
22
23
24
25
#### `aggregated_sources` - Information about sources of aggregated exposure models

Aggregated exposure models are an input to the Global Dynamic Exposure (GDE) model. Details on
the sources can be found [here](https://git.gfz-potsdam.de/dynamicexposure/datasources).

| column name          | data type | description                 |
|----------------------|-----------|-----------------------------|
| aggregated_source_id | smallint  | Serial primary key.         |
| name                 | varchar   | Name of the source.         |
| format               | varchar   | Format of the source files. |

Felix Delattre's avatar
Felix Delattre committed
26
27
28
29
30
31
32
33
34
35
#### `migrations` - Information about database migrations

Created and managed by [inyuu](https://git.gfz-potsdam.de/dynamicexposure/openbuildingmap/inyuu):

| column name | data type    |
|-------------|--------------|
| name        | varchar(100) |
| hash        | varchar(60)  |
| executed_at | timestamp    |

36
#### `data_units` - Information about data units
37
38
39
40
41
42

Data-units are the smallest geographical unit where an exposure model is defined, i.e. where
data is available from an input aggregated exposure model for a particular occupancy case.
Consequently, the primary key of this table consists of three fields: `data_unit_id`,
`occupancy_case` and `aggregated_source_id`.

43
44
45
46
47
48
49
50
51
52
| column name                       | data type     | description                                                               |
|-----------------------------------|---------------|---------------------------------------------------------------------------|
| data_unit_id                      | varchar       | Identifier of the data unit.                                              |
| occupancy_case                    | occupancycase | See [personalised enumerated types](#personalised-enumerated-types).      |
| aggregated_source_id              | smallint      | Identifier of the source of the aggregated model.                         |
| exposure_entity                   | char(3)       | Identifier of the exposure entity. If a country, ISO3 code.               |
| buildings_total                   | float         | Total number of buildings as per the aggregated exposure model.           |
| dwellings_total                   | float         | Total number of dwellings as per the aggregated exposure model.           |
| people_census                     | float         | Total number of census people as per the aggregated exposure model.       |
| cost_total                        | float         | Total replacement cost of buildings as per the aggregated exposure model. |
53

54
55
#### `data_unit_tiles` - Information about data-unit tiles

56
57
58
Data-unit tiles result from the intersection of data units with zoom level 18 quadtiles.
Consequently, the primary key of this table consists of four fields: `quadkey`,
`aggregated_source_id`, `occupancy_case` and `data_unit_id`.
59

60
61
62
63
64
65
66
67
68
69
70
71
| column name                       | data type     | description                                                                                                |
|-----------------------------------|---------------|------------------------------------------------------------------------------------------------------------|
| quadkey                           | char(18)      | Zoom level 18 tile identifier.                                                                             |
| aggregated_source_id              | smallint      | Identifier of the source of the aggregated model.                                                          |
| occupancy_case                    | occupancycase | See [personalised enumerated types](#personalised-enumerated-types).                                       |
| exposure_entity                   | char(3)       | Identifier of the exposure entity. If a country, ISO3 code.                                                |
| data_unit_id                      | varchar       | Identifier of the data unit.                                                                               |
| size_data_unit_tile_area          | float         | Area of the data-unit tile, in m2.                                                                         |
| size_data_unit_tile_built_up_area | float         | Built-up area within the data-unit tile, in m2.                                                            |
| fraction_data_unit_area           | float         | `size_data_unit_tile_area` / area of the data unit.                                                        |
| fraction_data_unit_built_up_area  | float         | `size_data_unit_tile_built_up_area` / built-up area of the data unit.                                      |
| aggregated_buildings              | float         | Number of buildings from (`aggregated_source_id`, `occupancy_case`, `data_unit_id`) in the data-unit tile. |
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
#### `data_units_buildings` - Information about buildings in a data unit

This table stores parameters associated with building classes in a particular data unit: the
proportion in which the building class is present in the data unit (all proportions associated
with the same primaary key add up to 1.0), the number of census-derived people per building and
the total replacement cost per building (including structural and non-structural components, as
well as contents).
The primary key of this table consists of six fields: `building_class_name`,
`settlement_type`, `occupancy_subtype`, `aggregated_source_id`, `occupancy_case`
 and `data_unit_id`.

| column name                | data type     | description                                                                              |
|----------------------------|---------------|------------------------------------------------------------------------------------------|
| building_class_name        | varchar       | Building class as per the [GEM Building Taxonomy](https://github.com/gem/gem_taxonomy).  |
| settlement_type            | settlement    | See [personalised enumerated types](#personalised-enumerated-types).                     |
| occupancy_subtype          | varchar       | Details on the occupancy, if relevant to characterise the building classes.              |
| aggregated_source_id       | smallint      | Identifier of the source of the aggregated model.                                        |
| occupancy_case             | occupancycase | See [personalised enumerated types](#personalised-enumerated-types).                     |
| exposure_entity            | char(3)       | Identifier of the exposure entity. If a country, ISO3 code.                              |
| data_unit_id               | varchar       | Identifier of the data unit.                                                             |
| proportions                | float         | Proportions in which this building class (**Note 1**) is present in the data unit.       |
| census_people_per_building | float         | Number of census-derived people per building (i.e. not accounting for time of the day).  |
| total_cost_per_building    | float         | Total replacement cost per building (including structural, non-structural and contents). |

**Note 1**: A "building class" in this table is defined by the primary key.

99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
#### `exposure_entities_costs_assumptions` - Information about assumptions associated with building replacement costs

This table stores the currency in which the replacement costs of buildings of a particular
`occupancy_case` in a particular `exposure_entity` of an aggregated exposure model with ID
`aggregated_source_id` are given, as well as the factors by which to multiply total costs to
obtain the costs of structural components, non-structural components and building contents.
The primary key of this table consists of three fields: `exposure_entity`, `occupancy_case` and
`aggregated_source_id`.

| column name          | data type     | description                                                                                  |
|----------------------|---------------|----------------------------------------------------------------------------------------------|
| exposure_entity      | char(3)       | Identifier of the exposure entity. If a country, ISO3 code.                                  |
| occupancy_case       | occupancycase | See [personalised enumerated types](#personalised-enumerated-types).                         |
| aggregated_source_id | smallint      | Identifier of the source of the aggregated model.                                            |
| structural           | float         | Factor by which to multiply total costs to obtain the cost of the structural components.     |
| non_structural       | float         | Factor by which to multiply total costs to obtain the cost of the non-structural components. |
| contents             | float         | Factor by which to multiply total costs to obtain the cost of the building contents.         |
| currency             | varchar       | Currency in which the replacement costs are expressed.                                       |

118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#### `exposure_entities_population_time_distribution` - Information about the distribution of people in buildings at different times of the day

This table stores the factors by which the census population per building can be multiplied to
obtain an estimate of the people in the buildings at a certain time of the day.
The primary key of this table consists of three fields: `exposure_entity`, `occupancy_case` and
`aggregated_source_id`.

| column name          | data type     | description                                                                                                                             |
|----------------------|---------------|-----------------------------------------------------------------------------------------------------------------------------------------|
| exposure_entity      | char(3)       | Identifier of the exposure entity. If a country, ISO3 code.                                                                             |
| occupancy_case       | occupancycase | See [personalised enumerated types](#personalised-enumerated-types).                                                                    |
| aggregated_source_id | smallint      | Identifier of the source of the aggregated model.                                                                                       |
| day                  | float         | Factor by which to multiply census people to obtain the number of people during day time (approx. 10 am to 6 pm).                       |
| night                | float         | Factor by which to multiply census people to obtain the number of people during night time (approx. 10 pm to 6 am).                     |
| transit              | float         | Factor by which to multiply census people to obtain the number of people during transit time (approx. 6 am to 10 am and 6 pm to 10 pm). |

134
135
136
137
138
139
140
141
142
143
144
## Personalised enumerated types

####  `occupancycase`

The `occupancycase` enumerated type can have any of the following values:

| value       |
|-------------|
| residential |
| commercial  |
| industrial  |
145

146
147
148
149
150
151
152
153
154
155
156
####  `settlement`

The `settlement` enumerated type can have any of the following values:

| value    |
|----------|
| urban    |
| rural    |
| big_city |
| all      |

Felix Delattre's avatar
Felix Delattre committed
157
158
159
## More information

* [Database versioning best practices](https://enterprisecraftsmanship.com/posts/database-versioning-best-practices/)