[postgis-users] PgRouting in PostGIS

Anton Patrushev anton at orkney.co.jp
Thu Sep 25 08:10:36 PDT 2008


Wrapper functions narrow down a search area - they load only a part of
entire data. That's why they are faster.
Please look at routing_core_wrappers.sql file to get an idea how to do it.
Hints: BOX3D, && operator.

Anton.

On Thu, Sep 25, 2008 at 1:04 AM, Nandorov <nandorov at gmail.com> wrote:
>
> i could resolved it doing
>
> BEGIN TRANSACTION;
> DROP TABLE IF EXISTS shortest_path_table_2;
> CREATE TABLE shortest_path_table_2(gid int4) with oids;
> SELECT AddGeometryColumn('','shortest_path_table_2', 'the_geom', -1,
> 'MULTILINESTRING', 2 );
> INSERT INTO shortest_path_table_2(the_geom)
> select the_geom from tb_mygeom
> where start_node in (
> select vertex_id from shortest_path('SELECT
> the_geom,id,source,target,x1,y1,x2,y2,cost,reverse_cost from mygeom where
> is_blocked != ''Y'' '
> ,3989 ,145,true,true) )
> and end_node in (
> select vertex_id from shortest_path('SELECT
> the_geom,id,source,target,x1,y1,x2,y2,cost,reverse_cost from mygeom where
> is_blocked != ''Y'' '
> ,3989 ,145,true,true)
> );
> COMMIT
>
> i the coordinates, but i dont know if that might affect the performance.
>
>
>
> Nandorov wrote:
>>
>> Hi Anton,
>> i have a problem when applying where clause in the sql parameter for
>> shortest_path function. Function seems to work well but how do i recover
>> the coordinates?
>>
>> i used to do this way, for example
>> select * from shortest_path('SELECT
>> id,source,target,x1,y1,x2,y2,cost,reverse_cost from tb_mygeom',3989
>> ,145,true,true); to verify and then applied:
>> insert into my_temptable(geom)
>> select the_geom from dijkstra_sp_directed('tb_mygeom',3989,145,true,true);
>> to recover the coordinates.
>>
>> Now i applied
>> select * from shortest_path('SELECT
>> id,source,target,x1,y1,x2,y2,cost,reverse_cost from tb_mygeom where
>> isBlocked != ''Y'' ',3989 ,145,true,true);
>> the functions seems to work, but i dont know how to get the coordinates,
>> because if i apply dijkstra_sp_directed i can't filter there. I tried
>>
>> INSERT INTO shortest_path_table_2(the_geom)
>> select the_geom from tb_mygeom
>> where gid in (
>> select vertex_id from shortest_path('SELECT
>> the_geom,id,source,target,x1,y1,x2,y2,cost,reverse_cost from
>> full_calleslima where is_blocked != ''Y'' '
>> ,3989 ,145,true,true) );
>>
>> but it returns a messy format, no linked vertix
>>
>> thanks a lot
>>
>>
>>
>>
>> Anton A. Patrushev wrote:
>>>
>>> Hi Fernando,
>>>
>>> Of course you can use any SQL query with any filters you wish.
>>> That's actually what pgRouting was made for!
>>>
>>> Please read this:
>>> http://pgrouting.postlbs.org/wiki/Dijkstra
>>> http://pgrouting.postlbs.org/wiki/AStar
>>> http://pgrouting.postlbs.org/wiki/ShootingStar
>>>
>>> Anton.
>>>
>>> On 9/24/08, Nandorov <nandorov at gmail.com> wrote:
>>>>
>>>>  hi,
>>>>  thanks Anton for your answer at pgrouting forum. i'm goingo to reply
>>>> here i
>>>>  hope it's ok.
>>>>
>>>>  Hi Fernando,
>>>>
>>>>  I think I answered you already in the PostGIS mailing list.
>>>>  Anyway, it is worth to repeat. You can block edges by assigning high
>>>> value
>>>>  to cost field. Or, if you wish, you can add one more field, let's say,
>>>> of
>>>>  boolean type and name it is_blocked, and then use core routing function
>>>> (not
>>>>  wrapper), where you can limit your search area with non-blocked edges
>>>> only -
>>>>  "SELECT ... WHERE NOT is_blocked".
>>>>
>>>>  So i the input sql statement at shortest path function can include
>>>> where
>>>>  filters? sounds nice. i'm going to try it later (00:16 here lol)
>>>>
>>>>  thanks a lot
>>>>
>>>>
>>>>
>>>>  Anton A. Patrushev wrote:
>>>>  >
>>>>  > Hi,
>>>>  >
>>>>  > Of course you can. You can put any float value as a cost. And if a
>>>>  > cost of an edge is high enough comparing to other costs, algorithm
>>>>  > will prefer not to pass through that edge.
>>>>  >
>>>>  > Anton.
>>>>  >
>>>>  > On 9/24/08, Nandorov <nandorov at gmail.com> wrote:
>>>>  >>
>>>>  >>  can i block some segments? for example cad gives the posibility to
>>>> put a
>>>>  >> max
>>>>  >>  resistance value, let's say it's 100. So if the resistance of your
>>>>  >> segment
>>>>  >>  is 100 that segment is blocked and you cannot go thorught this way.
>>>> if
>>>>  >> this
>>>>  >>  possible with pgrouting?
>>>>  >>
>>>>  >>  thanks!
>>>>  >>
>>>>  >>
>>>>  >>
>>>>  >>  Stephen Woodbridge wrote:
>>>>  >>  >
>>>>  >>  > Nandorov wrote:
>>>>  >>  >> hi,
>>>>  >>  >> i've tried pgrouting algorithms and works pretty well. However,
>>>> i
>>>>  >> dont
>>>>  >>  >> know
>>>>  >>  >> exactly the way they applied "the resistance" of a way
>>>> (represented
>>>>  >> by a
>>>>  >>  >> multilinestring). has someone worked  with pgrouting before? and
>>>> know
>>>>  >> if
>>>>  >>  >> i
>>>>  >>  >> can modify their algorithms to adapt them to my requirements?
>>>>  >>  >>
>>>>  >>  >> thanks
>>>>  >>  >
>>>>  >>  > "the resistance" is applied as a "cost" to traverse the segment.
>>>> All
>>>>  >>  > cost must be positive. You can use length of the segment as cost
>>>> for
>>>>  >>  > shortest distance. or you can compute and assign a cost value
>>>> based on
>>>>  >>  > other attributes.
>>>>  >>  >
>>>>  >>  > -Steve
>>>>  >>  > _______________________________________________
>>>>  >>  > postgis-users mailing list
>>>>  >>  > postgis-users at postgis.refractions.net
>>>>  >>  > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>  >>  >
>>>>  >>  >
>>>>  >>
>>>>  >>
>>>>  >> --
>>>>  >>  View this message in context:
>>>>  >> http://www.nabble.com/PgRouting-in-PostGIS-tp19638201p19638473.html
>>>>  >>
>>>>  >> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>>>  >>
>>>>  >>  _______________________________________________
>>>>  >>
>>>>  >> postgis-users mailing list
>>>>  >>  postgis-users at postgis.refractions.net
>>>>  >>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>  >>
>>>>  > _______________________________________________
>>>>  > postgis-users mailing list
>>>>  > postgis-users at postgis.refractions.net
>>>>  > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>  >
>>>>  >
>>>>
>>>>  --
>>>>
>>>> View this message in context:
>>>> http://www.nabble.com/PgRouting-in-PostGIS-tp19638201p19642156.html
>>>>
>>>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>>>
>>>>  _______________________________________________
>>>>  postgis-users mailing list
>>>>  postgis-users at postgis.refractions.net
>>>>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/PgRouting-in-PostGIS-tp19638201p19652169.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



More information about the postgis-users mailing list