Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
On 27 December 2011 08:10, Oon Arfiandwi ro...@ijo.me wrote: Hi brett, On Wed, Dec 21, 2011 at 5:04 AM, Brett Henderson br...@bretth.com wrote: On 20 December 2011 03:14, Oon Arfiandwi ro...@ijo.me wrote: I just try to apply osmosis command below, the problem is: Caused by: org.postgresql.util.PSQLException: ERROR: relation schema_migrations does not exist I read a solution about schema_migrations relation here: http://forum.openstreetmap.org/viewtopic.php?id=7396 then I change the command to: osmosis --read-xml-change file=asiadiff.osc --write-pgsql-change database=gis user=osm password=x host=localhost or osmosis --read-xml-change file=asiadiff.osc --write-pgsimp-change database=gis user=osm password=x host=localhost but still no luck, I got another exception: org.postgresql.util.PSQLException: ERROR: relation schema_info does not exist any other references? How have you created your database? Specifically, which scripts did you use to create the database tables? The OpenStreetMap project has a number of different incompatible schemas using PostgreSQL PostGIS extensions all optimised for different purposes. http://wiki.openstreetmap.org/wiki/List_of_Database_Schemas I created database using osm2pgsql, here's the command: ./osm2pgsql -S default.style --slim -d gis -C 1024 --number-processes=1 --cache-strategy=dense asia.osm.bz2 how can I update the database which previously created by osm2pgsql? any reference? Perhaps this page will be of some use: http://wiki.openstreetmap.org/wiki/Minutely_Mapnik#Import_Replication_Data In summary, you can use Osmosis to download and merge the replication files, but you need to use osm2pgsql to apply them. If you wish to use the pgsql tasks in Osmosis you will need to use the pgsnapshot_*.sql scripts from the scripts directory of the Osmosis distribution. More details are available here: http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#PostGIS_Tasks_.28Snapshot_Schema.29 or should I drop the existing database, and setup the new one by following http://wiki.openstreetmap.org/wiki/Osmosis_PostGIS_Setup ? so next time I can update my database using Osmosis? That depends on what you're using the database for (apologies if you've already mentioned it). The osm2pgsql schema stores a subset of the OSM data in a schema suitable for rendering, the Osmosis pgsnapshot provides a complete snapshot of the OSM dataset in a generic schema that isn't suitable for rendering. Brett ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi brett, On Wed, Dec 21, 2011 at 5:04 AM, Brett Henderson br...@bretth.com wrote: On 20 December 2011 03:14, Oon Arfiandwi ro...@ijo.me wrote: I just try to apply osmosis command below, the problem is: Caused by: org.postgresql.util.PSQLException: ERROR: relation schema_migrations does not exist I read a solution about schema_migrations relation here: http://forum.openstreetmap.org/viewtopic.php?id=7396 then I change the command to: osmosis --read-xml-change file=asiadiff.osc --write-pgsql-change database=gis user=osm password=x host=localhost or osmosis --read-xml-change file=asiadiff.osc --write-pgsimp-change database=gis user=osm password=x host=localhost but still no luck, I got another exception: org.postgresql.util.PSQLException: ERROR: relation schema_info does not exist any other references? How have you created your database? Specifically, which scripts did you use to create the database tables? The OpenStreetMap project has a number of different incompatible schemas using PostgreSQL PostGIS extensions all optimised for different purposes. http://wiki.openstreetmap.org/wiki/List_of_Database_Schemas I created database using osm2pgsql, here's the command: ./osm2pgsql -S default.style --slim -d gis -C 1024 --number-processes=1 --cache-strategy=dense asia.osm.bz2 how can I update the database which previously created by osm2pgsql? any reference? If you wish to use the pgsql tasks in Osmosis you will need to use the pgsnapshot_*.sql scripts from the scripts directory of the Osmosis distribution. More details are available here: http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#PostGIS_Tasks_.28Snapshot_Schema.29 or should I drop the existing database, and setup the new one by following http://wiki.openstreetmap.org/wiki/Osmosis_PostGIS_Setup ? so next time I can update my database using Osmosis? thank you for any help. -- sincerely, Oon Arfiandwi ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi Oon, On 20 December 2011 03:14, Oon Arfiandwi ro...@ijo.me wrote: Hi Frederik, I just try to apply osmosis command below, the problem is: Caused by: org.postgresql.util.PSQLException: ERROR: relation schema_migrations does not exist I read a solution about schema_migrations relation here: http://forum.openstreetmap.org/viewtopic.php?id=7396 then I change the command to: osmosis --read-xml-change file=asiadiff.osc --write-pgsql-change database=gis user=osm password=x host=localhost or osmosis --read-xml-change file=asiadiff.osc --write-pgsimp-change database=gis user=osm password=x host=localhost but still no luck, I got another exception: org.postgresql.util.PSQLException: ERROR: relation schema_info does not exist any other references? How have you created your database? Specifically, which scripts did you use to create the database tables? The OpenStreetMap project has a number of different incompatible schemas using PostgreSQL PostGIS extensions all optimised for different purposes. http://wiki.openstreetmap.org/wiki/List_of_Database_Schemas If you wish to use the pgsql tasks in Osmosis you will need to use the pgsnapshot_*.sql scripts from the scripts directory of the Osmosis distribution. More details are available here: http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#PostGIS_Tasks_.28Snapshot_Schema.29 Brett ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi Frederik, I just try to apply osmosis command below, the problem is: Caused by: org.postgresql.util.PSQLException: ERROR: relation schema_migrations does not exist I read a solution about schema_migrations relation here: http://forum.openstreetmap.org/viewtopic.php?id=7396 then I change the command to: osmosis --read-xml-change file=asiadiff.osc --write-pgsql-change database=gis user=osm password=x host=localhost or osmosis --read-xml-change file=asiadiff.osc --write-pgsimp-change database=gis user=osm password=x host=localhost but still no luck, I got another exception: org.postgresql.util.PSQLException: ERROR: relation schema_info does not exist any other references? thank you. -- sincerely, Oon Arfiandwi On Mon, Dec 19, 2011 at 4:19 AM, Frederik Ramm frede...@remote.org wrote: Hi, The correct way to apply file C to your database is osmosis --read-xml-change asiadiff.osc --write-apidb-change dbType=postgresql database=osm user=osm password=xxx Be aware that an APIDB database records object history, i.e. it can have old versions of each object, but the approach you are following does not give you the full history - if an object was at version 1 on 10 December and at version 5 on 17 December, then after updating your database will only contain versions 1 and 5, not versions 1,2,3,4,5. Bye Frederik ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi OSM dev, I need help to understand how to keep the OSM data update. I deployed OSM data to postgresql db from asia.osm.pbf (for example from date 10 des). at 17des, i try to update my postgresql db, please correct my step-by-step below: 1. download asia.17des.osm.pbf 2. sort asia.10des.osm.pbf to asia.10des.sorted.osm.pbf (--sort type=TypeThenId) 3. sort asia.17des.osm.pbf to asia.17des.sorted.osm.pbf 4. read from both sorted.osm.pbf then --derive-change --write-xml-change file=asiadiff.osc here's the main problem, I don't know how to apply the changeset asiadiff.osc to my existing postgresql database. any help? I have tried to write the change to pbf and it success: osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --apply-change --write-pbf file=newchange.pbf but these steps all error: osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --apply-change --write-apidb dbType=postgresql database=osm user=osm password=xxx osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --apply-change --write-apidb-change dbType=postgresql database=osm user=osm password=xxx osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --write-apidb-change dbType=postgresql database=osm user=osm password=xxx osmosis --read-xml-change file=asiadiff.osc --write-apidb-change dbType=postgresql database=osm user=osm password=xxx ps: I use osmosis-0.40.1 thank you for any help. -- sincerely, Oon Arfiandwi ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi, On 12/18/2011 09:23 PM, Oon Arfiandwi wrote: osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --apply-change --write-apidb dbType=postgresql database=osm user=osm password=xxx There is a logic problem here. You have an OSM file O1 (10 December) and an OSM file O2 (17 December). You compute a change file C so that O1 + C1 = O2. Your database has O1 loaded. You wish to update your database to O2. The only thing your database needs, therefore, is C. But above, you try and feed O1 *as well as* C into the database. The correct way to apply file C to your database is osmosis --read-xml-change asiadiff.osc --write-apidb-change dbType=postgresql database=osm user=osm password=xxx Be aware that an APIDB database records object history, i.e. it can have old versions of each object, but the approach you are following does not give you the full history - if an object was at version 1 on 10 December and at version 5 on 17 December, then after updating your database will only contain versions 1 and 5, not versions 1,2,3,4,5. If you need all historic versions then you will have to find another way to update your database, working from the replication diffs that you can download. I have never done that and cannot say if/how well it works. If you don't need historic versions at all, then you might as well import the 17 December file anew instead of computing and applying an update. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi Frederik, Thank you for your explanation. Before I apply command as below, I have one more question about your statement. If you don't need historic versions at all, then you might as well import the 17 December file anew instead of computing and applying an update. I'm interesting to update data without any historic version. Any explation how I can update my database without creating changeset? (And without any downtime) Did you mean I have to drop the database/tables that consist of the 10 December data then reinsert all data from the 17 December file? Thank you. -- Sincerely, Oon Arfiandwi -Original Message- From: Frederik Ramm frede...@remote.org Date: Sun, 18 Dec 2011 22:19:08 To: devdev@openstreetmap.org Subject: Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db Hi, On 12/18/2011 09:23 PM, Oon Arfiandwi wrote: osmosis --read-xml-change file=asiadiff.osc --read-pbf file=asia.10des.sorted.osm.pbf --apply-change --write-apidb dbType=postgresql database=osm user=osm password=xxx There is a logic problem here. You have an OSM file O1 (10 December) and an OSM file O2 (17 December). You compute a change file C so that O1 + C1 = O2. Your database has O1 loaded. You wish to update your database to O2. The only thing your database needs, therefore, is C. But above, you try and feed O1 *as well as* C into the database. The correct way to apply file C to your database is osmosis --read-xml-change asiadiff.osc --write-apidb-change dbType=postgresql database=osm user=osm password=xxx Be aware that an APIDB database records object history, i.e. it can have old versions of each object, but the approach you are following does not give you the full history - if an object was at version 1 on 10 December and at version 5 on 17 December, then after updating your database will only contain versions 1 and 5, not versions 1,2,3,4,5. If you need all historic versions then you will have to find another way to update your database, working from the replication diffs that you can download. I have never done that and cannot say if/how well it works. If you don't need historic versions at all, then you might as well import the 17 December file anew instead of computing and applying an update. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] problem with osmosis to apply changeset to postgresql db
Hi, On 12/19/2011 06:23 AM, Oon Arfiandwi wrote: Any explation how I can update my database without creating changeset? (And without any downtime) Did you mean I have to drop the database/tables that consist of the 10 December data then reinsert all data from the 17 December file? Yes. If you want to avoid downtime then you can create a new database, import into that while the old is running, and then drop the old and rename the new. That gives you a very short downtime. You can even do it without *any* downtime by using the pgbouncer utility if you have that requirement. Bye Frederik ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
