1

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?

enter image description here

enter image description here

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.

enter image description here

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?

enter image description here

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)
3
  • Hi @KadirŞahbaz - given you don't have access to my DB i thought the code was less relevant and i was really trying to highlight the output that works for distance functions but not the KNN. The code is basically from the tutorials adapted from the tutorial below - gaia-gis.it/fossil/libspatialite/wiki?name=KNN I guess the point was trying to make was that f_table_name doesn't seem to work (i.e its calling blanks info even when the table exists
    – xovut
    May 11 at 13:03
  • If you make it impossible for folks to cut/paste your code, you run the risk that they won't bother to try.
    – Vince
    May 11 at 21:13
  • @Vince sure understand. Added the code for the 2 queries - the distance that works and the knn that fails
    – xovut
    May 11 at 23:06
2

Maybe you should follow the tutorial https://www.gaia-gis.it/fossil/libspatialite/wiki?name=KNN and function reference manual http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html with the airport data and make sure that you have a working system before switching to your own data.

You commands seem to be mostly OK but this part in one of your tests is wrong:

ref_geometry=PointFromText(MakePoint...

MakePoint functions returns a geometry object, and passing that to PointFromText returns NULL. Therefore no selected lines from KNN is an expected result. Use either MakePoint or PointFromText with correct syntax and your first query should return results. However, I see that you have used just MakePoint inother case.

You do not need to include f_geometry_column if your table does not have many geometry fields. That would simplify the SQL a bit.

12
  • Thanks! I actually did follow the tutorial - then i tried other things to troubleshoot. Thought it was having trouble finding the f_geometry_column so i added it (docs say it is not required). Thought maybe it was unable to read my point (so i messed with the point function a bit) Turns out the f_table_name is not reading my table (even tho its there and is a geospatial table) but can't figure out why or how to fix. I can put anything after the = and it will return a blank which suggest that when i put my actual table its not even reading that either.
    – xovut
    May 11 at 13:13
  • How did you create your own database? Does the test database gaia-gis.it/gaia-sins/knn/airports.7z work in your environment?
    – user30184
    May 11 at 13:26
  • The test data base works in my environment - just ran it and the KNN runs fine. I created my db using sqlite3 in python: 1. Upload without geoms to_sql 2. AddGeometryColumn after InitSpatialMetaData 3. Convert geoms to WKB then Update/Set/Where on id to update the table with the geoms
    – xovut
    May 11 at 14:17
  • What if you DROP TABLE knn and then CREATE VIRTUAL TABLE knn USING VirtualKNN(); for creating it again?
    – user30184
    May 11 at 15:23
  • I tried that but still doesn't work However, I took a look at the files in the airport.sqlite and my db and noticed that in the example there is a idx_airports_geom table that creates a virtual table using rtree that I don't have in mine for my tables (along with idx_airports_geom_node, _parent _rowid). From what i understand the KNN runs off RTree so it seems like i need this table?
    – xovut
    May 11 at 22:57

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.