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);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"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);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"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);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"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],...);
*/