441 lines
18 KiB
Python
441 lines
18 KiB
Python
from dataclasses import dataclass
|
|
import os
|
|
|
|
import pymysql
|
|
|
|
|
|
@dataclass(frozen=True)
|
|
class DbConfig:
|
|
host: str = os.getenv("NAVSEA_DB_HOST", "localhost")
|
|
port: int = int(os.getenv("NAVSEA_DB_PORT", "3306"))
|
|
user: str = os.getenv("NAVSEA_DB_USER", "root")
|
|
password: str = os.getenv("NAVSEA_DB_PASSWORD", "2chi9ks2")
|
|
database: str = os.getenv("NAVSEA_DB_NAME", "pbf_analysis")
|
|
unix_socket: str | None = os.getenv("NAVSEA_DB_SOCKET", "/tmp/mysql.sock")
|
|
|
|
|
|
class SemanticOverlayValidator:
|
|
REQUIRED_TABLES = {
|
|
"canonical_layer_rules": {"source_layer", "semantic_granularity", "canonical_family"},
|
|
"canonical_object_rules": {
|
|
"canonical_object_type",
|
|
"canonical_family",
|
|
"classification_basis",
|
|
"source_layer_scope",
|
|
"allowed_geom_types",
|
|
},
|
|
"object_geometry_allowlist": {
|
|
"canonical_object_type",
|
|
"geometry_type",
|
|
"is_allowed",
|
|
},
|
|
"pbf_relayer_candidates": {
|
|
"feature_id",
|
|
"source_layer",
|
|
"geom_type",
|
|
"canonical_object_type",
|
|
"canonical_family",
|
|
"classification_basis",
|
|
"source_layer_scope",
|
|
"semantic_granularity",
|
|
"detection_key",
|
|
"render_layer",
|
|
"object_type_source",
|
|
"style_bound",
|
|
"z",
|
|
"x",
|
|
"y",
|
|
},
|
|
"pbf_render_compatibility": {
|
|
"source_layer",
|
|
"render_layer",
|
|
"feature_count",
|
|
},
|
|
"pbf_source_object_stats": {
|
|
"source_layer",
|
|
"render_layer",
|
|
"canonical_family",
|
|
"semantic_granularity",
|
|
"canonical_object_type",
|
|
"geom_type",
|
|
"feature_count",
|
|
},
|
|
"pbf_detection_catalog": {
|
|
"detection_key",
|
|
"canonical_object_type",
|
|
"canonical_family",
|
|
"geom_type",
|
|
"feature_count",
|
|
},
|
|
"style_layers": {"source_layer"},
|
|
"features": {"id", "z", "x", "y", "vt_layer", "geom_type"},
|
|
"tile_density": {"z", "x", "y", "feature_count"},
|
|
"tile_layer_density": {"z", "x", "y", "vt_layer", "feature_count"},
|
|
}
|
|
|
|
def __init__(self, db_config: DbConfig) -> None:
|
|
self.db_config = db_config
|
|
|
|
def connect(self):
|
|
kwargs = {
|
|
"host": self.db_config.host,
|
|
"port": self.db_config.port,
|
|
"user": self.db_config.user,
|
|
"password": self.db_config.password,
|
|
"database": self.db_config.database,
|
|
"charset": "utf8mb4",
|
|
"autocommit": False,
|
|
}
|
|
if self.db_config.unix_socket and self.db_config.host in {"localhost", "127.0.0.1"}:
|
|
kwargs["unix_socket"] = self.db_config.unix_socket
|
|
return pymysql.connect(**kwargs)
|
|
|
|
def validate_schema(self) -> None:
|
|
with self.connect() as conn:
|
|
with conn.cursor() as cur:
|
|
for table, required_columns in self.REQUIRED_TABLES.items():
|
|
cur.execute(
|
|
"""
|
|
SELECT COLUMN_NAME
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s
|
|
""",
|
|
(self.db_config.database, table),
|
|
)
|
|
columns = {row[0] for row in cur.fetchall()}
|
|
if not columns:
|
|
raise RuntimeError(f"required table missing: {table}")
|
|
missing = required_columns - columns
|
|
if missing:
|
|
raise RuntimeError(f"table {table} missing columns: {sorted(missing)}")
|
|
|
|
def run(self) -> None:
|
|
self.validate_schema()
|
|
with self.connect() as conn:
|
|
with conn.cursor() as cur:
|
|
self.drop_validation_tables(cur)
|
|
self.build_classification_validation(cur)
|
|
self.build_geometry_consistency(cur)
|
|
self.build_style_render_equivalence(cur)
|
|
self.build_detection_catalog_integrity(cur)
|
|
self.build_spatial_anomalies(cur)
|
|
self.build_summary(cur)
|
|
conn.commit()
|
|
print(
|
|
"Built classification_validation, geometry_consistency, "
|
|
"style_render_equivalence, detection_catalog_integrity, "
|
|
"spatial_anomalies, semantic_validation_summary"
|
|
)
|
|
|
|
@staticmethod
|
|
def drop_validation_tables(cur) -> None:
|
|
for table in (
|
|
"classification_validation",
|
|
"geometry_consistency",
|
|
"style_render_equivalence",
|
|
"detection_catalog_integrity",
|
|
"spatial_anomalies",
|
|
"semantic_validation_summary",
|
|
):
|
|
cur.execute(f"DROP TABLE IF EXISTS {table}")
|
|
|
|
@staticmethod
|
|
def build_classification_validation(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE classification_validation AS
|
|
SELECT
|
|
base.canonical_object_type,
|
|
base.feature_count,
|
|
base.distinct_source_layers,
|
|
base.distinct_geom_types,
|
|
CASE
|
|
WHEN base.canonical_object_type IS NULL OR base.canonical_object_type = '' THEN 'UNKNOWN_OBJECT'
|
|
WHEN rules.canonical_object_type IS NULL THEN 'UNKNOWN_OBJECT'
|
|
WHEN rules.classification_basis = 'source_layer_preserved' THEN 'OK'
|
|
WHEN base.distinct_families > 1 THEN 'RULE_CONFLICT'
|
|
WHEN rules.source_layer_scope = 'single_source' AND base.distinct_source_layers > 1 THEN 'AMBIGUOUS_MAPPING'
|
|
ELSE 'OK'
|
|
END AS classification_status
|
|
FROM (
|
|
SELECT
|
|
canonical_object_type,
|
|
SUM(feature_count) AS feature_count,
|
|
COUNT(DISTINCT source_layer) AS distinct_source_layers,
|
|
COUNT(DISTINCT geom_type) AS distinct_geom_types,
|
|
COUNT(DISTINCT canonical_family) AS distinct_families
|
|
FROM pbf_source_object_stats
|
|
GROUP BY canonical_object_type
|
|
) AS base
|
|
LEFT JOIN canonical_object_rules rules
|
|
ON base.canonical_object_type = rules.canonical_object_type
|
|
"""
|
|
)
|
|
cur.execute("ALTER TABLE classification_validation ADD KEY idx_status (classification_status)")
|
|
cur.execute("ALTER TABLE classification_validation ADD KEY idx_object (canonical_object_type(100))")
|
|
|
|
@staticmethod
|
|
def build_geometry_consistency(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE geometry_consistency AS
|
|
SELECT
|
|
g.canonical_object_type,
|
|
g.geom_type AS geometry_type,
|
|
g.feature_count,
|
|
CASE
|
|
WHEN allowlist.is_allowed = 1 THEN 'OK'
|
|
WHEN g.feature_share < 0.05 THEN 'GEOMETRY_DRIFT'
|
|
ELSE 'UNEXPECTED_GEOMETRY'
|
|
END AS geometry_consistency_status
|
|
FROM (
|
|
SELECT
|
|
canonical_object_type,
|
|
canonical_family,
|
|
geom_type,
|
|
SUM(feature_count) AS feature_count,
|
|
SUM(feature_count) / totals.total_features AS feature_share
|
|
FROM pbf_detection_catalog
|
|
JOIN (
|
|
SELECT
|
|
canonical_object_type,
|
|
SUM(feature_count) AS total_features
|
|
FROM pbf_detection_catalog
|
|
GROUP BY canonical_object_type
|
|
) AS totals
|
|
USING (canonical_object_type)
|
|
GROUP BY
|
|
canonical_object_type,
|
|
canonical_family,
|
|
geom_type,
|
|
totals.total_features
|
|
) AS g
|
|
LEFT JOIN object_geometry_allowlist allowlist
|
|
ON g.canonical_object_type = allowlist.canonical_object_type
|
|
AND g.geom_type = allowlist.geometry_type
|
|
"""
|
|
)
|
|
cur.execute("ALTER TABLE geometry_consistency ADD KEY idx_object (canonical_object_type(100))")
|
|
cur.execute("ALTER TABLE geometry_consistency ADD KEY idx_status (geometry_consistency_status)")
|
|
|
|
@staticmethod
|
|
def build_style_render_equivalence(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE style_render_equivalence AS
|
|
SELECT
|
|
style_scope.style_layer,
|
|
COALESCE(before_counts.feature_count_before, 0) AS feature_count_before,
|
|
COALESCE(after_counts.feature_count_after, 0) AS feature_count_after,
|
|
COALESCE(after_counts.feature_count_after, 0) - COALESCE(before_counts.feature_count_before, 0) AS difference,
|
|
CASE
|
|
WHEN COALESCE(before_counts.feature_count_before, 0) = COALESCE(after_counts.feature_count_after, 0) THEN 'OK'
|
|
WHEN COALESCE(before_counts.feature_count_before, 0) > 0 AND COALESCE(after_counts.feature_count_after, 0) = 0 THEN 'RENDER_BREAK_RISK'
|
|
ELSE 'COUNT_MISMATCH'
|
|
END AS render_status
|
|
FROM (
|
|
SELECT DISTINCT source_layer AS style_layer
|
|
FROM style_layers
|
|
WHERE source_layer IS NOT NULL
|
|
) AS style_scope
|
|
LEFT JOIN (
|
|
SELECT
|
|
vt_layer AS style_layer,
|
|
COUNT(*) AS feature_count_before
|
|
FROM features
|
|
GROUP BY vt_layer
|
|
) AS before_counts
|
|
ON style_scope.style_layer = before_counts.style_layer
|
|
LEFT JOIN (
|
|
SELECT
|
|
LEFT(render_layer, 100) AS style_layer,
|
|
SUM(feature_count) AS feature_count_after
|
|
FROM pbf_render_compatibility
|
|
GROUP BY LEFT(render_layer, 100)
|
|
) AS after_counts
|
|
ON style_scope.style_layer = after_counts.style_layer
|
|
"""
|
|
)
|
|
cur.execute("ALTER TABLE style_render_equivalence ADD KEY idx_status (render_status)")
|
|
cur.execute("ALTER TABLE style_render_equivalence ADD KEY idx_style_layer (style_layer)")
|
|
|
|
@staticmethod
|
|
def build_detection_catalog_integrity(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE detection_catalog_integrity AS
|
|
SELECT
|
|
d.detection_key,
|
|
SUM(d.feature_count) AS feature_count,
|
|
d.geom_type AS geometry_type,
|
|
CASE
|
|
WHEN d.detection_key IS NULL OR d.detection_key = '' THEN 'UNKNOWN_DETECTION_OBJECT'
|
|
WHEN COALESCE(object_meta.distinct_objects, 0) > 1 THEN 'UNKNOWN_DETECTION_OBJECT'
|
|
WHEN gc.geometry_consistency_status IS NOT NULL AND gc.geometry_consistency_status <> 'OK' THEN 'GEOMETRY_INCONSISTENT'
|
|
ELSE 'OK'
|
|
END AS catalog_status
|
|
FROM pbf_detection_catalog d
|
|
LEFT JOIN (
|
|
SELECT
|
|
detection_key,
|
|
COUNT(DISTINCT canonical_object_type) AS distinct_objects
|
|
FROM pbf_detection_catalog
|
|
GROUP BY detection_key
|
|
) AS object_meta
|
|
ON d.detection_key = object_meta.detection_key
|
|
LEFT JOIN geometry_consistency gc
|
|
ON d.canonical_object_type = gc.canonical_object_type
|
|
AND d.geom_type = gc.geometry_type
|
|
GROUP BY
|
|
d.detection_key,
|
|
d.geom_type,
|
|
object_meta.distinct_objects,
|
|
gc.geometry_consistency_status
|
|
"""
|
|
)
|
|
cur.execute("ALTER TABLE detection_catalog_integrity ADD KEY idx_status (catalog_status)")
|
|
cur.execute("ALTER TABLE detection_catalog_integrity ADD KEY idx_detection_key (detection_key(50))")
|
|
|
|
@staticmethod
|
|
def build_spatial_anomalies(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE spatial_anomalies AS
|
|
SELECT *
|
|
FROM (
|
|
SELECT
|
|
'OUT_OF_BOUNDS_TILE_INDEX' AS anomaly_type,
|
|
COUNT(*) AS affected_feature_count,
|
|
CONCAT('z=', MIN(z), '..', MAX(z), '; sample=', MIN(CONCAT(z, '/', x, '/', y))) AS region_hint,
|
|
'HIGH' AS severity
|
|
FROM pbf_relayer_candidates
|
|
WHERE z < 0
|
|
OR x < 0
|
|
OR y < 0
|
|
OR x >= POW(2, z)
|
|
OR y >= POW(2, z)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'DENSITY_STATISTICS_ERROR' AS anomaly_type,
|
|
tile_rows.feature_count AS affected_feature_count,
|
|
CONCAT('z=', tile_rows.z, '; tile=', tile_rows.z, '/', tile_rows.x, '/', tile_rows.y) AS region_hint,
|
|
'HIGH' AS severity
|
|
FROM tile_density AS tile_rows
|
|
CROSS JOIN (
|
|
SELECT COUNT(*) AS total_features
|
|
FROM features
|
|
) AS dataset_stats
|
|
WHERE tile_rows.feature_count > dataset_stats.total_features
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
CASE
|
|
WHEN layer_hint.vt_layer IN ('L海底地形', 'L等深線', 'L概略等深線', 'L海底線', 'p底質')
|
|
THEN 'TILE_OVERDENSE_BATHYMETRY'
|
|
WHEN layer_hint.vt_layer IN ('L陸上構造物陸', 'P陸上構造物陸', 'P橋りょう等構造物', 'p陸上構造物')
|
|
THEN 'TILE_OVERDENSE_LAND_STRUCTURE'
|
|
WHEN layer_hint.vt_layer LIKE '%ククリ'
|
|
OR layer_hint.vt_layer IN ('P基本線', 'L基本線', 'P危険界ククリ', 'P投錨注意障害物ククリ')
|
|
THEN 'TILE_OVERDENSE_OUTLINE'
|
|
ELSE 'EXTREME_TILE_DENSITY'
|
|
END AS anomaly_type,
|
|
tile_rows.feature_count AS affected_feature_count,
|
|
CONCAT(
|
|
'z=',
|
|
tile_rows.z,
|
|
'; tile=',
|
|
tile_rows.z,
|
|
'/',
|
|
tile_rows.x,
|
|
'/',
|
|
tile_rows.y,
|
|
'; top_layer=',
|
|
COALESCE(layer_hint.vt_layer, '<unknown>'),
|
|
'; layer_features=',
|
|
COALESCE(layer_hint.feature_count, 0)
|
|
) AS region_hint,
|
|
'MEDIUM' AS severity
|
|
FROM tile_density AS tile_rows
|
|
JOIN (
|
|
SELECT
|
|
z,
|
|
AVG(feature_count) AS avg_feature_count,
|
|
STD(feature_count) AS std_feature_count
|
|
FROM tile_density
|
|
GROUP BY z
|
|
) AS zoom_stats
|
|
ON tile_rows.z = zoom_stats.z
|
|
LEFT JOIN (
|
|
SELECT
|
|
winners.z,
|
|
winners.x,
|
|
winners.y,
|
|
MIN(winners.vt_layer) AS vt_layer,
|
|
winners.feature_count
|
|
FROM tile_layer_density AS winners
|
|
JOIN (
|
|
SELECT
|
|
z,
|
|
x,
|
|
y,
|
|
MAX(feature_count) AS feature_count
|
|
FROM tile_layer_density
|
|
GROUP BY z, x, y
|
|
) AS maxima
|
|
ON winners.z = maxima.z
|
|
AND winners.x = maxima.x
|
|
AND winners.y = maxima.y
|
|
AND winners.feature_count = maxima.feature_count
|
|
GROUP BY
|
|
winners.z,
|
|
winners.x,
|
|
winners.y,
|
|
winners.feature_count
|
|
) AS layer_hint
|
|
ON tile_rows.z = layer_hint.z
|
|
AND tile_rows.x = layer_hint.x
|
|
AND tile_rows.y = layer_hint.y
|
|
WHERE tile_rows.z >= 1
|
|
AND tile_rows.feature_count > (
|
|
zoom_stats.avg_feature_count + (5 * COALESCE(zoom_stats.std_feature_count, 0))
|
|
)
|
|
) AS anomaly_union
|
|
WHERE affected_feature_count IS NOT NULL AND affected_feature_count > 0
|
|
"""
|
|
)
|
|
cur.execute("ALTER TABLE spatial_anomalies ADD KEY idx_severity (severity)")
|
|
|
|
@staticmethod
|
|
def build_summary(cur) -> None:
|
|
cur.execute(
|
|
"""
|
|
CREATE TABLE semantic_validation_summary AS
|
|
SELECT
|
|
(SELECT COUNT(*) FROM pbf_relayer_candidates) AS total_features_checked,
|
|
(SELECT COUNT(*) FROM classification_validation WHERE classification_status <> 'OK') AS classification_errors,
|
|
(SELECT COUNT(*) FROM geometry_consistency WHERE geometry_consistency_status <> 'OK') AS geometry_errors,
|
|
(SELECT COUNT(*) FROM style_render_equivalence WHERE render_status <> 'OK') AS render_equivalence_errors,
|
|
(SELECT COUNT(*) FROM detection_catalog_integrity WHERE catalog_status <> 'OK') AS detection_integrity_errors,
|
|
(SELECT COUNT(*) FROM spatial_anomalies) AS spatial_anomalies,
|
|
CASE
|
|
WHEN (SELECT COUNT(*) FROM style_render_equivalence WHERE render_status = 'RENDER_BREAK_RISK') > 0 THEN 'FAIL'
|
|
WHEN (SELECT COUNT(*) FROM classification_validation WHERE classification_status IN ('RULE_CONFLICT', 'UNKNOWN_OBJECT')) > 0 THEN 'FAIL'
|
|
WHEN (SELECT COUNT(*) FROM geometry_consistency WHERE geometry_consistency_status = 'UNEXPECTED_GEOMETRY') > 0 THEN 'FAIL'
|
|
WHEN (SELECT COUNT(*) FROM detection_catalog_integrity WHERE catalog_status <> 'OK') > 0 THEN 'WARNING'
|
|
WHEN (SELECT COUNT(*) FROM spatial_anomalies) > 0 THEN 'WARNING'
|
|
ELSE 'PASS'
|
|
END AS validation_status
|
|
"""
|
|
)
|
|
|
|
|
|
def main() -> None:
|
|
SemanticOverlayValidator(DbConfig()).run()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|