0

We are working with a geometry DB (Oracle) and use GDAL to download some data and apply changes to it.

Supposing that we were applying the following workflow:

DB -> GeoJSON -> modified_GeoJSON

Is it possible to close the circle using GDAL to apply the geometry modifications to the table? I am imagining something similar to the download phase:

ogr2ogr -f OCI connection-string filename
  • Would there be only updates of existing geometries or possibly also inserts and deletes? – user30184 Oct 1 '18 at 18:55
  • Hi, main idea would be updating. I've seen we can just use directly Oracle Spatial queries for doing that. However, we would like to use ogr2ogr so that we can be abstractes of the final DB that is being used if the client wants to ever change it and not write a code that is married with Oracle. – Ayeron Oct 2 '18 at 6:17
  • ogr2ogr allows you to append new data to an existing file or database table. It does not provide any way to apply changes, i.e. delete rows or update (replace) rows. A possible approach can be to load your updates (using ogr2ogr) into a separate table, with changes clearly flagged as inserts, deletions or updates. Then use some custom SQL to apply those changes to the actual full table. Or even have triggers to do that automatically ... – Albert Godfrind Oct 2 '18 at 7:28
  • thank you @AlbertGodfrind for the clarifying comment. We will design a workaround then :) – Ayeron Oct 2 '18 at 7:34
  • Good. Let me then move my comment as an answer for future reference. – Albert Godfrind Oct 2 '18 at 7:37
1

ogr2ogr allows you to append new data to an existing file or database table.

But it does not provide any way to apply changes, i.e. delete rows or update (replace) rows.

A possible approach can be to load the GeoJSON file that contains only your updates into a separate table (using ogr2ogr). That table needs to have changes clearly flagged as inserts, deletions or updates via some flag (like a 'I', 'U', 'D' code. It could even contain the unchanged rows (with an empty flag).

Then you can run some custom SQL to apply those changes to the actual data table. The code could look something like this:

(Disclaimer: this is just a code skeleton. I have not run it, so there may be typos or syntax errors)

begin
  for c in (
    select * from changes_table
  )
  loop
    case c.change_code 
      when 'I' then
        insert into main_table (key, col1, col2, ... coln) 
        values (c.key, c.col1, c.col2, ... c.coln);
      when 'D' then 
        delete from main_table t
        where t.key = c.key;
      when 'U' then
        update main_table t set
          t.col1 = c.col1,
          t.col2 = c.col2,
          ...
          t.coln = c.coln
        where t.key = c.key;
    end case;
  end loop;
end;
/
commit;

I assume that the individual rows are identified by a persistent unique key, aka a primary key. This is a fundamental requirement to update them properly. In the example, I assume a single column called key. But the primary key could be a combination of columns.

Or even have triggers to do that automatically ...

create or replace trigger apply_changes
after insert on changes_table
for each row
begin
  case :new.change_code 
    when 'I' then
      insert into main_table (key, col1, col2, ... coln) 
      values (:new.key, :new.col1, :new.col2, ... :new.coln);
    when 'D' then 
      delete from main_table t
      where t.key = :new.key;
    when 'U' then
      update main_table t set
        t.col1 = :new.col1,
        t.col2 = :new.col2,
        ...
        t.coln = :new.coln
      where t.key = :new.key;
  end case;
end;
/

Here you must make sure that changes_table exists with the exact same structure as expected by ogr2ogr. The best for that would be to let ogr2ogr create it first and reuse it on subsequent update cycles. Then each change row loaded by ogr2ogr into changes_table gets processed by the trigger, and the corresponding updates are automatically applied to the real table (main_table).

Again, I have not run that code, so there may be errors. But it should get you started. You may also need to had some proper exception handling code: what should happen if a row to be deleted or updated does not exist in the main table ? Or if a row to be inserted already exists ?

  • We would manually check the PrimaryKeys, so the behaviour of new/not new elements would be under a controlled scenario – Ayeron Oct 2 '18 at 10:11
  • Your method is a proper one but even you can't delete or update records with ogr2ogr you can do it with ogrinfo. – user30184 Oct 2 '18 at 15:39

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.