<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div>Hi guys<br><br>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:<br><br>CREATE table topology (stop_a, stop_b, distance) <br>AS SELECT DISTINCT a.stop_reference, b.stop_reference, distance(a.east_north, b.east_north) <br>FROM bus_stops1 a, bus_stops1 b, service1 c, service1 d <br>WHERE distance(a.east_north, b.east_north) < 200 <br>AND a.stop_reference <> b.stop_reference <br>AND c.service_id <> d.service_id <br>ORDER BY distance(a.east_north, b.east_north);<br><br>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.<br><br>Thanks for your help<br><br>Alan<br><br></div></div><br>
<hr size=1>
<a href="http://us.rd.yahoo.com/mail/uk/taglines/default/messenger/*http://uk.messenger.yahoo.com">Yahoo! Messenger</a> - with free PC-PC calling and photo sharing.</body></html>