Calculate distance travelled using postgis

Question

I am trying to write my fist time postgis query

My table looks like below

time (with time time zone), track_id, geography(geography(Point, 4326))

I am trying to calculate distance on particular track within some time range. e.g

select track_id, distance (in meters) from mytable where time between x and y group by track_id

I saw couple of posts online but none of those work with geography or does not take multiple geography points

CREATE TABLE data (
    time        TIMESTAMPTZ         NOT NULL,
    track_id    VARCHAR NOT NULL
    geography geography(point,4326) 
);

Sample data

"2017-04-05 09:10:12-04";"1";"0101000020E6100000689B768C864A5540E00CA3C5BE953940"
"2017-04-06 15:23:10-04";"1";"0101000020E6100000358584DED14B55408F49905BDD953940"
"2017-05-12 05:30:03-04";"1";"0101000020E610000008420D3A7C50554060BE068E15903940"
"2017-05-20 01:19:24-04";"1";"0101000020E61000001D5C959F2F805540EEDBFFB6DDC23940"
"2017-05-20 01:17:52-04";"1";"0101000020E6100000E3F50599897F55406523C621D1C03940"

So, this is gps data of a vehicle whose id is one. I need to calculate total distance travelled by vehicle say for date 2017-05-20 in meters. There can be other views with different id.

Reference eg. https://gis.stackexchange.com/questions/268776/finding-total-distance-of-path-along-points-in-postgis but this is taking geometry instead of geography

Could you elaborate and put an actual table creation statement (DDL). Also, 3/4 lines of sample data are great You want to see the distance regarding of multiple point ? I so, the steps are : * list all the point * create a line with it * st_length of the line — Rémi Desgrange
As @RémiDesgrange says it is tough to know without seeing more details. However if you look at stackoverflow.com/questions/42861999/… the example query I post may give you some clues on how to calculate the distance along a path. — Steven W. Klassen

Bruno Carballo Bruno Carballo · Accepted Answer · 2019-01-07T18:03:34

You can use ST_MakeLine to aggregate the points an then ST_Length, which can take geography arguments to get the distance

SELECT trak_id, ST_Length(ST_MakeLine("geography"::geometry)::geography) as d
FROM data 
GROUP by track_id --optionally group by date for example

Calculate distance travelled using postgis

1 Answers