CREATE_ADE_DB.sql

-- This document was automatically created by the ADE-Manager tool of 3DCityDB (https://www.3dcitydb.org) on 2021-01-10 14:56:23 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEA_areasurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_areasurvey
(
    id INTEGER NOT NULL,
    buildingfootrpintarea NUMERIC,
    buildingfootrpintarea_uom VARCHAR(1000),
    fwearea_consistsoffwesurv_id INTEGER,
    objectclass_id INTEGER,
    openplotarea NUMERIC,
    openplotarea_uom VARCHAR(1000),
    population INTEGER,
    settlementarea NUMERIC,
    settlementarea_uom VARCHAR(1000),
    surfacearea NUMERIC,
    surfacearea_uom VARCHAR(1000),
    surveydescription VARCHAR(1000),
    surveyyear INTEGER,
    surveyyearversion NUMERIC,
    trafficarea NUMERIC,
    trafficarea_uom VARCHAR(1000),
    vegetationarea NUMERIC,
    vegetationarea_uom VARCHAR(1000),
    waterbodyarea NUMERIC,
    waterbodyarea_uom VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEA_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_energysurvey
(
    id INTEGER NOT NULL,
    areasurvey_energysurvey_id INTEGER,
    biomasselectricitypotential NUMERIC,
    biomasselectricitypotentialu VARCHAR(1000),
    biomassprimaryenergypotent_1 VARCHAR(1000),
    biomassprimaryenergypotentia NUMERIC,
    biomassthermalpotential NUMERIC,
    biomassthermalpotentialunit VARCHAR(1000),
    electricitypotentialfrompv_1 VARCHAR(1000),
    electricitypotentialfrompvbu NUMERIC,
    residentialelectricitydema_1 VARCHAR(1000),
    residentialelectricitydemand NUMERIC,
    spacecoolingdemand NUMERIC,
    spacecoolingdemandunit VARCHAR(1000),
    spaceheatingdemand NUMERIC,
    spaceheatingdemandunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEA_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_foodsurvey
(
    id INTEGER NOT NULL,
    areasurvey_foodsurvey_id INTEGER,
    foodcategory VARCHAR(1000),
    foodcategory_codespace VARCHAR(1000),
    foodconsumption NUMERIC,
    foodconsumptionunit VARCHAR(1000),
    fooddemand NUMERIC,
    fooddemandunit VARCHAR(1000),
    foodproduction NUMERIC,
    foodproductionenergydemand NUMERIC,
    foodproductionenergydemandun VARCHAR(1000),
    foodproductionunit VARCHAR(1000),
    foodproductionwaterdemand NUMERIC,
    foodproductionwaterdemanduni VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEA_fwearea 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_fwearea
(
    id INTEGER NOT NULL,
    fweareaname VARCHAR(1000),
    fwesystemname VARCHAR(1000),
    lod0multisurface_id INTEGER,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEA_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_watersurvey
(
    id INTEGER NOT NULL,
    areasurvey_watersurvey_id INTEGER,
    domestichotwaterdemand NUMERIC,
    domestichotwaterdemandunit VARCHAR(1000),
    domesticsolidwaste NUMERIC,
    domesticsolidwasteunit VARCHAR(1000),
    domesticwaterdemand NUMERIC,
    domesticwaterdemandunit VARCHAR(1000),
    domesticwaterwaste NUMERIC,
    domesticwaterwasteunit VARCHAR(1000),
    totaldomesticwaste NUMERIC,
    totaldomesticwasteenergypo_1 VARCHAR(1000),
    totaldomesticwasteenergypote NUMERIC,
    totaldomesticwasteunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEA_areasurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEA_areasurvey ADD CONSTRAINT FWEA_areasurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEA_areasurvey ADD CONSTRAINT FWEA_areasurve_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWEA_areasurvey ADD CONSTRAINT FWEA_areasu_fwear_consi_fk FOREIGN KEY (fwearea_consistsoffwesurv_id)
REFERENCES FWEA_fwearea (id)
ON DELETE SET NULL;

-- -------------------------------------------------------------------- 
-- FWEA_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEA_energysurvey ADD CONSTRAINT FWEA_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWEA_areasurvey (id);

ALTER TABLE FWEA_energysurvey ADD CONSTRAINT FWEA_energy_areas_energ_fk FOREIGN KEY (areasurvey_energysurvey_id)
REFERENCES FWEA_areasurvey (id);

-- -------------------------------------------------------------------- 
-- FWEA_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEA_foodsurvey ADD CONSTRAINT FWEA_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWEA_areasurvey (id);

ALTER TABLE FWEA_foodsurvey ADD CONSTRAINT FWEA_foodsu_areas_foods_fk FOREIGN KEY (areasurvey_foodsurvey_id)
REFERENCES FWEA_areasurvey (id);

-- -------------------------------------------------------------------- 
-- FWEA_fwearea 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEA_fwearea ADD CONSTRAINT FWEA_fwearea_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEA_fwearea ADD CONSTRAINT FWEA_fwearea_lod0multis_fk FOREIGN KEY (lod0multisurface_id)
REFERENCES surface_geometry (id);

-- -------------------------------------------------------------------- 
-- FWEA_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEA_watersurvey ADD CONSTRAINT FWEA_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWEA_areasurvey (id);

ALTER TABLE FWEA_watersurvey ADD CONSTRAINT FWEA_waters_areas_water_fk FOREIGN KEY (areasurvey_watersurvey_id)
REFERENCES FWEA_areasurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEA_areasurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_areas_fwear_consi_fkx ON FWEA_areasurvey
    USING btree
    (
      fwearea_consistsoffwesurv_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

CREATE INDEX FWEA_areasurv_objectcl_fkx ON FWEA_areasurvey
    USING btree
    (
      objectclass_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEA_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_energ_areas_energ_fkx ON FWEA_energysurvey
    USING btree
    (
      areasurvey_energysurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEA_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_foods_areas_foods_fkx ON FWEA_foodsurvey
    USING btree
    (
      areasurvey_foodsurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEA_fwearea 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_fwearea_lod0multi_fkx ON FWEA_fwearea
    USING btree
    (
      lod0multisurface_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEA_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_water_areas_water_fkx ON FWEA_watersurvey
    USING btree
    (
      areasurvey_watersurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Sequences *********************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++