[pgrouting-dev] Proposal for OSRM access via PostgreSQL

[Stephen Woodbridge]

Hi all,

Below are a list of PostgreSQL stored procedure function definitions 
that could provide access to OSRM. Additional functions could be added 
to like pgr_tsp_osrm(points[], ...) return the the optimized path ot 
other useful higher level functions.

The functions below are mostly simple wrappers to access the osrm-routed 
server(s). A few of points to help understand the functions below.

1. osrm_id is a reference to a table like:

create table osrm_servers (
   id integer,
   url text,
   comment text
);

If it is set to -1 (default) the the assumed url is 
'http://localhost:5000/', but is you have multiple local servers setup 
on your network, then you can enter them in this table and pass in the 
id for the server you want this query to access.

2. osrm_viaroute returns a json document as text. It seems to me that 
the most efficient way to handle this is to just return the json 
document and let the caller save it in a variable or table or where 
ever. Then to provide functions to access the various objects of the 
document to extract those objects in a useful structure.

3. If it is not obvious to the reader, this extension will require 
postGIS extension because it is using geometry objects. This is also 
required for pgRouting.

4. There is an implicit assumption that everything is using SRID:4326. 
There might be a need to handle this more explicitly. For example, the 
point geometry input and the geometry outputs are assumed to be 
SRID:4326 and no attempt is made to transform them inside the C 
functions. This also assume that the lat/lon data in OSRM is in SRID:4326.

I am aware the postgresql 9.3 has json parsing support, so that might 
deprecate the osrm_jget_* functions in the future.

Looking for feedback on this proposal.
Any better or different ways of doing this?
Anything that is missing from your point of view?

Thoughts?
   -Steve

---------------------------------------------------------------------
-- Core functions to access OSRM from postgresql
-- Author: Stephen Woodbridge <woodbri (at) swoodbridge (dot) com>
-- Date: 2013-11-16
---------------------------------------------------------------------

DROP TYPE IF EXISTS osrm_instruction CASCADE;
CREATE TYPE osrm_instruction AS (
     rid integer,
     seq integer,
     direction integer,
     name text,
     meters integer,
     postion integer,
     time integer,
     length text,
     dir text,
     azimuth float
);

CREATE OR REPLACE FUNCTION osrm_locate(
         IN lat float8
         IN lon float8
         IN osrm_id integer default -1
         OUT m_lat float8,
         OUT m_lon float8
     ) RETURNS RECORD

CREATE OR REPLACE FUNCTION osrm_nearest(
         IN lat float8
         IN lon float8
         IN osrm_id integer default -1
         OUT m_lat float8,
         OUT m_lon float8,
         OUT name text
     ) RETURNS RECORD

CREATE OR REPLACE FUNCTION osrm_viaroute(
         IN pnts geometry[],
         IN alt boolean default false,
         IN instructions boolean default false,
         IN zoom integer default 18
         IN osrm_id integer default -1
     ) RETURNS TEXT

CREATE OR REPLACE FUNCTION osrm_dmatrix(
         IN pnts geometry[],
         IN dist boolean default false,
         IN osrm_id integer default -1
     ) RETURNS FLOAT8[]

CREATE OR REPLACE FUNCTION osrm_dmatrix(
         IN pnt geometry,
         IN pnts geometry[],
         IN dist boolean default false,
         IN osrm_id integer default -1
     ) RETURNS FLOAT8[]

CREATE OR REPLACE FUNCTION osrm_jget_version(
         IN json text
     ) RETURNS TEXT

CREATE OR REPLACE FUNCTION osrm_jget_status(
         IN json text,
         OUT status integer,
         OUT message text
     ) RETURNS RECORD

CREATE OR REPLACE FUNCTION osrm_jget_route(
         IN json text,
         IN alt boolean default false
         OUT rid integer,
         OUT geom geometry
     ) RETURNS SETOF RECORD

CREATE OR REPLACE FUNCTION osrm_jget_instructions(
         IN json text,
         IN alt boolean default false
     ) RETURNS SETOF osrm_instruction

CREATE OR REPLACE FUNCTION osrm_jget_summary(
         IN json text,
         IN alt boolean default false,
         OUT rid integer
         OUT tot_dist integer,
         OUT tot_time integer,
         OUT start_point text,
         OUT end_point text
     ) RETURNS SETOF RECORD

CREATE OR REPLACE FUNCTION osrm_jget_viapoints(
         IN json text
     ) RETURNS text

CREATE OR REPLACE FUNCTION osrm_jget_hints(
         IN json text
     ) RETURNS text