(K) Nearest Neighbor search

[erikatbeof]

Hi!
I while ago, 250523, I asked on discord about using DuckDB Spatial extension for (K) Nearest Neighbor search. Since then there have been a lot of progress and there is also the VSS extension. But still, I need help to sort this out. Can I do KNN search with Duckdb and extensions and in case I can, how and does it scale well?

Background:
With points (from table a) and linesegments (from table b), all in a 2D-plane. I would like to find the orthogonal projection of the points to the linesegments. This post Distinct st_shortestline explains the problem well and offers a solution in Postgis.

/Erik

See also:
#572
#573