* Alan Cunnane ([EMAIL PROTECTED]) wrote: > SELECT f.stop_reference AS origstop, a.stop_reference AS origconnect, > h.route_number, c.stop_reference AS destconnect, i.route_number, > d.stop_reference AS deststop, (e.arrival_time - g.depart_time) AS time > FROM service1 a, stop_link b, service1 c, bus_stops1 d, service1 e, > bus_stops1 f, service1 g, routes1 h, routes1 i
In general I find that it's much clearer to use JOIN syntax rather than
doing a cartesian join and then limiting it using where clauses. It can
also help identify joins which aren't being properly constrained.
> WHERE distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) <
> 200
> AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200
You should include a bounding box restriction before doing distance
calculations to limit the amount of data being run through the distance
function, ie:
WHERE f.east_north &&
setsrid(box3d(expand(PointFromText('POINT(318475
673980)',27700),400)),27700)
AND
distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) <
200
AND
d.east_north &&
setsrid(box3d(expand(PointFromText('POINT(331727
664569)',27700),400)),27700)
AND
distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) <
200
> I have limited the query to just one result and have indexes built on all the
> columns that are being linked however this has not helped the performance at
> all. In fact when I run an EXPLAIN the cost is massive! See below:
Seeing the rest of it would certainly help, along with your table
definitions and foreign-key constraints and indexes...
Thanks,
Stephen
signature.asc
Description: Digital signature
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
