<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">I need some expert help on developing a spatial query related to public transport: <br><br>- I have a table of public bus routes in a city.  One route (LINESTRING) for each direction. <br>
-
Assume a passenger wishes to go from point A to point B, with an
optional via point C with driver detour distance a fixed constant
meters and<br>
passenger detour distance variable X meters (specified by passenger) . <br><br><br>I need a query to find:<br><br>A - All singular routes going in the right direction<br>B
- compound routes  of 2 or 3 legs,  that will get the passenger there. 
Need to eliminate duplicates and false compound matches. <br>
<br><br>I've developed some code, using line_locate_point()  to check direction.   Here is a prototype query I've been working: </a><br><br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank"></a><pre>
<span>FOR</span> q <span>in</span> <span>SELECT</span><br><br><br>mr1.route_id <span>as</span> mr1_route_id , mr2.route_id <span>as</span> mr2_route_id,<br>mr1.route_geom <span>as</span> mr1_route_geom , mr2.route_geom <span>as</span> mr2_route_geom,<br>
<br>mr1.groute_id <span>as</span> mr1_groute_id , mr2.groute_id <span>as</span> mr2_groute_id,<br>mr1.mroute_id <span>as</span> mr1_mroute_id , mr2.mroute_id <span>as</span> mr2_mroute_id,<br><br>mr1.group_id <span>as</span> mr1_group_id , mr2.group_id <span>as</span> mr2_group_id,<br>
mr1.groute_type <span>as</span> mr1_groute_type , mr2.groute_type <span>as</span> mr2_groute_type,<br><br>mr1.agency_id <span>as</span> mr1_agency_id , mr2.agency_id <span>as</span> mr2_agency_id,<br>mr1.groute_long_name <span>as</span> mr1_groute_long_name , mr2.groute_long_name <span>as</span> mr2_groute_long_name,<br>
<br><span></span><span></span><span></span><span></span><br><span>FROM</span><br><span>public</span>.gettouchroutes(porg, tript, walkdet )  mr1,<br><br><span>public</span>.gettouchroutes(pdest, tript, walkdet ) mr2<br><br>
<span>WHERE</span><br>(<br><br>(<br><span>--route1 also intersects with the destination<br></span><br> st_intersects( buffer(<span>transform</span>(mr1.route_geom, 32613), mr1.driving_detour), <br><br>                           buffer(pdest, walkdet) ) <br>
                                                                 <br>                             <span>AND</span><br>                          <br><span>--the direction of the route is correct<br></span><br> line_locate_point(<span>transform</span>(mr1.route_geom, 32613), porg) < <br><br>                             line_locate_point(<span>transform</span>(mr1.route_geom, 32613), pdest)<br>
)<br><br><span>OR</span><br><span>-- the routes cross<br></span>(<br>st_crosses( <span>transform</span>(mr1.route_geom, 32613),<br><br><span>transform</span>(mr2.route_geom, 32613)  )<br><br><span>AND</span><br><br>mr1.route_geom <> mr2.route_geom<br>
<br><span>AND</span><br><br><span>-- their compound direction is same as passengers<br><br></span>(<br>line_locate_point( <span>transform</span>(mr1.route_geom, 32613),<br>porg ) < <br>line_locate_point (<span>transform</span>(mr1.route_geom, 32613),<br>
st_centroid(<br>st_intersection(<span>transform</span>(mr1.route_geom, 32613),<br><br> <span>transform</span>(mr2.route_geom, 32613))  ))<br><br><span>AND</span><br><br>line_locate_point( <span>transform</span>(mr2.route_geom, 32613),<br>
st_centroid(<br>st_intersection(<span>transform</span>(mr1.route_geom, 32613),<br><br> <span>transform</span>(mr2.route_geom, 32613))  )) < <br>line_locate_point (<span>transform</span>(mr2.route_geom, 32613),<br> pdest )<br>
 <br> )<br>)<br>)<br></pre>However,
I run into problems when multiple routes running along the same street
cross each other (false compound matches) and when passenger detour<br>
distance is large, this query only works on 2 legs.   <br><br>Any experts out there?<br><br>Tom<br><br><br><br>