3

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?)

Improve this question

1 Answer 1

Reset to default
2

You might try to download the shapefile of timezones from here. Import the whole polygon shapefile into your spatialite database, then contruct an update query using ST_Contains() on the points table to find which tz it's in.

You will definitely want to setup an index on the timezones table, and use it in your query.

(edit...) Referring to the comment below: I think you have the spatial index backwards. You created an index on the timezones table (correctly) but you refer to a index on the trackpoints table (incorrect). It should be:

... AND z.ROWID in (SELECT ROWID from SpatialIndex WHERE f_table_name='tz_world_mp' AND search_frame = t.geom)

Improve this answer
3
  • I imported the timezones shapefile and made a spatial index on it and tried the following sql: select trkpt_uid, utctimestamp, x(t.geom) as long, y(t.geom) as lat, tzid from trackpoints as t, tz_world_mp as z where user_uid = 1 and strftime('%Y-%m-%d', utctimestamp) = '2015-01-01' and ST_Contains(z.Geometry, t.geom) order by utctimestamp I only performed the test on tracks from one day (1129 trackpoints) which took 2m35s. I tried adding and t.ROWID in (SELECT ROWID from SpatialIndex WHERE f_table_name='trackpoints' AND search_frame = c.geom) after 35 minutes I gave up.
    – Belasco
    Sep 13, 2016 at 20:58
  • Last week I edited my answer, responding to your comment. Did that help?
    – Micha
    Sep 22, 2016 at 10:21
  • You hit the nail on the head! I'd completely misunderstood what table I should be running the spatial index query and what the 'search_frame' should be. My query for those 1129 trackpoints is now down to a couple of seconds!
    – Belasco
    Sep 23, 2016 at 12:30

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.