[postgis-users] ST_Distance_Sphere too slow

[Stephen Woodbridge]

I would make sure there is an gist index on path and use a query like:

   select
     t1.id,
     t2.id
   from
     table t1
       inner join table t2
         on (t1.path && st_expand(t2.path, 300/111120) and t1.id!=t2.id 
and ST_Distance_Sphere(t1.path, t2.path) < 300);

the 300/111120 is to convert 300 meters into approximately degrees. If 
you are worried about some near misses you can expand it a little more 
and distance_sphere will filter extras out of the results.

-Steve W

On 3/24/2011 3:43 PM, Julian Perelli wrote:
> 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.
>
>    select
>      t1.id,
>      t2.id
>    from
>      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.
>
> Regards!
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users