SOLVED: f_table_name was not working as no spatialindex was created/defined using just the tutorial for VirtualKNN - Solution: Run the query
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)