PostgreSQL/PostGIS/pgRouting - Implementation of Tobler's Hiking function for approximate hiking speeds with regard to terrain slope.

ST_ToblerSpeed.sql

/*
 * Public signatures common INPUT params:
 *  @track_mode - travel mode { 1: on_path | 2: off_path | 3: horse_back }; default 1
 *  @max_speed  - maximum travel speed: default 1.67 [m/s -> 6km/h]
 *
 * GEOMETRY X|Y components are assumed to be in meter!
 * GEOMETRY|GEOGRAPHY Z components are assumed to be in meter!
 * 
 * Public signatures common OUTPUT fields:
 *  @slope    - [degrees] inclination
 *  @gradient - [no unit] vertical to horizontal ratio
 *  @speed    - [m/s] approximate hikin speed
 *  @pace     - [s/m] approximate hiking pace
 *
 */
 
-- Internal implemetation of Tobler Hiking function
CREATE OR REPLACE FUNCTION _ST_ToblerSpeed(
  IN  delta_hz FLOAT,
  IN  delta_vt FLOAT,
  IN  trc_mode INT,
  IN  max_spd  FLOAT,
  OUT slope    FLOAT,
  OUT gradient FLOAT,
  OUT speed    FLOAT,
  OUT pace     FLOAT
) RETURNS RECORD AS
  $FUNCTION$
  BEGIN
    gradient := COALESCE(delta_vt / NULLIF(delta_hz, 0), 0);
    slope    := DEGREES(ATAN(gradient));
    speed    := ( max_spd * EXP(-3.5 * ABS(gradient + 0.05)) ) * ( ARRAY[1.0, 3.0 / 5.0, 5.0 / 4.0] )[trc_mode];
    pace     := 1.0 / speed;
  END;
  $FUNCTION$
LANGUAGE 'plpgsql'
IMMUTABLE STRICT
PARALLEL SAFE
;

-- Public function for Tobler metrics between two POINT Z; GEOMETRY version
DROP FUNCTION ST_ToblerSpeed(GEOMETRY(POINTZ),GEOMETRY(POINTZ),INT, FLOAT);
CREATE OR REPLACE FUNCTION ST_ToblerSpeed(
  IN  start_point GEOMETRY(POINTZ),
  IN  end_point   GEOMETRY(POINTZ),
  IN  track_mode  INT DEFAULT 1,
  IN  max_speed   FLOAT DEFAULT 1.67,
  OUT slope       FLOAT,
  OUT gradient    FLOAT,
  OUT speed       FLOAT,
  OUT pace        FLOAT
) RETURNS RECORD AS
  $FUNCTION$
  SELECT
    _ST_ToblerSpeed(
      ST_Distance(start_point, end_point),
      ST_Z(end_point) - ST_Z(start_point),
      track_mode,
      max_speed
    )
  ;
  $FUNCTION$
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
;

-- Public function for Tobler metrics between two POINT Z; GEOGRAPHY version
DROP FUNCTION ST_ToblerSpeed(GEOGRAPHY(POINTZ),GEOGRAPHY(POINTZ),INT, FLOAT);
CREATE OR REPLACE FUNCTION ST_ToblerSpeed(
  IN  start_point GEOGRAPHY(POINTZ),
  IN  end_point   GEOGRAPHY(POINTZ),
  IN  track_mode  INT DEFAULT 1,
  IN  max_speed   FLOAT DEFAULT 1.67,
  OUT slope       FLOAT,
  OUT gradient    FLOAT,
  OUT speed       FLOAT,
  OUT pace        FLOAT
) RETURNS RECORD AS
  $FUNCTION$
  SELECT
    _ST_ToblerSpeed(
      ST_Distance(start_point, end_point),
      ST_Z(end_point::GEOMETRY) - ST_Z(start_point::GEOMETRY),
      track_mode,
      max_speed
    )
  ;
  $FUNCTION$
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
;

-- Public function for Tobler metrics over LINESTRING Z; GEOMETRY version
CREATE OR REPLACE FUNCTION ST_ToblerSpeed(
  IN  track_path GEOMETRY(LINESTRINGZ),
  IN  track_mode INT DEFAULT 1,
  IN  max_speed  FLOAT DEFAULT 1.67,
  OUT slope      FLOAT,
  OUT gradient   FLOAT,
  OUT speed      FLOAT,
  OUT pace       FLOAT
) RETURNS RECORD AS
  $FUNCTION$
  SELECT
    AVG(ts.slope), AVG(ts.gradient), AVG(ts.speed), AVG(ts.pace)
  FROM
    Generate_Series(1, ST_NPoints(track_path)-1) AS pts,
    LATERAL
      _ST_ToblerSpeed(
        ST_Distance(ST_PointN(track_path, pts), ST_PointN(track_path, pts+1)),
	ST_Z(ST_PointN(track_path, pts+1)) - ST_Z(ST_PointN(track_path, pts)),
	track_mode,
	max_speed
      ) AS ts
    ;
  $FUNCTION$
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
;

-- Public function for Tobler metrics over LINESTRING Z; GEOGRAPHY version
CREATE OR REPLACE FUNCTION ST_ToblerSpeed(
  IN  track_path GEOGRAPHY(LINESTRINGZ),
  IN  track_mode INT DEFAULT 1,
  IN  max_speed  FLOAT DEFAULT 1.67,
  OUT slope      FLOAT,
  OUT gradient   FLOAT,
  OUT speed      FLOAT,
  OUT pace       FLOAT
) RETURNS RECORD AS
  $FUNCTION$
  SELECT
    AVG(ts.slope), AVG(ts.gradient), AVG(ts.speed), AVG(ts.pace)
  FROM
    Generate_Series(1, ST_NPoints(track_path)-1) AS pts,
    LATERAL
      _ST_ToblerSpeed(
	ST_Distance(ST_PointN(track_path, pts), ST_PointN(track_path, pts+1)),
	ST_Z(ST_PointN(track_path::GEOMETRY, pts+1)) - ST_Z(ST_PointN(track_path::GEOMETRY, pts)),
	track_mode,
	max_speed
      ) AS ts
  ;
  $FUNCTION$
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
;