How to calculate the track length of GPX files with PostGIS?

Question

I am trying to calculate the length of a GPX track. The original GPS data looks like this:

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<gpx creator="nl.sogeti.android.gpstracker" 
  version="1.1" xmlns="http://www.topografix.com/GPX/1/1" 
  xmlns:gpx10="http://www.topografix.com/GPX/1/0" 
  xmlns:ogt10="http://gpstracker.android.sogeti.nl/GPX/1/0" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://www.topografix.com/GPX/1/1 
  http://www.topografix.com/gpx/1/1/gpx.xsd">
    <metadata>
        <time>2014-04-25T19:20:39Z</time>
    </metadata>
    <trk>
        <name>Track 2014-04-25 19:20</name>
        <trkseg/>
        <trkseg>
            <trkpt lat="52.51025036" lon="13.3907098">
                <ele>93.30000305175781</ele>
                <time>2014-04-25T19:59:24Z</time>
                <extensions>
                    <gpx10:speed>2.75</gpx10:speed>
                    <ogt10:accuracy>7.0</ogt10:accuracy>
                    <gpx10:course>236.5</gpx10:course>
                </extensions>
            </trkpt>
            <trkpt lat="52.51021522" lon="13.390611">
                <ele>94.0</ele>
                <time>2014-04-25T19:59:26Z</time>
                <extensions>
                    <gpx10:speed>2.5</gpx10:speed>
                    <ogt10:accuracy>7.0</ogt10:accuracy>
                    <gpx10:course>235.1999969482422</gpx10:course>
                </extensions>
            </trkpt>
            <!-- And many more -->
        </trkseg>
    </trk>
</gpx>

I imported the data into PostgreSQL/PostGIS. The track points have been imported into the table track_points:

gpxdata=# \d track_points
          Table "public.track_points"
       Column       |           Type           |
--------------------+--------------------------+
 ogc_fid            | integer                  | 
 wkb_geometry       | geometry(Point,4326)     | 
 track_fid          | integer                  | 
 track_seg_id       | integer                  | 
 track_seg_point_id | integer                  | 
 ele                | double precision         | 
 time               | timestamp with time zone | 
 magvar             | double precision         | 
 geoidheight        | double precision         | 
 name               | character varying        | 
 cmt                | character varying        | 
 desc               | character varying        | 
 src                | character varying        | 
 link1_href         | character varying        | 
 link1_text         | character varying        | 
 link1_type         | character varying        | 
 link2_href         | character varying        | 
 link2_text         | character varying        | 
 link2_type         | character varying        | 
 sym                | character varying        | 
 type               | character varying        | 
 fix                | character varying        | 
 sat                | integer                  | 
 hdop               | double precision         | 
 vdop               | double precision         | 
 pdop               | double precision         | 
 ageofdgpsdata      | double precision         | 
 dgpsid             | integer                  | 
 gpx10_speed        | double precision         | 
 ogt10_accuracy     | double precision         | 
 gpx10_course       | double precision         | 

Now I use the following query which I found here.

SELECT gps.ogc_fid, tracks.name, 
ST_Length_Spheroid(ST_MakeLine(gps.wkb_geometry), 
'SPHEROID["WGS 84",6378137,298.257223563]') AS track_len
    FROM (
        SELECT ogc_fid, time, wkb_geometry 
        FROM track_points 
        ORDER BY ogc_fid, time
    ) 
    AS gps, tracks
    WHERE gps.ogc_fid = tracks.ogc_fid
    GROUP BY gps.ogc_fid, tracks.name
    ORDER BY gps.ogc_fid;

The result of the query is:

   | ogc_fid |          name          |    track_len     |
   | integer |    character varying   | double precision |
----------------------------------------------------------
 1 |    1    | Track 2014-04-25 19:20 |        0         |

---

The following query posted by Brad ..

SELECT ogc_fid, time, ST_AsEWKT(wkb_geometry) FROM track_points LIMIT 5;

... produces this result:

ogc_fid |           time           | st_asewkt
integer | timestamp with time zone | text
------------------------------------------------------------------------------
   1    |  2014-04-25 19:31:13+02  | SRID=4326;POINT(13.38866662 52.54784071) 
   2    |  2014-04-25 19:31:14+02  | SRID=4326;POINT(13.3886988 52.54778812)
   3    |  2014-04-25 19:31:15+02  | SRID=4326;POINT(13.38873776 52.54773285)
   4    |  2014-04-25 19:31:16+02  | SRID=4326;POINT(13.38877592 52.5476608)
   5    |  2014-04-25 19:31:17+02  | SRID=4326;POINT(13.38881506 52.54760139)

Answer 1

I think I see your problem. You are grouping points (to make tracks) using gps.ogc_fid, but that gives you a single point in each track. You need to group using gps.track_fid:

SELECT gps.track_fid, tracks.name, 
ST_Length_Spheroid(ST_MakeLine(gps.wkb_geometry), 
                   'SPHEROID["WGS 84",6378137,298.257223563]') AS track_len
    FROM (
        SELECT track_fid, time, wkb_geometry 
        FROM track_points 
        ORDER BY ogc_fid, time
    ) 
    AS gps, tracks
    WHERE gps.track_fid = tracks.ogc_fid
    GROUP BY gps.track_fid, tracks.name
    ORDER BY gps.track_fid;

I've just tried this with a couple of my GPS tracks, and I get lengths consistent with those calculated by projecting into local coordinates.

Answer 2

create view track_with_distance as
select
    to.*,
    cast(case
        when from.latitude=to.latitude and from.longitude=to.longitude then 0
        else 1852*acos(sin(from.latitude/180*pi())*sin(to.latitude/180*pi())+cos(from.latitude/180*pi())*
            cos(to.latitude/180*pi())*cos(from.longitude/180*pi()-to.longitude/180*pi()))*
            180*60/pi()
    end as numeric) as meter_from_previous,
    to.tidspunkt-from.tidspunkt as time_from_previous
from track_points as to
    left outer join track_points as from on from.track_id=to.track_id and from.seq=to.seq-1;

where track_id is a unique identifier for the track and seq is a counter from 1 to the number of points in track.