Creating Linestring from GPX file with M coordinate as time

Question

I have several GPX files that I'm trying to load into a PostGIS table, with the goal of map-matching them (I intend to use pgMapMatch but open to other services I can run on my own machine). pgMapMatch requires the GPS trace to be a linestring with an M coordinate indicating the timestamp of each point. I cannot figure out how to do this.

There's an answer here Storing GPS tracks with timestamps in PostGIS that suggests storing each gps track_point in a table, then converting to a linestring with ST_Makeline. I think it could work, but I need to figure out how to get ST_Makeline to consume a point geom with just an x,y coordinate and a separate time value, and combine them to produce a linestring with points with x,y,m coordinates.

The best I've been able to do so far is get the each gpx track layer as its own multilinestring with just x,y coordinates using

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres port=5432 dbname=my_db_name password=my_password" my_gpx_filename.gpx -overwrite -lco GEOMETRY_NAME=geom -nln "my_table_name" track_points

I've looked over the documentation on the GDAL site for both the GPX format (https://gdal.org/drivers/vector/gpx.html) and PostGIS format (https://gdal.org/drivers/vector/pg.html), but haven't figured it out

Answer 1

Once you have the data in Postgres/Postgis, you can use st_makeline and st_makepointm. You can run this query in the terminal after you connect to your database, or use pgadmin.

create table gpx_linestringm as
select track_id, st_setsrid(st_makeline(geom order by t),<your srid ie 4326>)::geometry(linestringm,<your srid>) as geom from
(select track_id, time_value as t, st_makepointm(x_coord, y_coord, time_value) as geom
  from gpx_track_points
) foo
group by track_id;

Replace with the spatial reference id you're using. Replace the table and column names to fit your data.