[postgis-users] Performance Help

[Alan Cunnane]

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.



----- Original Message ----
From: Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Thursday, 26 July, 2007 10:44:58 AM
Subject: Re: [postgis-users] Performance Help

On Thu, 2007-07-26 at 02:04 +0000, Alan Cunnane wrote:
> Hi guys 
> 
> I have a query here that joins about 10 tables tables and uses two
> distance queries. Im am having massive performance issues with it and
> im hoping you could help me to tune it or change it so that
> performance is increased. All of the columns being used for the joins
> have indexes and I have performed ANALYZE and VACUUM on each table.
> The query is as follows:

(rather complex query cut)

> As you can see it is taking entirely too much time for a query of this
> size in my opinion. Therefore im assuming I have done something wrong
> or could improve it in some way. I would really appreciate your help.
> 
> Sincerely
> 
> Alan


Hi Alan.

In order to help, we're going to need your table definitions and an
explanation of what your query is actually trying to in terms of
locating stops within certain times etc.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users







      ___________________________________________________________ 
Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for
your free account today http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070726/9d323b7e/attachment.html>