Use Osmosis PostGIS schema with QGIS

Question

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?

Accepted Answer

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

Answer 2

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.

Answer 3

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;

Use Osmosis PostGIS schema with QGIS

Related questions