[postgis-users] Performance Help

[Mark Cave-Ayland]

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