dsp-job-data-migration — Generic layer migration¶

Tutorial guide for the geographic layers module (batch.layers) of the dsp-job-data-migration job. Job overview: Overview.

What this module does¶

The job reads extra PostGIS tables in the source database and replicates their features in the geo-target database (display / WMS).

You specify role columns (PK, link to AOI, creation date, geometry). The job discovers the rest of the schema and creates the destination table.

Important concepts¶

Assumption: each feature belongs to one area of interest. The column that links it at the source is declared in YAML; on the destination it always becomes area_of_interest_id.

In the core wizard (adopter-config.yaml), this field is called parent_key and is translated to area-of-interest-id-column.

Where data lands¶

flowchart LR
  src[("Source<br/>source")]
  geo[("Geo-target<br/>WMS display")]
  dsp[("DSP DB<br/>operational")]

  src -->|"Fixed jobs<br/>(AU, AOI)"| dsp
  src -->|"Fixed jobs"| geo
  src -->|"Generic layers<br/>(this module)"| geo

Generic layers only write to geo-target. Destination comes from resolved layer-name (if omitted, source table name), hyphen → underscore:

geo-target → dsp.<normalized_layer_name>

Examples: source conservation.rivers without layer-name → dsp.rivers; layer-name: tipo-a → dsp.tipo_a. The same source-table can appear in more than one entry if destinations differ.

Canonical columns on destination: id (varchar(255)), area_of_interest_id (varchar(255)), created_at (timestamptz), updated_at (if present), label (if present), geom. Only one geometry is migrated — the one in geometry-column.

Execution order¶

Layers depend on area_of_interest_id pointing to records that already exist in dsp.area_of_interest.

1. Admin units (level-1 → level-2 → level-3)
2. Area of interest (area-of-interest)
3. Generic layers (layer-jobs)   ← this module
4. KPI calculation (kpi-job)     ← after the layers used by themes

In practice, fixed jobs’ JobRunner runs before the layer runner (@Order(1) and @Order(2)); kpiCalculationJob runs after (@Order(3)). Each theme KPI in the wizard points at the layer-name of a layer migrated here.

Prerequisites¶

• Source database with PostGIS and tables to migrate
• Geo-target database reachable (spring.datasource.geo-target)
• Each source table with simple PK (one column)
• Geometry column specified in YAML
• AOI link column filled on features
• creation-date-column filled (watermark)
• Area of interest job already run (valid FK values)
• Spring Batch schema created on dsp-db (data_migration)

Minimum configuration¶

1. Geo-target datasource¶

Besides batch, source, and target, configure:

spring:
  datasource:
    geo-target:
      url: jdbc:postgresql://localhost:5432/dsp-geoserver-db
      username: dsp_geo
      password: dsp_geo

2. Declare layers¶

batch:
  layers:
    - source-table: conservation.rivers
      primary-key: feature_id
      area-of-interest-id-column: conservation_unit_id
      creation-date-column: created_at
      geometry-column: geom
      layer-name: rivers
      srid: 4674

3. Enable execution¶

execution-jobs:
  layer-jobs: true

4. Parallelization (optional)¶

Job name follows layerMigrationJob_ + layer key (dsp_<physical_name>):

parallelization:
  jobs:
    layerMigrationJob_dsp_rivers:
      enabled: true
      thread-pool-size: 2
      chunk-size: 100
      page-size: 1000
      queue-capacity: 100

If there is no entry for a layer, the job uses defaults.

Optional properties¶

If the destination table already exists, the job requires created_at timestamptz on it (CREATE TABLE IF NOT EXISTS does not alter an existing table).

What happens on each run¶

Each layer gets one independent Spring Batch job.

flowchart TD
  A[Setup] --> B[Watermark change detection]
  B --> C{Temporal delta?}
  C -->|No| D[End — SKIP<br/>orphans may already have been deleted]
  C -->|Yes| E[Partitioned read]
  E --> F[UPSERT on geo-target]

Step 1 — Setup¶

1. Validates table exists at source
2. Discovers columns, PK, geometry, SRID, and indexes
3. Runs CREATE SCHEMA IF NOT EXISTS dsp on geo-target
4. Creates table dsp.<physical_name> (if not exists)
5. Creates GIST index on geom and index on area_of_interest_id

Step 2 — Change detection¶

Same engine as admin units and AOI:

• Delta — records with creation (or updated_at) after watermark stored in BATCH_JOB_EXECUTION_SYNC_STATE
• Orphans — PK only on geo-target → DELETE (scan every 24 h)
• No delta → SKIP (even if orphans were deleted)

Step 3 — Load (UPSERT)¶

• Reads features from source in pages (partitioning when PK is numeric or numeric VARCHAR)
• Writes to geo-target with INSERT ... ON CONFLICT DO UPDATE
• Renames link column: e.g. conservation_unit_id → area_of_interest_id
• Geometries with Z/M are flattened to 2D (ST_Force2D)

Example with multiple layers¶

batch:
  layers:
    - source-table: conservation.rivers
      primary-key: feature_id
      area-of-interest-id-column: conservation_unit_id
      creation-date-column: created_at
      geometry-column: geom
      layer-name: rivers
      srid: 4674

    - source-table: conservation.lakes
      primary-key: feature_id
      area-of-interest-id-column: conservation_unit_id
      creation-date-column: created_at
      geometry-column: geom
      layer-name: lakes
      srid: 4674

execution-jobs:
  admin-unit-level-1-geoserver-job: true
  admin-unit-level-2-geoserver-job: true
  admin-unit-level-3-geoserver-job: true
  area-of-interest-geoserver-job: true
  layer-jobs: true

parallelization:
  jobs:
    layerMigrationJob_dsp_rivers:
      enabled: true
      thread-pool-size: 1
      chunk-size: 100
      page-size: 1000
    layerMigrationJob_dsp_lakes:
      enabled: true
      thread-pool-size: 1
      chunk-size: 100
      page-size: 1000

How to run¶

At the root of the dsp-job-data-migration repository:

# 1. Spring Batch metadata on destination database (once)
psql -h localhost -U postgres -d dsp_db \
  -f src/main/resources/db/batch_metadata/01_spring_batch_schema.sql

# 2. Start the job
./mvnw spring-boot:run

To run only layers (fixed jobs off):

./mvnw spring-boot:run -Dspring-boot.run.arguments="\
--execution-jobs.admin-unit-level-1-geoserver-job=false \
--execution-jobs.admin-unit-level-2-geoserver-job=false \
--execution-jobs.admin-unit-level-3-geoserver-job=false \
--execution-jobs.area-of-interest-geoserver-job=false \
--execution-jobs.layer-jobs=true"

How to validate¶

On geo-target, check table was created and populated:

SELECT COUNT(*) FROM dsp.rivers;

SELECT area_of_interest_id, COUNT(*)
FROM dsp.rivers
GROUP BY area_of_interest_id;

SELECT COUNT(*) FROM dsp.rivers WHERE geom IS NOT NULL;

Layer watermark (on dsp-db):

SELECT sync_key, watermark_last_event_at, last_success_at, last_orphan_check_at
FROM data_migration.BATCH_JOB_EXECUTION_SYNC_STATE
WHERE source_table LIKE '%rivers%';

Useful logs (package br.car.dsp_batch):

• Introspection completed for ... — structure discovered
• Target table ready: dsp.rivers — DDL applied
• Upserted N features into geo-target dsp.rivers — load complete
• No changes detected — re-run with no temporal delta (SKIP)

Current limitations¶

Common errors¶

Difference from fixed jobs¶

In the dsp-core wizard¶

In stage 2/6 (etl.layers[] in adopter-config.yaml), each layer is configured once and feeds migration, map, and downloads:

The same source_table can repeat with a different layer_name; the wizard reuses column mapping from the previous layer with the same source.

To turn off a layer in the wizard flow, remove it from etl.layers[] and reapply ./config.sh — enabled is not accepted in adopter-config.yaml.

Full wizard detail: dsp-core.

Where to go deeper¶