<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:10pt"><div style="font-family: times new roman,new york,times,serif; font-size: 10pt;"><font size="3">I have actually already looked at PG routing however because of the constraints of this particular project I cannot use it. I have to use SQL queries as I was attempting however it seems that the query runs smoothly enough up until the 8th or 9th table is added to the join. Is this my own fault in the query or is this just the performance of Postgresql in handling a large number of joins? I do appreciate your help and advice,<br><br>Alan<br></font><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">----- Original Message ----<br>From: Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk><br>To: PostGIS Users Discussion
<postgis-users@postgis.refractions.net><br>Sent: Thursday, 26 July, 2007 12:01:58 PM<br>Subject: Re: [postgis-users] Performance Help<br><br><div>On Thu, 2007-07-26 at 10:05 +0000, Alan Cunnane wrote:<br>> Apologies for that last email it was sent by accident.<br>> <br>> Here are a list of my table definitions:<br>> <br>> Table "bus_stops1"<br>> Column | Type | Modifiers<br>> ----------------+-----------------------+-----------<br>> stop_reference | character varying(12) | not null<br>> easting | integer | not null<br>> northing |
integer | not null<br>> full_location | character varying(50) |<br>> east_north | geometry | not null<br>> Indexes:<br>> "bus_stops1_pkey" PRIMARY KEY, btree (stop_reference)<br>> "stops_distance1" gist (east_north) CLUSTER<br>> Check constraints:<br>> "enforce_dims_east_north" CHECK (ndims(east_north) = 2)<br>> "enforce_geotype_east_north" CHECK (geometrytype(east_north) =<br>> 'POINT'::text OR east_north IS NULL)<br>> "enforce_srid_east_north" CHECK (srid(east_north) = 27700)<br>> <br>> This table contains a list of bus stops, their full addresses and<br>> their coordinates.
"Stop_reference" is a unique ID that each<br>> individual bus stops contains.<br>> <br>> Table "Routes1"<br>> Column | Type | Modifiers<br>> --------------+----------------------+-----------<br>> service_id | integer | not null<br>> route_number | character varying(4) | not null<br>> Indexes:<br>> "routes1_pkey" PRIMARY KEY, btree (service_id)<br>> "routes1_route_number" btree (route_number)<br>> <br>> This table contains information about each route. A route is defined<br>> by each different bus number that travels that day. The service_id<br>> here
is a unique number given to each trip every bus makes that day.<br>> For example "route_number 3" bus will have 40+ service trips each day<br>> from 7am to 11pm.<br>> <br>> Table "service1"<br>> Column | Type | Modifiers<br>> ----------------+------------------------+-----------<br>> service_id | integer | not null<br>> stop_reference | character varying(12) | not null<br>> arrival_time | time without time zone |<br>> depart_time | time without time zone
|<br>> stop_order | integer | not null<br>> Indexes:<br>> "service1_service_id" btree (service_id)<br>> "service1_stop_order" btree (stop_order)<br>> "service1_stop_reference" btree (stop_reference)<br>> Foreign-key constraints:<br>> "service1_service_id_fkey" FOREIGN KEY (service_id) REFERENCES<br>> routes1(service_id)<br>> "service1_stop_reference_fkey" FOREIGN KEY (stop_reference)<br>> REFERENCES bus_stops1(stop_reference)<br>> <br>> <br>> The service1 table lists each of these service trips in detail.<br>> Showing the list of bus stops that each service uses along its route,<br>> the order in which the bus arrives at these stops and the time that<br>> the
bus arrives and departs at these stops.<br>> <br>> <br>> Table "stop_link"<br>> Column | Type | Modifiers<br>> --------+-----------------------+-----------<br>> stop_a | character varying(12) |<br>> stop_b | character varying(12) |<br>> Indexes:<br>> "link_stop_a" btree (stop_a)<br>> "link_stop_b" btree (stop_b)<br>> <br>> <br>> The table stop_link is a pre-generated table comprising of all stops<br>> within 300 metres of each other. <br>> <br>> What I want to be able to do is to find the routes needed to be taken<br>> from one location (Pointfromtext) to another using three connections.<br>> Three connections <font size="3">meaning </font>three routes
numbers. As you can see from<br>> the query the start bus stop and end bus stop must be located within<br>> 200 metres of the two corresponding points. And the first service must<br>> leave at the given time or any time ten minutes after that. Two<br>> changeovers of buses must be made mid-journey, and each change over<br>> must occur at least three minutes after arriving at the changeover<br>> stop. <br>> <br>> I hope this is all clear to you? If not please get back to me and I<br>> will try to explain further. Thanks so much for your help.<br><br>Right I see - ignoring the time constraints for a moment, this is a<br>routing problem. I suspect that the way forward with this would be to<br>look at the pgRouting project (<a target="_blank" href="http://pgrouting.postlbs.org/">http://pgrouting.postlbs.org/</a>) using your<br>stops as vertices and services as edges.<br><br>I would suggest you have a look at the above site first,
and then post<br>back with any more queries that you have. It's just I suspect that this<br>will be faster (and less complicated) if you can use this rather trying<br>to model the network functions in SQL.<br><br><br>Kind regards,<br><br>Mark.<br><br>-- <br>ILande - Open Source Consultancy<br><a target="_blank" href="http://www.ilande.co.uk">http://www.ilande.co.uk</a><br><br><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></div><br></div></div><br>
<hr size=1>
Yahoo! Mail is the world's favourite email. Don't settle for less, <a
href="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">sign up for your free
account today</a>.</body></html>