[postgis-users] Need help - spatial query - public transport

[Tom Kessler]

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 at postgis.refractions.net>

<postgis-users at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100126/ce40e836/attachment.html>