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