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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
