[postgis-devel] Predict returned geom in KNN with equal distance

[Regina Obe]

> -----Original Message-----
> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> Behalf Of Sandro Santilli
> Sent: Friday, August 13, 2021 6:36 AM
> To: postgis-devel at lists.osgeo.org
> Subject: [postgis-devel] Predict returned geom in KNN with equal distance
> 
> Is there a way to predict which geometry is returned from an KNN query
> (ORDER BY geom <-> $1 LIMIT 1) in case of equidistance ?
> 
> I think for sequencial scan the order is determined by the order data
entered
> the db or was updated (which I can control via clustering).
> 
> But how are geometries scanned in case of GiST index scan ?
> Would CLUSTER ON the_gist_index affect that return ?
> Would REINDEX INDEX the_gist_index affect that return ?
> 
> --strk;
> 
>   ()   Free GIS & Flash consultant/developer
>   /\   https://strk.kbt.io/services.html
[Regina Obe] 

You know PostgreSQL from PostgreSQL 13 on supports the ANSI standard FETCH
WITH TIES.

https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/


Try:

SELECT * FROM whatever
ORDER BY geom <-> $1 
FETCH FIRST 1 ROWS WITH TIES;