[postgis-users] points DWithin other points

[juli g. pausas]

Many thanks! these corrections has been very helpful.

ST_Buffer && geom was very slow, and apparently not needed.
An using a new column for the geography (and adding and Index to that col)
increased the speed a lot.

With these 2 changes, the query need only 7 minutes to run the 200 000
points.
Thanks!



Juli
--
*CIDE, CSIC*  |  www.uv.es/jgpausas  |  blog <http://jgpausas.blogs.uv.es/>


On Tue, Apr 25, 2017 at 2:15 PM, Hugues François <hugues.francois at irstea.fr>
wrote:

> 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" <juli.g.pausas at uv.es> a écrit :
>
> 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/20170428/3f5d355b/attachment.html>