CREATE_ADE_DB.sql

-- This document was automatically created by the ADE-Manager tool of 3DCityDB (https://www.3dcitydb.org) on 2021-03-29 17:57:18 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_building 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_building
(
    id INTEGER NOT NULL,
    fweareaname VARCHAR(1000),
    fwesystemname VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_buildingsurvey
(
    id INTEGER NOT NULL,
    building_consistsoffwesur_id INTEGER,
    buildingfootprintarea NUMERIC,
    buildingfootprintarea_uom VARCHAR(1000),
    objectclass_id INTEGER,
    occupants INTEGER,
    surveydescription VARCHAR(1000),
    surveyyear INTEGER,
    surveyyearversion NUMERIC,
    totalfloorarea NUMERIC,
    totalfloorarea_uom VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_energysurvey
(
    id INTEGER NOT NULL,
    buildingsurv_energysurvey_id INTEGER,
    electricitypotentialfrompv NUMERIC,
    electricitypotentialfrompvun VARCHAR(1000),
    residentialelectricitydema_1 VARCHAR(1000),
    residentialelectricitydemand NUMERIC,
    spacecoolingdemand NUMERIC,
    spacecoolingdemandunit VARCHAR(1000),
    spaceheatingdemand NUMERIC,
    spaceheatingdemandunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_foodsurvey
(
    id INTEGER NOT NULL,
    buildingsurvey_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)
);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_watersurvey
(
    id INTEGER NOT NULL,
    buildingsurve_watersurvey_id INTEGER,
    domestichotwaterdemand NUMERIC,
    domestichotwaterdemandunit VARCHAR(1000),
    domestichotwaterenergydema_1 VARCHAR(1000),
    domestichotwaterenergydemand NUMERIC,
    domesticsolidwaste NUMERIC,
    domesticsolidwasteunit VARCHAR(1000),
    domesticwaterdemand NUMERIC,
    domesticwaterdemandunit VARCHAR(1000),
    domesticwaterwaste NUMERIC,
    domesticwaterwasteunit VARCHAR(1000),
    totaldomesticwaste NUMERIC,
    totaldomesticwasteunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_building 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_building ADD CONSTRAINT FWEB_building_fk FOREIGN KEY (id)
REFERENCES building (id);

-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildingsurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildings_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildi_build_consi_fk FOREIGN KEY (building_consistsoffwesur_id)
REFERENCES FWEB_building (id);

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_energysurvey ADD CONSTRAINT FWEB_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_energysurvey ADD CONSTRAINT FWEB_energy_build_energ_fk FOREIGN KEY (buildingsurv_energysurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_foodsurvey ADD CONSTRAINT FWEB_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_foodsurvey ADD CONSTRAINT FWEB_foodsu_build_foods_fk FOREIGN KEY (buildingsurvey_foodsurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_watersurvey ADD CONSTRAINT FWEB_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_watersurvey ADD CONSTRAINT FWEB_waters_build_water_fk FOREIGN KEY (buildingsurve_watersurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_build_build_consi_fkx ON FWEB_buildingsurvey
    USING btree
    (
      building_consistsoffwesur_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

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

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_energ_build_energ_fkx ON FWEB_energysurvey
    USING btree
    (
      buildingsurv_energysurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_foods_build_foods_fkx ON FWEB_foodsurvey
    USING btree
    (
      buildingsurvey_foodsurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_water_build_water_fkx ON FWEB_watersurvey
    USING btree
    (
      buildingsurve_watersurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

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