I am querying a sqlite format geodatabase in which there is a label for each geometry shape.
Think of data as :
label = name of a district in your state geometry: polygon which specify the boundary of that district.
Now, I have coordinates of a person and I want to find out in which state he resides. The problem is that there are around 170 thousand districts and running my query for each input i.e. coordiantes takes around 7 seconds. After reading other articles, I tried spatial index but the results are same. It still takes around the same time. I guess I am not using it properly. I need a bit of advice on how can I improve the query time.
Code involved [Python 3.5]:
voting_district_conn= sqlite3.connect('Voting_Districts.sqlite') voting_district_conn.enable_load_extension(True) voting_district_conn.execute("SELECT load_extension('mod_spatialite')") ... index_cursor = voting_district_conn.execute("SELECT CreateSpatialIndex('voting_districts','GEOMETRY')") for item in index_cursor: print(item) #returning (0,) ... start_time = time.time() voting_district_cursor = voting_district_conn.execute("SELECT label from voting_districts where within(GeomFromText('POINT("+lng+" "+lat+")'),GeomFromWKB(GEOMETRY))") for item in voting_district_cursor: print(item) print("--- %s seconds ---" % (time.time() - start_time))