-- ============================================================ -- EIGIS Theme 2: Structural Features -- Discontinuities, slope stability, rock mass classification -- Multiple structural measurements per observation -- ============================================================ -- ----------------------------------------------------------- -- 1. DISCONTINUITY MEASUREMENTS -- Multiple sets per observation -- ----------------------------------------------------------- CREATE TABLE discontinuity_measurements ( disc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, set_label VARCHAR(20) NOT NULL, -- J1, J2, F1, etc. disc_type VARCHAR(30) NOT NULL CHECK (disc_type IN ( 'joint','fault','bedding','foliation','shear_zone', 'tension_crack','cleavage','vein','dyke','contact' )), dip_direction_deg DECIMAL(5,1) CHECK (dip_direction_deg BETWEEN 0 AND 360), dip_deg DECIMAL(4,1) CHECK (dip_deg BETWEEN 0 AND 90), strike_deg DECIMAL(5,1) GENERATED ALWAYS AS ( CASE WHEN dip_direction_deg IS NOT NULL THEN (dip_direction_deg + 90)::DECIMAL(5,1) % 360 ELSE NULL END ) STORED, spacing_m DECIMAL(6,3), aperture_mm DECIMAL(6,2), persistence_m DECIMAL(6,2), roughness VARCHAR(30) CHECK (roughness IN ('very_rough','rough','slightly_rough','smooth','slickensided','polished')), infill_material VARCHAR(50) CHECK (infill_material IN ('none','clay','calcite','quartz','chlorite','kaolin','hematite','gouge','breccia')), infill_thickness_mm DECIMAL(5,2), water_condition VARCHAR(30) CHECK (water_condition IN ('dry','damp','wet','flowing','seeping')), wall_weathering INTEGER CHECK (wall_weathering BETWEEN 1 AND 5), geom GEOMETRY(Point, 4326), -- Measurement point line_geom GEOMETRY(LineString, 4326), -- Discontinuity trace line sort_order INTEGER DEFAULT 0, notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_disc_obs ON discontinuity_measurements(observation_id); CREATE INDEX idx_disc_geom ON discontinuity_measurements USING GIST(geom); CREATE INDEX idx_disc_type ON discontinuity_measurements(disc_type); CREATE INDEX idx_disc_dip ON discontinuity_measurements(dip_direction_deg, dip_deg); CREATE INDEX idx_disc_line ON discontinuity_measurements USING GIST(line_geom); -- ----------------------------------------------------------- -- 2. SLOPE STABILITY ASSESSMENTS -- ----------------------------------------------------------- CREATE TABLE slope_stability ( slope_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, has_existing_slope BOOLEAN DEFAULT TRUE, slope_height_m DECIMAL(8,2), slope_angle_deg DECIMAL(5,1), aspect_deg DECIMAL(5,1), slope_form VARCHAR(30) CHECK (slope_form IN ('concave','convex','planar','stepped','composite')), stability_condition VARCHAR(30) CHECK (stability_condition IN ('stable','small_problems','large_problems','failed','critical')), hazard_level VARCHAR(20) CHECK (hazard_level IN ('low','moderate','high','very_high','extreme')), failure_mode VARCHAR(50) CHECK (failure_mode IN ('circular','planar','wedge','toppling','fall','flow','spreading','complex')), factor_of_safety DECIMAL(6,3), trigger_mechanism VARCHAR(100), volume_estimate_m3 DECIMAL(12,2), runout_distance_m DECIMAL(8,2), recommended_actions TEXT, mitigation_measures TEXT, monitoring_required BOOLEAN DEFAULT FALSE, slope_geom GEOMETRY(LineString, 4326), -- Slope profile line failure_geom GEOMETRY(Polygon, 4326), -- Failure area polygon created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_slope_obs ON slope_stability(observation_id); CREATE INDEX idx_slope_hazard ON slope_stability(hazard_level); CREATE INDEX idx_slope_geom ON slope_stability USING GIST(slope_geom); CREATE INDEX idx_slope_failure ON slope_stability USING GIST(failure_geom); -- ----------------------------------------------------------- -- 3. ROCK MASS CLASSIFICATION (RMR / Q / GSI) -- ----------------------------------------------------------- CREATE TABLE rock_mass_classifications ( classification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE, -- RMR89 Parameters rmr_strength INTEGER CHECK (rmr_strength BETWEEN 0 AND 15), rmr_drill_quality INTEGER CHECK (rmr_drill_quality BETWEEN 3 AND 20), rmr_spacing INTEGER CHECK (rmr_spacing BETWEEN 5 AND 20), rmr_condition INTEGER CHECK (rmr_condition BETWEEN 0 AND 30), rmr_groundwater INTEGER CHECK (rmr_groundwater BETWEEN 0 AND 15), rmr_adjustment INTEGER CHECK (rmr_adjustment BETWEEN -15 AND 0), rmr_total INTEGER CHECK (rmr_total BETWEEN 0 AND 100), rmr_class VARCHAR(30), -- I through V -- Q-System Parameters q_rqd DECIMAL(5,1) CHECK (q_rqd BETWEEN 0 AND 100), q_jn DECIMAL(5,1) CHECK (q_jn BETWEEN 0.5 AND 20), q_jr DECIMAL(5,1) CHECK (q_jr BETWEEN 0.1 AND 4), q_ja DECIMAL(5,1) CHECK (q_ja BETWEEN 0.1 AND 20), q_jw DECIMAL(5,1) CHECK (q_jw BETWEEN 0.05 AND 1), q_srf DECIMAL(5,1) CHECK (q_srf BETWEEN 0.5 AND 400), q_value DECIMAL(10,4), q_class VARCHAR(30), -- GSI gsi_value INTEGER CHECK (gsi_value BETWEEN 5 AND 100), gsi_structure VARCHAR(50), gsi_surface VARCHAR(50), -- Computed emod_gpa DECIMAL(8,2), -- Deformation modulus cohesion_mpa DECIMAL(6,3), friction_angle_deg DECIMAL(5,1), notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_rmc_obs ON rock_mass_classifications(observation_id); CREATE INDEX idx_rmc_rmr ON rock_mass_classifications(rmr_total); CREATE INDEX idx_rmc_q ON rock_mass_classifications(q_value); -- ----------------------------------------------------------- -- 4. LANDSLIDE INVENTORY -- ----------------------------------------------------------- CREATE TABLE landslide_inventory ( landslide_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL, project_id UUID NOT NULL REFERENCES projects(project_id), landslide_code VARCHAR(50) UNIQUE, landslide_type VARCHAR(50) NOT NULL CHECK (landslide_type IN ( 'rotational_slide','translational_slide','block_slide', 'rockfall','debris_flow','earthflow','mudflow', 'lateral_spreading','complex','toppling','creep' )), activity_state VARCHAR(30) CHECK (activity_state IN ('active','suspended','reactivated','dormant','stabilized','relic')), movement_rate VARCHAR(30), -- mm/year, m/year, etc. depth_estimated_m DECIMAL(6,2), area_m2 DECIMAL(12,2), volume_m3 DECIMAL(12,2), casualties INTEGER DEFAULT 0, damage_desc TEXT, causative_factors TEXT[], geom GEOMETRY(Polygon, 4326) NOT NULL, -- Landslide boundary source_geom GEOMETRY(Point, 4326), -- Crown/source point deposit_geom GEOMETRY(Point, 4326), -- Toe/deposit point threat_level VARCHAR(20) CHECK (threat_level IN ('low','moderate','high','very_high','extreme')), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_landslide_geom ON landslide_inventory USING GIST(geom); CREATE INDEX idx_landslide_source ON landslide_inventory USING GIST(source_geom); CREATE INDEX idx_landslide_project ON landslide_inventory(project_id); CREATE INDEX idx_landslide_type ON landslide_inventory(landslide_type); CREATE INDEX idx_landslide_activity ON landslide_inventory(activity_state);