Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)
share|improve this question
    
Instead of trying to get the length first, can you check what the geometry is for your track? So how about we just SELECT ogc_fid, time, AsEwkt(wkb_geometry) FROM track_points LIMIT 5? Does that look like what you expected? Can you show us that data as an edit to the question? –  BradHards May 6 at 3:09
    
@BradHards I could not run AsEwkt - the function cannot be found. Instead I used ST_AsEWKT. –  JJD May 6 at 7:50

1 Answer 1

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.

share|improve this answer
    
Why do you avoid using the PostGIS functions? –  JJD Jul 5 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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