· 8 years ago · Mar 09, 2018, 02:58 AM
1vanosten@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
2Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
3INFO: Osmosis Version 0.45
4Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
5INFO: Preparing pipeline.
6Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
7INFO: Launching pipeline execution.
8Feb 04, 2017 6:00:55 PM org.openstreetmap.osmosis.core.Osmosis run
9INFO: Pipeline executing, waiting for completion.
10Feb 04, 2017 6:01:00 PM org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger complete
11INFO: Processing completion steps.
12Feb 04, 2017 6:01:02 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
13INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
14Feb 04, 2017 6:01:02 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
15INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
16Feb 04, 2017 6:01:02 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
17SEVERE: Thread for task 1-read-xml failed
18org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id)]; ERROR: could not create unique index "pk_users"
19 Detail: Key (id)=(1913534) is duplicated.; nested exception is org.postgresql.util.PSQLException: ERROR: could not create unique index "pk_users"
20 Detail: Key (id)=(1913534) is duplicated.
21 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
22 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
23 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)
24 at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:534)
25 at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.IndexManager.completeAfterLoad(IndexManager.java:130)
26 at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.CopyFilesetLoader.run(CopyFilesetLoader.java:111)
27 at org.openstreetmap.osmosis.pgsnapshot.v0_6.PostgreSqlCopyWriter.complete(PostgreSqlCopyWriter.java:123)
28 at org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger.complete(EntityProgressLogger.java:82)
29 at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:92)
30 at java.lang.Thread.run(Thread.java:745)
31Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pk_users"
32 Detail: Key (id)=(1913534) is duplicated.
33 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
34 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
35 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
36 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:616)
37 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:452)
38 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:380)
39 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
40 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
41 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:523)
42 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:520)
43 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
44 ... 7 more
45
46Feb 04, 2017 6:01:02 PM org.openstreetmap.osmosis.core.Osmosis main
47SEVERE: Execution aborted.
48org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
49 at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
50 at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
51 at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
52 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
53 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
54 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
55 at java.lang.reflect.Method.invoke(Method.java:498)
56 at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330)
57 at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
58 at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
59 at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
60 at org.codehaus.classworlds.Launcher.main(Launcher.java:47)
61
62DROP TRIGGER IF EXISTS upsert_building_trigger ON ways;
63CREATE TRIGGER upsert_building_trigger
64 AFTER INSERT OR UPDATE ON ways
65 FOR EACH ROW
66 EXECUTE PROCEDURE upsert_building_in_osm();
67
68CREATE OR REPLACE FUNCTION upsert_building_in_osm()
69 RETURNS trigger
70AS $$
71DECLARE
72 bldg_poly geometry(Geometry,4326);
73 bldg_point geometry(Geometry,4326);
74 new_bldg_poly geometry(Geometry,4326);
75
76BEGIN
77 IF ST_NumPoints(NEW.linestring) >= 4 AND st_isvalid(NEW.linestring) THEN
78 bldg_poly := ST_MakePolygon(ST_ForceClosed(NEW.linestring));
79 bldg_point := ST_PointOnSurface(bldg_poly);
80
81 -- fix for invalid geometries in polys with overlapping edges
82 IF NOT st_isvalid(bldg_poly) THEN
83 IF st_area(st_multi(st_buffer(bldg_poly,0))) > 0 THEN
84 new_bldg_poly := st_multi(st_buffer(bldg_poly,0));
85 bldg_poly := new_bldg_poly;
86 bldg_point := ST_PointOnSurface(bldg_poly);
87 -- fix for self intersection not caught
88 ELSE
89 new_bldg_poly := st_makevalid(bldg_poly);
90 IF ST_GeometryType(new_bldg_poly) = 'ST_MultiPolygon' OR ST_GeometryType(new_bldg_poly) = 'ST_Polygon' THEN
91 bldg_poly := new_bldg_poly;
92 ELSEIF ST_GeometryType(new_bldg_poly) = 'ST_GeometryCollection' THEN
93 bldg_poly := ST_Collectionextract(ST_MakeValid(new_bldg_poly), 3);
94 ELSE
95 RAISE NOTICE 'Unexpected geometry type';
96 RETURN NULL;
97 END IF;
98 bldg_point := ST_PointOnSurface(bldg_poly);
99 END IF;
100 END IF;
101
102 -- fix for odd behavior when inserting a bogus linestring but updating a valid one;
103 INSERT INTO osm.buildings(id, the_geom_poly, the_geom_point)
104 VALUES (NEW.id, bldg_poly, bldg_point)
105 ON CONFLICT (id) DO UPDATE
106 SET the_geom_poly = bldg_poly,
107 the_geom_point = bldg_point;
108
109 END IF;
110 RETURN NULL;
111
112EXCEPTION WHEN OTHERS THEN
113 RAISE NOTICE 'The transaction is in an uncommittable state. '
114 'Transaction was rolled back';
115 RAISE NOTICE '% %', SQLERRM, SQLSTATE;
116 RETURN NULL;
117
118END $$ LANGUAGE plpgsql
119
120osmosis --truncate-pgsql host= database= user= password=