Calculating Risk Factors for Waste Management System Using SQL Database

Question

I am working on a waste management system where I need to prioritize actions based on risk factors associated with different types of waste, including industrial and sewage waste. The system utilizes a SQL database to store information about contaminated objects, such as their age, position, and type of contamination.

Here is an overview of the database schema:

-- Contaminated objects table
CREATE TABLE ContaminatedObject (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    position POINT,
    extension REAL
);

-- Sewage contamination table
CREATE TABLE SewageContamination (
    id INTEGER PRIMARY KEY,
    contaminatedObjectId INTEGER,
    sewageType TEXT,
    sewageVolume FLOAT,
    owner TEXT,
    FOREIGN KEY (contaminatedObjectId) REFERENCES ContaminatedObject(id)
);

-- Industrial contamination table
CREATE TABLE IndustrialContamination (
    id INTEGER PRIMARY KEY,
    contaminatedObjectId INTEGER,
    industrialWasteType TEXT,
    industrialWasteVolume TEXT,
    owner TEXT,
    FOREIGN KEY (contaminatedObjectId) REFERENCES ContaminatedObject(id)
);

-- Issues table
CREATE TABLE Issue (
    id INTEGER PRIMARY KEY,
    objectId INTEGER,
    issueType TEXT,
    date TIMESTAMP,
    documentation TEXT,
    FOREIGN KEY (objectId) REFERENCES ContaminatedObject(id)
);

-- Historical records table for documentation
CREATE TABLE HistoricalRecord (
    id INTEGER PRIMARY KEY,
    objectId INTEGER,
    action TEXT,
    timestamp TIMESTAMP,
    FOREIGN KEY (objectId) REFERENCES ContaminatedObject(id)
);

-- City bodies table
CREATE TABLE City (
    id INTEGER PRIMARY KEY,
    cityName TEXT,
    position POINT
);

-- Water bodies table
CREATE TABLE WaterBody (
    id INTEGER PRIMARY KEY,
    waterBodyname TEXT,
    position POINT
);

Here are the values that i have inserted into the tables:

-- Insert sample data into the ContaminatedObject table
INSERT INTO ContaminatedObject (name, age, position, extension) VALUES
    ('Object 1', 20, ST_GeomFromText('POINT(10 20)'), 50.5),
    ('Object 2', 30, ST_GeomFromText('POINT(30 40)'), 60.7),
    ('Object 3', 25, ST_GeomFromText('POINT(50 60)'), 70.2);

-- Insert sample data into the SewageContamination table
INSERT INTO SewageContamination (contaminatedObjectId, sewageType, sewageVolume, owner) VALUES
    (1, 'chromium', 100.5, 'Owner A'),
    (2, 'arsenic', 200.7, 'Owner B');

-- Insert sample data into the IndustrialContamination table
INSERT INTO IndustrialContamination (contaminatedObjectId, industrialWasteType, industrialWasteVolume, owner) VALUES
    (1, 'arsenic', 145, 'Owner C'),
    (3, 'arsenic', 105, 'Owner D');

-- Insert sample data into the City table
INSERT INTO City (cityName, position) VALUES
    ('City 1', ST_GeomFromText('POINT(15 25)'));

-- Insert sample data into the WaterBody table
INSERT INTO WaterBody (waterBodyname, position) VALUES
    ('Lake 1', ST_GeomFromText('POINT(20 30)')),
    ('Lake 2', ST_GeomFromText('POINT(40 50)'));

The criteria for prioritizing actions include:

1. Age Criterion: Facilities older than 40 years are prioritized for inspection.
2. Proximity to Water Bodies: Facilities within 250 meters of a water body are prioritized due to the higher risk of contamination affecting the water body.
3. Classification Schema: The prioritization considers factors such as the type and volume of contamination, proximity to urban areas, and other relevant considerations.

To calculate the risk factors, I have attempted to create a view named "RiskReport" using SQL. Here is the SQL code snippet:

CREATE VIEW RiskReport AS 
SELECT 
    co.id AS objectId, 
    co.name, 
    co.age, 
    ST_AsText(co.position) AS position_text, 
    co.extension, 
    CASE 
        WHEN sewageRiskScore = 30 AND sewageRiskScore = 30 AND industrialRiskScore 40 THEN 20 
        ELSE 0 
    END AS ageFactor 
FROM 
    ContaminatedObject co 
INNER JOIN 
    SewageContamination sc ON co.id = sc.contaminatedObjectId 
LEFT JOIN 
    (SELECT 
        sc.contaminatedObjectId, 
        CASE 
            WHEN sewageType = 'arsenic' THEN 30 
            WHEN sewageType = 'chromium' THEN 25 
            ELSE 0 
        END AS sewageContaminationFactor 
    FROM 
        SewageContamination sc 
    ) AS contamination ON sc.contaminatedObjectId = contamination.contaminatedObjectId 
LEFT JOIN 
    (SELECT 
        sc.contaminatedObjectId, 
        CASE 
            WHEN sewageVolume > 100 THEN 15 
            ELSE 0 
        END AS volumeFactor 
    FROM 
        SewageContamination sc 
    ) AS volume ON sc.contaminatedObjectId = volume.contaminatedObjectId 
) AS sewageRiskFactors ON co.id = sewageRiskFactors.contaminatedObjectId 
LEFT JOIN 
    (SELECT 
        ic.contaminatedObjectId, 
        (ageFactor + industrialContaminationFactor + volumeFactor) AS industrialRiskScore 
    FROM 
        IndustrialContamination ic 
    LEFT JOIN 
        (SELECT 
            ic.contaminatedObjectId, 
            CASE 
                WHEN age > 40 THEN 20 
                ELSE 0 
            END AS ageFactor 
        FROM 
            ContaminatedObject co 
        INNER JOIN 
            IndustrialContamination ic ON co.id = ic.contaminatedObjectId 
        ) AS age ON ic.contaminatedObjectId = age.contaminatedObjectId 
    LEFT JOIN 
        (SELECT 
            ic.contaminatedObjectId, 
            CASE 
                WHEN industrialWasteType = 'arsenic' THEN 30 
                WHEN industrialWasteType = 'chromium' THEN 25 
                ELSE 0 
            END AS industrialContaminationFactor 
        FROM 
            IndustrialContamination ic 
        ) AS contamination ON ic.contaminatedObjectId = contamination.contaminatedObjectId 
    LEFT JOIN 
        (SELECT 
            ic.contaminatedObjectId, 
            CASE 
                WHEN industrialWasteVolume > 100 THEN 15 
                ELSE 0 
            END AS volumeFactor 
        FROM 
            IndustrialContamination ic 
        ) AS volume ON ic.contaminatedObjectId = volume.contaminatedObjectId 
    ) AS industrialRiskFactors ON co.id = industrialRiskFactors.contaminatedObjectId;

However, I am encountering difficulties with this approach, and the view is not producing the desired results. Specifically, I am struggling to properly incorporate the criteria mentioned above into the calculation of risk scores for sewage and industrial waste, especially criteria 2.

When i run select * from RiskReport this is what I get:

objectId	name	age	position_text	extension	sewageRiskLevel	industrialRiskLevel
1	Object 1	20	POINT(10 20)	50.5	Low Risk	High Risk
2	Object 2	30	POINT(30 40)	60.7	Low Risk	High Risk
3	Object 3	25	POINT(50 60)	70.2	High Risk	Low Risk
4	Object 4	11	POINT(40 71)	48.2	High Risk	Low Risk

What I want is to get a more accurate reading on the risk, at the moment my code doesn't do such a good calculation and I'm unsure if it even calculates the positions correctly, given the criteria.

What I want is something like this:

objectId	name	age	position_text	extension	nearWaterRisk	nearCityRisk	distanceToWater	distanceToCity
1	Object 1	20	POINT(10 20)	50.5	Low Risk	High Risk	-	-
2	Object 2	30	POINT(30 40)	60.7	Low Risk	High Risk	-	-
3	Object 3	25	POINT(50 60)	70.2	High Risk	Low Risk	-	-
4	Object 4	11	POINT(40 71)	48.2	High Risk	Low Risk	-	-

Where distanceToWater and distanceToCity, tells me how close said waste is to the city and which has a highest risk to being a problem. Where water takes priority over the cities.

I am seeking assistance in refining the SQL code to accurately calculate the risk factors based on the specified criteria. Additionally, I am open to suggestions for alternative approaches to achieve this task effectively within the SQL database framework.

Any guidance or insights would be greatly appreciated. Thank you in advance for your assistance.