Enhance tour table, add 2 columns lat, lon

[Falsal]

Add bus stop position

[martinheppner]

@diditrummer Now I implemented it, that the two new columns are filled in the sync process on the server, as one of the last steps. I would appreciate it, if you could optimize the SQL. Thank you!

UPDATE tour AS t
SET connection_arrival_stop_lat = a.connection_arrival_stop_lat,
connection_arrival_stop_lon = a.connection_arrival_stop_lon
FROM (
    SELECT 
    f.hashed_url,
    f.connection_arrival_stop_lat,
    f.connection_arrival_stop_lon,
    ROW_NUMBER () OVER ( PARTITION BY f.hashed_url ORDER BY f.hashed_url, f.count_num DESC )
    FROM (
        SELECT 
        hashed_url,
        connection_arrival_stop_lat,
        connection_arrival_stop_lon,
        COUNT(*) AS count_num
        FROM fahrplan 
        GROUP BY hashed_url, connection_arrival_stop_lat, connection_arrival_stop_lon
        ) AS f
    INNER JOIN tour AS t
    ON t.hashed_url=f.hashed_url
    GROUP BY f.hashed_url, f.connection_arrival_stop_lat, f.connection_arrival_stop_lon, f.count_num
    ) AS a
WHERE a.ROW_NUMBER=1
AND t.hashed_url=a.hashed_url

[martinheppner]

@Falsal The two columns are called connection_arrival_stop_lat and connection_arrival_stop_lon They hold lat and lon of one (bus/train) stop, where the tour starts.