I am trying to calculate the length of a GPX track. The original GPS data looks like this:
2014-04-25T19:20:39Z Track 2014-04-25 19:20 93.30000305175781 2014-04-25T19:59:24Z 2.75 7.0 236.5 94.0 2014-04-25T19:59:26Z 2.5 7.0 235.1999969482422 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)
أكثر...
2014-04-25T19:20:39Z Track 2014-04-25 19:20 93.30000305175781 2014-04-25T19:59:24Z 2.75 7.0 236.5 94.0 2014-04-25T19:59:26Z 2.5 7.0 235.1999969482422 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)
أكثر...