From 08ef0ecdd82df12d0b73e0783d69d151b259a322 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Tue, 25 Aug 2026 17:19:45 +0200
Subject: [PATCH] use knn search to parent with nearest road

---
 lib-sql/functions/partition-functions.sql | 19 +++++++++----------
 1 file changed, 9 insertions(+), 10 deletions(-)

diff --git a/lib-sql/functions/partition-functions.sql b/lib-sql/functions/partition-functions.sql
index 632497be..780e2801 100644
--- a/lib-sql/functions/partition-functions.sql
+++ b/lib-sql/functions/partition-functions.sql
@@ -287,21 +287,20 @@ CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEO
   AS $$
 DECLARE
   r RECORD;
-  search_diameter FLOAT;
 BEGIN
 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
-    search_diameter := 0.00005;
-    WHILE search_diameter < 0.1 LOOP
-      FOR r IN
-        SELECT place_id FROM location_road_{{ partition }}
-          WHERE ST_DWithin(geometry, point, search_diameter)
-          ORDER BY ST_Distance(geometry, point) ASC limit 1
-      LOOP
+    FOR r IN
+      SELECT place_id, ST_Distance(geometry, point) as dist
+        FROM location_road_{{ partition }}
+        ORDER BY geometry <-> point
+        LIMIT 1
+    LOOP
+      IF r.dist < 0.1 THEN
         RETURN r.place_id;
-      END LOOP;
-      search_diameter := search_diameter * 2;
+      END IF;
+      RETURN NULL;
     END LOOP;
     RETURN NULL;
   END IF;
-- 
2.47.3