<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Hi,<br>
<br>
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?<br>
<br>
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:<br>
<br>
<pre>SELECT a.gid, a.geom, count(*) AS num_points</pre>
<pre>FROM mytable a</pre>
<pre>JOIN mytable b </pre>
<pre> ON a.gid<>b.gid
AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE)</pre>
<pre>GROUP BY a.gid,a.geom</pre>
<pre>;</pre>
<br>
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:<br>
<br>
<pre>SELECT a.gid, a.geom, count(*) AS num_points</pre>
<pre>FROM mytable a</pre>
<pre>JOIN mytable b </pre>
<pre> 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)</pre>
<pre>GROUP BY a.gid,a.geom</pre>
<pre>;</pre>
<br>
Is this the best approach or am I missing something? Would using a
LATERAL join improve things?<br>
<br>
Thanks,<br>
David<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
**********************************
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: <a class="moz-txt-link-abbreviated" href="mailto:david.kaplan@ird.fr">david.kaplan@ird.fr</a>
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95
<a class="moz-txt-link-freetext" href="http://www.umr-marbec.fr/kaplan-david.html">http://www.umr-marbec.fr/kaplan-david.html</a>
<a class="moz-txt-link-freetext" href="http://www.davidmkaplan.fr/">http://www.davidmkaplan.fr/</a>
**********************************
</pre>
</body>
</html>