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.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("--- %s seconds ---" % (time.time() - start_time))
  • Spatial index in SpatiaLite does not work transparently as in PostGIS. You must utilize spatial index table in a subquery or in a join. There are examples in this blog erouault.blogspot.com/2017/03/…. But I do not understand your idea about using multipoint for specifying the boundary of a district. Why not polygon? – user30184 Sep 9 at 11:15
  • @user30184 Thanks for your advice and it is a polygon. I will edit the question. Extending to your advice, I found this [blog] (blog.jrg.com.br/2016/05/23/…) which helped me to understand it. – Waterbyte Sep 9 at 12:01
  • Yes, search frame is a convenience in SpatiaLite that makes it easier to write the queries. – user30184 Sep 9 at 12:01
  • @user30184 However, running the query I found out that SpatialIndex was never created in the first place. The index_cursor is returning 0 and the resources I found till now don't give a reason as to when CreateSpatialIndex query could fail. – Waterbyte Sep 9 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.