[postgis-users] Nearest Neighbor problem using GIST Index (<-> function)

[Alexandre Neto]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120430/a950e2ed/attachment.html>