GDE Tiles Database Management
This repository handles the structure and migrations in the gde_tiles database.
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) |
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.
| 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. |
migrations - Information about database migrations
Created and managed by inyuu:
| column name | data type |
|---|---|
| name | varchar(100) |
| hash | varchar(60) |
| executed_at | timestamp |
data_units - Information about data units
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.
| column name | data type | description |
|---|---|---|
| data_unit_id | varchar | Identifier of the data unit. |
| occupancy_case | occupancycase | See 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. |
| geometry | geometry | Geometry of the data unit. |
data_unit_tiles - Information about data-unit tiles
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.
| 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. |
| 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. |
| obm_buildings | smallint | Number of OBM buildings of occupancy_case in the data-unit tile. |
| remainder_buildings | float | Number of remainder buildings in the data-unit tile (Note 1) . |
Note 1: The remainder_buildings are calculated as a function of the aggregated_buildings, the obm_buildings and the completeness of OpenBuildingMap of the tile.
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. |
| settlement_type | settlement | See 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. |
| 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 2) 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). |
| storeys_min | smallint | Minimum number of storeys of the building class. |
| storeys_max | smallint | Maximum number of storeys of the building class. |
Note 2: A "building class" in this table is defined by the primary key.
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. |
| 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. |
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. |
| 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). |
gde_buildings - OBM Buildings with GDE building classes
GDE buildings result from the assignment of building classes (as defined in an aggregated exposure model) to OpenBuildingMap buildings.
For each OBM building (= one entry in this table), building_class_names, settlement_types,
occupancy_subtypes and probabilities are arrays of the same length, and the elements of one
array correspond to those of the other. As in the
data_units_buildings table,
a building class is defined by the combination of the three parameters building_class_names,
settlement_types and occupancy_subtypes.
The primary key of this table consists of two fields: osm_id and aggregated_source_id.
| column name | data type | description |
|---|---|---|
| osm_id | integer | OpenStreetMap ID of the building (ID of the relation if building is presented by one). |
| aggregated_source_id | smallint | Identifier of the source of the aggregated model. |
| occupancy_case | occupancycase | See personalised enumerated types. |
| data_unit_id | varchar | Identifier of the data unit the OBM building belongs to. |
| building_class_names | array of varchar | Building classes as per the GEM Building Taxonomy. |
| settlement_types | array of settlement | See personalised enumerated types. |
| occupancy_subtypes | array of varchar | Details on the occupancy, if relevant to characterise the building classes. |
| probabilities | array of float | Probabilities of the building belonging to each building class. |
Personalised enumerated types
occupancycase
The occupancycase enumerated type can have any of the following values:
| value |
|---|
| residential |
| commercial |
| industrial |
settlement
The settlement enumerated type can have any of the following values:
| value |
|---|
| urban |
| rural |
| big_city |
| all |