738 lines
25 KiB
Python
738 lines
25 KiB
Python
#!/usr/bin/env python3
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import datetime as dt
|
|
import json
|
|
import math
|
|
from pathlib import Path
|
|
from typing import Callable, Iterable
|
|
|
|
import mapbox_vector_tile
|
|
import pymysql
|
|
from shapely.geometry import box, shape
|
|
from shapely.prepared import prep
|
|
|
|
|
|
DB_NAME = "navsea_fukuoka_saga_grid"
|
|
DB_USER = "root"
|
|
DB_PASSWORD = "2chi9ks2"
|
|
DB_HOST = "localhost"
|
|
DB_SOCKET = "/tmp/mysql.sock"
|
|
|
|
COAST_SRC = [
|
|
"coastline/C23-06_40_GML.zip",
|
|
"coastline/C23-06_41_GML.zip",
|
|
]
|
|
FISH_SRC = "coastline/C09-06.zip"
|
|
FISH_GRID_SRC = "src/pbf/coastline-only/fukuoka_saga_fish_port_navsplit_20m/fish_port_navsplit_fukuoka_saga_20m_grid.geojson"
|
|
COAST_GRID_SRC = "src/pbf/coastline/fukuoka_saga_blocked_cells.geojson"
|
|
PBF_TILE_ROOT = Path("/home/wwwroot/pbf-delivery-kyushu-reencoded")
|
|
PBF_TILE_Z = 12
|
|
DEFAULT_BBOX = (129.73110007140482, 32.9464631795233, 131.19176072338317, 34.25430274729531)
|
|
COAST_CELL_M = 200.0
|
|
FISH_CELL_M = 20.0
|
|
HAZARD_CELL_M = 50.0
|
|
|
|
HAZARD_LAYERS = (
|
|
"navigation_hazard_area",
|
|
"fixed_fishing_gear_area",
|
|
"anchor_caution_hazard_area",
|
|
"navigation_hazard_point",
|
|
"anchor_caution_hazard_point",
|
|
"navigation_marks",
|
|
)
|
|
POINT_LAYERS = {
|
|
"navigation_hazard_point",
|
|
"anchor_caution_hazard_point",
|
|
"navigation_marks",
|
|
}
|
|
|
|
RADIUS = 6378137.0
|
|
MAX_MERCATOR_LAT = 85.0511287798066
|
|
|
|
|
|
def mercator_x(lon: float) -> float:
|
|
return RADIUS * math.radians(lon)
|
|
|
|
|
|
def mercator_y(lat: float) -> float:
|
|
lat = max(min(lat, MAX_MERCATOR_LAT), -MAX_MERCATOR_LAT)
|
|
return RADIUS * math.log(math.tan(math.pi / 4.0 + math.radians(lat) / 2.0))
|
|
|
|
|
|
def lon_from_mercator(x: float) -> float:
|
|
return math.degrees(x / RADIUS)
|
|
|
|
|
|
def lat_from_mercator(y: float) -> float:
|
|
return math.degrees(2.0 * math.atan(math.exp(y / RADIUS)) - math.pi / 2.0)
|
|
|
|
|
|
def lonlat_from_text(text: str) -> tuple[float, float]:
|
|
parts = [p for p in text.replace(",", " ").split() if p]
|
|
if len(parts) < 2:
|
|
raise ValueError(f"invalid coordinate text: {text!r}")
|
|
lat = float(parts[0])
|
|
lon = float(parts[1])
|
|
return lon, lat
|
|
|
|
|
|
def local_name(tag: str) -> str:
|
|
return tag.split("}", 1)[-1]
|
|
|
|
|
|
def load_json(path: Path) -> dict:
|
|
return json.loads(path.read_text(encoding="utf-8"))
|
|
|
|
|
|
def load_xml(zip_path: Path):
|
|
import zipfile
|
|
import xml.etree.ElementTree as ET
|
|
|
|
with zipfile.ZipFile(zip_path) as zf:
|
|
xml_name = next(name for name in zf.namelist() if name.endswith(".xml") and "META" not in name)
|
|
return ET.fromstring(zf.read(xml_name))
|
|
|
|
|
|
def build_point_lookup(root) -> dict[str, tuple[float, float]]:
|
|
lookup: dict[str, tuple[float, float]] = {}
|
|
for point in root.iter():
|
|
if local_name(point.tag) != "GM_Point":
|
|
continue
|
|
point_id = point.attrib.get("id")
|
|
if not point_id:
|
|
continue
|
|
coord_text = None
|
|
for el in point.iter():
|
|
if local_name(el.tag).endswith("DirectPosition.coordinate") and (el.text or "").strip():
|
|
coord_text = el.text.strip()
|
|
break
|
|
if coord_text is None:
|
|
continue
|
|
lookup[point_id] = lonlat_from_text(coord_text)
|
|
return lookup
|
|
|
|
|
|
def extract_curve_coords(curve, point_lookup: dict[str, tuple[float, float]]) -> list[tuple[float, float]]:
|
|
coords: list[tuple[float, float]] = []
|
|
for pos in curve.iter():
|
|
tag = local_name(pos.tag)
|
|
if not tag.startswith("GM_Position"):
|
|
continue
|
|
direct = None
|
|
ref = None
|
|
for child in list(pos):
|
|
child_tag = local_name(child.tag)
|
|
if child_tag.endswith("DirectPosition.coordinate") and (child.text or "").strip():
|
|
direct = child.text.strip()
|
|
break
|
|
if child_tag.endswith("PointRef.point"):
|
|
ref = child.attrib.get("idref")
|
|
if direct is not None:
|
|
coords.append(lonlat_from_text(direct))
|
|
elif ref and ref in point_lookup:
|
|
coords.append(point_lookup[ref])
|
|
return coords
|
|
|
|
|
|
def bbox_from_coords(coords: list[list[float]] | list[tuple[float, float]]) -> tuple[float, float, float, float]:
|
|
min_lon = min_lat = float("inf")
|
|
max_lon = max_lat = float("-inf")
|
|
|
|
def walk(node):
|
|
nonlocal min_lon, min_lat, max_lon, max_lat
|
|
if not node:
|
|
return
|
|
if isinstance(node[0], (int, float)):
|
|
lon, lat = float(node[0]), float(node[1])
|
|
min_lon = min(min_lon, lon)
|
|
min_lat = min(min_lat, lat)
|
|
max_lon = max(max_lon, lon)
|
|
max_lat = max(max_lat, lat)
|
|
return
|
|
for item in node:
|
|
walk(item)
|
|
|
|
walk(coords)
|
|
return min_lon, min_lat, max_lon, max_lat
|
|
|
|
|
|
def rect_geojson(min_lon: float, min_lat: float, max_lon: float, max_lat: float) -> dict:
|
|
return {
|
|
"type": "Feature",
|
|
"geometry": {
|
|
"type": "Polygon",
|
|
"coordinates": [
|
|
[
|
|
[min_lon, min_lat],
|
|
[max_lon, min_lat],
|
|
[max_lon, max_lat],
|
|
[min_lon, max_lat],
|
|
[min_lon, min_lat],
|
|
]
|
|
],
|
|
},
|
|
}
|
|
|
|
|
|
def cell_bbox_from_mercator(ix: int, iy: int, cell_m: float) -> tuple[float, float, float, float]:
|
|
minx = ix * cell_m
|
|
miny = iy * cell_m
|
|
maxx = minx + cell_m
|
|
maxy = miny + cell_m
|
|
return (
|
|
lon_from_mercator(minx),
|
|
lat_from_mercator(miny),
|
|
lon_from_mercator(maxx),
|
|
lat_from_mercator(maxy),
|
|
)
|
|
|
|
|
|
def cell_bbox_from_feature_coords(coords) -> tuple[float, float, float, float]:
|
|
return bbox_from_coords(coords)
|
|
|
|
|
|
def stream_geojson(path: Path, features: Iterable[dict]) -> int:
|
|
path.parent.mkdir(parents=True, exist_ok=True)
|
|
count = 0
|
|
with path.open("w", encoding="utf-8") as fh:
|
|
fh.write('{"type":"FeatureCollection","features":[\n')
|
|
first = True
|
|
for feature in features:
|
|
if not first:
|
|
fh.write(",\n")
|
|
fh.write(json.dumps(feature, ensure_ascii=False))
|
|
first = False
|
|
count += 1
|
|
fh.write("\n]}\n")
|
|
return count
|
|
|
|
|
|
def mysql_connect(database: str | None = None):
|
|
kwargs = {
|
|
"host": DB_HOST,
|
|
"user": DB_USER,
|
|
"password": DB_PASSWORD,
|
|
"charset": "utf8mb4",
|
|
"autocommit": False,
|
|
"cursorclass": pymysql.cursors.Cursor,
|
|
}
|
|
if DB_SOCKET:
|
|
kwargs["unix_socket"] = DB_SOCKET
|
|
if database:
|
|
kwargs["database"] = database
|
|
return pymysql.connect(**kwargs)
|
|
|
|
|
|
def ensure_database() -> None:
|
|
conn = mysql_connect()
|
|
try:
|
|
with conn.cursor() as cur:
|
|
cur.execute(
|
|
f"CREATE DATABASE IF NOT EXISTS `{DB_NAME}` "
|
|
"DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
|
|
)
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def ensure_schema(conn) -> None:
|
|
ddl = [
|
|
"DROP TABLE IF EXISTS navsea_grid_cell",
|
|
"DROP TABLE IF EXISTS navsea_grid_layer_meta",
|
|
"""
|
|
CREATE TABLE navsea_grid_layer_meta (
|
|
layer_name VARCHAR(32) NOT NULL PRIMARY KEY,
|
|
description VARCHAR(255) NOT NULL,
|
|
source_desc TEXT NOT NULL,
|
|
cell_size_m DOUBLE NOT NULL,
|
|
feature_count BIGINT NOT NULL,
|
|
bbox_min_lon DOUBLE NOT NULL,
|
|
bbox_min_lat DOUBLE NOT NULL,
|
|
bbox_max_lon DOUBLE NOT NULL,
|
|
bbox_max_lat DOUBLE NOT NULL,
|
|
export_file VARCHAR(255) DEFAULT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
""",
|
|
"""
|
|
CREATE TABLE navsea_grid_cell (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
layer_name VARCHAR(32) NOT NULL,
|
|
cell_id VARCHAR(64) NOT NULL,
|
|
row_idx INT NOT NULL,
|
|
col_idx INT NOT NULL,
|
|
cell_size_m DOUBLE NOT NULL,
|
|
state_name VARCHAR(32) NOT NULL,
|
|
class_name VARCHAR(32) NOT NULL,
|
|
source_name VARCHAR(191) NOT NULL,
|
|
min_lon DOUBLE NOT NULL,
|
|
min_lat DOUBLE NOT NULL,
|
|
max_lon DOUBLE NOT NULL,
|
|
max_lat DOUBLE NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_layer_cell (layer_name, cell_id),
|
|
KEY idx_layer_state (layer_name, state_name),
|
|
KEY idx_layer_rowcol (layer_name, row_idx, col_idx)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
""",
|
|
]
|
|
with conn.cursor() as cur:
|
|
for stmt in ddl:
|
|
cur.execute(stmt)
|
|
conn.commit()
|
|
|
|
|
|
def insert_cells(cur, rows: list[tuple]) -> None:
|
|
cur.executemany(
|
|
"""
|
|
INSERT INTO navsea_grid_cell
|
|
(layer_name, cell_id, row_idx, col_idx, cell_size_m, state_name, class_name, source_name,
|
|
min_lon, min_lat, max_lon, max_lat)
|
|
VALUES
|
|
(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
""",
|
|
rows,
|
|
)
|
|
|
|
|
|
def upsert_meta(cur, layer_name: str, description: str, source_desc: str, cell_size_m: float, feature_count: int, bbox: tuple[float, float, float, float], export_file: str) -> None:
|
|
cur.execute(
|
|
"""
|
|
INSERT INTO navsea_grid_layer_meta
|
|
(layer_name, description, source_desc, cell_size_m, feature_count,
|
|
bbox_min_lon, bbox_min_lat, bbox_max_lon, bbox_max_lat, export_file)
|
|
VALUES
|
|
(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE
|
|
description=VALUES(description),
|
|
source_desc=VALUES(source_desc),
|
|
cell_size_m=VALUES(cell_size_m),
|
|
feature_count=VALUES(feature_count),
|
|
bbox_min_lon=VALUES(bbox_min_lon),
|
|
bbox_min_lat=VALUES(bbox_min_lat),
|
|
bbox_max_lon=VALUES(bbox_max_lon),
|
|
bbox_max_lat=VALUES(bbox_max_lat),
|
|
export_file=VALUES(export_file)
|
|
""",
|
|
(
|
|
layer_name,
|
|
description,
|
|
source_desc,
|
|
cell_size_m,
|
|
feature_count,
|
|
bbox[0],
|
|
bbox[1],
|
|
bbox[2],
|
|
bbox[3],
|
|
export_file,
|
|
),
|
|
)
|
|
|
|
|
|
def import_geojson_layer(
|
|
conn,
|
|
*,
|
|
layer_name: str,
|
|
description: str,
|
|
source_desc: str,
|
|
cell_size_m: float,
|
|
source_path: Path,
|
|
export_path: Path,
|
|
include_predicate: Callable[[dict], bool],
|
|
row_key: str,
|
|
col_key: str,
|
|
state_name: str,
|
|
class_name: str,
|
|
) -> tuple[int, tuple[float, float, float, float]]:
|
|
data = load_json(source_path)
|
|
features = data.get("features", [])
|
|
bbox = [float("inf"), float("inf"), float("-inf"), float("-inf")]
|
|
batch: list[tuple] = []
|
|
count = 0
|
|
|
|
export_features = []
|
|
|
|
with conn.cursor() as cur:
|
|
print(f"[{layer_name}] 开始导入 {source_path} ...")
|
|
for feature in features:
|
|
props = feature.get("properties") or {}
|
|
if not include_predicate(props):
|
|
continue
|
|
geom = feature.get("geometry") or {}
|
|
if geom.get("type") != "Polygon":
|
|
continue
|
|
coords = geom.get("coordinates") or []
|
|
min_lon, min_lat, max_lon, max_lat = cell_bbox_from_feature_coords(coords)
|
|
bbox[0] = min(bbox[0], min_lon)
|
|
bbox[1] = min(bbox[1], min_lat)
|
|
bbox[2] = max(bbox[2], max_lon)
|
|
bbox[3] = max(bbox[3], max_lat)
|
|
row_idx = int(props.get(row_key))
|
|
col_idx = int(props.get(col_key))
|
|
cell_id = str(props.get("cell_id") or f"{row_idx}:{col_idx}")
|
|
batch.append(
|
|
(
|
|
layer_name,
|
|
cell_id,
|
|
row_idx,
|
|
col_idx,
|
|
cell_size_m,
|
|
state_name,
|
|
class_name,
|
|
source_desc,
|
|
min_lon,
|
|
min_lat,
|
|
max_lon,
|
|
max_lat,
|
|
)
|
|
)
|
|
export_features.append(
|
|
{
|
|
"type": "Feature",
|
|
"properties": {
|
|
"layer_name": layer_name,
|
|
"cell_id": cell_id,
|
|
"row": row_idx,
|
|
"col": col_idx,
|
|
"cell_size_m": cell_size_m,
|
|
"state_name": state_name,
|
|
"class_name": class_name,
|
|
"source_name": source_desc,
|
|
},
|
|
"geometry": rect_geojson(min_lon, min_lat, max_lon, max_lat)["geometry"],
|
|
}
|
|
)
|
|
count += 1
|
|
if len(batch) >= 5000:
|
|
insert_cells(cur, batch)
|
|
conn.commit()
|
|
batch.clear()
|
|
if batch:
|
|
insert_cells(cur, batch)
|
|
conn.commit()
|
|
|
|
export_count = stream_geojson(export_path, export_features)
|
|
if export_count != count:
|
|
raise RuntimeError(f"export count mismatch for {layer_name}: {export_count} != {count}")
|
|
print(f"[{layer_name}] 导入完成：{count} 条，导出 {export_path}")
|
|
return count, (bbox[0], bbox[1], bbox[2], bbox[3])
|
|
|
|
|
|
def iter_hazard_cells(bbox: tuple[float, float, float, float]) -> Iterable[tuple[int, int]]:
|
|
lon_min, lat_min, lon_max, lat_max = bbox
|
|
n = 2**PBF_TILE_Z
|
|
x0 = max(0, int((lon_min + 180.0) / 360.0 * n))
|
|
x1 = min(n - 1, int((lon_max + 180.0) / 360.0 * n))
|
|
|
|
def ytile(lat: float) -> int:
|
|
return int((1.0 - math.asinh(math.tan(math.radians(lat))) / math.pi) / 2.0 * n)
|
|
|
|
y0 = max(0, ytile(lat_max))
|
|
y1 = min(n - 1, ytile(lat_min))
|
|
|
|
hazard_cells: set[tuple[int, int]] = set()
|
|
for tx in range(x0, x1 + 1):
|
|
for ty in range(y0, y1 + 1):
|
|
p = PBF_TILE_ROOT / f"{PBF_TILE_Z}/{tx}/{ty}.pbf"
|
|
if not p.exists():
|
|
continue
|
|
try:
|
|
tile = mapbox_vector_tile.decode(p.read_bytes())
|
|
except Exception:
|
|
continue
|
|
for layer_name in HAZARD_LAYERS:
|
|
layer = tile.get(layer_name)
|
|
if not layer:
|
|
continue
|
|
extent = int(layer.get("extent") or 1048576)
|
|
for feature in layer.get("features", []):
|
|
props = feature.get("properties") or {}
|
|
if props.get("canonical_object_type") == "fish_reef" or props.get("chart_symbol_code") == "fish_reef" or props.get("class_name") == "魚礁":
|
|
continue
|
|
geom = feature.get("geometry") or {}
|
|
if not geom:
|
|
continue
|
|
coords = geom.get("coordinates")
|
|
if not coords:
|
|
continue
|
|
minx, miny, maxx, maxy = tile_mercator_bounds(PBF_TILE_Z, tx, ty)
|
|
shp_coords = transform_geom_from_tile(coords, (minx, miny, maxx, maxy), extent)
|
|
shp = shape({"type": geom.get("type"), "coordinates": shp_coords})
|
|
if shp.is_empty:
|
|
continue
|
|
if layer_name in POINT_LAYERS:
|
|
shp = shp.buffer(25.0)
|
|
minx2, miny2, maxx2, maxy2 = shp.bounds
|
|
start_x = math.floor(minx2 / HAZARD_CELL_M) * HAZARD_CELL_M
|
|
start_y = math.floor(miny2 / HAZARD_CELL_M) * HAZARD_CELL_M
|
|
end_x = math.ceil(maxx2 / HAZARD_CELL_M) * HAZARD_CELL_M
|
|
end_y = math.ceil(maxy2 / HAZARD_CELL_M) * HAZARD_CELL_M
|
|
prep_geom = prep(shp)
|
|
cy = start_y
|
|
while cy < end_y:
|
|
cx = start_x
|
|
while cx < end_x:
|
|
cell = box(cx, cy, cx + HAZARD_CELL_M, cy + HAZARD_CELL_M)
|
|
if prep_geom.intersects(cell):
|
|
hazard_cells.add((int(round(cx / HAZARD_CELL_M)), int(round(cy / HAZARD_CELL_M))))
|
|
cx += HAZARD_CELL_M
|
|
cy += HAZARD_CELL_M
|
|
return sorted(hazard_cells, key=lambda item: (item[1], item[0]))
|
|
|
|
|
|
def tile_mercator_bounds(z: int, x: int, y: int) -> tuple[float, float, float, float]:
|
|
n = 2**z
|
|
lon_left = x / n * 360.0 - 180.0
|
|
lon_right = (x + 1) / n * 360.0 - 180.0
|
|
lat_top = math.degrees(math.atan(math.sinh(math.pi * (1 - 2 * y / n))))
|
|
lat_bottom = math.degrees(math.atan(math.sinh(math.pi * (1 - 2 * (y + 1) / n))))
|
|
return mercator_x(lon_left), mercator_y(lat_bottom), mercator_x(lon_right), mercator_y(lat_top)
|
|
|
|
|
|
def transform_geom_from_tile(geom, bounds_m: tuple[float, float, float, float], extent: int):
|
|
minx, miny, maxx, maxy = bounds_m
|
|
dx = maxx - minx
|
|
dy = maxy - miny
|
|
|
|
def coord(x: float, y: float):
|
|
mx = minx + (x / extent) * dx
|
|
my = miny + (y / extent) * dy
|
|
return mx, my
|
|
|
|
def walk(obj):
|
|
if isinstance(obj[0], (int, float)):
|
|
return coord(obj[0], obj[1])
|
|
return [walk(item) for item in obj]
|
|
|
|
return walk(geom)
|
|
|
|
|
|
def import_hazard_layer(
|
|
conn,
|
|
*,
|
|
layer_name: str,
|
|
description: str,
|
|
source_desc: str,
|
|
cell_size_m: float,
|
|
bbox: tuple[float, float, float, float],
|
|
export_path: Path,
|
|
) -> tuple[int, tuple[float, float, float, float]]:
|
|
cells = iter_hazard_cells(bbox)
|
|
batch: list[tuple] = []
|
|
count = 0
|
|
export_bbox = [float("inf"), float("inf"), float("-inf"), float("-inf")]
|
|
|
|
with conn.cursor() as cur:
|
|
print(f"[{layer_name}] 开始扫描 PBF 危险层 ...")
|
|
with export_path.open("w", encoding="utf-8") as fh:
|
|
fh.write('{"type":"FeatureCollection","features":[\n')
|
|
first = True
|
|
for ix, iy in cells:
|
|
min_lon, min_lat, max_lon, max_lat = cell_bbox_from_mercator(ix, iy, HAZARD_CELL_M)
|
|
export_bbox[0] = min(export_bbox[0], min_lon)
|
|
export_bbox[1] = min(export_bbox[1], min_lat)
|
|
export_bbox[2] = max(export_bbox[2], max_lon)
|
|
export_bbox[3] = max(export_bbox[3], max_lat)
|
|
cell_id = f"{ix}:{iy}"
|
|
batch.append(
|
|
(
|
|
layer_name,
|
|
cell_id,
|
|
iy,
|
|
ix,
|
|
cell_size_m,
|
|
"HAZARD_50M",
|
|
"HAZARD",
|
|
source_desc,
|
|
min_lon,
|
|
min_lat,
|
|
max_lon,
|
|
max_lat,
|
|
)
|
|
)
|
|
feature = {
|
|
"type": "Feature",
|
|
"properties": {
|
|
"layer_name": layer_name,
|
|
"cell_id": cell_id,
|
|
"row": iy,
|
|
"col": ix,
|
|
"cell_size_m": cell_size_m,
|
|
"state_name": "HAZARD_50M",
|
|
"class_name": "HAZARD",
|
|
"source_name": source_desc,
|
|
},
|
|
"geometry": rect_geojson(min_lon, min_lat, max_lon, max_lat)["geometry"],
|
|
}
|
|
if not first:
|
|
fh.write(",\n")
|
|
fh.write(json.dumps(feature, ensure_ascii=False))
|
|
first = False
|
|
count += 1
|
|
if len(batch) >= 5000:
|
|
insert_cells(cur, batch)
|
|
conn.commit()
|
|
batch.clear()
|
|
if count % 20000 == 0 and count > 0:
|
|
print(f"[{layer_name}] 已处理 {count} 个格子 ...")
|
|
if batch:
|
|
insert_cells(cur, batch)
|
|
conn.commit()
|
|
fh.write("\n]}\n")
|
|
|
|
print(f"[{layer_name}] 导入完成：{count} 条，导出 {export_path}")
|
|
return count, (export_bbox[0], export_bbox[1], export_bbox[2], export_bbox[3])
|
|
|
|
|
|
def main() -> None:
|
|
parser = argparse.ArgumentParser(description="Build NavSea Fukuoka/Saga grid into MySQL")
|
|
parser.add_argument(
|
|
"--out-dir",
|
|
default="src/pbf/coastline-mysql/fukuoka_saga",
|
|
help="GeoJSON 输出目录",
|
|
)
|
|
parser.add_argument(
|
|
"--db-name",
|
|
default=DB_NAME,
|
|
help="MySQL 数据库名",
|
|
)
|
|
args = parser.parse_args()
|
|
|
|
project_root = Path(__file__).resolve().parent.parent
|
|
out_dir = Path(args.out_dir)
|
|
if not out_dir.is_absolute():
|
|
out_dir = project_root / out_dir
|
|
out_dir.mkdir(parents=True, exist_ok=True)
|
|
|
|
ensure_database()
|
|
conn = mysql_connect(args.db_name)
|
|
try:
|
|
print(f"数据库 {args.db_name} 已连接，准备初始化表结构 ...")
|
|
ensure_schema(conn)
|
|
|
|
coast_path = project_root / COAST_GRID_SRC
|
|
fish_path = project_root / FISH_GRID_SRC
|
|
if not coast_path.exists():
|
|
raise SystemExit(f"missing coast grid source: {coast_path}")
|
|
if not fish_path.exists():
|
|
raise SystemExit(f"missing fish grid source: {fish_path}")
|
|
|
|
coast_export = out_dir / "coast_200m_grid.geojson"
|
|
fish_export = out_dir / "fish_port_20m_grid.geojson"
|
|
hazard_export = out_dir / "hazard_50m_grid.geojson"
|
|
|
|
coast_count, coast_bbox = import_geojson_layer(
|
|
conn,
|
|
layer_name="coast_200m",
|
|
description="福冈/佐贺海岸线 200m 硬阻塞格",
|
|
source_desc="coastline/C23-06_40_GML.zip + coastline/C23-06_41_GML.zip",
|
|
cell_size_m=COAST_CELL_M,
|
|
source_path=coast_path,
|
|
export_path=coast_export,
|
|
include_predicate=lambda props: True,
|
|
row_key="iy",
|
|
col_key="ix",
|
|
state_name="HARD_BLOCKED",
|
|
class_name="HARD_BLOCKED",
|
|
)
|
|
|
|
fish_count, fish_bbox = import_geojson_layer(
|
|
conn,
|
|
layer_name="fish_port_20m",
|
|
description="福冈/佐贺渔港 20m 不可航行红格",
|
|
source_desc="coastline/C09-06.zip",
|
|
cell_size_m=FISH_CELL_M,
|
|
source_path=fish_path,
|
|
export_path=fish_export,
|
|
include_predicate=lambda props: props.get("classification") == "LAND_BASE",
|
|
row_key="row",
|
|
col_key="col",
|
|
state_name="LAND_BASE",
|
|
class_name="LAND_BASE",
|
|
)
|
|
|
|
hazard_count, hazard_bbox = import_hazard_layer(
|
|
conn,
|
|
layer_name="hazard_50m",
|
|
description="福冈/佐贺 PBF 海上障碍 50m 黄格",
|
|
source_desc="pbf-delivery-kyushu-reencoded z12: navigation_hazard_area, fixed_fishing_gear_area, anchor_caution_hazard_area, navigation_hazard_point, anchor_caution_hazard_point, navigation_marks",
|
|
cell_size_m=HAZARD_CELL_M,
|
|
bbox=DEFAULT_BBOX,
|
|
export_path=hazard_export,
|
|
)
|
|
|
|
with conn.cursor() as cur:
|
|
upsert_meta(
|
|
cur,
|
|
"coast_200m",
|
|
"福冈/佐贺海岸线 200m 硬阻塞格",
|
|
"coastline/C23-06_40_GML.zip + coastline/C23-06_41_GML.zip",
|
|
COAST_CELL_M,
|
|
coast_count,
|
|
coast_bbox,
|
|
str(coast_export.relative_to(project_root)),
|
|
)
|
|
upsert_meta(
|
|
cur,
|
|
"fish_port_20m",
|
|
"福冈/佐贺渔港 20m 不可航行红格",
|
|
"coastline/C09-06.zip",
|
|
FISH_CELL_M,
|
|
fish_count,
|
|
fish_bbox,
|
|
str(fish_export.relative_to(project_root)),
|
|
)
|
|
upsert_meta(
|
|
cur,
|
|
"hazard_50m",
|
|
"福冈/佐贺 PBF 海上障碍 50m 黄格",
|
|
"pbf-delivery-kyushu-reencoded z12",
|
|
HAZARD_CELL_M,
|
|
hazard_count,
|
|
hazard_bbox,
|
|
str(hazard_export.relative_to(project_root)),
|
|
)
|
|
conn.commit()
|
|
|
|
manifest = {
|
|
"database": args.db_name,
|
|
"generated_at": dt.datetime.now().isoformat(timespec="seconds"),
|
|
"layers": {
|
|
"coast_200m": {
|
|
"count": coast_count,
|
|
"bbox_lonlat": list(coast_bbox),
|
|
"cell_size_m": COAST_CELL_M,
|
|
"export_file": str(coast_export.relative_to(project_root)),
|
|
},
|
|
"fish_port_20m": {
|
|
"count": fish_count,
|
|
"bbox_lonlat": list(fish_bbox),
|
|
"cell_size_m": FISH_CELL_M,
|
|
"export_file": str(fish_export.relative_to(project_root)),
|
|
},
|
|
"hazard_50m": {
|
|
"count": hazard_count,
|
|
"bbox_lonlat": list(hazard_bbox),
|
|
"cell_size_m": HAZARD_CELL_M,
|
|
"export_file": str(hazard_export.relative_to(project_root)),
|
|
},
|
|
},
|
|
}
|
|
manifest_path = out_dir / "manifest.json"
|
|
manifest_path.write_text(json.dumps(manifest, ensure_ascii=False, indent=2), encoding="utf-8")
|
|
|
|
print("已导入 MySQL 数据库:", args.db_name)
|
|
print(" - coast_200m:", coast_count, coast_export)
|
|
print(" - fish_port_20m:", fish_count, fish_export)
|
|
print(" - hazard_50m:", hazard_count, hazard_export)
|
|
print(" - manifest:", manifest_path)
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|