Hello all, I'm trying to update a table of polygons with the distance to the nearest polygon in the same table and of the same class. I suspect that there is something fishy about the way my query is build.
I was using something like this: UPDATE cosc.cosn1 SET enn = c.ENN FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid, ST_Distance(g1.geom,g2.geom) As ENN FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2 WHERE g1.gid < g2.gid AND g1.class = g2.class ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c WHERE gid = c.ref_gid; It's was taking a long time, but that wasn't surprising. I got my results in 150000ms. While doing some tests I tried no narrow the query a bit by specifying the gid of a feature in the bottom WHERE statement (of the update). UPDATE cosc.cosn1 SET enn = c.ENN FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid, ST_Distance(g1.geom,g2.geom) As ENN FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2 WHERE g1.gid < g2.gid AND g1.class = g2.class ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c WHERE gid = 2 AND gid = c.ref_gid; Also not surprising, this took a lot less, around 300ms What started to bug me was the fact that using a different condition to narrow the query by only updating 10 features... UPDATE cosc.cosn1 SET enn = c.ENN FROM (SELECT DISTINCT ON(g1.gid) g1.gid As ref_gid, ST_Distance(g1.geom,g2.geom) As ENN FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2 WHERE g1.gid < g2.gid AND g1.class = g2.class ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c WHERE gid < 10 AND gid = c.ref_gid; It needed 147510ms to show the results, almost the same as updating the all table! Even more weird was the fact that if I manual set gid = x from [1,10] none of the queries toke more that 400ms to perform. Therefore, I must do something wrong for sure! Thank you for your help, Alexandre Neto
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
