[GIS] Store GPS trace with timestamps, then interpolate location by time

gpsinterpolationpostgistimestampvideo

I'd like to:

  1. Load a 1-second interval GPS trace (not GPX) into PostGIS
  2. Then query the table at sub-second accuracy (about 5Hz), and get PostGIS to interpolate the location by time.

Is this possible, or am I dreaming?

The need is trying to find the lat/long of each frame in a Hawkeye video file, given a companion GPS trace in CSV format. If there are better ways to achieve this, I'm very open to them!

I'm familiar with PostGIS, but would consider other tools. I don't have access to ESRI.

The GPS data looks like this:

UniqueKey,PrimaryKey,PrimaryKeyUnsync,UTCTime,Latitude,Longitude,Altitude,FixType,SatsUsedInFix,HDOP,GPSSpeed(km/h),GPSDistance(km),IsSOWPulsed
0,59375222881843,59375222881843,011806.0,-38.197204000,146.533188000,42.7,2,12,0.7,0.005556,234.576104,1
1,59375222981747,59375222981747,011807.0,-38.197204167,146.533188000,42.7,2,12,0.7,0.016668,234.576109,1
2,59375223081754,59375223081754,011808.0,-38.197204167,146.533188000,42.7,2,12,0.7,0.024076,234.576109,1
3,59375223181858,59375223181858,011809.0,-38.197204333,146.533187833,42.7,2,12,0.7,0.040744,234.576109,1

The video frame data looks like this:

File entry count,Primary key, Frame number for the corresponding AVI file of the same name,UnsyncedPkHb3,UnsyncedPkHb2,LightCond,Brght,Gamma,Shut,ReqLum,Lum,Gain,Iris,Timestamp
1,59375291559140,0,59375291574176,59375291574176,9,0,110,625,10,0.00,268,0,1970:01:01 04:37:24.382478
2,59375291649744,1,59375291664882,59375291664882,9,0,110,625,10,0.00,268,0,1970:01:01 04:37:25.288534
3,59375291740362,2,59375291755985,59375291755985,9,0,110,625,10,0.00,268,0,1970:01:01 04:37:26.194716
4,59375291831084,3,59375291846200,59375291846200,9,0,110,625,10,0.00,268,0,1970:01:01 04:37:27.101950

EDIT

In response to the question regarding output format:

Inputs:

  • Table of lat-longs, recorded at 1Hz
  • Table of photo file names, recorded at 5Hz, without lat-longs

Output:

  • Table of photo file names, recorded at 5Hz, with lat longs.

Best Answer

This isn't exactly what your looking for, but if your comfortable with python i've got a script that takes a gpx file and exif tags photos with location, including interpolating between points, and it could easily be modified to read the GPS data from a format other than gpx.

https://gist.github.com/JesseCrocker/9c4b8e334ceba63f3d08

Related Question