[postgis-users] points DWithin other points

[juli g. pausas]

Hi list,
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.

Juli

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"


--
*CIDE, CSIC*  |  www.uv.es/jgpausas  |  blog <http://jgpausas.blogs.uv.es/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170425/4daef498/attachment.html>