The relation 303702 and many others in the table planet_osm_polygon have same osm_id, so, same ID and no other id (eg. z_order is zero)... There are a column that I not see? How to configure osm2pgsql to add a gid or any complement to add a counter for each "multiple osm_id"?

asked 05 Oct, 12:44

ppKrauss's gravatar image

ppKrauss
802823
accept rate: 0%


You could use the --multi-geometry flag with osm2pgsql which should get rid of most of these cases (instead, they would all be combined in one record with a multipolygon or multilinestring geometry). Other than that you could follow the standard postgres recommendations of adding an integer column and populating it with a default value that uses a sequence, as explained e.g. in https://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres - there's no particular osm2pgsql option that would do this for you.

permanent link

answered 05 Oct, 13:13

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
66.2k806121025
accept rate: 24%

The --multi-geometry is a good clue, I will test in the next planet-conversion... And will back here with results. If there are no complete solution, my answer below is a workaround using pure SQL and perhaps ALTER TABLE (adding UNIQUE clause to index it) enhance performance.

(05 Oct, 15:37) ppKrauss

If it is the truth, as @FraderikRamm say "there's no particular osm2pgsql option that would do this for you"... The only solution is by SQL,

select osm_id, row_number() OVER (PARTITION BY osm_id) as sub_id 
from planet_osm_polygon

so, now we have a UNIQUE(osm_id,sub_id).


Complete solution

(can be repeated changing table name)

ALTER TABLE planet_osm_polygon ADD COLUMN osm_id2 int;
UPDATE planet_osm_polygon
SET  osm_id2 = sub_id
FROM (
  SELECT osm_id, way, row_number() OVER (PARTITION BY osm_id) as sub_id 
  FROM planet_osm_polygon
) t WHERE t.osm_id=planet_osm_polygon.osm_id AND t.way=planet_osm_polygon.way
;
ALTER TABLE planet_osm_polygon ALTER COLUMN osm_id2 SET NOT NULL;
ALTER TABLE planet_osm_polygon ADD CONSTRAINT osm_uniqids UNIQUE(osm_id,osm_id2);
permanent link

answered 05 Oct, 14:08

ppKrauss's gravatar image

ppKrauss
802823
accept rate: 0%

edited 10 Oct, 00:27

Your answer
toggle preview

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:

×179

question asked: 05 Oct, 12:44

question was seen: 145 times

last updated: 10 Oct, 00:27

powered by OSQA