120 lines
5.0 KiB
Bash
120 lines
5.0 KiB
Bash
#!/bin/bash
|
||
# --------------------------------------------------------------------------
|
||
# apply-delta.sh
|
||
# Exports approved candidate_roads from PostGIS as OSM XML, then merges
|
||
# them into master_map.osm.pbf so GraphHopper routes on them.
|
||
#
|
||
# Usage: bash apply-delta.sh [APP_DIR] [MASTER_PBF] [DELTA_OSM]
|
||
# --------------------------------------------------------------------------
|
||
set -e
|
||
|
||
APP_DIR="${1:-/home/hamzadoctor/app}"
|
||
MASTER_PBF="${2:-${APP_DIR}/infrastructure/osm-data/master_map.osm.pbf}"
|
||
DELTA_OSM="${3:-${APP_DIR}/infrastructure/osm-data/delta.osm}"
|
||
MERGED_PBF="${MASTER_PBF%.pbf}_merged.pbf"
|
||
|
||
cd "${APP_DIR}"
|
||
|
||
echo "🛣️ Exporting approved roads from PostGIS → ${DELTA_OSM}..."
|
||
|
||
# Build valid OSM XML in pure SQL (nodes declared before ways, matching negative
|
||
# IDs). No python/psycopg2 is needed in the db container. Source is approved_roads
|
||
# (snapped-to-network geometry, EPSG:4326); each LineString becomes one <way>.
|
||
#
|
||
# Junction connectivity: if a road has start_node/end_node (real OSM node ids the
|
||
# API resolved at approval time), the first/last <nd ref> points at that REAL node
|
||
# instead of a fresh one — so after osmium-merge the way shares a node with the
|
||
# existing highway and GraphHopper routes THROUGH it. Interior vertices (and any
|
||
# endpoint with no resolved node) get fresh negative-id nodes as before.
|
||
docker compose exec -T db psql -U mapuser -d mapdb -t -A -X > "${DELTA_OSM}" 2>/dev/null <<'SQL'
|
||
WITH ways AS (
|
||
SELECT id, confidence, "uniqueDriverCount" AS drivers,
|
||
COALESCE(highway, 'residential') AS highway,
|
||
COALESCE(oneway, 0) AS oneway,
|
||
start_node, end_node,
|
||
ROW_NUMBER() OVER (ORDER BY id) AS way_seq
|
||
FROM approved_roads
|
||
),
|
||
pts AS (
|
||
SELECT w.way_seq, w.confidence, w.drivers, w.highway, w.oneway, w.start_node, w.end_node,
|
||
dp.path[1] AS pt_order,
|
||
COUNT(*) OVER (PARTITION BY w.way_seq) AS npts,
|
||
ST_Y(dp.geom) AS lat, ST_X(dp.geom) AS lon
|
||
FROM ways w
|
||
JOIN approved_roads ar ON ar.id = w.id,
|
||
LATERAL ST_DumpPoints(ar.geometry) AS dp
|
||
),
|
||
pts_numbered AS (
|
||
SELECT *, ROW_NUMBER() OVER (ORDER BY way_seq, pt_order) AS gseq FROM pts
|
||
),
|
||
pts_ref AS (
|
||
SELECT *,
|
||
CASE WHEN pt_order = 1 AND start_node IS NOT NULL THEN start_node
|
||
WHEN pt_order = npts AND end_node IS NOT NULL THEN end_node
|
||
ELSE -1000000 - gseq END AS node_ref,
|
||
NOT ( (pt_order = 1 AND start_node IS NOT NULL)
|
||
OR (pt_order = npts AND end_node IS NOT NULL) ) AS emit_node
|
||
FROM pts_numbered
|
||
),
|
||
nodes_xml AS (
|
||
SELECT string_agg(
|
||
format(' <node id="%s" version="1" lat="%s" lon="%s"/>', node_ref, lat, lon),
|
||
E'\n' ORDER BY gseq) AS x
|
||
FROM pts_ref WHERE emit_node
|
||
),
|
||
ways_xml AS (
|
||
SELECT string_agg(w.x, E'\n') AS x FROM (
|
||
SELECT format(
|
||
' <way id="%s" version="1">%s%s%s%s%s%s </way>',
|
||
-way_seq,
|
||
string_agg(format(E'\n <nd ref="%s"/>', node_ref), '' ORDER BY pt_order),
|
||
format(E'\n <tag k="highway" v="%s"/>', min(highway)),
|
||
E'\n <tag k="source" v="intaleq:telemetry"/>',
|
||
format(E'\n <tag k="confidence" v="%s"/>', round(min(confidence)::numeric, 2)),
|
||
format(E'\n <tag k="intaleq:drivers" v="%s"/>', min(drivers)),
|
||
CASE WHEN min(oneway) = 1 THEN E'\n <tag k="oneway" v="yes"/>'
|
||
WHEN min(oneway) = -1 THEN E'\n <tag k="oneway" v="-1"/>'
|
||
ELSE '' END
|
||
) AS x
|
||
FROM pts_ref GROUP BY way_seq
|
||
) w
|
||
)
|
||
SELECT format(
|
||
E'<?xml version="1.0" encoding="UTF-8"?>\n<osm version="0.6" generator="intaleq-delta">\n%s\n%s\n</osm>',
|
||
COALESCE((SELECT x FROM nodes_xml), ''),
|
||
COALESCE((SELECT x FROM ways_xml), '')
|
||
);
|
||
SQL
|
||
|
||
# Bail out cleanly if there are no approved roads yet.
|
||
if ! grep -q '<way ' "${DELTA_OSM}" 2>/dev/null; then
|
||
echo "ℹ️ No approved roads to export. Skipping delta merge."
|
||
rm -f "${DELTA_OSM}"
|
||
exit 0
|
||
fi
|
||
|
||
echo "✅ Exported $(grep -c '<way ' "${DELTA_OSM}") approved road(s) to OSM XML."
|
||
|
||
# Merge delta into master PBF using osmium
|
||
if command -v osmium &> /dev/null; then
|
||
# osmium merge requires inputs sorted by (type, id). Our SQL emits nodes in
|
||
# descending-id order, so sort the delta first — otherwise merge is undefined.
|
||
SORTED_OSM="${DELTA_OSM%.osm}_sorted.osm"
|
||
echo "🔃 Sorting delta (osmium requires sorted input)..."
|
||
osmium sort "${DELTA_OSM}" -o "${SORTED_OSM}" --overwrite
|
||
|
||
echo "🔀 Merging delta → ${MASTER_PBF} with osmium..."
|
||
osmium merge "${MASTER_PBF}" "${SORTED_OSM}" -o "${MERGED_PBF}" --overwrite
|
||
mv "${MERGED_PBF}" "${MASTER_PBF}"
|
||
rm -f "${SORTED_OSM}"
|
||
echo "✅ Master PBF updated with approved roads (endpoints share real OSM nodes where resolved)."
|
||
else
|
||
echo "⚠️ osmium not found on host."
|
||
# On Debian/Ubuntu: apt-get install -y osmium-tool
|
||
# On Mac: brew install osmium-tool
|
||
echo " Please install osmium-tool: https://osmcode.org/osmium-tool/"
|
||
echo " Approved roads saved to ${DELTA_OSM} for manual merge."
|
||
fi
|
||
|
||
echo "🏁 Delta apply complete."
|