[postgis-users] ST_Distance_Sphere too slow
woodbri at swoodbridge.com
Thu Mar 24 12:58:13 PDT 2011
I would make sure there is an gist index on path and use a query like:
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.
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.
> 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.
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users