I have recently been working on storing route data in PostgreSQL/PostGIS. I am wondering which way is better to store and manage lines:

1. Store the entire route as a single line.
2. Split the route into sublines.

After storing the data, I would like to perform operations like partially editing the line.

I think the first approach can generate problems when partially editing the line (when updating, I would not like to remove and save a very new line).

So far, I haven’t found any clear solution to update part of the route, but as far as I know, to update a part of the route, I have to divide the route into three parts: startPart, editedPart, and endPart, and then merge them as startPart, newPart, and endPart.

On the other hand, the second solution could be tricky when it comes to printing the entire line or printing the line from a particular point (let’s say I have routes A->B, B->C, C->D, and I would like to print the route B->D).

I thought about this kind of solution:

WITH RECURSIVE route_path AS (
    SELECT id, name, lines, lines::geometry(LineString, 4326) as full_line
    FROM routes
    WHERE id = 11

    UNION ALL

    SELECT r.id, r.name, r.lines, ST_LineMerge(ST_Union(rp.full_line, r.lines))::geometry(LineString, 4326)
    FROM routes r
             INNER JOIN route_path rp ON ST_StartPoint(r.lines) = ST_EndPoint(rp.lines)
    WHERE rp.id <> 14
)

SELECT id, st_astext(lines), st_astext(full_line)
FROM route_path where id = 14

I’m not sure whether this solution will be efficient or not though. I suppose to have 1k points in each slice of the line, and the entire line will consist of 10 parts.

Is there a recommended way to deal with this kind of thing?

I have looked for any tips or remarks regarding this question, unfortunately I couldn’t find any solution