Spatialite: insert points, do knn search. how to?

Question

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')
db.connect()
db.connection().enable_load_extension(True)

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.create_table()

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]
print(data_all)

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()
print(data_knn)

Yet it finds 0 points outputting:

[('3.34.0', '5.0.1')]
{"type":"MultiPoint","bbox":[4,9,41,19],"coordinates":[[4,9],[4,19],[9,9],[14,9],[14,19],[41,9]]}
[]

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?

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