SOLVED: f_table_name was not working as no spatialindex was created/defined using just the tutorial for VirtualKNN - Solution: Run the query
SELECT CREATESPATIALINDEX('tablename','geom_col')
Original Problem
First time using spatialite - followed a tutorial to set-up my database and ran a basic distance function to get the nearest objectid - works great but I have A LOT of points so I decided to use the virtualKNN - ran it on the same point to test but KNN returns nothing - tried without the PointFromText as well but it doesn't seem to work.
What am I doing wrong?
My end goal is to run a KNN from another bunch of points from another table but this is throwing up a blank table as well.
Looks like something wrong with the way i am calling f_table_name?
I have a table called LION but i guess it is not able to see that?
I get a blank result when i call anything in the between the '' that doesn't exist so f_table_name is having trouble reading my table?
Edit - Added code
This is the distance function that works
query='''
Select OBJECTID, Distance(PointFromText('POINT(-73.9215944000000036 40.8278258999999935)'),wkb_geometry) as distance
,wkb_geometry
FROM LION
Order by distance
'''
test=pd.read_sql_query(query,db)
test[~test['distance'].isnull()]
This is the knn query that returns a null table because it can't read f_table_name
query='''
Select * FROM knn
WHERE f_table_name = 'LION'
AND ref_geometry = MakePoint(-73.9215944000000036, 40.8278258999999935);
'''
test=pd.read_sql_query(query,db)
test.head(100)
