[postgis-users] Distance Query Performance

[Alan Cunnane]

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

Alan





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