If you try to use the simple PostGIS schema for Osmosis with QGIS, it complains about not having the right kind of index column in the schema. Is there a different schema for Osmosis that will work, or some hacking that you can do to make it work?

asked 28 Jun '10, 14:35

Jonathan%20Bennett's gravatar image

Jonathan Ben...
8.2k1785108
accept rate: 18%


From the hacking side of things, you can add a serial column.

alter table planet_osm_line add column "gid" serial;
alter table planet_osm_point add column "gid" serial;
alter table planet_osm_polygon add column "gid" serial;

There is the same problem with osm2pgsql database schema, where this hack needs to be repeated after every fresh import since the table is dropped. I don't know if it's the same with osmosis PostGIS schema

permanent link

answered 06 Jul '10, 16:29

Andy%20Allan's gravatar image

Andy Allan
11.8k23121144
accept rate: 30%

QGIS and other programs need a column with a unique 32 bit integer ("int" or "int4" in PostgreSQL) for every table they access. The Osmosis simple database schema uses larger integers ("bigint" or "int8" in PostgreSQL), so QGIS can't use them.

There are two ways to fix this: Either change the id columns from int8 to int4 in the simple database schema before importing or add an additional column to each table like this:

ALTER TABLE nodes ADD COLUMN gid SERIAL;

You might have to do this after every re-import of data if the table was dropped in between. Also you probably want to add an index to this column:

CREATE UNIQUE INDEX nodes_gid_idx ON nodes (gid);

Note that currently there are less than 2 billion nodes in the OSM database. This will change at some point. Then an int4 can't hold the id any more and we have to think about new solutions.

permanent link

answered 14 Jul '10, 13:47

Jochen%20Topf's gravatar image

Jochen Topf
4.4k54564
accept rate: 34%

A similar problem occurs with GeoDjango.

The affected tables in the Osmosis simple schema are all tables without id:

  • node_tags
  • relation_members
  • relation_tags
  • way_nodes
  • way_tags

Adding a serial column as Andy Allan proposed helps, for example

alter table node_tags add column "gid" serial;

permanent link

answered 10 Jul '10, 10:21

emka's gravatar image

emka
95225
accept rate: 0%

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:

×219
×121
×49

question asked: 28 Jun '10, 14:35

question was seen: 7,022 times

last updated: 14 Jul '10, 13:47

powered by OSQA