Tuesday, 8 May 2018

Nearest Neighbor problem in Postgis 2.0 using GIST Index ( function)


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.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...