CREATE TABLE entity (
id serial,
quadkey TEXT,
osm_id INTEGER,
geom GEOMETRY ( MULTIPOLYGON, 4326),
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX entity_quadkey_osmid_idx ON entity (quadkey, (osm_id IS NULL)) WHERE osm_id IS NULL;
CREATE TABLE asset (
entity_id INTEGER,
taxonomy_id INTEGER,
number REAL,
structural REAL,
night REAL
);
CREATE INDEX asset_entityid_idx ON asset(entity_id);
CREATE TABLE building (
osm_id INTEGER UNIQUE,
quadkey TEXT,
occupancy TEXT,
storeys INTEGER,
geom GEOMETRY (MULTIPOLYGON, 4326)
);
CREATE TABLE tile (
quadkey TEXT UNIQUE,
country_iso_code TEXT,
built_area_size REAL,
built_up_ratio REAL,
completeness INTEGER,
geom GEOMETRY (MULTIPOLYGON, 4326)
);
CREATE TABLE taxonomy (
id serial PRIMARY KEY,
taxonomy_string TEXT
);
CREATE TABLE assessment (
id serial PRIMARY KEY,
entity_id INTEGER,
assessment_source_id INTEGER,
date TIMESTAMP,
comment TEXT
);
CREATE TABLE damage (
assessment_id INTEGER,
damage_state_id INTEGER,
damage_probability REAL
);
CREATE TABLE damagestate (
id serial PRIMARY KEY,
damage_scale_name TEXT,
damage_state_name TEXT,
UNIQUE(damage_scale_name, damage_state_name)
);
CREATE TABLE buildingloss (
assessment_id INTEGER,
loss_structural REAL
);
CREATE TABLE humanloss (
assessment_id INTEGER,
fatality_night REAL
);
CREATE TABLE assessmentsource (
id serial PRIMARY KEY,
name TEXT,
method TEXT,
comment TEXT,
UNIQUE(name)
);
CREATE TABLE entityreference (
id serial PRIMARY KEY,
quadkey TEXT,
country_iso_code TEXT,
geom GEOMETRY (MULTIPOLYGON, 4326),
UNIQUE(quadkey)
);
CREATE INDEX entityreference_quadkey_idx ON
entityreference(quadkey);
CREATE INDEX entityreference_countryisocode ON
entityreference(country_iso_code);
CREATE TABLE assetreference (
entity_id INTEGER,
taxonomy_id INTEGER,
number REAL,
structural REAL,
night REAL
);
CREATE INDEX assetreference_entityid_idx ON
assetreference(entity_id);
CREATE TABLE assetcountry (
country_iso_code TEXT,
taxonomy_id INTEGER,
number REAL,
number_normalized REAL,
structural REAL,
structural_normalized REAL,
night REAL,
night_normalized REAL
);
CREATE INDEX assetcountry_entityid_idx ON
assetcountry(country_iso_code);