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 ?