I have a spatialite database containing every journey I have made every day since April 2003. I use this importer I co-wrote to populate a 'tracklines' table and a 'trackpoints' table from gpx files: ptrv/gpx2spatialite.
For another application (the geolocating of all my sms text messages!), I want to tag the points (probably in a separate table) with a time zone from the tz database, giving me a way of deriving the local time for these points as a starting point to matching up with text messages I have sent.
I have tried with python and the timezonefinder module but it is super slow, hence turning to spatialite since the points are in a spatialite database already. I then tried to follow some of Alessandro Furieri's excellent spatialite cookbook recipes but I can't find something that hits the spot.
I know I need to make a spatial index but I'm confused about whether the points or the time zones need one (or both?) and then what a moderately efficient query would be to associate millions of points with timezones. I say moderately because I would only have to run this once and store the results in a table (unless someone knows a better way?)