I am doing a local statistic, similar to the the one described in a earlier question / answer:
SELECT a.tree_id, a.species, avg(b.age) as age_avg, count(*) as samples, a.geomFROM trees a LEFT JOIN trees bON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.speciesWHERE a.age IS NULLGROUP BY a.tree_id, a.species, a.geom;This finds all trees of the same species in a radius of 100 meters, being the same species. This works quite nicely for small datasets with few missing data points (WHERE a.age IS NULL).
However, when I run the query for a larger dataset with more missing data, it gets very slow (i.e. several hours / days). In this case 6000 of of a total of 200000 points have no value (a.age).
Do you see a way to increase the speed of the query? Maybe an alternative function to st_dwithin is helpful?
أكثر...
SELECT a.tree_id, a.species, avg(b.age) as age_avg, count(*) as samples, a.geomFROM trees a LEFT JOIN trees bON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.speciesWHERE a.age IS NULLGROUP BY a.tree_id, a.species, a.geom;This finds all trees of the same species in a radius of 100 meters, being the same species. This works quite nicely for small datasets with few missing data points (WHERE a.age IS NULL).
However, when I run the query for a larger dataset with more missing data, it gets very slow (i.e. several hours / days). In this case 6000 of of a total of 200000 points have no value (a.age).
Do you see a way to increase the speed of the query? Maybe an alternative function to st_dwithin is helpful?
أكثر...