[openstreetmap/openstreetmap-website] Add a gpx_tracks table and convert trace points into linestrings (PR #7348)

[Ruben L. Mendoza]

Ref: https://github.com/openstreetmap/openstreetmap-website/issues/7339

This PR adds a gpx_tracks table that stores the geometry of each trace. We use geometry(GeometryZM, 4326), where Z is the altitude and M is the timestamp.

The table is filled in two ways:

- A rake task (`rake db:gpx_tracks`) converts the traces that already exist. It runs on its own and has to be started by hand. It skips the traces that already have rows, and a trace is written completely or not at all, so the task can stop and start again without leaving half traces.

- When a user uploads a new trace during the migration, a job converts it and saves it in gpx_tracks too, so new traces do not need the migration.


Nothing reads this table yet. The job only writes to it, so the API, the trace pages and the tiles work the same as before. The goal is to have all gps_points as linestrings in gpx_tracks, so the migration away from gps_points can later be done on top of this table.

What this PR does:

- New gpx_tracks table with a primary key of (gpx_id, trackid, segment), a geometry(GeometryZM, 4326) column, a GiST index on it, and a foreign key to gpx_files.
- A check constraint only allows ST_LineString and ST_Point, so nothing else can end up in the table.
- TraceLinestringJob runs after a successful import and does the work in a single INSERT ... SELECT, on the traces queue.
- Segments are cut every 500 points (max_points_per_track_segment in settings), so a long track becomes several rows instead of one very large geometry. 500 is a starting value, easy to change if the team prefers another number.
- Segments do not repeat the border point. To draw a full track, join them in order by trackid and segment.
- A track with a single point is saved as a point, because a line needs two.
- Points without a timestamp are skipped, because M cannot be null.
- Traces of any visibility are converted.
- The job is safe to run again for the same trace (a re-import or a retry): it replaces the rows of that trace in one transaction, so the result is always clean.
- Altitude uses 0 when the point has none.

You can view, comment on, or merge this pull request online at:

  https://github.com/openstreetmap/openstreetmap-website/pull/7348

-- Commit Summary --

  * Add a gpx_tracks table for trace linestrings
  * Add a job to convert trace points into linestrings
  * Write new traces to both tables
  * Add a rake task to backfill old traces

-- File Changes --

    M .database_consistency.yml (3)
    M app/jobs/trace_importer_job.rb (1)
    A app/jobs/trace_linestring_job.rb (55)
    A app/models/gpx_track.rb (25)
    M app/models/trace.rb (1)
    M config/settings.yml (2)
    A db/migrate/20260810150000_create_gpx_tracks.rb (16)
    M db/structure.sql (37)
    A lib/tasks/backfill_gpx_tracks.rake (41)
    M test/factories/tracepoints.rb (6)
    M test/jobs/trace_importer_job_test.rb (13)
    A test/jobs/trace_linestring_job_test.rb (122)

-- Patch Links --

https://github.com/openstreetmap/openstreetmap-website/pull/7348.patch
https://github.com/openstreetmap/openstreetmap-website/pull/7348.diff

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/7348
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/7348 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20260831/1564c96c/attachment.htm>