Maybe my answer will be valuable for anyone who faces the same problem.
Just to remind, I want to download official OSM map but keep my local informations (in my case maxspeed
tags).
Process which I wrote goes like this:
1) When my current changes are saved in local OSM editor, than I run my SQL script directly on Postgres database. That script export all tags from maxspeed
, maxspedd:forward
, maxspeed:backward
from way_tags
table to the CSV file.
CREATE TABLE IF NOT EXISTS temp_tags (LIKE way_tags);
ALTER TABLE temp_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default TRUE;
ALTER TABLE way_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean DEFAULT FALSE;
COPY
(SELECT DISTINCT ON(way_id, k)
way_tags.way_id, way_tags.k, way_tags.v, way_tags.version, way_tags.ah_edited
FROM way_tags
JOIN ways ON way_tags.way_id = ways.way_id AND way_tags.version = ways.version
JOIN changesets on ways.changeset_id=changesets.id
JOIN users ON changesets.user_id=users.id
WHERE (k like 'maxspeed:backward'
OR k like 'maxspeed:forward'
OR k like 'maxspeed')
AND ((users.email like '%admin.com%' AND ways.changeset_id != 0)
OR way_tags.ah_edited = TRUE)
ORDER BY way_id, k, version desc) TO STDOUT (format csv, delimiter ';', header false);
ALTER TABLE way_tags
DROP COLUMN IF EXISTS ah_edited;
DROP TABLE IF EXISTS temp_tags;
As some can notice I use additional column ah_edited
just to keep information that information is my edited one and should overwrite the "world" data if exists with the same tag.
After doing SAVE in OSM editor, in fact we save two copies of the same tags but with different version and incremented changeset_id
- that's why I look for tags which has that value !=0, and I use DISTINCT ON function which returns higher version value (after my modifications).
Thanks to that I got an CSV with my tags which I want to keep in the future downloaded map.
2) Truncate DB
3) Download latest map version from geofabrik.de
4) Upload map to the database
Here lets pause for a moment
Our data we can divide into two groups -> completely new tags for some ways, and already existing in official maps but our values should overwrite them. That's why I divided that process into two parts.
5) Insert new values to the DB
-- Get table to the original structure
ALTER TABLE way_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default FALSE;
-- Create temp table from CSV which holds pre-merge changes
CREATE TABLE IF NOT EXISTS temp_tags (LIKE way_tags);
COPY temp_tags(way_id, k, v, version, ah_edited) FROM '/home/map-data/exportedTags.csv' (FORMAT csv, delimiter ';', header false);
-- Add edidtion column to the way_tags and insert unique values which only AH added
INSERT INTO way_tags (way_id, k, v, version, ah_edited)
SELECT temp_tags.way_id, temp_tags.k, temp_tags.v, w.version, temp_tags.ah_edited
FROM temp_tags
FULL JOIN way_tags ON temp_tags.way_id = way_tags.way_id AND temp_tags.k like way_tags.k
JOIN ways w ON w.way_id = temp_tags.way_id
WHERE way_tags.way_id IS NULL;
-- DROP temp table from CSV which holds pre-merge changes
DROP TABLE public.temp_tags;
6) Next we update already existing values
CREATE TABLE IF NOT EXISTS temp_tags (LIKE way_tags);
ALTER TABLE temp_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default true;
COPY temp_tags(way_id, k, v, version, ah_edited) FROM '/home/map-data/exportedTags.csv' (FORMAT csv, delimiter ';', header false);
UPDATE temp_tags SET ah_edited= TRUE;
-- Add edidtion column to the way_tags and insert unique values which only AH added
ALTER TABLE way_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default FALSE;
UPDATE way_tags
SET v=csv_source.v, ah_edited = true
FROM temp_tags csv_source
WHERE csv_source.way_id = way_tags.way_id
AND csv_source.k like way_tags.k
AND csv_source.ah_edited = true;
-- DROP temp table from CSV which holds pre-merge changes
DROP TABLE public.temp_tags;
I know that I create and drop temporal tables every time, but every script is written to be executable independly
Almost done. Thanks to that We have got our datas in DB but... are not visible in editor. During read process PBF to the editor some inner structures are created that's why We need to save our map to the PBF file and reaload again.
And again little problem... OSM doesn't know our ah_edited
column values. He will restore column, but not values. That way I need to run one little script:
-- Create temp table from CSV which holds pre-merge changes
CREATE TABLE IF NOT EXISTS temp_tags (LIKE way_tags);
ALTER TABLE temp_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default TRUE;
COPY temp_tags(way_id, k, v, version, ah_edited) FROM '/home/map-data/exportedTags.csv' (FORMAT csv, delimiter ';', header false);
-- Add edidtion column to the way_tags and insert unique values which only AH added
ALTER TABLE way_tags
ADD COLUMN IF NOT EXISTS ah_edited boolean default FALSE;
UPDATE way_tags
SET ah_edited = true
FROM temp_tags
WHERE way_tags.way_id = temp_tags.way_id
AND way_tags.k = temp_tags.k;
-- DROP temp table from CSV which holds pre-merge changes
DROP TABLE public.temp_tags;
Voila! Done. We have new OSM map with our custom tags transferred.
Additional information. I use additional column ah_edited
to keep information for the further process that one specific values are edited by me, and I want it to be exported into CSV in the future. After that process, our changeset_id values are set to 0 so we don't have any current information which informations were changed by me as owner -> that's why I use ah_edited
column.
P.S. I know it's complicated, but it works and I couldn't find any answer for my question.