25

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.

4
  • 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. May 9, 2012 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 May 9, 2012 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. May 9, 2012 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, 2012 at 14:55

1 Answer 1

1

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
FROM 
  "cosn1" AS g1, "cosn1" AS g2
WHERE
  g1.gid <> g2.gid
  AND g1.class = g2.class
  AND g1.the_geom && g2.the_geom
GROUP BY
  g1.gid
6
  • 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. Dec 27, 2012 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, 2012 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. Dec 27, 2012 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, 2012 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. Dec 27, 2012 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.