Hello,
If I'm not wrong, I think the on the fly geography casting can be costly and you may loose the benefit of your index for st_dwithin function. You may try to add a geography column to store your data in geography format and then add a spatial index.
I also think that the bbox condition is redundant with st_dwithin and st_buffer is quite long to run.
Regards,
Hug
Le 25 avr. 2017 1:17 PM, "juli g. pausas" <[email protected]> a écrit :
Hi list,Juli
I have a table with about 200,000 points (table: WDplots), and another larger table with also point (in fact, fires; table: n3_cmg_terra). I'd like to know how many of the points (fires) in the n3_cmg_terra are close (e.g., 0.5 km radius) to my plots (WDplots). So I compute nfires (and also avgfrp), as following:
UPDATE WDplots AS t1 SET nfires= tt.nfires,
avgfrp= tt.avgfrp
FROM (
SELECT sa.gid AS gid, count(*) AS nfires, avg(fi.frp) as avgfrp
FROM WDplots AS sa,
n3_cmg_terra AS fi
WHERE ST_DWithin(geography(fi.geom), geography(sa.geom), 500)
AND (ST_Buffer(geography(fi.geom), 500) && sa.geom)
AND fi.dist < 100
AND confidence >30
AND EXTRACT(YEAR FROM fi.date) BETWEEN 2002 AND 2015
GROUP BY sa.gid ORDER BY sa.gid
) AS tt
WHERE (t1.gid = tt.gid);
But this has been run for 26 days and has not finished yet (I've stopped!). My computer is not that bad (see below), so I'm sure I'm doing something wrong (very wrong probable). Any help?
The two tables have indices:
CREATE INDEX wdplots_geom_gist
ON wdplots
USING gist (geom);
CREATE INDEX wdplots_gid_index
ON wdplots
USING btree (gid);
CREATE INDEX n3_cmg_terra_geom_gist
ON n3_cmg_terra
USING gist (geom);
Thanks for any comment.
Computer: Intel® Xeon(R) CPU E5-2620 v4 @ 2.10GHz × 32, 125,8 GiB, on Ubuntu 16.04
SELECT version();
"PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit"
SELECT PostGIS_version();
"2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
