[pgrouting-users] question : Shortest Path Shooting star using python

bino_oetomo · February 3, 2012, 3:41am

Dear all ...
I learning from http://workshop.pgrouting.org/chapters/php_server.html , but need to adopt it to python.

I want to count the total length of route between :
--Start at lat/lon = -7.779954532607184,110.37820327878194
--end at lat/lon = -7.777881661899044,110.36775339245992

First I try to got nearest edges , using :
--------START----------
------start_edge
SELECT gid, source, target, the_geom,
             distance(the_geom, GeometryFromText(
             'POINT(110.378203279 -7.77995453261)', 4326)) AS dist
             FROM "ways"
             WHERE the_geom && setsrid(
             'BOX3D(110.278203279 -7.87995453261,110.478203279 -7.67995453261)'::box3d, 4326)
             ORDER BY dist LIMIT 1 ;
------end_edge
SELECT gid, source, target, the_geom,
             distance(the_geom, GeometryFromText(
             'POINT(110.367753392 -7.7778816619)', 4326)) AS dist
             FROM "ways"
             WHERE the_geom && setsrid(
             'BOX3D(110.267753392 -7.8778816619,110.467753392 -7.6778816619)'::box3d, 4326)
             ORDER BY dist LIMIT 1 ;
--------STOP-----------

I got 195690 as my start-edge gid and 11690 end_edge gid

for 'Shortest Path Shooting*', my sql query :
------Start-------
SELECT rt.gid, ST_AsGeoJSON(rt.the_geom) AS geojson,
         length(rt.the_geom) AS length, ways.gid
         FROM "ways",
         (SELECT gid, the_geom
         FROM shootingstar_sp(
         'ways',
         195690, 11690,
         0.1, 'length', true, true)
         ) as rt
         WHERE ways.gid=rt.gid;
------stop-----------

I got returns lines like :
-------START------------
(195690, '{"type":"MultiLineString","coordinates":[[[110.3776917,-7.7799519],[110.378560500000006,-7.7800485]]]}', 0.00087415387662070204, 195690)
(228202, '{"type":"MultiLineString","coordinates":[[[110.377695599999996,-7.7799235],[110.3776917,-7.7799519]]]}', 2.8666531007453801e-05, 228202)
(226891, '{"type":"MultiLineString","coordinates":[[[110.375391800000003,-7.7796208],[110.377695599999996,-7.7799235]]]}', 0.0023236010264171799, 226891)
(208736, '{"type":"MultiLineString","coordinates":[[[110.375391800000003,-7.7796208],[110.375979000000001,-7.7762031]]]}', 0.0034677769723553001, 208736)
(151742, '{"type":"MultiLineString","coordinates":[[[110.375979000000001,-7.7762031],[110.375815299999999,-7.7761449],[110.375752199999994,-7.7759897]]]}', 0.00034127511834667598, 151742)
(151712, '{"type":"MultiLineString","coordinates":[[[110.375752199999994,-7.7759897],[110.375602700000002,-7.7761528],[110.374589200000003,-7.7761589],[110.374242199999998,-7.7760816]]]}', 0.0015902747114182199, 151712)
(211523, '{"type":"MultiLineString","coordinates":[[[110.374242199999998,-7.7760816],[110.372598400000001,-7.7754287]]]}', 0.0017687161586837799, 211523)
(7597, '{"type":"MultiLineString","coordinates":[[[110.371240599999993,-7.7785458],[110.371377600000002,-7.7783286],[110.371773099999999,-7.7769228],[110.372203200000001,-7.7762957],[110.372598400000001,-7.7754287]]]}', 0.00343041625268363, 7597)
(213679, '{"type":"MultiLineString","coordinates":[[[110.371114800000001,-7.7785313],[110.371240599999993,-7.7785458]]]}', 0.00012663289461284301, 213679)
(213678, '{"type":"MultiLineString","coordinates":[[[110.370803300000006,-7.7784871],[110.371114800000001,-7.7785313]]]}', 0.00031462023138529102, 213678)
(213676, '{"type":"MultiLineString","coordinates":[[[110.369984299999999,-7.7784011],[110.370803300000006,-7.7784871]]]}', 0.00082350288403354205, 213676)
(180690, '{"type":"MultiLineString","coordinates":[[[110.3675791,-7.7781225],[110.369984299999999,-7.7784011]]]}', 0.0024212816853872699, 180690)
(11690, '{"type":"MultiLineString","coordinates":[[[110.3675791,-7.7781225],[110.369018800000006,-7.7665256],[110.369872299999997,-7.7544829],[110.371099400000006,-7.7512179]]]}', 0.027246810615796702, 11690)
-------STOP-------------

Taking the third element of each line (that is '0.00087415387662070204' for the first line) as what I think as 'road length',
I got total value of 0.0447577289587.

If '0.0447577289587' the total length of route, what is it's 'Unit of measure' ? How to got value in 'Kilometers/meters' ?

Note : When I try to count the route using same start Lat/Lon and end lat/lon, google map said it was about 2.1 km

Sincerely
-bino-

dkastl · February 3, 2012, 4:18am

Taking the third element of each line (that is ‘0.00087415387662070204’ for the first line) as what I think as ‘road length’,
I got total value of 0.0447577289587.

If ‘0.0447577289587’ the total length of route, what is it’s ‘Unit of measure’ ?

The unit is the unit of your projection. It’s probably degree in your case.

How to got value in ‘Kilometers/meters’ ?

You can transform your projection for example to some meter projection like “World Mercator” (ie. EPSG:3785).
Then length(geometry) will be in meter.

Like “SELECT length(ST_transform(,3785)) AS meter FROM

”.

Daniel

Note : When I try to count the route using same start Lat/Lon and end lat/lon, google map said it was about 2.1 km

Sincerely
-bino-

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

bino_oetomo · February 3, 2012, 4:47am

Dear Daniel and all
I really appreciate your enlightment

The unit is the unit of your projection. It's probably degree in your case.

You can transform your projection for example to some meter projection like
"World Mercator" (ie. EPSG:3785).
Then length(geometry) will be in meter.

Like "SELECT length(ST_transform(<geometry column>,3785)) AS meter FROM
<table>".

I tried to change the query to :
—START----
SELECT rt.gid, ST_AsGeoJSON(rt.the_geom) AS geojson,
length(ST_transform(rt.the_geom,3785)) AS length, ways.gid
FROM “ways”,
(SELECT gid, the_geom
FROM shootingstar_sp(
‘ways’,
195690, 11690,
0.1, ‘length’, true, true)
) as rt
WHERE ways.gid=rt.gid;
----STOP----

but I got error, looks like that ‘3785’ is not listed in ‘spatial_ref_sys’ :
—Start—
Traceback (most recent call last):
File “routecalc01a.py”, line 21, in
cur.execute(mysqlquery)
psycopg2.InternalError: AddToPROJ4SRSCache: Cannot find SRID (3785) in spatial_ref_sys
----stop—

How to add that projection to spatial_ref_sys ?
Anyway … my data is for indonesia are, and I want the unit is in meter. What projection is the right one ?

Sincerely
-bino-

Stephen_Woodbridge1 · February 3, 2012, 4:58am

You do not even need to do the transform if you use:

  select sum(ST_Length2D_Spheroid(the_geom,
             'SPHEROID["GRS_1980",6378137,298.257222101]'))
  FROM shootingstar_sp(
  'ways',
  195690, 11690,
  0.1, 'length', true, true)
  ) as rt
  WHERE ways.gid=rt.gid;

Also, You should be aware that shootingstar_sp() function is broken in v1.03 and v1.05.

-Steve

On 2/2/2012 11:47 PM, bino oetomo wrote:

Dear Daniel and all
I really appreciate your enlightment
On 02/03/2012 11:18 AM, Daniel Kastl wrote:

The unit is the unit of your projection. It's probably degree in your case.

You can transform your projection for example to some meter projection like
"World Mercator" (ie. EPSG:3785).
Then length(geometry) will be in meter.

Like"SELECT length(ST_transform(<geometry column>,3785)) AS meter FROM
<table>".

I tried to change the query to :
---START----
SELECT rt.gid, ST_AsGeoJSON(rt.the_geom) AS geojson,
length(ST_transform(rt.the_geom,3785)) AS length, ways.gid
FROM "ways",
(SELECT gid, the_geom
FROM shootingstar_sp(
'ways',
195690, 11690,
0.1, 'length', true, true)
) as rt
WHERE ways.gid=rt.gid;
----STOP----

but I got error, looks like that '3785' is not listed in 'spatial_ref_sys' :
---Start---
Traceback (most recent call last):
File "routecalc01a.py", line 21, in <module>
cur.execute(mysqlquery)
psycopg2.InternalError: AddToPROJ4SRSCache: Cannot find SRID (3785) in
spatial_ref_sys
----stop---

How to add that projection to spatial_ref_sys ?
Anyway .. my data is for indonesia are, and I want the unit is in meter.
What projection is the right one ?

Sincerely
-bino-

Daniel

Note : When I try to count the route using same start Lat/Lon and end
lat/lon, google map said it was about 2.1 km

Sincerely
-bino-

______________________________**_________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.**org <mailto:Pgrouting-users@lists.osgeo.**org> <Pgrouting-users@lists.osgeo.org> <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/**mailman/listinfo/pgrouting-**users<http://lists.osgeo.org/mailman/listinfo/pgrouting-users>

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

bino_oetomo · February 3, 2012, 5:51am

Dear Stephen.
I really appreciate your enlightment.
On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:

You do not even need to do the transform if you use:

select sum(ST_Length2D_Spheroid(the_geom,
'SPHEROID["GRS_1980",6378137,298.257222101]'))
FROM shootingstar_sp(
'ways',
195690, 11690,
0.1, 'length', true, true)
) as rt
WHERE ways.gid=rt.gid;

When I try to use your query , I got
---Start---
ERROR: syntax error at or near ")"
LINE 7: ) as rt
^

********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Character: 179

--Stop---

Next I try to change it to :

--Start---
select sum(ST_Length2D_Spheroid(the_geom,
             'SPHEROID["GRS_1980",6378137,298.257222101]'))
  FROM shootingstar_sp(
  'ways',
  195690, 11690,
  0.1, 'length', true, true)
  as rt
  WHERE ways.gid=rt.gid;
---Stop---

and I got :
---Start---
ERROR: missing FROM-clause entry for table "ways"
LINE 8: WHERE ways.gid=rt.gid;
^

********** Error **********

ERROR: missing FROM-clause entry for table "ways"
SQL state: 42P01
Character: 192
---Stop---

ANother try , I change it to :
--Start---
select sum(ST_Length2D_Spheroid(the_geom,
             'SPHEROID["GRS_1980",6378137,298.257222101]'))
  FROM shootingstar_sp(
  'ways',
  195690, 11690,
  0.1, 'length', true, true)
  as rt
  WHERE gid=rt.gid;
---Stop---

No Error, but the result is '4946.66004129405' , is it in meter ?
If it is in meter, I believe it's wrong result .. since that route is my daily route.

Sincerely
-bino-

Also, You should be aware that shootingstar_sp() function is broken in v1.03 and v1.05.

-Steve

On 2/2/2012 11:47 PM, bino oetomo wrote:

Dear Daniel and all
I really appreciate your enlightment
On 02/03/2012 11:18 AM, Daniel Kastl wrote:

The unit is the unit of your projection. It's probably degree in your case.

You can transform your projection for example to some meter projection like
"World Mercator" (ie. EPSG:3785).
Then length(geometry) will be in meter.

Like"SELECT length(ST_transform(<geometry column>,3785)) AS meter FROM
<table>".

I tried to change the query to :
---START----
SELECT rt.gid, ST_AsGeoJSON(rt.the_geom) AS geojson,
length(ST_transform(rt.the_geom,3785)) AS length, ways.gid
FROM "ways",
(SELECT gid, the_geom
FROM shootingstar_sp(
'ways',
195690, 11690,
0.1, 'length', true, true)
) as rt
WHERE ways.gid=rt.gid;
----STOP----

but I got error, looks like that '3785' is not listed in 'spatial_ref_sys' :
---Start---
Traceback (most recent call last):
File "routecalc01a.py", line 21, in <module>
cur.execute(mysqlquery)
psycopg2.InternalError: AddToPROJ4SRSCache: Cannot find SRID (3785) in
spatial_ref_sys
----stop---

How to add that projection to spatial_ref_sys ?
Anyway .. my data is for indonesia are, and I want the unit is in meter.
What projection is the right one ?

Sincerely
-bino-

Daniel

Note : When I try to count the route using same start Lat/Lon and end
lat/lon, google map said it was about 2.1 km

Sincerely
-bino-

______________________________**_________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.**org <mailto:Pgrouting-users@lists.osgeo.**org> <Pgrouting-users@lists.osgeo.org> <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/**mailman/listinfo/pgrouting-**users<http://lists.osgeo.org/mailman/listinfo/pgrouting-users>

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

dkastl · February 3, 2012, 6:28am

How to add that projection to spatial_ref_sys ?

http://spatialreference.org/ref/epsg/3785/postgis/
You can use others like 900913, etc…

Anyway … my data is for indonesia are, and I want the unit is in meter. What projection is the right one ?

There is no right or wrong one.
You use what your application requires. If you use Google Maps in OpenLayers many people often use 900913.

Daniel

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

bino_oetomo · February 3, 2012, 6:36am

Dear Daniel and all


How to add that projection to spatial_ref_sys ?

[http://spatialreference.org/ref/epsg/3785/postgis/](http://spatialreference.org/ref/epsg/3785/postgis/)
You can use others like 900913, etc..

I also read http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/Understandingandtestingthedata.html

Hmmm … I thing i will set kne blank database that have EPSG 900913, and reload the OSM data using ‘-E 900913’ option

Anyway .. my data is for indonesia are, and I want the unit is in meter.
What projection is the right one ?

There is no right or wrong one.
You use what your application requires. If you use Google Maps in
OpenLayers many people often use 900913.

I use OSM tiles … but I want the calculation results in metre

Sincerely
-bino-

bino_oetomo · February 3, 2012, 7:19am

Dear Daniel and All

I try to reproject my base data to 900913, base on → http://download.osgeo.org/pgrouting/forum/pgrouting.postlbs.org/wiki/Understandingandtestingthedata.html

But When I try to finde nearest edge gid using query :
—Start—
SELECT gid, source, target, the_geom,
distance(the_geom, GeometryFromText(
‘POINT(110.367753392 -7.7778816619)’, 900913)) AS dist
FROM “ways”
WHERE the_geom && setsrid(
‘BOX3D(110.267753392 -7.8778816619,110.467753392 -7.6778816619)’::box3d, 900913)
ORDER BY dist LIMIT 1 ;
—Stop—
I got nothing

Since I still keep a copy of the original data at ways.the_geom_orig, i do the same query agains this column,
—Start–
SELECT gid, source, target, the_geom_orig,
distance(the_geom_orig, GeometryFromText(
‘POINT(110.367753392 -7.7778816619)’, 4326)) AS dist
FROM “ways”
WHERE the_geom_orig && setsrid(
‘BOX3D(110.267753392 -7.8778816619,110.467753392 -7.6778816619)’::box3d, 4326)
ORDER BY dist LIMIT 1 ;
—Stop—

I got ‘11690’ as it’s gid

Sincerely
-bino-

bino_oetomo · February 3, 2012, 7:45am

Dear Stephen and All
On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:

Also, You should be aware that shootingstar_sp() function is broken in v1.03 and v1.05.

So ... if I actualy want to calculate 'fastest driving distance' ... what function I can use ?

Sincerely
-bino

Dave_Potts · February 3, 2012, 9:34am

How is shootingstar_sp() function broken?

bino oetomo wrote:

Dear Stephen and All
On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:

Also, You should be aware that shootingstar_sp() function is broken in
v1.03 and v1.05.

So ... if I actualy want to calculate 'fastest driving distance' ...
what function I can use ?

Sincerely
-bino
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

--

Stephen_Woodbridge1 · February 3, 2012, 2:56pm

On 2/3/2012 2:45 AM, bino oetomo wrote:

Dear Stephen and All
On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:

Also, You should be aware that shootingstar_sp() function is broken in
v1.03 and v1.05.

So ... if I actualy want to calculate 'fastest driving distance' ...
what function I can use ?

Use Dijkstra or AStar and pass it cost column where the cost is length/speed.

-Steve

Stephen_Woodbridge1 · February 3, 2012, 3:04pm

On 2/3/2012 4:34 AM, Dave Potts wrote:

How is shootingstar_sp() function broken?

ShootingStar has some tickets open against it, but as I remember:

1. it does not honor oneway roads
2. it does not honor some turn restrictions

As best as I can tell, v1.02 was the last working version and it had some minor bug and attempts to fix it in v1.03 and v1.05 created other problems. Also it is currently 3-4 times slower than dijkstra or AStar. The only real value that it has is that it supports turn restrictions. If you have to have turn restrictions, the is some new code that was just developed and not released yet, but is in git as trsp branch that supports turn restrictions. It si not documented, read the list archives, I have explained how to use it, if you get stuck ask. That said, it provides NO value above Dijkstra and Astar it you do NOT need turn restrictions. I will also note the the trsp cose will likely undergo another change before it is released.

-Steve

bino oetomo wrote:

Dear Stephen and All
On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:

Also, You should be aware that shootingstar_sp() function is broken in
v1.03 and v1.05.

So ... if I actualy want to calculate 'fastest driving distance' ...
what function I can use ?

Sincerely
-bino
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Max_Weninger · February 3, 2012, 3:15pm

Hi

On Fri, 03 Feb 2012 10:04:54 -0500
Stephen Woodbridge <woodbri@swoodbridge.com> wrote:

On 2/3/2012 4:34 AM, Dave Potts wrote:
> How is shootingstar_sp() function broken?

ShootingStar has some tickets open against it, but as I remember:

1. it does not honor oneway roads
2. it does not honor some turn restrictions

Unfortunately this is correct
You can "solve" Nr 1 by using an "older" version
or merge the part into the actual version. The "fix" is only
a few lines where the graph edges are added.

But Nr 2 is harder to "fix"
I tried some time to find the "bug" but gave up also because the
code is really hard to understand I would suggest that you use the
"new" trsp algorithm if you need "turn restrictions"

Regards

max

As best as I can tell, v1.02 was the last working version and it had
some minor bug and attempts to fix it in v1.03 and v1.05 created
other problems. Also it is currently 3-4 times slower than dijkstra
or AStar. The only real value that it has is that it supports turn
restrictions. If you have to have turn restrictions, the is some new
code that was just developed and not released yet, but is in git as
trsp branch that supports turn restrictions. It si not documented,
read the list archives, I have explained how to use it, if you get
stuck ask. That said, it provides NO value above Dijkstra and Astar
it you do NOT need turn restrictions. I will also note the the trsp
cose will likely undergo another change before it is released.

-Steve

> bino oetomo wrote:
>> Dear Stephen and All
>> On 02/03/2012 11:58 AM, Stephen Woodbridge wrote:
>>>
>>> Also, You should be aware that shootingstar_sp() function is
>>> broken in v1.03 and v1.05.
>>>
>>
>> So ... if I actualy want to calculate 'fastest driving
>> distance' ... what function I can use ?
>>
>>
>> Sincerely
>> -bino
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>
>

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users