Problem editing map data from a PostGis database

[ioan...@gmail.com]

Hi,

First I'd like to thank the devs for their fine product OpenJump!

I am having issues using Open Jump with a PostGis database and need some help.

I've just starting using OJ with Postgis, previously I worked with shapefiles for which OpenJump is great at editing the geometries and the data fields.  I work with custom map data so I do a lot of manual editing.

But now that I've switched to PostGis I've found that I cannot edit the maps as I have with shapefiles. 

As I make edits to my geometries and data fields by zooming and scrolling around my map the program reloads the geometries and/or the data values from the database and wipes out my changes before I can save them! 

I noticed this and tried saving before scrolling or zooming to a new location, but while zoomed in the total count of features (seen by hovering over the layer name) changes to a value less than the total for the full map, then when I save it often only saves the features visible in the window and I lose the rest of the features that were outside the visible area.

Is there a way to stop the layer from reloading itself from the database while I'm editing it then turn it back on after I've made my changes?  And to have it save all the features in the layer not just the zoomed in portion?

I'm running Openjump 1.91 on Windows 8.

Thanks!

[Jukka Rahkonen]

Hi,

How did you open the layer that you want to edit? There are two PostGIS
drivers in OpenJUMP. The one that is used through "Data Store Layer" is
read only and planned to make it possible to use large PostGIS tables
with OpenJUMP which is memory bound. Therefore it is utilizing BBOX
queries and intelligent caching with an optional LIMIT setting.

The other PostGIS is used when you add layers with "Writable Data Store"
option and it should be able to log the changes that you do on the map
and send updates/inserts/deletes into the database. Pay attention to
have a unique identifier field in the table If you have problems with
the writable data store we will need some more information about your
data.

-Jukka Rahkonen-

> --
> -- You received this message because you are subscribed to the Google
> Groups openjump-users group. To post to this group, send email to
> openjum...@googlegroups.com. To unsubscribe from this group, send
> email to openjump-user...@googlegroups.com. For more
> options, visit this group at
> https://groups.google.com/d/forum/openjump-users?hl=en
> ---
> You received this message because you are subscribed to the Google
> Groups "openjump-users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to openjump-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[ioan...@gmail.com]

I'm sorry I forgot to check replies for this post!

Thanks for your reply.

I've back to using shapefiles for editing and only saved to the database when done editing so as to avoid the issue I had.

I've been using the 'Data Store Layer' , I didn't know that it was meant for a 'read only' access.  I didn't notice the 'writeable data store' option and haven't ever used it.

I will try out the 'writeable data store' and post back if I have any issues with it.

Thanks!

[Michaël Michaud]

This answer is a bit late, sorry,

To complete the Jukka's answer, here are 4 ways to play with Postgis tables :

1/ Data Store Layer : the driver you used is read-only. It is "dynamic". Dataset is reloaded after a pan or a zoom. Main advantage is that it makes possible to navigate in a database containing millions of features in a smooth way.

2/ Data Store Query (File > Execute SQL query) : read-only too, but not dynamic. After you have imported the result of a query into OpenJUMP, it does not reload from the database automatically (if you want, you can re-synchronize with the database from the context menu, but if you changed the layer in-between, you'll loose your changes). Main advantage is that you can do a sql query as complex as your sql skill permits.

3/ Write Dataset as a PostGIS table (from the context menu) : this is the first postgis writer. Mainly used to upload a new table from a OpenJUMP layer but has also a few options to make updates if your database table if it has an unique identifier.

4/ Writable Data Store : this is a new Postgis Driver which makes it possible to import a PostGIS table and to keep it synchronized with the OJ Layer using a transaction manager. After data has been imported, every event is notified and you can commit from the Transaction Manager (a plugin located in the Toolbar)

Michaël 

[ioan...@gmail.com]

Very helpful, thanks!

Ioan

[ioan...@gmail.com]

Well I'm back with a question on option 4, the Writable Data Store.

Doing basic edits its working nicely.

But when I needed to edit my layer by splitting a feature (which creates a new row in the database) I could not commit the layer and got an error something like a 'null value in column "id" violates non null constraint".  (The "id" field is the primary key.)  I didn't have a null value in my new feature but it was a duplicate value, which I understand is not allowed.   But I was not allowed to change the duplicate "id" field value (the layer is set to be editable)

I then tried making a new feature and had a similar problem, this time the new feature does have a null "id" field when created and I'm still not allowed to change it which results in the same error as above when I try to commit.

So overall is there a way to make a new feature (either by drawing or splitting and existing one) and fix the non-editiable duplicate or null primary key value that's created along with the new feature?

Thanks again!

[ioan...@gmail.com]

I had another question and forgot add it above ...

When I edit a layer and commit the changes I see the layer is marked as 'modified' when the mouse hovers over the layer name.

Should I still save the layer after  the changes are committed in Transaction Manager?  I would think not but want to be sure.
Again thanks!

[Michaël Michaud]

Maybe your table has no way to manage the database identifier.

If the table is first created from OpenJUMP, you must check the option "create a primary key". It will create a sequence, and the table id called gid will use this sequence to set ids to new features.

If the table already exists in your database, it must have a similar mechanism to manage unique identifiers.

I just did the following test to check :

- create a layer

- save it as a postgis table

- connect to this layer with the writable postgis driver

- create a feature, commit

- split the feature, commit

and it worked,

Let me know a bit more about your use-case, and especially the way your table has been created (e.g. a copy of its schema)

Michaël

[Michaël Michaud]

This is a good question. Maybe the layer should not be marked as modified after a commit.

Let me have a deeper look on this.

Michaël

[ioan...@gmail.com]

Thanks Michael your comment helped a lot and I figured out what to do.

I've been creating my own postgres shape tables in java but not using a serial value as my primary key, instead I had an integer that I incremented in the program and saved with each feature.

It's working now since I changed my code to create tables that use a serial value named "gid" as primary key.  My editing in Openjump will go more smoothly now as well, thanks!