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