On Thu, 2007-07-26 at 10:05 +0000, Alan Cunnane wrote: > Apologies for that last email it was sent by accident. > > Here are a list of my table definitions: > > Table "bus_stops1" > Column | Type | Modifiers > ----------------+-----------------------+----------- > stop_reference | character varying(12) | not null > easting | integer | not null > northing | integer | not null > full_location | character varying(50) | > east_north | geometry | not null > Indexes: > "bus_stops1_pkey" PRIMARY KEY, btree (stop_reference) > "stops_distance1" gist (east_north) CLUSTER > Check constraints: > "enforce_dims_east_north" CHECK (ndims(east_north) = 2) > "enforce_geotype_east_north" CHECK (geometrytype(east_north) = > 'POINT'::text OR east_north IS NULL) > "enforce_srid_east_north" CHECK (srid(east_north) = 27700) > > This table contains a list of bus stops, their full addresses and > their coordinates. "Stop_reference" is a unique ID that each > individual bus stops contains. > > Table "Routes1" > Column | Type | Modifiers > --------------+----------------------+----------- > service_id | integer | not null > route_number | character varying(4) | not null > Indexes: > "routes1_pkey" PRIMARY KEY, btree (service_id) > "routes1_route_number" btree (route_number) > > This table contains information about each route. A route is defined > by each different bus number that travels that day. The service_id > here is a unique number given to each trip every bus makes that day. > For example "route_number 3" bus will have 40+ service trips each day > from 7am to 11pm. > > Table "service1" > Column | Type | Modifiers > ----------------+------------------------+----------- > service_id | integer | not null > stop_reference | character varying(12) | not null > arrival_time | time without time zone | > depart_time | time without time zone | > stop_order | integer | not null > Indexes: > "service1_service_id" btree (service_id) > "service1_stop_order" btree (stop_order) > "service1_stop_reference" btree (stop_reference) > Foreign-key constraints: > "service1_service_id_fkey" FOREIGN KEY (service_id) REFERENCES > routes1(service_id) > "service1_stop_reference_fkey" FOREIGN KEY (stop_reference) > REFERENCES bus_stops1(stop_reference) > > > The service1 table lists each of these service trips in detail. > Showing the list of bus stops that each service uses along its route, > the order in which the bus arrives at these stops and the time that > the bus arrives and departs at these stops. > > > Table "stop_link" > Column | Type | Modifiers > --------+-----------------------+----------- > stop_a | character varying(12) | > stop_b | character varying(12) | > Indexes: > "link_stop_a" btree (stop_a) > "link_stop_b" btree (stop_b) > > > The table stop_link is a pre-generated table comprising of all stops > within 300 metres of each other. > > What I want to be able to do is to find the routes needed to be taken > from one location (Pointfromtext) to another using three connections. > Three connections meaning three routes numbers. As you can see from > the query the start bus stop and end bus stop must be located within > 200 metres of the two corresponding points. And the first service must > leave at the given time or any time ten minutes after that. Two > changeovers of buses must be made mid-journey, and each change over > must occur at least three minutes after arriving at the changeover > stop. > > I hope this is all clear to you? If not please get back to me and I > will try to explain further. Thanks so much for your help.
Right I see - ignoring the time constraints for a moment, this is a routing problem. I suspect that the way forward with this would be to look at the pgRouting project (http://pgrouting.postlbs.org/) using your stops as vertices and services as edges. I would suggest you have a look at the above site first, and then post back with any more queries that you have. It's just I suspect that this will be faster (and less complicated) if you can use this rather trying to model the network functions in SQL. Kind regards, Mark. -- ILande - Open Source Consultancy http://www.ilande.co.uk _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
