NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

1
1

The problem which I faced is quite complex. I work with OSM editor where I can override some ways properties. Those properties are tags called "maxspeed:backward" and "maxspeed:forward". Sometimes I want to update my local map with OSM official data (new roads, roads under constructions etc.).
What I want is to merge my local map with official osm to keep all road and nodes changes from official source but keep my tags with values from editor. I work with Postgres DB, osmosis (merge tool), osmfilter and osmconvert tools.

This is process which I found out:
1. Take my local map from editor and filter out only tags. With no nodes, ways, relations.
2. Take official OSM map and filter out only tags (the same as step 1)
3. Merge those sources with osmosis with conflictResult strategy - my local tag values override official OSM map.
4. Again take official OSM map but now I drop tags, so I get full map without tags I'm using.
5. Merge result from step 3 (correct tags and values) with result from step 4 (official map without tags) with conflictResult strategy - official map nodes override my local.

What I want to achieve is go teg most current map with my own tags.
I have a problem with step 1. Can I retrieve from DB ONLY tags? Without any nodes or another informations which could override official map in step 5? What I can see in DB tags are placed in separate table and has got reference to whe way_id, so if I merge that values with another source with the same way_id.

What I try with osmfilter is:
./osmfilter $EDITED_OSM_NAME --keep-tags="all maxspeed:backward= maxspeed:forward= maxspeed=" -o=$EDITED_OSM_TAGS
It filter correctly- only listed tags are in output pbf file, but is there any possibility to retreive them without any nodes,ways,relations etc?

I have also tried something with
--drop-relations,--drop-ways,--drop-nodes, --ignore-dependencies but it didn't work the way I wish.

Thank you in advance for help.

asked 28 Oct '20, 14:45

engopy's gravatar image

engopy
126101015
accept rate: 0%

edited 29 Oct '20, 09:20


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.

permanent link

answered 04 Nov '20, 11:06

engopy's gravatar image

engopy
126101015
accept rate: 0%

edited 04 Nov '20, 11:10

Hello! Try to use QGis, I once found instructions in yuotube on how to sort and unload certain tags, try to search, if you can't find it, I'll try to find it then and send it exactly where I saw it.

permanent link

answered 30 Oct '20, 08:26

Viki_travel_weak's gravatar image

Viki_travel_...
11
accept rate: 0%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×930
×252
×66
×60
×41

question asked: 28 Oct '20, 14:45

question was seen: 2,333 times

last updated: 04 Nov '20, 11:10

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum