Add data to existing populated PostGIS database through Osmosis

Question

I have a PostGIS database populated with data using /home/user/bin/osmosis-latest/bin/osmosis --read-xml /media/sf_fg_customscenery/projects/LSMP/lsmp.osm --log-progress --write-pgsql database=osmogis. This works fine.

However I do not understand how I can add additional data with a different area coverage to the same database. Using the same write-pgsql command results in errors as shown below.

=> Is is possible to add new data (not changesets) into the same database or I need to create a new database?

vanosten@vanosten-VirtualBox:~$ /home/vanosten/bin/osmosis-latest/bin/osmosis --read-xml /media/sf_fg_customscenery/projects/TEST/puttgarden.osm --log-progress --write-pgsql database=osmogis
Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.45
Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Feb 04, 2017 6:01:00 PM org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger complete
INFO: Processing completion steps.
Feb 04, 2017 6:01:02 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Feb 04, 2017 6:01:02 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
Feb 04, 2017 6:01:02 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml failed
org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id)]; ERROR: could not create unique index "pk_users"
  Detail: Key (id)=(1913534) is duplicated.; nested exception is org.postgresql.util.PSQLException: ERROR: could not create unique index "pk_users"
  Detail: Key (id)=(1913534) is duplicated.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:534)
    at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.IndexManager.completeAfterLoad(IndexManager.java:130)
    at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.CopyFilesetLoader.run(CopyFilesetLoader.java:111)
    at org.openstreetmap.osmosis.pgsnapshot.v0_6.PostgreSqlCopyWriter.complete(PostgreSqlCopyWriter.java:123)
    at org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger.complete(EntityProgressLogger.java:82)
    at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:92)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pk_users"
  Detail: Key (id)=(1913534) is duplicated.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:616)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:452)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:380)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:523)
    at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:520)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
    ... 7 more

Feb 04, 2017 6:01:02 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
    at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
    at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
    at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
    at org.codehaus.classworlds.Launcher.main(Launcher.java:47)

Answer 1

I stumbled across this question when encountering a similar issue with my own project. The reason to avoid appending to the existing tables in this manner is so that the topology of the OSM data is maintained.

My solution was to set a trigger on the ways table to extract any necessary data to a separate table and finally truncate all data from the standard osmosis-postgis tables (ways, nodes, users, etc.) so that the schema could be used again.

In my case I was only interested in buildings in OSM so I used a trigger on the ways table to update a separate table with the osm id and postgis geometry. The trigger is set via:

DROP TRIGGER IF EXISTS upsert_building_trigger ON ways;
CREATE TRIGGER upsert_building_trigger
  AFTER INSERT OR UPDATE ON ways
  FOR EACH ROW
  EXECUTE PROCEDURE upsert_building_in_osm();

the actual function used:

CREATE OR REPLACE FUNCTION upsert_building_in_osm()
  RETURNS trigger
AS $$
DECLARE
    bldg_poly geometry(Geometry,4326);
    bldg_point geometry(Geometry,4326);
    new_bldg_poly geometry(Geometry,4326);

BEGIN
  IF ST_NumPoints(NEW.linestring) >= 4 AND st_isvalid(NEW.linestring) THEN
    bldg_poly := ST_MakePolygon(ST_ForceClosed(NEW.linestring));
    bldg_point := ST_PointOnSurface(bldg_poly);

    -- fix for invalid geometries in polys with overlapping edges
    IF NOT st_isvalid(bldg_poly) THEN
      IF st_area(st_multi(st_buffer(bldg_poly,0))) > 0 THEN
        new_bldg_poly := st_multi(st_buffer(bldg_poly,0));
        bldg_poly := new_bldg_poly;
        bldg_point := ST_PointOnSurface(bldg_poly);
    -- fix for self intersection not caught
      ELSE
        new_bldg_poly := st_makevalid(bldg_poly);
        IF ST_GeometryType(new_bldg_poly) = 'ST_MultiPolygon' OR ST_GeometryType(new_bldg_poly) = 'ST_Polygon' THEN
          bldg_poly := new_bldg_poly;
        ELSEIF ST_GeometryType(new_bldg_poly) = 'ST_GeometryCollection' THEN
          bldg_poly := ST_Collectionextract(ST_MakeValid(new_bldg_poly), 3);
        ELSE 
          RAISE NOTICE 'Unexpected geometry type';
          RETURN NULL;
        END IF;
        bldg_point := ST_PointOnSurface(bldg_poly);
      END IF;
    END IF;

    -- fix for odd behavior when inserting a bogus linestring but updating a valid one;
    INSERT INTO osm.buildings(id, the_geom_poly, the_geom_point)
    VALUES (NEW.id, bldg_poly, bldg_point)
    ON CONFLICT (id) DO UPDATE
      SET the_geom_poly = bldg_poly,
          the_geom_point = bldg_point;

  END IF;
  RETURN NULL;

EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'The transaction is in an uncommittable state. '
                 'Transaction was rolled back';
    RAISE NOTICE '% %', SQLERRM, SQLSTATE;
    RETURN NULL;

END $$ LANGUAGE plpgsql

Finally, to avoid the osmosis errors clear out the default osmosis postgis tables after you've got what you need:

osmosis --truncate-pgsql host= database= user= password=