[postgis-users] Distance Query Performance

Rodrigo Martín LÓPEZ GREGORIO rodrigomartin at lopezgregorio.com.ar
Tue Jul 10 14:36:10 PDT 2007

Hi Alan.

Also I'm not sure if the "service1 c, service1 d" and the "AND c.service_id<>
d.service_id" parts are right. I'm not sure but sinse you don't uses this
two tables in your query you can remove that parts of your query and it can
be faster cause otherwise this two tables will be combined as cartesian
product with the others and I don't think this is what you want. (I'm not
pretty sure but I think this is what will happen if you do not remove that
parts of your querys).


On 7/10/07, Paul Ramsey <pramsey at refractions.net> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070710/6d1238ab/attachment.html>

More information about the postgis-users mailing list