If you are running a query on the Geography, you should create the index on Geography, using something like this: Create Index g_geog_idx on mySchema.myTable using GIST(geom::geography);
Using this increased the speed of my query 1000 times. Source: https://twitter.com/postgis/status/675001071505383424 Regards, Devdatta On Tue, Jan 15, 2019 at 4:07 PM David M. Kaplan <[email protected]> wrote: > Hi, > > For a while it has not been clear in my head if and when GIST indexes are > used when doing geography based calculations. If the data is stored in a > table with a geometry column that has a GIST index on it, will that index > be used if one does something like > ST_DWithin(a.geom::geography,b.geom,1000)? If not, then if the data was > stored in geography format instead of geometry, would this make the index > more useful? > > To give a specific context, I have a table of point geometries with SRID > 4326 and a GIST index, and I want to find the number of points in that > table that are within a certain distance of each other point in that table, > but I am not sure what is the most efficient way to do so. To do this, I > have a query of the form: > > SELECT a.gid, a.geom, count(*) AS num_points > > FROM mytable a > > JOIN mytable b > > ON a.gid<>b.gid > AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE) > > GROUP BY a.gid,a.geom > > ; > > > This is quite slow, so I presume the GIST index is not being used (and > EXPLAIN didn't show anything that made it clear that it was being used > though bounding box comparisons are included in the join filter). Also > adding a lonlat bounding box comparison does speed the calculation up > significantly: > > SELECT a.gid, a.geom, count(*) AS num_points > > FROM mytable a > > JOIN mytable b > > ON a.gid<>b.gid > AND a.geom <#> b.geom < 0.02 -- in region where 0.02 degrees is > 1000 m > AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE) > > GROUP BY a.gid,a.geom > > ; > > > Is this the best approach or am I missing something? Would using a LATERAL > join improve things? > > Thanks, > David > > > -- > ********************************** > David M. Kaplan > Charge de Recherche 1 > > Institut de Recherche pour le Developpement (IRD) > UMR MARBEC (IRD/Ifremer/CNRS/UMII) > av. Jean Monnet > CS 30171 > 34203 Sete cedex > France > > Email: [email protected] > Phone: +33 (0)4 99 57 32 25 > Fax: +33 (0)4 99 57 32 95 > http://www.umr-marbec.fr/kaplan-david.htmlhttp://www.davidmkaplan.fr/ > ********************************** > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
