[postgis-users] points DWithin other points

[Darafei "Komяpa" Praliaskouski]

Please share your query plan using EXPLAIN.

https://explain.depesz.com/

вт, 25 апр. 2017 г. в 14:17, juli g. pausas <juli.g.pausas at uv.es>:

> 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/>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170425/32adec50/attachment.html>