312 lines
24 KiB
SQL
312 lines
24 KiB
SQL
-- Database Structure and Sample Data for Total Particulate Measurement
|
||
-- Based on PMK RI No.2 Tahun 2023
|
||
|
||
-- ------------------------------------------------------------------------------
|
||
-- Master Tables - Particulate Specific
|
||
-- ------------------------------------------------------------------------------
|
||
|
||
-- Master Area Types
|
||
CREATE TABLE IF NOT EXISTS master_area_types (
|
||
MasterAreaTypeID INT PRIMARY KEY,
|
||
MasterAreaTypeName VARCHAR(100) NOT NULL,
|
||
MasterAreaTypeDescription TEXT,
|
||
MasterAreaTypeIsActive BOOLEAN DEFAULT TRUE,
|
||
MasterAreaTypeCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
MasterAreaTypeCreatedUserID INT,
|
||
MasterAreaTypeUpdatedAt DATETIME,
|
||
MasterAreaTypeUpdatedUserID INT,
|
||
MasterAreaTypeDeletedAt DATETIME,
|
||
MasterAreaTypeDeletedUserID INT
|
||
);
|
||
|
||
-- Master Particulate Standards
|
||
CREATE TABLE IF NOT EXISTS master_particulate_standards (
|
||
MasterParticulateStandardID INT PRIMARY KEY,
|
||
MasterRegulationID INT NOT NULL,
|
||
MasterAreaTypeID INT NOT NULL,
|
||
MasterParticulateStandardParameterCode VARCHAR(20) NOT NULL, -- TSP, PM10, PM2.5
|
||
MasterParticulateStandardValue DECIMAL(7,2) NOT NULL,
|
||
MasterParticulateStandardUnit VARCHAR(20) DEFAULT 'μg/Nm³',
|
||
MasterParticulateStandardAveragingTime VARCHAR(50),
|
||
MasterParticulateStandardDescription TEXT,
|
||
MasterParticulateStandardIsActive BOOLEAN DEFAULT TRUE,
|
||
MasterParticulateStandardCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
MasterParticulateStandardCreatedUserID INT,
|
||
MasterParticulateStandardUpdatedAt DATETIME,
|
||
MasterParticulateStandardUpdatedUserID INT,
|
||
MasterParticulateStandardDeletedAt DATETIME,
|
||
MasterParticulateStandardDeletedUserID INT
|
||
);
|
||
|
||
-- Master Particulate Measurement Methods
|
||
CREATE TABLE IF NOT EXISTS master_particulate_methods (
|
||
MasterParticulateMethodID INT PRIMARY KEY,
|
||
MasterParticulateMethodCode VARCHAR(50) NOT NULL,
|
||
MasterParticulateMethodName VARCHAR(255) NOT NULL,
|
||
MasterParticulateMethodParameterCode VARCHAR(20) NOT NULL, -- TSP, PM10, PM2.5
|
||
MasterParticulateMethodDescription TEXT,
|
||
MasterParticulateMethodStandardReference VARCHAR(100),
|
||
MasterParticulateMethodIsActive BOOLEAN DEFAULT TRUE,
|
||
MasterParticulateMethodCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
MasterParticulateMethodCreatedUserID INT,
|
||
MasterParticulateMethodUpdatedAt DATETIME,
|
||
MasterParticulateMethodUpdatedUserID INT,
|
||
MasterParticulateMethodDeletedAt DATETIME,
|
||
MasterParticulateMethodDeletedUserID INT
|
||
);
|
||
|
||
-- Master Particulate Measurement Equipment
|
||
CREATE TABLE IF NOT EXISTS master_particulate_equipment (
|
||
MasterParticulateEquipmentID INT PRIMARY KEY,
|
||
MasterParticulateEquipmentCode VARCHAR(20) NOT NULL,
|
||
MasterParticulateEquipmentName VARCHAR(100) NOT NULL,
|
||
MasterParticulateEquipmentType VARCHAR(50),
|
||
MasterParticulateEquipmentBrand VARCHAR(100),
|
||
MasterParticulateEquipmentModel VARCHAR(100),
|
||
MasterParticulateEquipmentSerialNumber VARCHAR(100),
|
||
MasterParticulateEquipmentParameterMeasured VARCHAR(100), -- TSP, PM10, PM2.5, Multi-parameter
|
||
MasterParticulateEquipmentSpecifications TEXT,
|
||
MasterParticulateEquipmentFlowRate VARCHAR(50),
|
||
MasterParticulateEquipmentFilterType VARCHAR(100),
|
||
MasterParticulateEquipmentCalibrationDate DATE,
|
||
MasterParticulateEquipmentNextCalibrationDate DATE,
|
||
MasterParticulateEquipmentCalibrationStatus VARCHAR(20),
|
||
MasterParticulateEquipmentCertificateFile VARCHAR(255),
|
||
MasterParticulateEquipmentIsActive BOOLEAN DEFAULT TRUE,
|
||
MasterParticulateEquipmentCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
MasterParticulateEquipmentCreatedUserID INT,
|
||
MasterParticulateEquipmentUpdatedAt DATETIME,
|
||
MasterParticulateEquipmentUpdatedUserID INT,
|
||
MasterParticulateEquipmentDeletedAt DATETIME,
|
||
MasterParticulateEquipmentDeletedUserID INT
|
||
);
|
||
|
||
-- ------------------------------------------------------------------------------
|
||
-- Transaction Tables - Particulate Specific
|
||
-- ------------------------------------------------------------------------------
|
||
|
||
-- Particulate Sampling Plan
|
||
CREATE TABLE IF NOT EXISTS particulate_sampling_plans (
|
||
ParticulateSamplingPlanID INT PRIMARY KEY,
|
||
ParticulateSamplingPlanCode VARCHAR(50) NOT NULL,
|
||
ParticulateSamplingPlanProjectName VARCHAR(255) NOT NULL,
|
||
ClientID INT NOT NULL,
|
||
ParticulateSamplingPlanDate DATE NOT NULL,
|
||
ParticulateSamplingPlanLocation TEXT NOT NULL,
|
||
ParticulateSamplingPlanPointCount INT,
|
||
ParticulateSamplingPlanParameters VARCHAR(100), -- Comma separated list: TSP, PM10, PM2.5
|
||
ParticulateSamplingPlanStatus VARCHAR(20) DEFAULT 'DRAFT',
|
||
ParticulateSamplingPlanNotes TEXT,
|
||
ParticulateSamplingPlanCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
ParticulateSamplingPlanCreatedUserID INT,
|
||
ParticulateSamplingPlanUpdatedAt DATETIME,
|
||
ParticulateSamplingPlanUpdatedUserID INT,
|
||
ParticulateSamplingPlanDeletedAt DATETIME,
|
||
ParticulateSamplingPlanDeletedUserID INT
|
||
);
|
||
|
||
-- Particulate Measurement Results - Header
|
||
CREATE TABLE IF NOT EXISTS particulate_measurements (
|
||
ParticulateMeasurementID INT PRIMARY KEY,
|
||
ParticulateSamplingPlanID INT NOT NULL,
|
||
ParticulateMeasurementCode VARCHAR(50) NOT NULL,
|
||
ParticulateMeasurementDate DATE NOT NULL,
|
||
ParticulateMeasurementStartTime TIME,
|
||
ParticulateMeasurementEndTime TIME,
|
||
ParticulateMeasurementWeather VARCHAR(100),
|
||
ParticulateMeasurementTemperature DECIMAL(5,2),
|
||
ParticulateMeasurementHumidity DECIMAL(5,2),
|
||
ParticulateMeasurementWindSpeed DECIMAL(5,2),
|
||
ParticulateMeasurementWindDirection VARCHAR(10),
|
||
ParticulateMeasurementPressure DECIMAL(7,2),
|
||
ParticulateMeasurementOfficers VARCHAR(100),
|
||
ParticulateMeasurementStatus VARCHAR(20) DEFAULT 'DRAFT',
|
||
ParticulateMeasurementNotes TEXT,
|
||
ParticulateMeasurementCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
ParticulateMeasurementCreatedUserID INT,
|
||
ParticulateMeasurementUpdatedAt DATETIME,
|
||
ParticulateMeasurementUpdatedUserID INT,
|
||
ParticulateMeasurementDeletedAt DATETIME,
|
||
ParticulateMeasurementDeletedUserID INT
|
||
);
|
||
|
||
-- Particulate Measurement Details
|
||
CREATE TABLE IF NOT EXISTS particulate_measurement_details (
|
||
ParticulateMeasurementDetailID INT PRIMARY KEY,
|
||
ParticulateMeasurementID INT NOT NULL,
|
||
ParticulateMeasurementDetailPointCode VARCHAR(20) NOT NULL,
|
||
ParticulateMeasurementDetailLocation VARCHAR(255) NOT NULL,
|
||
ParticulateMeasurementDetailAreaDescription TEXT,
|
||
MasterAreaTypeID INT NOT NULL,
|
||
ParticulateMeasurementDetailLatitude DECIMAL(10,6),
|
||
ParticulateMeasurementDetailLongitude DECIMAL(10,6),
|
||
ParticulateMeasurementDetailHeight DECIMAL(4,2),
|
||
ParticulateMeasurementDetailDuration DECIMAL(5,2),
|
||
ParticulateMeasurementDetailParameterCode VARCHAR(20) NOT NULL, -- TSP, PM10, PM2.5
|
||
MasterParticulateMethodID INT NOT NULL,
|
||
MasterParticulateEquipmentID INT NOT NULL,
|
||
ParticulateMeasurementDetailFilterID VARCHAR(50),
|
||
ParticulateMeasurementDetailInitialWeight DECIMAL(10,5),
|
||
ParticulateMeasurementDetailFinalWeight DECIMAL(10,5),
|
||
ParticulateMeasurementDetailFlowRate DECIMAL(7,2),
|
||
ParticulateMeasurementDetailVolume DECIMAL(10,2),
|
||
ParticulateMeasurementDetailResult DECIMAL(8,2) NOT NULL,
|
||
MasterParticulateStandardID INT NOT NULL,
|
||
ParticulateMeasurementDetailComplianceStatus VARCHAR(20),
|
||
ParticulateMeasurementDetailSources TEXT,
|
||
ParticulateMeasurementDetailRecommendations TEXT,
|
||
ParticulateMeasurementDetailNotes TEXT,
|
||
ParticulateMeasurementDetailCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
ParticulateMeasurementDetailCreatedUserID INT,
|
||
ParticulateMeasurementDetailUpdatedAt DATETIME,
|
||
ParticulateMeasurementDetailUpdatedUserID INT,
|
||
ParticulateMeasurementDetailDeletedAt DATETIME,
|
||
ParticulateMeasurementDetailDeletedUserID INT
|
||
);
|
||
|
||
-- Dust Control Recommendations
|
||
CREATE TABLE IF NOT EXISTS dust_control_recommendations (
|
||
DustControlRecommendationID INT PRIMARY KEY,
|
||
ParticulateMeasurementDetailID INT NOT NULL,
|
||
DustControlRecommendationType VARCHAR(50) NOT NULL, -- Engineering, Administrative, PPE
|
||
DustControlRecommendationDescription TEXT NOT NULL,
|
||
DustControlRecommendationPriority INT,
|
||
DustControlRecommendationEstimatedCost DECIMAL(12,2),
|
||
DustControlRecommendationEstimatedReduction DECIMAL(5,1),
|
||
DustControlRecommendationStatus VARCHAR(20) DEFAULT 'PLANNED',
|
||
DustControlRecommendationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
DustControlRecommendationCreatedUserID INT,
|
||
DustControlRecommendationUpdatedAt DATETIME,
|
||
DustControlRecommendationUpdatedUserID INT,
|
||
DustControlRecommendationDeletedAt DATETIME,
|
||
DustControlRecommendationDeletedUserID INT
|
||
);
|
||
|
||
-- ------------------------------------------------------------------------------
|
||
-- Sample Data Insertion - Master Tables
|
||
-- ------------------------------------------------------------------------------
|
||
|
||
-- Insert Data Master Area Types
|
||
INSERT INTO master_area_types (MasterAreaTypeID, MasterAreaTypeName, MasterAreaTypeDescription, MasterAreaTypeIsActive, MasterAreaTypeCreatedUserID)
|
||
VALUES
|
||
(1, 'Industrial Areas', 'Manufacturing and industrial activity areas', TRUE, 1),
|
||
(2, 'Residential Areas', 'Residential housing and settlements', TRUE, 1),
|
||
(3, 'Office and Commercial Areas', 'Office buildings and commercial areas', TRUE, 1),
|
||
(4, 'Parks and Green Areas', 'Public parks and green spaces', TRUE, 1),
|
||
(5, 'Sensitive Areas', 'Hospitals, schools, and other sensitive locations', TRUE, 1);
|
||
|
||
-- Insert Data Master Particulate Standards
|
||
INSERT INTO master_particulate_standards (MasterParticulateStandardID, MasterRegulationID, MasterAreaTypeID, MasterParticulateStandardParameterCode, MasterParticulateStandardValue, MasterParticulateStandardUnit, MasterParticulateStandardAveragingTime, MasterParticulateStandardDescription, MasterParticulateStandardIsActive, MasterParticulateStandardCreatedUserID)
|
||
VALUES
|
||
-- TSP/Total Particulate Matter Standards
|
||
(1, 1, 1, 'TSP', 230.00, 'μg/Nm³', '24 hours', 'Industrial areas TSP standard', TRUE, 1),
|
||
(2, 1, 2, 'TSP', 90.00, 'μg/Nm³', '24 hours', 'Residential areas TSP standard', TRUE, 1),
|
||
(3, 1, 3, 'TSP', 90.00, 'μg/Nm³', '24 hours', 'Office and commercial areas TSP standard', TRUE, 1),
|
||
(4, 1, 4, 'TSP', 90.00, 'μg/Nm³', '24 hours', 'Parks and green areas TSP standard', TRUE, 1),
|
||
(5, 1, 5, 'TSP', 90.00, 'μg/Nm³', '24 hours', 'Sensitive areas TSP standard', TRUE, 1),
|
||
|
||
-- PM10 Standards
|
||
(6, 1, 1, 'PM10', 150.00, 'μg/Nm³', '24 hours', 'Industrial areas PM10 standard', TRUE, 1),
|
||
(7, 1, 2, 'PM10', 75.00, 'μg/Nm³', '24 hours', 'Residential areas PM10 standard', TRUE, 1),
|
||
(8, 1, 3, 'PM10', 75.00, 'μg/Nm³', '24 hours', 'Office and commercial areas PM10 standard', TRUE, 1),
|
||
(9, 1, 4, 'PM10', 75.00, 'μg/Nm³', '24 hours', 'Parks and green areas PM10 standard', TRUE, 1),
|
||
(10, 1, 5, 'PM10', 75.00, 'μg/Nm³', '24 hours', 'Sensitive areas PM10 standard', TRUE, 1),
|
||
|
||
-- PM2.5 Standards
|
||
(11, 1, 1, 'PM2.5', 55.00, 'μg/Nm³', '24 hours', 'Industrial areas PM2.5 standard', TRUE, 1),
|
||
(12, 1, 2, 'PM2.5', 55.00, 'μg/Nm³', '24 hours', 'Residential areas PM2.5 standard', TRUE, 1),
|
||
(13, 1, 3, 'PM2.5', 55.00, 'μg/Nm³', '24 hours', 'Office and commercial areas PM2.5 standard', TRUE, 1),
|
||
(14, 1, 4, 'PM2.5', 55.00, 'μg/Nm³', '24 hours', 'Parks and green areas PM2.5 standard', TRUE, 1),
|
||
(15, 1, 5, 'PM2.5', 55.00, 'μg/Nm³', '24 hours', 'Sensitive areas PM2.5 standard', TRUE, 1);
|
||
|
||
-- Insert Data Master Particulate Measurement Methods
|
||
INSERT INTO master_particulate_methods (MasterParticulateMethodID, MasterParticulateMethodCode, MasterParticulateMethodName, MasterParticulateMethodParameterCode, MasterParticulateMethodDescription, MasterParticulateMethodStandardReference, MasterParticulateMethodIsActive, MasterParticulateMethodCreatedUserID)
|
||
VALUES
|
||
(1, 'SNI-7119.3-2005', 'High Volume Method for TSP', 'TSP', 'Gravimetric method for TSP using high volume sampler', 'SNI 19-7119.3-2005', TRUE, 1),
|
||
(2, 'SNI-7119.15-2016', 'High Volume Method for PM10', 'PM10', 'Gravimetric method for PM10 using high volume sampler with PM10 inlet', 'SNI 19-7119.15-2016', TRUE, 1),
|
||
(3, 'SNI-7119.14-2016', 'Gravimetric Method for PM2.5', 'PM2.5', 'Gravimetric method for PM2.5 using low volume sampler with PM2.5 inlet', 'SNI 19-7119.14-2016', TRUE, 1),
|
||
(4, 'US-EPA-IO-2.1', 'EPA Method IO-2.1 for TSP', 'TSP', 'EPA Method for sampling TSP using high volume samplers', 'US EPA IO-2.1', TRUE, 1),
|
||
(5, 'US-EPA-IO-3.1', 'EPA Method IO-3.1 for PM10', 'PM10', 'EPA Method for PM10 sampling', 'US EPA IO-3.1', TRUE, 1);
|
||
|
||
-- Insert Data Master Particulate Measurement Equipment
|
||
INSERT INTO master_particulate_equipment (MasterParticulateEquipmentID, MasterParticulateEquipmentCode, MasterParticulateEquipmentName, MasterParticulateEquipmentType, MasterParticulateEquipmentBrand, MasterParticulateEquipmentModel, MasterParticulateEquipmentSerialNumber, MasterParticulateEquipmentParameterMeasured, MasterParticulateEquipmentSpecifications, MasterParticulateEquipmentFlowRate, MasterParticulateEquipmentFilterType, MasterParticulateEquipmentCalibrationDate, MasterParticulateEquipmentNextCalibrationDate, MasterParticulateEquipmentCalibrationStatus, MasterParticulateEquipmentCertificateFile, MasterParticulateEquipmentIsActive, MasterParticulateEquipmentCreatedUserID)
|
||
VALUES
|
||
(1, 'HVAS-01', 'High Volume Air Sampler', 'HVAS', 'Tisch Environmental', 'TE-5000', 'HV12345', 'TSP', 'Flow rate: 1.13-1.70 m³/min, Motor: Continuous duty brushes', '1.13-1.70 m³/min', 'Glass Fiber Filter 8×10 inch', '2024-04-05', '2025-04-05', 'VALID', '/documents/calibration/hvas_tisch_2024.pdf', TRUE, 1),
|
||
(2, 'PM10-01', 'PM10 High Volume Sampler', 'PM10 HVAS', 'Tisch Environmental', 'TE-6070', 'PM10-7890', 'PM10', 'Flow rate: 1.13-1.70 m³/min, Size-selective inlet', '1.13-1.70 m³/min', 'Quartz Fiber Filter 8×10 inch', '2024-04-10', '2025-04-10', 'VALID', '/documents/calibration/pm10_tisch_2024.pdf', TRUE, 1),
|
||
(3, 'PM25-01', 'Low Volume PM2.5 Sampler', 'PM2.5 LVS', 'BGI', 'PQ200', 'PQ-5678', 'PM2.5', 'Flow rate: 16.7 L/min, WINS impactor', '16.7 L/min', '47mm PTFE membrane filter', '2024-03-15', '2025-03-15', 'VALID', '/documents/calibration/pm25_bgi_2024.pdf', TRUE, 1),
|
||
(4, 'MET-01', 'Weather Station', 'Weather Monitor', 'Davis', 'Vantage Pro2', 'WS78901', 'Meteorological Parameters', 'Temperature, humidity, pressure, wind speed/direction', 'N/A', 'N/A', '2024-03-15', '2025-03-15', 'VALID', '/documents/calibration/met_davis_2024.pdf', TRUE, 1),
|
||
(5, 'BAL-01', 'Analytical Balance', 'Microbalance', 'Mettler Toledo', 'XPR2U', 'MT34567', 'Filter Weighing', 'Range: 0-2.1g, Readability: 0.1μg, Repeatability: 0.15μg', 'N/A', 'N/A', '2024-02-20', '2025-02-20', 'VALID', '/documents/calibration/bal_mettler_2024.pdf', TRUE, 1);
|
||
|
||
-- ------------------------------------------------------------------------------
|
||
-- Sample Data Insertion - Transaction Tables
|
||
-- ------------------------------------------------------------------------------
|
||
|
||
-- Sample Particulate Sampling Plan
|
||
INSERT INTO particulate_sampling_plans (ParticulateSamplingPlanID, ParticulateSamplingPlanCode, ParticulateSamplingPlanProjectName, ClientID, ParticulateSamplingPlanDate, ParticulateSamplingPlanLocation, ParticulateSamplingPlanPointCount, ParticulateSamplingPlanParameters, ParticulateSamplingPlanStatus, ParticulateSamplingPlanNotes, ParticulateSamplingPlanCreatedAt, ParticulateSamplingPlanCreatedUserID)
|
||
VALUES
|
||
(1, 'SP-PART-2024-002', 'Total Particulate Measurement at PT. Integrated Manufacturing Industries', 201, '2024-06-15', 'PT. Integrated Manufacturing Industries, Industrial Zone Block C5, Karawang, West Java', 6, 'TSP,PM10', 'APPROVED', 'Particulate measurement for environmental compliance monitoring', '2024-06-05 10:00:00', 1);
|
||
|
||
-- Sample Particulate Measurement Results - Header
|
||
INSERT INTO particulate_measurements (ParticulateMeasurementID, ParticulateSamplingPlanID, ParticulateMeasurementCode, ParticulateMeasurementDate, ParticulateMeasurementStartTime, ParticulateMeasurementEndTime, ParticulateMeasurementWeather, ParticulateMeasurementTemperature, ParticulateMeasurementHumidity, ParticulateMeasurementWindSpeed, ParticulateMeasurementWindDirection, ParticulateMeasurementPressure, ParticulateMeasurementOfficers, ParticulateMeasurementStatus, ParticulateMeasurementNotes, ParticulateMeasurementCreatedAt, ParticulateMeasurementCreatedUserID)
|
||
VALUES
|
||
(1, 1, 'LHU/PART/06/2024/005', '2024-06-15', '08:30:00', '16:30:00', 'Sunny', 31.0, 68.0, 1.2, 'North', 760.0, 'Rudi Hartono, Siti Fauziah', 'FINAL', 'Measurement conducted during normal operations', '2024-06-15 17:00:00', 1);
|
||
|
||
-- Sample Particulate Measurement Details
|
||
INSERT INTO particulate_measurement_details (ParticulateMeasurementDetailID, ParticulateMeasurementID, ParticulateMeasurementDetailPointCode, ParticulateMeasurementDetailLocation, ParticulateMeasurementDetailAreaDescription, MasterAreaTypeID, ParticulateMeasurementDetailLatitude, ParticulateMeasurementDetailLongitude, ParticulateMeasurementDetailHeight, ParticulateMeasurementDetailDuration, ParticulateMeasurementDetailParameterCode, MasterParticulateMethodID, MasterParticulateEquipmentID, ParticulateMeasurementDetailFilterID, ParticulateMeasurementDetailInitialWeight, ParticulateMeasurementDetailFinalWeight, ParticulateMeasurementDetailFlowRate, ParticulateMeasurementDetailVolume, ParticulateMeasurementDetailResult, MasterParticulateStandardID, ParticulateMeasurementDetailComplianceStatus, ParticulateMeasurementDetailSources, ParticulateMeasurementDetailRecommendations, ParticulateMeasurementDetailNotes, ParticulateMeasurementDetailCreatedAt, ParticulateMeasurementDetailCreatedUserID)
|
||
VALUES
|
||
-- Main Factory Entrance - TSP
|
||
(1, 1, 'TPM-1', 'Main Factory Entrance', 'Main entrance with moderate traffic', 1, -6.372500, 107.524167, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-01', 4.52130, 4.62875, 1.15, 552.0, 215.4, 1, 'COMPLY', 'Vehicle traffic, material transport activities', '', 'Moderate traffic flow during sampling', '2024-06-15 17:05:00', 1),
|
||
|
||
-- Main Factory Entrance - PM10
|
||
(2, 1, 'TPM-1', 'Main Factory Entrance', 'Main entrance with moderate traffic', 1, -6.372500, 107.524167, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-01', 4.48730, 4.54947, 1.15, 552.0, 112.7, 6, 'COMPLY', 'Vehicle traffic, material transport activities', '', 'PM10 fraction shows significant contribution', '2024-06-15 17:10:00', 1),
|
||
|
||
-- Production Area (Crusher Unit) - TSP
|
||
(3, 1, 'TPM-2', 'Production Area (Crusher Unit)', 'Area with crushing equipment and high dust generation', 1, -6.372778, 107.524444, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-02', 4.51890, 4.67765, 1.15, 552.0, 287.6, 1, 'NOT_COMPLY', 'Crushing activities, material handling, conveyor transfer points', 'Install dust suppression systems, implement proper enclosure', 'Visible dust emissions during crusher operation', '2024-06-15 17:15:00', 1),
|
||
|
||
-- Production Area (Crusher Unit) - PM10
|
||
(4, 1, 'TPM-2', 'Production Area (Crusher Unit)', 'Area with crushing equipment and high dust generation', 1, -6.372778, 107.524444, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-02', 4.49120, 4.57748, 1.15, 552.0, 156.3, 6, 'NOT_COMPLY', 'Crushing activities, material handling, conveyor transfer points', 'Install dust collection systems with adequate filtration', 'High respirable fraction indicates health concerns', '2024-06-15 17:20:00', 1),
|
||
|
||
-- Office Building - TSP
|
||
(5, 1, 'TPM-3', 'Office Building', 'Administrative office area', 3, -6.372944, 107.525000, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-03', 4.52670, 4.57255, 1.15, 552.0, 83.1, 3, 'COMPLY', 'Limited sources, mainly from HVAC system', '', 'Indoor air quality with minimal outdoor influence', '2024-06-15 17:25:00', 1),
|
||
|
||
-- Office Building - PM10
|
||
(6, 1, 'TPM-3', 'Office Building', 'Administrative office area', 3, -6.372944, 107.525000, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-03', 4.48950, 4.51467, 1.15, 552.0, 45.6, 8, 'COMPLY', 'Limited sources, mainly from HVAC system', '', 'Low PM10 levels indicate good air filtration', '2024-06-15 17:30:00', 1),
|
||
|
||
-- Eastern Facility Boundary - TSP
|
||
(7, 1, 'TPM-4', 'Eastern Facility Boundary', 'Eastern boundary of the facility', 1, -6.373111, 107.526111, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-04', 4.53010, 4.62778, 1.15, 552.0, 176.8, 1, 'COMPLY', 'General facility operations, stockpiles, road dust', '', 'Unpaved areas contributing to dust levels', '2024-06-15 17:35:00', 1),
|
||
|
||
-- Eastern Facility Boundary - PM10
|
||
(8, 1, 'TPM-4', 'Eastern Facility Boundary', 'Eastern boundary of the facility', 1, -6.373111, 107.526111, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-04', 4.49070, 4.53997, 1.15, 552.0, 89.3, 6, 'COMPLY', 'General facility operations, stockpiles, road dust', '', 'PM10 levels below industrial standard', '2024-06-15 17:40:00', 1),
|
||
|
||
-- Material Storage Area - TSP
|
||
(9, 1, 'TPM-5', 'Material Storage Area', 'Raw material storage with open piles', 1, -6.373278, 107.524722, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-05', 4.52230, 4.67025, 1.15, 552.0, 268.2, 1, 'NOT_COMPLY', 'Raw material stockpiles, loading/unloading operations', 'Cover stockpiles, implement water spraying', 'Wind-blown dust from uncovered material piles', '2024-06-15 17:45:00', 1),
|
||
|
||
-- Material Storage Area - PM10
|
||
(10, 1, 'TPM-5', 'Material Storage Area', 'Raw material storage with open piles', 1, -6.373278, 107.524722, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-05', 4.48870, 4.56737, 1.15, 552.0, 142.5, 6, 'COMPLY', 'Raw material stockpiles, loading/unloading operations', 'Minimize material handling during high winds', 'PM10 fraction still within limits but elevated', '2024-06-15 17:50:00', 1),
|
||
|
||
-- Nearest Residential Area - TSP
|
||
(11, 1, 'TPM-6', 'Nearest Residential Area', 'Residential area near the industrial facility', 2, -6.374167, 107.527222, 1.5, 8.0, 'TSP', 1, 1, 'GF-20240615-06', 4.52450, 4.57884, 1.15, 552.0, 98.5, 2, 'NOT_COMPLY', 'Industrial facility emissions, community activities', 'Establish vegetation buffer, improve dust control', 'Residential area ~500m from facility boundary', '2024-06-15 17:55:00', 1),
|
||
|
||
-- Nearest Residential Area - PM10
|
||
(12, 1, 'TPM-6', 'Nearest Residential Area', 'Residential area near the industrial facility', 2, -6.374167, 107.527222, 1.5, 8.0, 'PM10', 2, 2, 'QF-20240615-06', 4.49210, 4.52103, 1.15, 552.0, 52.4, 7, 'COMPLY', 'Industrial facility emissions, community activities', '', 'PM10 levels within residential standard', '2024-06-15 18:00:00', 1);
|
||
|
||
-- Sample Dust Control Recommendations
|
||
INSERT INTO dust_control_recommendations (DustControlRecommendationID, ParticulateMeasurementDetailID, DustControlRecommendationType, DustControlRecommendationDescription, DustControlRecommendationPriority, DustControlRecommendationEstimatedCost, DustControlRecommendationEstimatedReduction, DustControlRecommendationStatus, DustControlRecommendationCreatedAt, DustControlRecommendationCreatedUserID)
|
||
VALUES
|
||
-- Recommendations for Production Area (Crusher Unit)
|
||
(1, 3, 'Engineering Control', 'Installation of water spray systems at crusher loading and discharge points', 1, 45000000.00, 30.0, 'PLANNED', '2024-06-20 09:00:00', 1),
|
||
(2, 3, 'Engineering Control', 'Enclosure of crushing and screening equipment with appropriate dust collection', 1, 120000000.00, 50.0, 'PLANNED', '2024-06-20 09:05:00', 1),
|
||
(3, 3, 'Administrative Control', 'Implement preventive maintenance program for dust collection systems', 2, 0.00, 15.0, 'PLANNED', '2024-06-20 09:10:00', 1),
|
||
(4, 4, 'PPE', 'Provision of N95 respirators for workers in crushing area', 1, 15000000.00, 0.0, 'PLANNED', '2024-06-20 09:15:00', 1),
|
||
|
||
-- Recommendations for Material Storage Area
|
||
(5, 9, 'Engineering Control', 'Installation of wind barriers around material storage area', 1, 75000000.00, 25.0, 'PLANNED', '2024-06-20 09:20:00', 1),
|
||
(6, 9, 'Engineering Control', 'Coverage of stockpiles with tarpaulins or other appropriate materials', 1, 30000000.00, 35.0, 'PLANNED', '2024-06-20 09:25:00', 1),
|
||
(7, 9, 'Engineering Control', 'Installation of fixed water sprinkler system for storage area', 2, 60000000.00, 30.0, 'PLANNED', '2024-06-20 09:30:00', 1),
|
||
(8, 9, 'Administrative Control', 'Implement SOP for minimizing drop heights during material transfer', 1, 5000000.00, 10.0, 'PLANNED', '2024-06-20 09:35:00', 1),
|
||
|
||
-- Recommendations for Nearest Residential Area
|
||
(9, 11, 'Engineering Control', 'Establishment of vegetation buffer zone between facility and residential areas', 1, 90000000.00, 15.0, 'PLANNED', '2024-06-20 09:40:00', 1),
|
||
(10, 11, 'Engineering Control', 'Paving of unpaved roads near residential boundary', 2, 120000000.00, 20.0, 'PLANNED', '2024-06-20 09:45:00', 1),
|
||
(11, 11, 'Administrative Control', 'Implementation of community notification system for high-dust activities', 1, 25000000.00, 0.0, 'PLANNED', '2024-06-20 09:50:00', 1),
|
||
(12, 11, 'Administrative Control', 'Schedule dust-generating activities based on wind direction and speed', 1, 0.00, 10.0, 'PLANNED', '2024-06-20 09:55:00', 1); |