Finding nearest line from points in SpatiaLite?

Question

I have writtern a query in Spatialite to get the nearest line from a point. I want to do that for all points. How can I modify the below codes:

SELECT g1.id As gref_id, g2.id As gnn_gid

FROM CLP_substations As g1, ib5000_contours As g2  

where g1.id = 1 and g1.id <> g2.id

ORDER BY ST_Distance(g1.geom,g2.geom) 

limit 1

Answer 1

Try with

SELECT  a.id,
        b.id,
        MIN(ST_Distance(a.geometry, b.geometry)) AS dist
FROM    <points> AS a,
        <lines> AS b
GROUP BY
        a.id
;

but note that this will be equally inefficient. There is currently no table function support within SQLite, so concepts like the LATERAL join are not available. This also means there can be no index enhanced (K)NN search.

---

With SpatiaLite 4.4.0-RC0 and above (5.0.0 is in active development) there seems to be KNN support via VirtualKNN tables. AFAIK, currently you'll have to compile both versions yourself, as both haven't been released.