SQLRouter

The following script shows the length between two nodes based on the PostgreSQL infrastructure provided by a Mapnik installation.

#!/bin/bash

echo "select SUM(ST_length_spheroid(transform(way,4326),'SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG\",\"7030\"]]')) as length from planet_osm_roads where osm_id IN (select regexp_split_to_table(x0.id||','||xn.id, ',')::int from
planet_osm_ways as x0,"

for i in `seq 1 $(($3 - 1))`; do
        echo "planet_osm_ways as x$i,"
done

echo "planet_osm_ways as xn where
x0.nodes[1] = (
select osm_id from planet_osm_point where name = '$1' and (railway in ('halt', 'metro_station', 'tramstop'))
)
 
AND x0.nodes[array_upper(x0.nodes,1)] = x1.nodes[1] AND"

for i in `seq 1 $(($3 - 2))`; do
  echo "x$i.nodes[array_upper(x$i.nodes,1)] = x"$(( $i + 1 ))".nodes[1] AND"
done
 
echo "x"$(($3 - 1))".nodes[array_upper(x"$(($3 - 1))".nodes,1)] = xn.nodes[1] AND"

echo "xn.nodes[array_upper(xn.nodes,1)] = (
 select osm_id from planet_osm_point where name = '$2' and (railway in ('halt', 'metro_station', 'tramstop'))
 ));"

The script can be executed by:

sh sql-tmp "Wibautstraat" "Amstelstation" 2 | psql -U mapnikro osm

Since it is unknown how far the two nodes are from each other, the script needs to be run with an increasing number. In essence this is graph creation.

with temprouting (node
select nodes[1], nodes[array_upper(nodes,1)], 0 from planet_osm_ways where nodes[1] = (select osm_id from planet_osm_point where name = 'Amstelstation' and (railway in ('halt', 'metro_station', 'tramstop'))) union all select a.nodes[1], b.nodes[array_upper(nodes,1)], a.iteration+1 FROM temprouting

CREATE OR REPLACE FUNCTION skinkie_routing(int8, int8, boolean) RETURNS varchar AS 'DECLARE
itemid ALIAS FOR $1;
itemid2 ALIAS FOR $2;
forward ALIAS FOR $3;
itemfullname varchar(255);
itemrecord RECORD;
BEGIN
        IF forward = TRUE THEN
                select max(id), nodes[array_upper(nodes,1)] as nextstart INTO itemrecord from planet_osm_ways where nodes[1] = itemid AND id NOT IN (select regexp_split_to_table(itemfullname, '','')::int);
        ELSE
                select max(id), nodes[1] as nextstart INTO itemrecord from planet_osm_ways where nodes[array_upper(nodes,1)] = itemid AND id NOT IN (select regexp_split_to_table(itemfullname, '','')::int);
        END IF;
        itemfullname := itemrecord.id;

        IF itemrecord.nextstart <> itemid2 THEN
                RETURN itemfullname;
        ELSE IF itemrecord IS NOT NULL THEN
                RETURN itemfullname || '','' || skinkie_routing(itemrecord.nextstart, itemid2, TRUE);
        ELSE IF forward = TRUE THEN
                RETURN itemfullname || '','' || skinkie_routing(itemid, itemid2, FALSE);
        ELSE
                RETURN NULL;
        END IF;
        END IF;
        END IF;
END' LANGUAGE 'plpgsql';

    SELECT s.* INTO itemrecord FROM supplyitem s  where si_id=itemid;
     itemfullname := itemfullname + itemrecord.si_item;
     IF itemrecord.si_parentid IS NOT NULL  THEN
           itemfullname := cp_getitemfullname(itemrecord.si_parentid) + ''->'' + itemfullname ;
           RETURN itemfullname;
     ELSE
           RETURN itemfullname;
     END IF;
END'  LANGUAGE 'plpgsql'

select SUM(ST_length_spheroid(transform(way,4326),'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]')) as length from planet_osm_roads where osm_id IN (select regexp_split_to_table(skinkie_routing(3571269,301746256), ',')::int);