282 lines
13 KiB
Python
282 lines
13 KiB
Python
#!/usr/bin/env python3
|
|
"""Sync only explicitly mapped OpenStreetMap entrances into place_gates.
|
|
|
|
Entrances are extracted from the same OSM PBF used for the routing/map update.
|
|
The importer associates a node only when it is covered by exactly one named
|
|
target facility polygon (hospital/clinic, mall, or government facility).
|
|
No coordinates or gate types are inferred from facility centroids.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import csv
|
|
import json
|
|
import os
|
|
import re
|
|
import subprocess
|
|
import sys
|
|
import tempfile
|
|
from collections import Counter
|
|
from pathlib import Path
|
|
|
|
|
|
APP_DIR = Path(__file__).resolve().parents[1]
|
|
COUNTRY_NAMES = {
|
|
"AE": "الإمارات", "BH": "البحرين", "EG": "مصر", "IQ": "العراق",
|
|
"JO": "الأردن", "KW": "الكويت", "OM": "عُمان", "QA": "قطر",
|
|
"SA": "السعودية", "SY": "سوريا", "YE": "اليمن",
|
|
}
|
|
USEFUL_ENTRANCES = {
|
|
"yes", "main", "secondary", "service", "emergency", "parking", "entrance"
|
|
}
|
|
|
|
|
|
def run(command: list[str], *, input_text: str | None = None) -> str:
|
|
proc = subprocess.run(
|
|
command, cwd=APP_DIR, input=input_text, text=True,
|
|
stdout=subprocess.PIPE, stderr=subprocess.PIPE,
|
|
)
|
|
if proc.returncode:
|
|
sys.stderr.write(proc.stderr)
|
|
raise RuntimeError(f"Command failed ({proc.returncode}): {' '.join(command)}")
|
|
return proc.stdout
|
|
|
|
|
|
def psql(sql: str) -> str:
|
|
return run([
|
|
"docker", "compose", "exec", "-T", "db", "psql", "-X", "-v",
|
|
"ON_ERROR_STOP=1", "-U", os.getenv("DB_USER", "mapuser"), "-d",
|
|
os.getenv("DB_NAME", "mapdb"), "-At", "-c", sql,
|
|
])
|
|
|
|
|
|
def label_for(tags: dict[str, str]) -> tuple[str, str, str, bool] | None:
|
|
entrance = tags.get("entrance", "").lower()
|
|
routing_entrance = tags.get("routing:entrance", "").lower()
|
|
barrier = tags.get("barrier", "").lower()
|
|
if (entrance not in USEFUL_ENTRANCES
|
|
and routing_entrance not in {"yes", "main"}
|
|
and barrier not in {"gate", "entrance"}):
|
|
return None
|
|
if tags.get("access", "").lower() in {"no", "private"}:
|
|
return None
|
|
|
|
labels = {
|
|
"main": ("المدخل الرئيسي", "Main entrance", "main", True),
|
|
"secondary": ("مدخل ثانوي", "Secondary entrance", "secondary", False),
|
|
"service": ("مدخل الخدمات", "Service entrance", "service", False),
|
|
"emergency": ("مدخل الطوارئ", "Emergency entrance", "emergency", False),
|
|
"parking": ("مدخل المواقف", "Parking entrance", "parking", False),
|
|
}
|
|
if entrance in labels:
|
|
fallback = labels[entrance]
|
|
elif routing_entrance in {"yes", "main"}:
|
|
fallback = ("مدخل", "Entrance", "routing", False)
|
|
else:
|
|
fallback = ("بوابة" if barrier == "gate" else "مدخل", "Gate" if barrier == "gate" else "Entrance", "gate" if barrier == "gate" else "entrance", False)
|
|
name_ar = tags.get("name:ar") or fallback[0]
|
|
name_en = tags.get("name:en") or tags.get("name") or fallback[1]
|
|
return name_ar, name_en, fallback[2], fallback[3]
|
|
|
|
|
|
def main() -> int:
|
|
parser = argparse.ArgumentParser(description="Import source-mapped OSM entrance nodes.")
|
|
parser.add_argument("--pbf", required=True, help="The current merged OSM PBF used by the map updater")
|
|
parser.add_argument("--dry-run", action="store_true", help="Extract and report exact matches without changing the live table")
|
|
args = parser.parse_args()
|
|
pbf_path = Path(args.pbf)
|
|
if not pbf_path.is_absolute():
|
|
pbf_path = APP_DIR / pbf_path
|
|
if not pbf_path.is_file():
|
|
parser.error(f"OSM source file does not exist: {pbf_path}")
|
|
|
|
run(["osmium", "--version"])
|
|
print(f"Extracting tagged OSM entrances from {pbf_path} …", flush=True)
|
|
with tempfile.TemporaryDirectory(prefix="siro-osm-gates-") as temp:
|
|
extracted = Path(temp) / "entrances.osm.pbf"
|
|
geojson_path = Path(temp) / "entrances.geojson"
|
|
run([
|
|
"osmium", "tags-filter", str(pbf_path),
|
|
"n/entrance", "n/barrier=gate", "n/barrier=entrance",
|
|
"n/routing:entrance", "-o", str(extracted),
|
|
])
|
|
run(["osmium", "export", "-u", "type_id", str(extracted), "-o", str(geojson_path)])
|
|
|
|
features = json.loads(geojson_path.read_text(encoding="utf-8")).get("features", [])
|
|
rows: list[tuple] = []
|
|
tag_counts: Counter[str] = Counter()
|
|
for feature in features:
|
|
osm_node_id = feature.get("id", "")
|
|
match = re.fullmatch(r"n(-?\d+)", str(osm_node_id))
|
|
geometry = feature.get("geometry") or {}
|
|
coordinates = geometry.get("coordinates") or []
|
|
tags = feature.get("properties") or {}
|
|
if not match or len(coordinates) < 2:
|
|
continue
|
|
classified = label_for(tags)
|
|
if classified is None:
|
|
continue
|
|
lon, lat = coordinates[:2]
|
|
rows.append((
|
|
int(match.group(1)), float(lon), float(lat),
|
|
tags.get("entrance"), tags.get("routing:entrance"), tags.get("barrier"),
|
|
tags.get("access"), tags.get("name"), tags.get("name:ar"),
|
|
tags.get("name:en"), *classified,
|
|
json.dumps(tags, ensure_ascii=False, separators=(",", ":")),
|
|
))
|
|
tag_counts[tags.get("entrance") or tags.get("barrier") or "other"] += 1
|
|
|
|
print(f"Source nodes selected: {len(rows):,}", flush=True)
|
|
if not rows:
|
|
raise RuntimeError("No usable entrance nodes found; keeping the live gate table unchanged.")
|
|
|
|
# Stage exact WGS84 source points. This table is not read by the API.
|
|
psql("""
|
|
CREATE TABLE IF NOT EXISTS place_gates_osm_stage (
|
|
osm_id bigint PRIMARY KEY, longitude double precision NOT NULL,
|
|
latitude double precision NOT NULL, entrance text, routing_entrance text,
|
|
barrier text, access text, name text, name_ar text, name_en text,
|
|
gate_name_ar text NOT NULL, gate_name_en text NOT NULL,
|
|
gate_type text NOT NULL, is_main_gate boolean NOT NULL,
|
|
source_tags jsonb NOT NULL
|
|
);
|
|
TRUNCATE place_gates_osm_stage;
|
|
""")
|
|
with tempfile.TemporaryFile(mode="w+t", encoding="utf-8", newline="") as tsv:
|
|
writer = csv.writer(tsv, delimiter="\t", lineterminator="\n", quoting=csv.QUOTE_MINIMAL)
|
|
writer.writerows(rows)
|
|
tsv.seek(0)
|
|
run([
|
|
"docker", "compose", "exec", "-T", "db", "psql", "-X", "-v",
|
|
"ON_ERROR_STOP=1", "-U", os.getenv("DB_USER", "mapuser"), "-d",
|
|
os.getenv("DB_NAME", "mapdb"), "-c",
|
|
"COPY place_gates_osm_stage (osm_id,longitude,latitude,entrance,routing_entrance,barrier,access,name,name_ar,name_en,gate_name_ar,gate_name_en,gate_type,is_main_gate,source_tags) FROM STDIN WITH (FORMAT csv, DELIMITER E'\\t', NULL '')",
|
|
], input_text=tsv.read())
|
|
|
|
psql("""
|
|
ALTER TABLE place_gates_osm_stage ADD COLUMN IF NOT EXISTS geom geometry(Point,3857);
|
|
UPDATE place_gates_osm_stage
|
|
SET geom=ST_Transform(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),3857);
|
|
CREATE INDEX IF NOT EXISTS place_gates_osm_stage_geom_idx
|
|
ON place_gates_osm_stage USING gist (geom);
|
|
ANALYZE place_gates_osm_stage;
|
|
""")
|
|
|
|
print("Matching source nodes to OSM facility polygons by exact polygon coverage …", flush=True)
|
|
report_sql = """
|
|
WITH facilities AS (
|
|
SELECT p.osm_id, p.way, r.country_code,
|
|
'regional_' || r.country_code || '_osm_polygon_' || r.source_id AS place_id
|
|
FROM planet_osm_polygon p
|
|
JOIN regional_place_names r
|
|
ON r.source = 'osm_polygon' AND r.source_id = p.osm_id::text
|
|
WHERE p.name IS NOT NULL
|
|
AND (p.amenity IN ('hospital','clinic','townhall','courthouse')
|
|
OR p.shop = 'mall' OR p.office = 'government' OR p.building = 'government')
|
|
), matched AS (
|
|
SELECT g.osm_id AS gate_osm_id, f.country_code, f.place_id
|
|
FROM place_gates_osm_stage g
|
|
JOIN facilities f
|
|
ON ST_Covers(f.way, g.geom)
|
|
), unique_matches AS (
|
|
SELECT gate_osm_id, min(country_code) AS country_code, min(place_id) AS place_id
|
|
FROM matched GROUP BY gate_osm_id HAVING count(DISTINCT place_id) = 1
|
|
)
|
|
SELECT country_code || '|' || count(*) || '|' || count(DISTINCT place_id)
|
|
FROM unique_matches GROUP BY country_code ORDER BY country_code;
|
|
"""
|
|
report = psql(report_sql).strip().splitlines()
|
|
for line in report:
|
|
code, gates, places = line.split("|")
|
|
print(f"{COUNTRY_NAMES.get(code, code)} ({code}): {gates} مصدرًا موثقًا ↔ {places} منشأة", flush=True)
|
|
if not report:
|
|
raise RuntimeError("No source nodes matched named target facilities; the live gate table is unchanged.")
|
|
|
|
samples = psql("""
|
|
WITH matches AS (
|
|
SELECT g.osm_id,g.gate_name_ar,g.gate_type,r.country_code,r.name,
|
|
'regional_'||r.country_code||'_osm_polygon_'||r.source_id AS place_id
|
|
FROM place_gates_osm_stage g
|
|
JOIN planet_osm_polygon p
|
|
ON ST_Covers(p.way,g.geom)
|
|
JOIN regional_place_names r ON r.source='osm_polygon' AND r.source_id=p.osm_id::text
|
|
WHERE p.name IS NOT NULL
|
|
AND (p.amenity IN ('hospital','clinic','townhall','courthouse') OR p.shop='mall'
|
|
OR p.office='government' OR p.building='government')
|
|
), unique_ids AS (
|
|
SELECT osm_id,min(place_id) AS place_id FROM matches
|
|
GROUP BY osm_id HAVING count(DISTINCT place_id)=1
|
|
)
|
|
SELECT m.country_code||'|'||m.name||'|'||m.gate_name_ar||'|'||m.gate_type||'|'||m.osm_id
|
|
FROM matches m JOIN unique_ids u ON u.osm_id=m.osm_id AND u.place_id=m.place_id
|
|
ORDER BY m.country_code,m.name LIMIT 12;
|
|
""").strip()
|
|
if samples:
|
|
print("Source-matched examples (country|facility|gate label|OSM tag type|node id):", flush=True)
|
|
print(samples, flush=True)
|
|
if args.dry_run:
|
|
print("Dry run complete; live place_gates was not changed.", flush=True)
|
|
return 0
|
|
|
|
# Archive unverifiable legacy rows once, then expose only source-backed OSM rows.
|
|
sync_sql = """
|
|
BEGIN;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS source text;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS source_id text;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS gate_type text;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS access text;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS source_tags jsonb;
|
|
ALTER TABLE place_gates ADD COLUMN IF NOT EXISTS last_seen_at timestamptz;
|
|
CREATE TABLE IF NOT EXISTS place_gates_unverified_archive_20260929 AS
|
|
SELECT * FROM place_gates WHERE source IS NULL AND false;
|
|
INSERT INTO place_gates_unverified_archive_20260929
|
|
SELECT * FROM place_gates WHERE source IS NULL;
|
|
DELETE FROM place_gates WHERE source IS NULL OR source = 'openstreetmap';
|
|
|
|
WITH facilities AS (
|
|
SELECT p.osm_id, p.way, r.country_code,
|
|
'regional_' || r.country_code || '_osm_polygon_' || r.source_id AS place_id
|
|
FROM planet_osm_polygon p
|
|
JOIN regional_place_names r
|
|
ON r.source = 'osm_polygon' AND r.source_id = p.osm_id::text
|
|
WHERE p.name IS NOT NULL
|
|
AND (p.amenity IN ('hospital','clinic','townhall','courthouse')
|
|
OR p.shop = 'mall' OR p.office = 'government' OR p.building = 'government')
|
|
), matches AS (
|
|
SELECT g.*, f.place_id
|
|
FROM place_gates_osm_stage g
|
|
JOIN facilities f
|
|
ON ST_Covers(f.way, g.geom)
|
|
), unique_matches AS (
|
|
SELECT osm_id, min(place_id) AS place_id FROM matches
|
|
GROUP BY osm_id HAVING count(DISTINCT place_id) = 1
|
|
), exact_matches AS (
|
|
SELECT DISTINCT ON (m.osm_id) m.*
|
|
FROM matches m JOIN unique_matches u ON u.osm_id=m.osm_id AND u.place_id=m.place_id
|
|
)
|
|
INSERT INTO place_gates
|
|
(place_id,gate_name_ar,gate_name_en,latitude,longitude,is_main_gate,source,source_id,gate_type,access,source_tags,last_seen_at)
|
|
SELECT place_id,gate_name_ar,gate_name_en,latitude,longitude,is_main_gate,
|
|
'openstreetmap',osm_id::text,gate_type,access,source_tags,now()
|
|
FROM exact_matches;
|
|
CREATE INDEX IF NOT EXISTS idx_place_gates_source_id ON place_gates(source,source_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_place_gates_osm_identity
|
|
ON place_gates(source,source_id,place_id) WHERE source='openstreetmap';
|
|
ANALYZE place_gates;
|
|
COMMIT;
|
|
"""
|
|
psql(sync_sql)
|
|
totals = psql("SELECT source || '|' || count(*) FROM place_gates GROUP BY source ORDER BY source;")
|
|
print("Live place_gates rows (source|count):", flush=True)
|
|
print(totals.strip(), flush=True)
|
|
return 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
try:
|
|
raise SystemExit(main())
|
|
except Exception as exc: # preserve the existing live table on any failed preflight/import
|
|
print(f"ERROR: {exc}", file=sys.stderr)
|
|
raise SystemExit(1)
|