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