<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:12pt"><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">Apologies for that last email it was sent by accident.<br><br>Here are a list of my table definitions:<br><br><font size="2"> 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) = 'POINT'::text OR
east_north IS NULL)<br> "enforce_srid_east_north" CHECK (srid(east_north) = 27700)</font><br><br>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.<br><br><font size="2"> 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)</font><br><br>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.<br><br><font size="2"> 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 routes1(service_id)<br> "service1_stop_reference_fkey" FOREIGN KEY (stop_reference) REFERENCES bus_stops1(stop_reference)<br></font><br><br>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.<br><br><br><font size="2">
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)</font><br><br><br>The table stop_link is a pre-generated table comprising of all stops 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 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. <br><br>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.<br><br><br><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 10:44:58 AM<br>Subject: Re: [postgis-users] Performance Help<br><br><div>On Thu, 2007-07-26 at 02:04 +0000, Alan Cunnane wrote:<br>> Hi guys <br>> <br>> I have a query here that joins about 10 tables tables and uses two<br>> distance queries. Im am having massive performance issues with it and<br>> im hoping you could help me to tune it or change it so that<br>> performance is increased. All of
the columns being used for the joins<br>> have indexes and I have performed ANALYZE and VACUUM on each table.<br>> The query is as follows:<br><br>(rather complex query cut)<br><br>> As you can see it is taking entirely too much time for a query of this<br>> size in my opinion. Therefore im assuming I have done something wrong<br>> or could improve it in some way. I would really appreciate your help.<br>> <br>> Sincerely<br>> <br>> Alan<br><br><br>Hi Alan.<br><br>In order to help, we're going to need your table definitions and an<br>explanation of what your query is actually trying to in terms of<br>locating stops within certain times etc.<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! Answers - Get better answers from someone who knows. <a
href="http://uk.answers.yahoo.com/;_ylc=X3oDMTEydmViNG02BF9TAzIxMTQ3MTcxOTAEc2VjA21haWwEc2xrA3RhZ2xpbmU">Try
it now</a>.</body></html>