1

We have set up a postgis database to manage all our old shapefiles.

There is one table that contains the geometry and some basic information. There are several tables linked to that in a 1:m relation.

We want to clean up the geometry (snapping, splitting, moving, etc.). We are using QGIS as a desktop-application to do that. As long as we do not split a geometry, there is no problem in the database.

While splitting a geometry, you create a new feature in the basic table. This one need to get a new unique id. All original linked features in the other tables have to be duplicated with that new unique id as a reference.

In a manual way I could do

INSERT INTO weg_bron ("weg_id", "bron_id", "bronnummer1", "bronnummer2")
SELECT <id new segment>, "bron_id", "bronnummer1", "bronnummer2" FROM weg_bron WHERE weg_id = <original id>;

What is the workaround to do this (semi)automaticly while working with the splitting tool in QGIS?

--- some more information about the tables ---

The basic table is called 'weg':

 CREATE TABLE weg
(
  id serial NOT NULL,
  naam text,
  geometrie geometry(LineString,31370) NOT NULL,
  lengte integer,
  deelgemeente_id integer,
  verharding_id character varying(2),
  categorie_id character varying(3),
  juridisch_statuut_id smallint,
  breedte_id smallint,
  toegankelijkheid boolean,
  beschrijving_toegankelijkheid text,
  zichtbaar boolean,
  trage_weg boolean,
  opmerkingen text,
  eigenaar_id integer,
  inventariseerder_id integer,
  inventarisatiedatum date,
  aanmaakdatum timestamp with time zone NOT NULL DEFAULT now(),
  aanpasdatum timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT weg_pkey PRIMARY KEY (id)
  )

One of the linked table is called 'weg_bron':

    CREATE TABLE weg_bron
(
  weg_id integer NOT NULL,
  bron_id integer NOT NULL,
  bronnummer1 character varying(25),
  bronnummer2 character varying(25),
  aanmaakdatum timestamp with time zone NOT NULL DEFAULT now(),
  aanpasdatum timestamp with time zone NOT NULL DEFAULT now(),
  opmerking text,
  CONSTRAINT weg_bron_pkey PRIMARY KEY (weg_id, bron_id),
  CONSTRAINT weg_bron_bron_id_fkey FOREIGN KEY (bron_id)
      REFERENCES bron (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT weg_bron_weg_id_fkey FOREIGN KEY (weg_id)
      REFERENCES weg (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
  • What is exactly the split tool you use? Does it already keep all non-id attributes from weg feature when you split it? Depending on your answer to the second question I might have an idea. – MarHoff Oct 29 '15 at 11:12
  • i use the built-in split toll from qgis. And it keeps all other attributes like it does when splitting a feature in eg. a shapefile – PieterB Oct 29 '15 at 12:04
  • Potentially related: gis.stackexchange.com/a/77052/37413 (uses row IDs in query results as a surrogate for the ID column so results are better handled in QGIS). – Nate Wanner Mar 30 at 13:16
1

If i got your problem right, I got a solution but which will require you to work in two separate step for each editing session:

  1. Do all your edits on weg table
  2. Run a script that will update weg_bron accordingly

Initial setup

You'll need to add a column to weg, let's call it "old_id" of the same type as the primary key of the table. Then update the table as

SET old_id=id

Because this column don't have a unique constraint unlike "id", this "old_id" value will be kept whenever you split a polygon.


1.Do edits on weg table

Just do your edit session as usual (dont think of more advices for now :P)


2.Script to update weg_bron

After each edits session you'll have to run a script that will INSERT the duplicated entries you need in "weg_bron" according to lines in "weg" where id <> old_id.

Also after the INSERT is successful you'll have to update again "weg" table so that old_id=id and so the layer is ready for a new editing session.

Here is a stub that should work:

INSERT INTO weg_bron ("weg_id", "bron_id", "bronnummer1", "bronnummer2")
SELECT weg.id, weg_bron."bron_id", weg_bron."bronnummer1", weg_bron."bronnummer2"
FROM weg_bron
LEFT JOIN weg
ON weg.old_id=weg_bron.weg_id AND weg.id<>weg.old_id;

UPDATE weg SET old_id=id;

Enjoy & repeat

But stay careful about when to run the script in respect to your editing process. This is the global idea and might need some error-proof testing before getting to production.

  • So, if I understand well: after each single split I need to run the script on the affected tables. Or can i do more than one split and then run that script? – PieterB Oct 29 '15 at 14:39
  • This will depend on you data, if you have concurrent access and so on. As I see it, I think you can group up many edits in a session and run the script once at the end. But if, let say you edit weg_bron in the meantime, you might have syncing issues. – MarHoff Oct 29 '15 at 14:45
  • The last part was more a general "think about what you are doing" warning. And of course backup and test before production!!!! – MarHoff Oct 29 '15 at 14:47
  • I' am working on a dummy-version of the database. I give it a try and see what happens to the linked tables. I will test different scenario's. thanks for helping. – PieterB Oct 29 '15 at 14:51
  • The scenario that could go really wrong, is if you add or remove a weg_bron entry that concern only one of the splited weg before syncing. So that totally rely on your use case. – MarHoff Oct 29 '15 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.