The actual SQL behind "The Answer You Didn't Want" — Rounds 4 and 5

[ptzivras]

A reader of the DEV article asked for the real SQL from the two most instructive rounds. Fair ask. Here it is, from scripts/t27_q4.sql in the repo (commit a6c0d98):

https://github.com/argos-geo/kefalonia-digital-twin/blob/main/scripts/t27_q4.sql

Round 4: mixed SRIDs

Three different SRIDs were in play: argos.road_seg_vertices.geom in EPSG:32634 (UTM), argos.road_seg and argos.pois in 4326, and the raw planet_osm_* tables in 3857. The census check:

sql

SELECT 'road_seg_vertices' AS tbl, ST_SRID(geom) AS srid FROM argos.road_seg_vertices LIMIT 1
UNION ALL SELECT 'road_seg', ST_SRID(geom) FROM argos.road_seg LIMIT 1
UNION ALL SELECT 'pois', ST_SRID(geom) FROM argos.pois LIMIT 1
UNION ALL SELECT 'planet_osm_point', ST_SRID(way) FROM public.planet_osm_point LIMIT 1;
-- real output: 32634 / 4326 / 4326 / 3857

Comparing geometries across that mix without transforming does two kinds of damage: the PostGIS <-> KNN runs an internal ST_Distance recheck that errors on mixed SRIDs, and when the plan skips the recheck it silently picks a garbage anchor vertex. The fix in the production query is explicit transforms at every comparison point:

sql

WITH o AS (
  -- Fiskardo anchor point: planet_osm way is EPSG:3857, transform to 4326
  SELECT ST_Transform(p.way,4326) AS g FROM public.planet_osm_point p
  WHERE p.name LIKE '%ισκάρδ%' AND p.place IS NOT NULL LIMIT 1
),
anchor AS (
  -- routing vertices are EPSG:32634, transform to 4326 BEFORE the KNN
  SELECT v.id AS vid FROM argos.road_seg_vertices v, o
  ORDER BY ST_Transform(v.geom,4326) <-> o.g LIMIT 1
)

Note: ST_Transform inside the ORDER BY defeats the GiST index. Acceptable for a one-off anchor lookup on 17,468 vertices; for batch work you materialize transformed endpoints into a table with its own index first.

Round 5: the row-order bug

The wrong pattern, which I had written:

sql

-- WRONG: over one-to-many pgr_dijkstra output
SELECT r.end_vid, r.agg_cost
FROM pgr_dijkstra(
  'SELECT id, source, target, cost_m AS cost, rcost_m AS reverse_cost FROM argos.road_seg',
  <anchor_vid>,
  (SELECT array_agg(net_vid) FROM argos.pois
   WHERE poi_type IN ('ferry_terminal','marina') AND net_vid IS NOT NULL),
  true
) r
ORDER BY r.agg_cost
LIMIT 1;

In one-to-many mode every path's anchor row carries agg_cost = 0, so ORDER BY agg_cost LIMIT 1 returns the first row of an arbitrary path, not the cheapest destination. (Lixouri "won" by row-order luck before the fix.) The fixed pattern from the script:

sql

tot AS (
  -- agg_cost accumulates along each path, so the per-destination total
  -- is the MAX within each end_vid group
  SELECT end_vid, max(agg_cost) AS total_min FROM d0 GROUP BY end_vid
),
best AS (SELECT end_vid, total_min FROM tot ORDER BY total_min LIMIT 1)

Then the winning route is reconstructed by filtering to that end_vid and ordering by seq:

sql

SELECT d.seq, d.cost, rn.geom
FROM d0 d JOIN argos.road_seg rn ON rn.id = d.edge
WHERE d.end_vid = (SELECT end_vid FROM best) AND d.edge > 0
ORDER BY d.seq;

Two details worth keeping: d.edge > 0 drops pgRouting's terminal row per path (edge = -1, no geometry), and ORDER BY d.seq is what turns a pile of segments back into a drawn route.

Final answer from this exact script: Agia Effimia Marina. 29.0 km. 44 minutes. 116 segments.

The full file (three queries: stats JSON, route GeoJSON, origin/destination markers) is MIT-licensed in the repo; the pre-computed outputs it generates live at docs/assets/demo/.

Background reading: [The Answer You Didn't Want (DEV)](https://dev.to/ptzivras/the-answer-you-didnt-want-fiskardo-evacuation-routing-4hok)