[Qgis-user] QGIS is great! (and fixed the broken geotagging in my Insta360 photos)

[Adam Nielsen]

Hi all,

This isn't a request for help, I just wanted to give a big thanks to
everyone involved in the development of QGIS.  I have never used a
program that seems to be able to cater to everything I might want to do.

I recently purchased an Insta360 camera, with the goal of going walking
and having it collect a bunch of 360 degree photos, that I could turn
into my own private Google Street View.

Unfortunately having just loaded the photos in using the "import
geotagged photos" tool, it turns out that in their infinite wisdom, the
Insta360 developers thought it would be sufficient to just use the GPS
coordinates of the first photo taken.  So now I have 400 photos at the
exact same GPS coordinate, making the camera entirely unsuitable for
Street View photography!  (Funny how you find all the complaints on
Google only AFTER you've bought a product.)

However I was also recording my track using the GPS Logger app on my
phone.  I wondered whether perhaps there was a way to somehow apply
that GPS track against the photos.

As it turned out, the .gpx export from the app includes a track_points
table, which has one row every second with the current GPS coordinates.
This means there is a timestamp in that table, with GPS coordinates,
for every timestamp in the photo table!  Unfortunately one is UTC and
the other is local time, but a little maths should be able to solve
that.

In the end I worked out that I could create a virtual layer, which uses
an SQL query to join the two tables together and pick the GPS
coordinates out of the GPS track but the rest of the data from the
photos table.  This worked perfectly and now I have a proper layer with
each of the 400 photos at the correct location!

This was another feature I never thought I would need but QGIS provided
the means to get it done.

In case anyone else ever needs to do something similar, here is the
query I used:

  SELECT
    "photos"."fid",
    "photo",
    "filename",
    "directory",
    "ele" AS "altitude",
    "direction",
    "rotation",
    X("track_points"."geometry") AS "longitude",
    Y("track_points"."geometry") AS "latitude",
    "timestamp",
    "track_points"."geometry" AS "geometry"
  FROM "photos"
  JOIN "track_points"
    ON unixepoch("track_points"."time") = unixepoch("photos"."timestamp") - 36000;
    -- (36000 converts UTC+10 into UTC)

To use it, create a new virtual layer, import the geotagged photos
layer (and call it "photos") then add the "track_points" layer from the
.gpx file (and call it "track_points") then click the Test button and
it should come back as OK.  Then you can add the layer.

Just be aware that it's quite slow and makes QGIS freeze for a few
minutes - you just have to leave it alone and it will eventually come
back to life.  The first thing I did was to quickly hide the new virtual
layer seconds after I added it (to stop it from rendering and freezing
QGIS), then I exported the virtual layer to a new layer in a temporary
.gpkg file.  This way I could remove the slow virtual layer and continue
working with the faster temporary .gpkg file.

Anyway, I just wanted to say thanks for all the effort everyone has put
into such a fantastic program!

Cheers,
Adam.