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:59:30 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_energysurvey
(
    id INTEGER NOT NULL,
    biomasspotentialbioethanol NUMERIC,
    biomasspotentialbioethanolun VARCHAR(1000),
    biomasspotentialbiogas NUMERIC,
    biomasspotentialbiogasunit VARCHAR(1000),
    biomasspotentialforest NUMERIC,
    biomasspotentialforestunit VARCHAR(1000),
    biomasspotentialplantoil NUMERIC,
    biomasspotentialplantoilunit VARCHAR(1000),
    biomasspotentialresidualso_1 VARCHAR(1000),
    biomasspotentialresidualsoli NUMERIC,
    biomasspotentialsolidfuel NUMERIC,
    biomasspotentialsolidfueluni VARCHAR(1000),
    biomassprimaryenergypotent_1 VARCHAR(1000),
    biomassprimaryenergypotentia NUMERIC,
    biomasstoelectricitypotent_1 VARCHAR(1000),
    biomasstoelectricitypotentia NUMERIC,
    biomasstothermalpotential NUMERIC,
    biomasstothermalpotentialuni VARCHAR(1000),
    biomassweight NUMERIC,
    biomassweightunit VARCHAR(1000),
    electricitypotentialfrompv_1 VARCHAR(1000),
    electricitypotentialfrompvbu NUMERIC,
    landusesurve_energysurvey_id INTEGER,
    residentialelectricitydema_1 VARCHAR(1000),
    residentialelectricitydemand NUMERIC,
    spacecoolingdemand NUMERIC,
    spacecoolingdemandunit VARCHAR(1000),
    spaceheatingdemand NUMERIC,
    spaceheatingdemandunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_foodsurvey
(
    id INTEGER NOT NULL,
    animalcalorie INTEGER,
    animalcalorieunit VARCHAR(1000),
    cropcalorie INTEGER,
    cropcalorieunit VARCHAR(1000),
    foodcategory VARCHAR(1000),
    foodcategory_codespace VARCHAR(1000),
    foodconsumption NUMERIC,
    foodconsumptionunit VARCHAR(1000),
    fooddemand NUMERIC,
    fooddemandunit VARCHAR(1000),
    foodproduction NUMERIC,
    foodproductionenergydemand NUMERIC,
    foodproductionengerydemandun VARCHAR(1000),
    foodproductionunit VARCHAR(1000),
    foodproductionwaterdemand NUMERIC,
    foodproductionwaterdemanduni VARCHAR(1000),
    landusesurvey_foodsurvey_id INTEGER,
    vegetalcalorie INTEGER,
    vegetalcalorieunit VARCHAR(1000),
    PRIMARY KEY (id)
);

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

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_landusesurvey
(
    id INTEGER NOT NULL,
    buildingfootprintarea NUMERIC,
    buildingfootprintarea_uom VARCHAR(1000),
    croptype VARCHAR(1000),
    croptype_codespace VARCHAR(1000),
    land_use_consistsoffwesur_id INTEGER,
    objectclass_id INTEGER,
    population INTEGER,
    soiltype VARCHAR(1000),
    soiltype_codespace VARCHAR(1000),
    surfacearea NUMERIC,
    surfacearea_uom VARCHAR(1000),
    surveydescription VARCHAR(1000),
    surveyyear INTEGER,
    surveyyearversion NUMERIC,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_watersurvey
(
    id INTEGER NOT NULL,
    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),
    irrigationdemand NUMERIC,
    irrigationdemandunit VARCHAR(1000),
    landusesurvey_watersurvey_id INTEGER,
    totaldomesticwaste NUMERIC,
    totaldomesticwasteunit VARCHAR(1000),
    transpirationloss NUMERIC,
    transpirationlossunit VARCHAR(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_energysurvey ADD CONSTRAINT FWEL_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_energysurvey ADD CONSTRAINT FWEL_energy_landu_energ_fk FOREIGN KEY (landusesurve_energysurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_foodsurvey ADD CONSTRAINT FWEL_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_foodsurvey ADD CONSTRAINT FWEL_foodsu_landu_foods_fk FOREIGN KEY (landusesurvey_foodsurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- -------------------------------------------------------------------- 
-- FWEL_land_use 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_land_use ADD CONSTRAINT FWEL_land_use_fk FOREIGN KEY (id)
REFERENCES land_use (id);

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_landusesurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_landusesu_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_land_land_use_cons_fk FOREIGN KEY (land_use_consistsoffwesur_id)
REFERENCES FWEL_land_use (id);

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_watersurvey ADD CONSTRAINT FWEL_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_watersurvey ADD CONSTRAINT FWEL_waters_landu_water_fk FOREIGN KEY (landusesurvey_watersurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_energ_landu_energ_fkx ON FWEL_energysurvey
    USING btree
    (
      landusesurve_energysurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_foods_landu_foods_fkx ON FWEL_foodsurvey
    USING btree
    (
      landusesurvey_foodsurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_land_land_use_con_fkx ON FWEL_landusesurvey
    USING btree
    (
      land_use_consistsoffwesur_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

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

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_water_landu_water_fkx ON FWEL_watersurvey
    USING btree
    (
      landusesurvey_watersurvey_id ASC NULLS LAST
    )   WITH (FILLFACTOR = 90);

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