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 NUMBER,
    buildingfootrpintarea_uom VARCHAR2(1000),
    fwearea_consistsoffwesurv_id INTEGER,
    objectclass_id INTEGER,
    openplotarea NUMBER,
    openplotarea_uom VARCHAR2(1000),
    population INTEGER,
    settlementarea NUMBER,
    settlementarea_uom VARCHAR2(1000),
    surfacearea NUMBER,
    surfacearea_uom VARCHAR2(1000),
    surveydescription VARCHAR2(1000),
    surveyyear INTEGER,
    surveyyearversion NUMBER,
    trafficarea NUMBER,
    trafficarea_uom VARCHAR2(1000),
    vegetationarea NUMBER,
    vegetationarea_uom VARCHAR2(1000),
    waterbodyarea NUMBER,
    waterbodyarea_uom VARCHAR2(1000),
    PRIMARY KEY (id)
);

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

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

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

-- -------------------------------------------------------------------- 
-- FWEA_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEA_watersurvey
(
    id INTEGER NOT NULL,
    areasurvey_watersurvey_id INTEGER,
    domestichotwaterdemand NUMBER,
    domestichotwaterdemandunit VARCHAR2(1000),
    domesticsolidwaste NUMBER,
    domesticsolidwasteunit VARCHAR2(1000),
    domesticwaterdemand NUMBER,
    domesticwaterdemandunit VARCHAR2(1000),
    domesticwaterwaste NUMBER,
    domesticwaterwasteunit VARCHAR2(1000),
    totaldomesticwaste NUMBER,
    totaldomesticwasteenergypo_1 VARCHAR2(1000),
    totaldomesticwasteenergypote NUMBER,
    totaldomesticwasteunit VARCHAR2(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 (fwearea_consistsoffwesurv_id);

CREATE INDEX FWEA_areasurv_objectcl_fkx ON FWEA_areasurvey (objectclass_id);

-- -------------------------------------------------------------------- 
-- FWEA_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_energ_areas_energ_fkx ON FWEA_energysurvey (areasurvey_energysurvey_id);

-- -------------------------------------------------------------------- 
-- FWEA_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_foods_areas_foods_fkx ON FWEA_foodsurvey (areasurvey_foodsurvey_id);

-- -------------------------------------------------------------------- 
-- FWEA_fwearea 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_fwearea_lod0multi_fkx ON FWEA_fwearea (lod0multisurface_id);

-- -------------------------------------------------------------------- 
-- FWEA_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEA_water_areas_water_fkx ON FWEA_watersurvey (areasurvey_watersurvey_id);

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