Hi,<br><br>Just a recap to our planned tdsp query model:<br><br><br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div class="gmail_quote"><div>I agree. So, now can we assume we have following database tables, as per things discussed till now:<br>
<br><ol><li>Ways: (On lines of the one in pgrouting-workshop)<br></li></ol><ul><li>gid</li><li>source</li><li>target<br></li><li>name</li><li>the_geom</li><li>static_length (In case time dependent routing is not used)<br>
</li><li>any other attributes..</li></ul> 2. TimeVaryingCosts (Any other suitable name?)<br><ul><li>gid</li><li>start_time_and_date (May divide this into two columns: day_of_week and time_of_day ? )<br></li><li>travel_time / arrival_time<br>
</li></ul> 3. TurnRestrictions (Time dependent)<br><ul><li>node/source</li><div class="im"><li>time_from <br></li><li>time_to <br></li><li>node_to <br></li><li>ancestors</li></div></ul><br>If only static shortest path query is made, then only table 1 is required.<br>
If time-dependent shortest path query is made, table 1 and 2 will be used.<br>If in time-dependent shortest path query with turn restrictions is made , all 3 tables will be used.<font color="#888888"><br>
</font></div></div></blockquote></div><br>So, for a general time dependent query, we will fetch edge information from ways table and the time dependent costs information from the <br clear="all">TimeDepCosts table. (See previous msg above.)<br>
<br>The query on ways table can be supplied by the usual as on normal dijkstra shortest_path() query. <br><br>How do we form the query on the TimeDepCosts table? I see two options:<br><br>1. We generate the query according to the gids obtained from the query on ways table. We will have to query all the time slots available, or form a query so that the time-window is appropriate and we have sufficient travel_time info so that we can reach destination.<br>
<br>Here we can form query like<br><br>Select * from TimeDepCosts where gid in (list of gids queried from ways table).<br><br>We can allow user to more filtering parameters according to start_time , date ?<br>But this will make our query specific to the database design. As already discussed, we cant predict the time units / requirements of applications and so it would be better if we keep the query independent of the database design.<br>
<br>Thats the reason our weight_map assumes start time from source as 0, and all travel times offset from the start time from source. A function that will generate such weight map is to be written. <br><br>2. We allow user to pass the query for TimeDepCosts table too. So, we will have our pgsql function as:<br>
<br><pre><span class="k">CREATE</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="k">FUNCTION</span> <span class="n">shortest_path</span><span class="p">(</span><br> <span class="k">ways_sql</span> <span class="nb">text</span><span class="p">,<br>
time_dep_cost_sql text,<br></span> <span class="n">source_id</span> <span class="nb">integer</span><span class="p">,</span><br>
<span class="n">target_id</span> <span class="nb">integer</span><span class="p">,</span><br> <span class="n">directed</span> <span class="nb">boolean</span><span class="p">,</span><br>
<span class="n">has_reverse_cost</span> <span class="nb">boolean</span><span class="p">)</span><br> <span class="k">RETURNS</span> <span class="k">SETOF</span> <span class="n">path_result</span><br>
<br></pre>
This way, user can have more control on the amount of data being
queried. We will only consider the time dependent windows that fit the
query.<br><br>Example of the time_dep_cost_sql can be:<br><br>Select * from TimeDepCosts where " USER_SUPPLIED_CONSTRAINTS".<br><br>The user can now supply his constraints according to time_window he needs, start times, holidays etc according to the database table design.<br>
<br>The problem with this is, we cannot filter out edges according to bounding box etc since the_geom field is not there in this table. This is because that information would be redundant.<br><br>Thoughts? Any idea that combines the merits of both approaches? <br>
<br>--<br>Regards,<br>-Jay Mahadeokar<br><br>