[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] Nearest Neighbor problem using GIST Index (<-> function)
From:       Alexandre Neto <senhor.neto () gmail ! com>
Date:       2012-04-30 15:49:47
Message-ID: CA+H0G_Gkkf4B4N-w9PdZysLJAAW3C+0pO_nKeQe+_Tg6py58AQ () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


I'm trying to use Postgis 2.0 new function <-> (Geometry Distance
Centroid<http://postgis.refractions.net/docs/geometry_distance_centroid.html>)
in order to calculate, for each row of my table (cosn1), the distance to
the nearest polygon of the same class.

I was trying to use the following code:

WITH index_query AS (
  SELECT g1.gid As ref_gid, ST_Distance(g1.the_geom,g2.the_geom) As ENN
FROM "cosn1" As g1, "cosn1" As g2
WHERE g1.gid <> g2.gid AND g1.class = g2.class
ORDER BY g1.gid, g1.the_geom <-> g2.the_geom)
  SELECT DISTINCT ON (ref_gid) ref_gid, ENN
  FROM index_query
  ORDER BY ref_gid, ENN;

But then I realize the warning:


Note: Index only kicks in if one of the geometries is a constant (not in a
subquery/cte). e.g. 'SRID=3005;POINT(1011102 450541)'::geometry instead of
a.geom

Meaning that the Index wont be used at all, and the query will take almost
the same time as before using:

SELECT DISTINCT ON(g1.gid)  g1.gid As ref_gid,
ST_Distance(g1.the_geom,g2.the_geom) As ENN
FROM "cosn1" As g1, "cosn1" As g2
WHERE g1.gid <> g2.gid AND g1.class = g2.class
ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom)

Can anyone point me a workaround that allows me to improve performance of
my query?

Thank you very much.

Alexandre Neto

[Attachment #5 (text/html)]

<div>I&#39;m trying to use Postgis 2.0 new function &lt;-&gt; (<a \
href="http://postgis.refractions.net/docs/geometry_distance_centroid.html">Geometry \
Distance Centroid</a>) in order to calculate, for each row of my table (cosn1), the \
distance to the nearest polygon of the same class.</div> <div><br></div><div>I was \
trying to use the following code:</div><div><br></div><div><div>WITH index_query AS \
(</div><div>  SELECT g1.gid As ref_gid, ST_Distance(g1.the_geom,g2.the_geom) As ENN   \
</div><div><span class="Apple-tab-span" style="white-space:pre">		</span>FROM \
&quot;cosn1&quot; As g1, &quot;cosn1&quot; As g2   </div> <div><span \
class="Apple-tab-span" style="white-space:pre">		</span>WHERE g1.gid &lt;&gt; g2.gid \
AND g1.class = g2.class</div><div><span class="Apple-tab-span" \
style="white-space:pre">		</span>ORDER BY g1.gid, g1.the_geom &lt;-&gt; g2.the_geom) \
</div> <div>  SELECT DISTINCT ON (ref_gid) ref_gid, ENN </div><div>  <span \
class="Apple-tab-span" style="white-space:pre">	</span>FROM index_query</div><div>  \
ORDER BY ref_gid, ENN;</div></div><div><br></div><div>But then I realize the warning: \
</div> <div><br></div><div><br></div><div><span \
style="color:rgb(46,46,46);font-family:&#39;Lucida \
Grande&#39;,Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13px;text-align:-webkit-left;background-color:rgb(228,247,221)">Note: \
Index only kicks in if one of the geometries is a constant (not in a subquery/cte). \
e.g. &#39;SRID=3005;POINT(1011102 450541)&#39;::geometry instead of a.geom</span> \
</div><div><span style="color:rgb(46,46,46);font-family:&#39;Lucida \
Grande&#39;,Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13px;text-align:-webkit-left;background-color:rgb(228,247,221)"><br></span></div><div>Meaning \
that the Index wont be used at all, and the query will take almost the same time as \
before using:</div> <div><br></div><div><div>SELECT DISTINCT ON(g1.gid)  g1.gid As \
ref_gid, ST_Distance(g1.the_geom,g2.the_geom) As ENN    </div><div><span \
class="Apple-tab-span" style="white-space:pre">		</span>FROM &quot;cosn1&quot; As g1, \
&quot;cosn1&quot; As g2   </div> <div><span class="Apple-tab-span" \
style="white-space:pre">		</span>WHERE g1.gid &lt;&gt; g2.gid AND g1.class = \
g2.class</div><div><span class="Apple-tab-span" \
style="white-space:pre">		</span>ORDER BY g1.gid, \
ST_Distance(g1.the_geom,g2.the_geom)</div> </div><div><br></div><div>Can anyone point \
me a workaround that allows me to improve performance of my query? \
</div><div><br></div><div>Thank you very much.</div><div><br></div><div>Alexandre \
Neto</div><div><br></div><div><br></div><div style="text-align:-webkit-left"><font \
color="#2e2e2e" face="&#39;Lucida Grande&#39;, Verdana, Geneva, Arial, Helvetica, \
sans-serif"><br> </font></div><div><br></div>



_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]