[openstreetmap/openstreetmap-website] Store all GPS traces as LineStrings (Issue #7339)

[Ruben L. Mendoza]

Rub21 left a comment (openstreetmap/openstreetmap-website#7339)

>From the discussion here: https://github.com/openstreetmap/openstreetmap-website/pull/7348#discussion_r3913616193

I move it to this ticket so it stays documented. Pablo asked why 500 points per segment.

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 should be, we can change it.


-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/7339#issuecomment-5523592308
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/issues/7339/5523592308 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20260903/6ab8a5a0/attachment-0001.htm>