2

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.
Improve this question
7
  • 1
    Yes, you should be able to use ST_Line_Interpolate_Point for this, using the change in time and required framerate to calculate the second parameter. I would have put up an example, but I am not too sure what the units are in your UTCTime column, as 0118806.0 doesn't look like a unix timestamp or current UTC time.
    – John Powell
    Commented Dec 9, 2014 at 6:30
  • Loading the data is straightforward, using the copy command, and ST_MakePoint in an update to calculate a geometry column (point), that could be used as a basis for ST_Line_Interpolate_Point. Obviously, if this is dynamic, this update could be put in a trigger, or similar.
    – John Powell
    Commented Dec 9, 2014 at 6:35
  • 1
    What is the actual output you would like to have? Do you want to have individual frames from the video saved with lat/lon text, or a companion file that shows position for each frame during a video?
    – Simbamangu
    Commented Dec 9, 2014 at 7:52
  • Related: gis.stackexchange.com/questions/53641 and gis.stackexchange.com/questions/9736
    – Chris W
    Commented Dec 9, 2014 at 20:42
  • @JohnBarça, yeah I'm not sure about that column. I suspect it's a seconds-since-epoch that matches the faulty 1970-01-01 date in the other table. So 011806.0 (not 118806) is about 3am UTC, or around 2pm local time (Victoria, Australia). (The two fragments I quoted don't correspond exactly.)
    – Steve Bennett
    Commented Dec 10, 2014 at 2:58

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.