[postgis-users] confusion regarding use of indexes when doing geography calculations

[David M. Kaplan]

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: david.kaplan at ird.fr
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95

http://www.umr-marbec.fr/kaplan-david.html
http://www.davidmkaplan.fr/
**********************************

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190115/5d6ac748/attachment.html>