[postgis-users] ST_Distance_Sphere too slow

Julian Perelli jperelli at gmail.com
Thu Mar 24 12:43:49 PDT 2011

Hello Postgis List!

I'm trying to get the pair of paths that crosses each other or are 300
meters or less distant from each other, making this SQL.

    table t1
      inner join table t2
        on (t1.id!=t2.id and ST_Distance_Sphere(t1.path, t2.path) < 300);

it was 14 hours running and it doesn't finish... I have 1200+ rows in
the table, each path has between 100 and 500 points.

I tried to make an index on the path column, but when I use explain on
the query, it seems that pg doesn't use the index.

should I increase the memory assigned to pgsql?

I don't know where to begin, what to do, to make this query faster.
Maybe I have an error and it just hangs up.
It would be nice to know how to debug the query.. to see it running or
something like that. EXPLAIN helps, but not too much.


More information about the postgis-users mailing list