[postgis-users] Performance Help

[Alan Cunnane]

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,

Alan

----- 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 12:01:58 PM
Subject: Re: [postgis-users] Performance Help

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
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/e8b94e64/attachment.html>