how to identify parts of the same osm_id in planet_osm_polygon?

Question

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"?

Answer 1

1

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 '18, 13:13

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

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 '18, 15:37) ppKrauss

Answer 2

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);

how to identify parts of the same osm_id in planet_osm_polygon?

Complete solution

Follow this question

Related questions