Updating layers without duplicates: replace, append and incremental load

Abstract

The four ways to update a database that receives new data, and an incremental-load recipe for DETER that survives three real pitfalls: repeated codes, a date filter with time zones and alerts published late.

Level Advanced Reading 10 min Data October 2026 Programs ArcGIS

Almost every script in my folder ended the same way: delete the features of a layer and append the new ones (DeleteFeatures + Append). It works, but each script reinvented the rule, and an error halfway through left the layer empty or duplicated.

This article brings together the four patterns that appeared in the scripts and shows the most useful one for DETER, the incremental load, with three pitfalls that only showed up when testing with the real data.

Four panels. Replace everything: the whole table is swapped. Replace a part: only the rows of one year are swapped. Append only the new: new rows go at the end. Insert or update: new rows go in and existing ones are updated by key. — The four update patterns.

The four patterns

Pattern	When to use it	In the old script
1. Replace everything	small layer that the source publishes in full (embargoes, protected areas)	`DeleteFeatures` + `Append` of IBAMA embargoes
2. Replace a part	data by period that may be republished (one PRODES year)	delete the year with `UpdateCursor` and append
3. Append only the new	data that only grows (daily alerts)	take the maximum date and append what is newer
4. Insert or update (“upsert”)	when a record can change after it is published	did not exist: it duplicated

Pattern 2 is in the article PRODES in protected areas. Patterns 1, 3 and 4 are below.

1. Replace everything, without leaving the layer empty

The risk of “delete and append” is the script failing between the two steps: the layer stays empty until someone notices. Make the swap in one go:

Program	How
ArcGIS Pro	`arcpy.management.TruncateTable(target)` and `Append(new, target, "NO_TEST")`, inside a `with arcpy.da.Editor(gdb):` to roll back on error
QGIS	`native:savefeatures` with `ACTION_ON_EXISTING_FILE = 1` (overwrites only the layer, in one operation)
PostGIS	`BEGIN; TRUNCATE target; INSERT INTO target SELECT … FROM new; COMMIT;`
Python	`gdf.to_file("base.gpkg", layer="embargos")` writes the whole layer again

3 and 4. Incremental load of DETER

DETER publishes alerts every day. Downloading the whole service each time is slow; the right way is to request only what changed and merge it into the database. The recipe that worked, after testing:

Find the latest date in the database.
Ask the server for alerts from 30 days before that date (the “safety window”).
Compare by the key gid + classname + view_date and keep only what is missing.
Append (or, in PostGIS, insert and update what changed).

To test it, I created an “old” database with only the alerts up to 2026-09-30 and ran each version twice in a row. Names in Portuguese: chave = key, existentes = existing, ultima = latest, desde = since, janela = window, faltam = missing, novos = new, historico = history.

ArcGIS Pro’s WFSToFeatureClass tool does not filter WFS by date. The way around it is to request the filtered GeoJSON from the server, as in the Python tab, and convert it with JSONToFeatures:

import arcpy
import urllib.parse
import urllib.request
from datetime import timedelta

arcpy.env.overwriteOutput = True
base = r"C:\dados\resultados.gdb\deter_historico"

def chave(gid, classe, data):
    """The gid alone repeats in DETER: the key is code + class + date."""
    return f"{gid}|{classe}|{str(data)[:10]}"

# 1. How far does the database go? Fetch the previous 30 days again
campos = ["gid", "classname", "view_date"]
existentes = {chave(*linha) for linha in arcpy.da.SearchCursor(base, campos)}
ultima = max(linha[2] for linha in arcpy.da.SearchCursor(base, campos))
desde = (ultima - timedelta(days=30)).strftime("%Y-%m-%d")

# 2. Ask the server only for the window, only in the study area
filtro = f"view_date >= '{desde}' AND BBOX(geom,-56.5,-8.5,-54.5,-5.5,'EPSG:4674')"
url = ("https://terrabrasilis.dpi.inpe.br/geoserver/deter-amz/ows?service=WFS&version=2.0.0"
       "&request=GetFeature&typeNames=deter-amz:deter_amz&outputFormat=application/json"
       f"&srsName=EPSG:4674&CQL_FILTER={urllib.parse.quote(filtro)}")
urllib.request.urlretrieve(url, r"C:\dados\deter_janela.geojson")
janela = arcpy.conversion.JSONToFeatures(r"C:\dados\deter_janela.geojson",
                                         r"memory\janela", "POLYGON")

# 3. Only what is missing from the database (the date comes as text in GeoJSON)
faltam = [oid for oid, *linha in arcpy.da.SearchCursor(janela, ["OID@"] + campos)
          if chave(*linha) not in existentes]
print("new alerts:", len(faltam))

# 4. Convert the date and append
if faltam:
    novos = arcpy.analysis.Select(janela, r"memory\novos",
                                  f"OBJECTID IN ({','.join(map(str, faltam))})")
    arcpy.management.CalculateField(
        novos, "data", "datetime.datetime.strptime(!view_date![:10], '%Y-%m-%d')",
        "PYTHON3", field_type="DATE")
    arcpy.management.DeleteField(novos, "view_date")
    arcpy.management.AlterField(novos, "data", "view_date", "view_date")
    arcpy.management.Append(novos, base, "NO_TEST")

The same works for an ArcGIS Online layer: use the service URL (.../FeatureServer/0) as base, as the original scripts did.

import processing

BASE = "C:/dados/deter_historico.gpkg"
WFS = ("WFS:https://terrabrasilis.dpi.inpe.br/geoserver/deter-amz/ows"
       "|layername=deter-amz:deter_amz")
CAMPOS = ["gid", "classname", "view_date", "sensor", "satellite", "municipality", "uf"]

def chave(f):
    """The gid alone repeats in DETER: the key is code + class + date."""
    return f"{f['gid']}|{f['classname']}|{f['view_date'].toString('yyyy-MM-dd')}"

# 1. How far does the database go? Fetch the previous 30 days again
historico = QgsVectorLayer(f"{BASE}|layername=deter", "historico", "ogr")
ultima = historico.maximumValue(historico.fields().indexOf("view_date"))
desde = ultima.addDays(-30).toString("yyyy-MM-dd")
existentes = {chave(f) for f in historico.getFeatures()}
print("current database:", len(existentes), "alerts; fetching since", desde)

# 2. WFS layer filtered on the server (date) and by the area-of-interest rectangle
servidor = QgsVectorLayer(WFS, "deter_wfs", "ogr")
servidor.setSubsetString(f"view_date >= '{desde}'")
retangulo = QgsFeatureRequest().setFilterRect(QgsRectangle(-56.5, -8.5, -54.5, -5.5))
janela = servidor.materialize(retangulo)           # copies into memory

# 3. Keep only what is not yet in the database, and only the database's fields
ids = [f.id() for f in janela.getFeatures() if chave(f) not in existentes]
novos = janela.materialize(QgsFeatureRequest().setFilterFids(ids))
novos = processing.run("native:retainfields", {
    "INPUT": novos, "FIELDS": CAMPOS, "OUTPUT": "memory:"})["OUTPUT"]
print("new alerts:", novos.featureCount())

# 4. Append to the database layer (action 2 = append, without creating fields)
if novos.featureCount():
    processing.run("native:savefeatures", {
        "INPUT": novos, "OUTPUT": BASE, "LAYER_NAME": "deter",
        "ACTION_ON_EXISTING_FILE": 2})
depois = QgsVectorLayer(f"{BASE}|layername=deter", "depois", "ogr")
print("database after:", depois.featureCount(), "alerts")

materialize copies the features into memory without going through Processing, which would stop at DETER’s invalid geometries (see the article Inside and around).

In the database, the key becomes a primary key, and INSERT … ON CONFLICT inserts what is new and updates what already exists, in a single operation:

-- First time only: the base table, from the GeoPackage's deter layer
CREATE TABLE artigos.deter_hist AS
SELECT gid, classname, view_date, sensor, satellite, municipality, uf, geom
FROM artigos.deter;
ALTER TABLE artigos.deter_hist ADD PRIMARY KEY (gid, classname, view_date);
CREATE INDEX ON artigos.deter_hist USING gist (geom);

The update script uses ogr2ogr to bring the window from the WFS:

#!/usr/bin/env bash
# Incremental DETER load in PostGIS: only the alerts newer than those in the database.
set -euo pipefail
BANCO=monitoramento_ambiental

# 1. How far does the database go? Fetch the previous 30 days again (safety window)
DESDE=$(psql -d $BANCO -Atc "SELECT max(view_date) - 30 FROM artigos.deter_hist")
echo "fetching alerts since $DESDE"

# 2. Bring only the window from the WFS into a staging table
ogr2ogr -f PostgreSQL "PG:dbname=$BANCO" \
  "WFS:https://terrabrasilis.dpi.inpe.br/geoserver/deter-amz/ows" deter-amz:deter_amz \
  -nln artigos.deter_novos -overwrite -nlt MULTIPOLYGON -lco GEOMETRY_NAME=geom \
  -spat -56.5 -8.5 -54.5 -5.5 -where "view_date >= '$DESDE'" \
  -select gid,classname,view_date,sensor,satellite,municipality,uf

# 3. Insert the new ones; if the key already exists, update it
psql -d $BANCO -v ON_ERROR_STOP=1 -f upsert.sql

And upsert.sql:

INSERT INTO artigos.deter_hist (gid, classname, view_date, sensor, satellite,
                                municipality, uf, geom)
SELECT gid, classname, view_date, sensor, satellite, municipality, uf, geom
FROM artigos.deter_novos
ON CONFLICT (gid, classname, view_date) DO UPDATE
    SET geom = EXCLUDED.geom,
        municipality = EXCLUDED.municipality;

SELECT count(*) AS alertas, max(view_date) AS ate FROM artigos.deter_hist;

import geopandas as gpd
import pandas as pd
from urllib.parse import quote

BASE = "dados/deter_historico.gpkg"
CAMPOS = ["gid", "classname", "view_date", "sensor", "satellite", "municipality", "uf", "geometry"]

def chave(gdf):
    """The gid alone repeats in DETER: the key is code + class + date."""
    return gdf["gid"] + "|" + gdf["classname"] + "|" + gdf["view_date"].astype(str)

# 1. How far does the database go? Fetch the previous 30 days again (safety window)
historico = gpd.read_file(BASE, layer="deter")
ultima = pd.to_datetime(historico["view_date"]).max()
desde = (ultima - pd.Timedelta(days=30)).date()
print("current database:", len(historico), "alerts, up to", ultima.date(), "- fetching since", desde)

# 2. Ask the server only for the window, only in the study area (the filter runs on the server)
filtro = f"view_date >= '{desde}' AND BBOX(geom,-56.5,-8.5,-54.5,-5.5,'EPSG:4674')"
url = ("https://terrabrasilis.dpi.inpe.br/geoserver/deter-amz/ows?service=WFS&version=2.0.0"
       "&request=GetFeature&typeNames=deter-amz:deter_amz&outputFormat=application/json"
       f"&srsName=EPSG:4674&CQL_FILTER={quote(filtro)}")
novos = gpd.read_file(url)

if novos.empty:
    print("nothing new on the server")
else:
    # 3. Keep only what is not yet in the database
    novos = novos.set_crs(4674, allow_override=True)[CAMPOS]
    novos = novos[~chave(novos).isin(chave(historico))]
    # 4. Append to the same GeoPackage layer
    if len(novos):
        novos.to_file(BASE, layer="deter", mode="a")
    print("alerts appended:", len(novos))

depois = gpd.read_file(BASE, layer="deter")
print("database after:", len(depois), "alerts; repeated keys:", chave(depois).duplicated().sum())

Checking the result

The three tested versions (QGIS, PostGIS and Python) started from the same database of 7,483 alerts:

Incremental DETER load in the example area (data up to 2026-10-16).
	1st run	2nd run
Database before	7,483	7,823
Alerts appended	340	0
Database after	7,823	7,823
Repeated keys	0	0

The second run appended nothing, which is the most important test: an update script must be able to run twice without damaging the database.

The three pitfalls the data revealed

1. The alert code repeats

The gid field looks like an identifier, but 28 codes in the study area appear in two different alerts (different class, date and place). E.g., 36608_hist is a clearing from March 2024 and a burn scar from October 2024. With gid as the key, PostGIS rejects the table; using it in an isin, new alerts would be discarded. The combination gid + classname + view_date is unique across the 7,823 alerts.

2. The date filter returns the last day again

Through ogr2ogr, the filter view_date > '2026-10-16' returned 148 alerts from the 16th itself. The server compares the date as a date and time, with a time zone. ON CONFLICT prevented duplication, but a script that only appends would have duplicated those 148. Lesson: never rely on the date filter alone; compare by key.

3. Alerts published late

An alert can enter the service days after the image date (view_date). If the script only fetches “what is newer than the maximum date”, that alert never gets in. The 30-day window catches the late ones, and the key discards what was already in the database.

Scheduling and taking it to Power BI

Scheduling: Windows Task Scheduler or cron (see the article Fire hotspots). Run it once a day, at night.
Power BI: with the database updated in PostGIS, the dashboard from Module 7 (in Portuguese) only needs a Refresh. In the Power BI service, scheduled refresh of a database on your own computer requires the on-premises data gateway installed and running.

Updates

2026-10-29: first published, with data from INPE, MMA, IBAMA and SICAR from October 2026.

Versão em português

Reuse

CC BY 4.0

Citation

BibTeX citation:

@misc{alves2026,
  author = {Alves, Hendesson},
  publisher = {Hendesson Alves},
  title = {Updating Layers Without Duplicates: Replace, Append and
    Incremental Load},
  date = {2026-10-29},
  url = {https://hendesson.github.io/en/articles/13-updating-layers.html},
  doi = {10.5281/zenodo.23780270},
  langid = {en},
  abstract = {The four ways to update a database that receives new data,
    and an incremental-load recipe for DETER that survives three real
    pitfalls: repeated codes, a date filter with time zones and alerts
    published late.}
}

For attribution, please cite this work as:

Alves, Hendesson. 2026. “Updating Layers Without Duplicates: Replace, Append and Incremental Load.” In Geoprocessing Articles for Environmental Monitoring. Preprint, Hendesson Alves, October 29. https://doi.org/10.5281/zenodo.23780270.