Finding total distance of path along points in postgis

Question

We have a table which contains a list of points, originating from a GPS device's lat/long output every 20 seconds. We want to calculate the total distance travelled within a given time period. We tried out several combination of queries, but it seems that the distance calculated doesn't come near the actual distance travelled.

One of the query that tried so far:

SELECT ST_LengthSpheroid(ST_MakeLine(a.geom), 'SPHEROID["WGS 84",6378137,298.257223563]') AS track_len
    FROM (SELECT geom FROM
gps_data_99 where log_date between '2017-01-21 00:00:00' and '2018-01-22 23:00:00') as a;

But somehow we can't make it work correctly. The total distance travelled today would be around 25kms, and the value returned is:

    track_len
------------------
 203001.154494023
(1 row)

Already looked at: http://postgis-users.postgis.refractions.narkive.com/JvjGfK5X/newbie-get-total-distance-travelled-from-query-of-points

Total great circle distance along a path of points

We are just starting on PostGIS, so not sure how to get it done correctly.

Table Structure:
CREATE TABLE gps_data_99
(
    gps_data_id bigint NOT NULL DEFAULT nextval('gps_data_99_gps_data_id_seq'::regclass),
    log_date timestamp without time zone DEFAULT now(),
    device_id bigint,
    geom geometry(Geometry,4326),
    CONSTRAINT gps_data_99_pkey PRIMARY KEY (gps_data_id),
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Environment:
Postgresql 10.1
Postgis: 2.4 (2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1)
OS: Debian 9.3

Answer 1

Your query doesn't work because you are simply piling all points between two timestamps together here:

WHERE log_date BETWEEN '2017-01-21 00:00:00' AND '2018-01-22 23:00:00'

This means there is no guarantee that the line you create will actually be in order.

You want to append above statement like this:

WHERE log_date BETWEEN '2017-01-21 00:00:00' AND '2018-01-22 23:00:00' ORDER BY log_date

Alternatively, if you know that your gps_data_id is incremental for every new point, you can also ORDER BY gps_data_id

Bonus tip: Whenever you get odd-resulting geometries, it's always good to inspect them visually. Fire up QGIS, load in the geometry (QGIS can easily read directly from PostGIS databases) and check what's going on. Even when you're still stuck, you at least have a screenshot to present alongside your question here on Stackexchange, making it a lot easier for everyone else.