I'm trying to use Postgis 2.0 new function <-> (Geometry Distance Centroid) 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.

  • 3
    You can use g1.gid>g2.gid in the where clause, which will reduce the number of distance calculations you have to do. Unfortunately, until the <-> operator works without constants, we won't see much of speed improvement in this kind of query. – John Powell May 9 '12 at 12:39
  • John, I need to keep all the gids, even those that are repeated as I need to update the EEN for each one of the polygons in my "cosn1" table. But what you said gave me an idea. I could do as you say using g1.gid > g2.gis to reduce distance calculations, but keeping g1.gid and g2.gid in the result. After that, I could union two subqueries of it (one with g1.gis as gid, and other with g2.gid). Thanks – Alexandre Neto May 9 '12 at 16:20
  • I found that a possible solution to workaround the constant problem would be to use the <-> inside a SQL Function, using the_geom as a parameter. I have made some tests, and in some cases its much faster (). But in my case, since distances are inside the same table, many distance calculations are repeated during the process, making it slower than using the direct query. – Alexandre Neto May 9 '12 at 16:31
  • I assume that using ST_DWithin() is not relevant in this case? Also I don't know if it would make any difference but perhaps you could use SELECT .... LIMIT 1 on your second query instead of SELECT DISTINCT ON – djq Dec 28 '12 at 14:55

Doing some tests on my machine suggested this operator <-> is not working properly. I am not sure that is a bug but it reported zero distance on not overlapped geometries.

I tried the fair traditional SQL query optimizations. Since those unexpected results with <-> operator I replace it with st_centroid. Got much better results in speed.

Hope semantics with st_overlaps keep same. At least this was I understood from documentation about <->

From docs on Postigs <->

For other geometry types the distance between the floating point bounding box centroids is returned.

On my test data with ~5.5k polygons got speed up from ~1000 seconds to ~5 seconds without spatial indexing.

I see some people using DISTINCT ON to do grouping but not the group by exists to eliminate duplicates.

Your query with standard SQL optimizations without the st_centroid error introduced

select g1.gid, min( st_distance( g1.the_geom, g2.the_geom ) ) AS enn
  "cosn1" AS g1, "cosn1" AS g2
  g1.gid <> g2.gid
  AND g1.class = g2.class
  AND g1.the_geom && g2.the_geom
  • Sorry but your answer does not solve the problem. It's in fact much faster, but the results are not accurate, since the final result is calculated using the centroids of the polygons, instead of their real geometry. The <-> aims to optimize the search for candidates to nearest neighbour but in the end should use the real geometries to calculate the distance to the best candidates. I also tried using the MIN\GROUP BY instead of the DISTINCT ON \ ORDER BY, and it seems to be slower. – Alexandre Neto Dec 27 '12 at 14:55
  • But the postgis manual for the operator <-> states that it uses centroid for non point geometries. So my solution would give you similar results. It should give you same results as your top query. Please check that results with the operator <-> are correct also. It reported me zero length geometries on my test data so the results of it could be broken and this solution given more accurate data. If you able to post some sample records showing errors at some pastie site we could discover flaws on solution. – cavila Dec 27 '12 at 15:11
  • If you check my query, the <-> operator would be used only to order the candidates, the final result is calculated using the actual geometries. Anyway, like I said before, the <-> performance boost only works with fixed points. That was my original question. – Alexandre Neto Dec 27 '12 at 15:38
  • So, do you agree that the top query is not equivalent to bottom query? Since the order will change because the operator <-> will ORDER BY st_centroid and the st_distance will give you a differente value? Different order can bring a different query as the first row to pass on DISTINCT ON clause? The valid query would be bottom one that needs speed improvement? – cavila Dec 27 '12 at 17:51
  • Yes, the first query is an intent to improve speed on the bottom one. And yes, it might give a bit different result, since g1.geom <-> g2.geom uses the centroids, and that means that the first row might not be the closer. In order to make it work I believe I would have to put a limit to the order by clause say limit 10 then extract the real values of distance. Could even use the <#> instead, that uses the bounding boxes instead of the centroids. – Alexandre Neto Dec 27 '12 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.