[OSRM-talk] OSRM postgresql Wrapper Update
Stephen Woodbridge
woodbri at swoodbridge.com
Wed Nov 20 04:58:40 UTC 2013
Hi all,
I just want to give everyone an update on my progress as I have parts of
this working, but have to take some time off to work a funded project so
I can pay the bills.
I have the basic extension built and working for the commands I have
coded so far. This includes making calls out to the OSRM server and
parsing the json responses and return information as postgres records. I
also have a mechanism where by you can define multiple OSRM servers in a
table and then tell the command which server the query should use to
make it easy to manage different server configs, different data sources,
or different clients.
For routes, I return the json text and this can be saved in a table and
post processed to extract the information you want out of it. This has
some interesting benefits like you cache the json and can later extract
the route and/or the instructions without going back to the OSRM server.
For example think of the problem of doing a large TSP or VRP problem
where you need to compute 100s or 1000s of routes. These can be saved in
a temp table, the distances can be extracted into a distance matrix and
then analyzed. After the analysis you need the actual routes and
instructions, you can easily extract those from the cached json records
in your temp table. At the end of your analysis, your temp table is
dropped automatically keeping your database clean.
This is the strategy that I will be using for the osrm_dmatrix()
implementations when I get to them.
I probably have another 2-3 weeks effort to finish this up when I have
time to get back to work on it, but I'm very happy with the progress so
far and the fact that after 4 days of coding, I have been able to get
the basics working.
Thanks,
-Steve
Here are some sample queries for what I have working so far:
$ psql -U postgres -h localhost _osrm_test_ -f test.sql -a
\pset pager off
Pager usage is off.
--create extension postgis;
--create extension osrm;
drop table if exists json cascade;
DROP TABLE
create table json (
id serial not null primary key,
json text
);
psql:test.sql:9: NOTICE: CREATE TABLE will create implicit sequence
"json_id_seq" for serial column "json.id"
psql:test.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "json_pkey" for table "json"
CREATE TABLE
select * from osrm_locate(43.235198,-76.420898);
m_lat | m_lon
-----------+------------
43.235294 | -76.411765
(1 row)
select * from osrm_locate(43.235198,-76.420898, -1);
m_lat | m_lon
-----------+------------
43.235294 | -76.411765
(1 row)
select * from osrm_nearest(43.235198,-76.420898);
m_lat | m_lon | name
-----------+------------+------
43.235294 | -76.420897 | N20
(1 row)
select * from osrm_viaroute(array[43.235198,43.709579],
array[-76.420898,-76.286316], true, true);
osrm_viaroute
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"version": 0.3,"status":0,"status_message": "Found route between
points","route_geometry":
"{|zmqA`qjwpC?wyP?o{qBl{qB??m{qBm{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB??ufN","route_instructions":
[["10","N20",5506,0,2915,"5506m","E",90],["3","N60",6542,2,562,"6542m","S",180],["7","N2",4771,3,146,"4771m","E",90],["7","N61",58884,4,1797,"58884m","N",0],["3","N28",627,13,153,"627m","E",90],["15","",0,14,0,"","N",0.0]],"route_summary":{"total_distance":76332,"total_time":4676,"start_point":"N20","end_point":"N28"},"alternative_geometries":
[],"alternative_instructions":[],"alternative_summaries":[],"route_name":["N60","N61"],"alternative_names":[["",""]],"via_points":[[43.235294,-76.420897
],[43.705882,-76.286315 ]],"hint_data": {"checksum":52824373,
"locations": ["Dw0AAA4AAADwOAAAdgoAAJqulHdeCOs_3reTAt_ocfs",
"MRIAABYAAADkBgAAES0AAEeiWVys-sA_GuaaApX2c_s"]},"transactionId": "OSRM
Routing Engine JSON Descriptor (v0.3)"}
(1 row)
select * from osrm_viaroute(array[
st_makepoint(-76.420898,43.235198),
st_makepoint(-76.286316,43.709579)], true, true);
osrm_viaroute
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"version": 0.3,"status":0,"status_message": "Found route between
points","route_geometry":
"{|zmqA`qjwpC?wyP?o{qBl{qB??m{qBm{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB??ufN","route_instructions":
[["10","N20",5506,0,2915,"5506m","E",90],["3","N60",6542,2,562,"6542m","S",180],["7","N2",4771,3,146,"4771m","E",90],["7","N61",58884,4,1797,"58884m","N",0],["3","N28",627,13,153,"627m","E",90],["15","",0,14,0,"","N",0.0]],"route_summary":{"total_distance":76332,"total_time":4676,"start_point":"N20","end_point":"N28"},"alternative_geometries":
[],"alternative_instructions":[],"alternative_summaries":[],"route_name":["N60","N61"],"alternative_names":[["",""]],"via_points":[[43.235294,-76.420897
],[43.705882,-76.286315 ]],"hint_data": {"checksum":52824373,
"locations": ["Dw0AAA4AAADwOAAAdgoAAJqulHdeCOs_3reTAt_ocfs",
"MRIAABYAAADkBgAAES0AAEeiWVys-sA_GuaaApX2c_s"]},"transactionId": "OSRM
Routing Engine JSON Descriptor (v0.3)"}
(1 row)
select * from osrm_viaroute('{43.235198,43.709579}'::float8[],
'{-76.420898,-76.286316}'::float8[], true, true);
osrm_viaroute
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"version": 0.3,"status":0,"status_message": "Found route between
points","route_geometry":
"{|zmqA`qjwpC?wyP?o{qBl{qB??m{qBm{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB?o{qB?m{qB??ufN","route_instructions":
[["10","N20",5506,0,2915,"5506m","E",90],["3","N60",6542,2,562,"6542m","S",180],["7","N2",4771,3,146,"4771m","E",90],["7","N61",58884,4,1797,"58884m","N",0],["3","N28",627,13,153,"627m","E",90],["15","",0,14,0,"","N",0.0]],"route_summary":{"total_distance":76332,"total_time":4676,"start_point":"N20","end_point":"N28"},"alternative_geometries":
[],"alternative_instructions":[],"alternative_summaries":[],"route_name":["N60","N61"],"alternative_names":[["",""]],"via_points":[[43.235294,-76.420897
],[43.705882,-76.286315 ]],"hint_data": {"checksum":52824373,
"locations": ["Dw0AAA4AAADwOAAAdgoAAJqulHdeCOs_3reTAt_ocfs",
"MRIAABYAAADkBgAAES0AAEeiWVys-sA_GuaaApX2c_s"]},"transactionId": "OSRM
Routing Engine JSON Descriptor (v0.3)"}
(1 row)
insert into json (json) select * from
osrm_viaroute(array[43.235198,43.709579],
array[-76.420898::float8,-76.286316::float8]::float8[], true, true);
INSERT 0 1
select * from osrm_jget_version((select json from json where id=1));
osrm_jget_version
-------------------
0.300000
(1 row)
select * from osrm_jget_status((select json from json where id=1));
status | message
--------+----------------------------
0 | Found route between points
(1 row)
select * from osrm_jget_route((select json from json where id=1));
rid | seq | lat | lon
-----+-----+-----------+------------
0 | 1 | 43.235294 | -76.420897
0 | 2 | 43.235294 | -76.411765
0 | 3 | 43.235294 | -76.352941
0 | 4 | 43.176471 | -76.352941
0 | 5 | 43.176471 | -76.294118
0 | 6 | 43.235294 | -76.294118
0 | 7 | 43.294118 | -76.294118
0 | 8 | 43.352941 | -76.294118
0 | 9 | 43.411765 | -76.294118
0 | 10 | 43.470588 | -76.294118
0 | 11 | 43.529412 | -76.294118
0 | 12 | 43.588235 | -76.294118
0 | 13 | 43.647059 | -76.294118
0 | 14 | 43.705882 | -76.294118
0 | 15 | 43.705882 | -76.286315
(15 rows)
select * from osrm_jget_route((select json from json where id=1), false);
rid | seq | lat | lon
-----+-----+-----------+------------
0 | 1 | 43.235294 | -76.420897
0 | 2 | 43.235294 | -76.411765
0 | 3 | 43.235294 | -76.352941
0 | 4 | 43.176471 | -76.352941
0 | 5 | 43.176471 | -76.294118
0 | 6 | 43.235294 | -76.294118
0 | 7 | 43.294118 | -76.294118
0 | 8 | 43.352941 | -76.294118
0 | 9 | 43.411765 | -76.294118
0 | 10 | 43.470588 | -76.294118
0 | 11 | 43.529412 | -76.294118
0 | 12 | 43.588235 | -76.294118
0 | 13 | 43.647059 | -76.294118
0 | 14 | 43.705882 | -76.294118
0 | 15 | 43.705882 | -76.286315
(15 rows)
select * from osrm_jget_route((select json from json where id=1), true);
rid | seq | lat | lon
-----+-----+-----+-----
(0 rows)
/*
-- these still have to be coded
select * from osrm_jget_summary((select json from json where id=1), alt
:= false);
select * from osrm_jget_instructions((select json from json where id=1),
alt := false);
select * from osrm_jget_hints((select json from json where id=1));
select * from osrm_jget_route_name((select json from json where id=1),
alt := false);
select * from osrm_jget_via_points((select json from json where id=1),
alt := false);
-- N x N distance matrix
select * from osrm_dmatrix(array[43.235198,43.709579,...],
array[-76.420898,-76.286316,...]);
-- one to many distance matrix
select * from osrm_dmatrix(43.500846, -75.476632,
array[43.235198,43.709579,...], array[-76.420898,-76.286316],...);
*/
More information about the OSRM-talk
mailing list