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

[Ruben L. Mendoza]

@Rub21 commented on this pull request.



> @@ -61,6 +61,8 @@ max_note_query_limit: 10000
 max_issues_count: 99
 # Maximum number of points in a GPX trace
 max_trace_size: 1000000
+# Maximum number of points in one gpx_tracks segment
+max_points_per_track_segment: 500

It is not a random number, but I did not benchmark it either.

The reason for cutting the track is the one the PostGIS docs give for [`ST_Subdivide`](https://postgis.net/docs/ST_Subdivide.html):

> Since the bounding boxes for the parts usually cover a smaller area than the
> original geometry bbox, index queries produce fewer "hit" cases. The "hit" cases
> are faster because the spatial operations executed by the index recheck process
> fewer points.

That happens because the GiST index does not keep the geometry, only its box ([spatial indexing](https://postgis.net/workshops/postgis-intro/indexing.html)):

> Spatial indexes are a little different – they are unable to index the geometric
> features themselves and instead index the bounding boxes of the features.

So one row per track means one big box. A bbox query would match that box even when the part of the track you want is far from the requested area, smaller segments keep the boxes tight.

I chose 500 because at one point per second, which is the common default, 500 points are about 8 minutes of movement.

| | speed | 500 points |
|---|---|---|
| walking | 5 km/h | ~0.7 km |
| bike | 20 km/h | ~2.8 km |
| car in town | 50 km/h | ~7 km |
| car on a motorway | 100 km/h | ~14 km |

Some comparison, with the 31.8 billion points we have today:

- 500 points per segment -> around 64 million rows in `gpx_tracks`
- 300 points per segment -> around 106 million rows in `gpx_tracks`

It is only a starting value. If the team has other ideas about what that size could be, we can change it.


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

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