Update DB Geometry using GDAL

Question

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

Answer 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 ?