So I want to add 20 points, run Spatialite knn and find 10 points. I created a simple test in colaboratory:

import os
from datetime import datetime
import json
from pprint import pprint
from playhouse.sqlite_ext import *
from peewee import *
from playhouse.kv  import *
from playhouse.shortcuts import model_to_dict
from playhouse.reflection import generate_models, print_model, print_table_sql
from IPython.display import SVG
import folium

db = SqliteExtDatabase('./my_database.db')

print(db.execute_sql("SELECT load_extension('mod_spatialite.so');").fetchall())
print(db.execute_sql("SELECT InitSpatialMetaData(); ").fetchall())
print(db.execute_sql("SELECT sqlite_version(), spatialite_version();").fetchall())
#print(db.execute_sql("CREATE VIRTUAL TABLE knn2 USING VirtualKNN2();").fetchall())
class BaseModel(Model):
    class Meta:
        database = db

class GeometryField(Field):
    db_field = 'geometry'

def geometryFromWKT(wellknowntext):
    return fn.ST_GeomFromText(wellknowntext)

class Tbl(BaseModel):
    geometry = GeometryField()

models = generate_models(db)

Tbl.insert(geometry=geometryFromWKT('POINT(4 9)')).execute()
Tbl.insert(geometry=geometryFromWKT('POINT(9 9)')).execute()
Tbl.insert(geometry=geometryFromWKT('POINT(14 9)')).execute()
Tbl.insert(geometry=geometryFromWKT('POINT(4 19)')).execute()
Tbl.insert(geometry=geometryFromWKT('POINT(41 9)')).execute()
Tbl.insert(geometry=geometryFromWKT('POINT(14 19)')).execute()

cmd = """SELECT AsGeoJSON(ST_Union(CastToXY(geometry)), 5, 3)  from Tbl"""
data_all = db.execute_sql(cmd).fetchall()[0][0]

cmd = """SELECT * FROM knn
WHERE f_table_name = 'Tbl' AND ref_geometry = GeomFromText('POINT(5 5)') AND f_geometry_column = 'geometry' AND max_items  = 2;"""
data_knn = db.execute_sql(cmd).fetchall()

Yet it finds 0 points outputting:

[('3.34.0', '5.0.1')]

Similar thing happens to me if I use knn2 here. 0 points after some are definitely inserted. Insertion is performed in the same style ref_geometry is provided. And I do not understand how I my simple table is not compatible with knn. So how to insert points, do knn search in Spatialite?


1 Answer 1


Before you can use KNN or KNN2 the table must be registered into "geometry_columns" with RecoverGeometryColumn, and it must have a spatial index created with CreateSpatialIndex.

The requirement to have a geometry column and spatial index is indirectly documented in https://www.gaia-gis.it/fossil/libspatialite/wiki?name=KNN

In both cases f_table_name and f_geometry_column must exactly match a properly defined Geometry column supported by a corresponding Spatial Index.

I could not find KNN2 from available Windows binaries so I used KNN for testing.

Python does not play role here so I write a test case that contains just the SQL that is required.

create table tbltest (geometry);
insert into tbltest (geometry) select st_geomfromtext('POINT(41 19)');
insert into tbltest (geometry) select st_geomfromtext('POINT(4 9)');
insert into tbltest (geometry) select st_geomfromtext('POINT(9 9)');

At this stage the following query returns an empty resultset:

select * from knn WHERE f_table_name = 'tbltest' AND ref_geometry = MakePoint(10, 43);

Recover geometry column, create spatial index and try again:

select RecoverGeometryColumn('tbltest','geometry',0,'POINT');
select CreateSpatialIndex('tbltest','geometry');

select * from knn WHERE f_table_name = 'tbltest' AND ref_geometry = MakePoint(10, 43);

tbltest geometry    BLOB sz=60 GEOMETRY 3   1   3   34.014703
tbltest geometry    BLOB sz=60 GEOMETRY 3   2   2   34.525353
tbltest geometry    BLOB sz=60 GEOMETRY 3   3   1   39.204592
  • Could you please share the code for KNN version?
    – DuckQueen
    May 4, 2022 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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