[postgis-users] Distance Query Performance

[Paul Ramsey]

Try this:

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 expand(a.east_north, 200) && b.east_north
AND a.stop_reference <> b.stop_reference
AND c.service_id <> d.service_id
ORDER BY distance;

-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632