<div dir="ltr"><div><div><div><div>Many thanks! these corrections has been very helpful.<br></div><br>ST_Buffer && geom was very slow, and apparently not needed.<br></div>An using a new column for the geography (and adding and Index to that col) increased the speed a lot. <br><br></div>With these 2 changes, the query need only 7 minutes to run the 200 000 points.<br></div>Thanks!<br><div><div><br><br></div></div></div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr">Juli<div><span style="color:rgb(51,51,153)">--</span><br><font color="#333399"><b>CIDE, CSIC</b>  |  <a href="http://www.uv.es/jgpausas" target="_blank">www.uv.es/jgpausas</a>  |  <a href="http://jgpausas.blogs.uv.es/" target="_blank">blog</a><br><br></font></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">On Tue, Apr 25, 2017 at 2:15 PM, Hugues François <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="auto">Hello,<div dir="auto"><br></div><div dir="auto">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. </div><div dir="auto"><br></div><div dir="auto">I also think that the bbox condition is redundant with st_dwithin and st_buffer is quite long to run. </div><div dir="auto"><br></div><div dir="auto">Regards,</div><div dir="auto"><br></div><div dir="auto">Hug</div><div><div class="h5"><br><div class="gmail_extra" dir="auto"><br><div class="gmail_quote">Le 25 avr. 2017 1:17 PM, "juli g. pausas" <<a href="mailto:juli.g.pausas@uv.es" target="_blank">juli.g.pausas@uv.es</a>> a écrit :<br type="attribution"><blockquote class="m_2261375525468684893quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hi list,<br>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:<br><br>UPDATE WDplots AS t1 SET nfires= tt.nfires, <br>                                           avgfrp= tt.avgfrp<br>    FROM (<br>             SELECT sa.gid AS gid, count(*) AS nfires, avg(fi.frp) as avgfrp<br>             FROM WDplots AS sa,<br>                  n3_cmg_terra AS fi<br>               WHERE ST_DWithin(geography(fi.geom), geography(sa.geom), 500)<br>               AND (ST_Buffer(geography(fi.geom), 500) && sa.geom)<br>               AND fi.dist < 100<br>               AND confidence >30<br>               AND EXTRACT(YEAR FROM fi.date) BETWEEN 2002 AND 2015<br>               GROUP BY sa.gid ORDER BY sa.gid<br>    ) AS tt<br>    WHERE (t1.gid = tt.gid);<br><br><br>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? <br><br>The two tables have indices:<br><br>CREATE INDEX wdplots_geom_gist<br>  ON wdplots<br>  USING gist (geom);<br><br>CREATE INDEX wdplots_gid_index<br>  ON wdplots<br>  USING btree   (gid);<br><br> CREATE INDEX n3_cmg_terra_geom_gist<br>  ON n3_cmg_terra<br>  USING gist  (geom);<br><div></div><br>Thanks for any comment.<br><br></div>Juli<br><div><br>Computer: Intel® Xeon(R) CPU E5-2620 v4 @ 2.10GHz × 32, 125,8 GiB, on Ubuntu 16.04<br><br>SELECT version();<br>"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"<br><br>SELECT PostGIS_version();<br>"2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"<br><br><br><div><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><span style="color:rgb(51,51,153)">--</span><br><font color="#333399"><b>CIDE, CSIC</b>  |  <a href="http://www.uv.es/jgpausas" target="_blank">www.uv.es/jgpausas</a>  |  <a href="http://jgpausas.blogs.uv.es/" target="_blank">blog</a><br><br></font></div></div></div></div></div></div></div></div>
</div></div>
</blockquote></div><br></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>