[postgis-users] Distance Query Performance

Alan Cunnane alan_cunnane at yahoo.co.uk
Tue Jul 10 14:01:51 PDT 2007

Hi guys

I have two tables that contain bus stop related data and I am trying to pregenerate another table containing each bus stops that is located within 200 metres to each other. I have a spatial column with British national grid coordinates in the bus_stops1 table and am using this query to get the distance between each stop:

CREATE table topology (stop_a, stop_b, distance) 
AS SELECT DISTINCT a.stop_reference, b.stop_reference, distance(a.east_north, b.east_north) 
FROM bus_stops1 a, bus_stops1 b, service1 c, service1 d 
WHERE distance(a.east_north, b.east_north) < 200 
AND a.stop_reference <> b.stop_reference 
AND c.service_id <> d.service_id 
ORDER BY distance(a.east_north, b.east_north);

Now the problem it just seems to either stall or else take a long long time to complete this query, is this normal?? There are about 15,000 rows in the table bus_stops1 with a cooridnate for each of those stops. Is there anything I am doing wrong or that I can do to speed this up? I have built a gist index on the spatial column already and on the foreign keys.

Thanks for your help


New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes. 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070710/988b5753/attachment.html>

More information about the postgis-users mailing list