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

Try with

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

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.

  • I find it a bit confusing to follow the SpatiaLite release management; feel free to correct me if the version considerations are not correct.
    – geozelot
    Mar 25 '19 at 12:12
  • 1
    There are 5.0-beta Windows binaries available from gaia-gis.it/gaia-sins/index.html through the "next generation" links.
    – user30184
    Mar 25 '19 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.