# GeoSAT Open GIS reference lab

A reproducible migration exercise in English and Spanish. Start with one dataset, preserve its identifiers, publish it through documented service contracts, and record what actually works. Everything in `data/` is synthetic: the regular grid is near Medellín for geographic context but does not identify real municipal assets. There is no customer case, measured migration saving, Esri performance comparison, or production capacity claim here.

## What is included

| Asset | Purpose |
|---|---|
| `data/municipal-assets.csv` | 10,000 generated points with explicit longitude and latitude |
| `data/municipal-assets.geojson` | The same records as RFC 7946 GeoJSON |
| `data/municipal-assets.gpkg` | GDAL-generated GeoPackage, `assets` layer and spatial index |
| `data/assets-shapefile.zip` | Optional native GeoServer fixture; `inspected_on` becomes `inspect_dt` because DBF names are limited |
| `qgis/municipal-assets.qgs` | Relative-path GeoPackage project and condition value-map form |
| `sql/01-init.sql` | PostGIS schema, constraints, import, indexes, separate reader and restricted editor |
| `sql/02-acceptance.sql` | Counts, SRID, geometry, spatial filter and role checks |
| `sql/03-export.sql` | Stable-ID GeoJSON export |
| `geoserver/assets.sld` | Good/inspect/repair point styles |
| `pygeoapi/` | CSV and PostGIS configurations; dependency pins; container build |
| `viewer/` | Local MapLibre viewer with vendored JS/CSS; no paid basemap or API key |
| `worksheets/` | Migration inventory, cost inputs, and acceptance evidence worksheets |
| `measurements/` | Actual local results, raw timings, method and limitations |

## Verification boundaries

Executed for this release: CSV/GeoJSON parity; GeoJSON → GeoPackage roundtrip with pyogrio 0.11.1 / GDAL 3.10.3; pygeoapi 0.24.0 CSV HTTP contract and timing checks; native GeoServer 2.28.5 on Java 17.0.20 with a Shapefile store, WMS rendering, WFS queries, and anonymous REST denial. Every measured request in the published runs returned 200.

Not executed here: the Docker Compose stack, PostGIS SQL, the GeoServer PostGIS datastore, the pygeoapi PostgreSQL provider, QGIS GUI, QField/device synchronization, identity federation, backup restoration, concurrent load, or production deployment. Those assets are supplied as implementation examples with acceptance commands below, not as completed acceptance results. Registry manifests were read to pin container images; that is not a container startup test.

## 1. Inspect and validate the fixture (Python 3.11+)

Extract the ZIP and open a terminal in `open-gis-lab`. Commands below use a POSIX shell; Windows users can use WSL2. Keep the directory structure intact. Never edit your only copy of a real dataset while following a training exercise.

```sh
python3 scripts/check-data.py
```

Expected: `PASS` with 10,000 unique stable identifiers, matching CSV and GeoJSON coordinates, district D01 count 1,000 and bounding-box count 441. IDs run from `asset-00001` to `asset-10000`. Coordinates are longitude, latitude; the extent is `[-75.65, 6.15, -75.551, 6.249]`.

`asset_type` is hydrant/streetlight/drain; `condition` is good/inspect/repair; `district` is D01–D10; `inspected_on` is an ISO date. Attribute patterns deliberately correlate to simplify checks; do not infer how actual infrastructure is distributed. Meter-based distance/area calculations require an appropriate projected CRS or geography operation; degrees are not meters.

To regenerate the CSV and GeoJSON, run `python3 scripts/generate-data.py`. This overwrites the synthetic fixture. To regenerate GeoPackage and Shapefile too, install the tools in the next step, then run `python scripts/make-geopackage.py` and `python scripts/make-shapefile.py`. These overwrite only their named fixture files. GeoPackage geometry and attribute parity are checked after writing.

## 2. Run an OGC API without Docker

Use an isolated environment; no global GIS installation is required. The tools requirements include packages for optional format regeneration. Downloading dependencies needs internet access; serving the fixture does not.

```sh
python3 -m venv .local/venv
. .local/venv/bin/activate
python -m pip install -r pygeoapi/requirements-tools.txt
export PYGEOAPI_CONFIG="$PWD/pygeoapi/config-csv.yml"
export PYGEOAPI_OPENAPI="$PWD/.local/openapi.yml"
pygeoapi openapi generate "$PYGEOAPI_CONFIG" > "$PYGEOAPI_OPENAPI"
pygeoapi serve
```

Keep this terminal running. In a second terminal:

```sh
curl --fail 'http://localhost:5000/collections?f=json'
curl --fail 'http://localhost:5000/collections/assets/items?f=json&limit=10'
curl --fail 'http://localhost:5000/collections/assets/items?f=json&limit=10&offset=10'
curl --fail 'http://localhost:5000/collections/assets/items?f=json&district=D01&limit=10'
curl --fail 'http://localhost:5000/collections/assets/items?f=json&bbox=-75.63,6.17,-75.61,6.19&limit=100'
curl --fail 'http://localhost:5000/collections/assets/items/asset-10000?f=json'
```

Check `numberMatched=10000` on the unfiltered collection; the second page begins with `asset-00011`; D01 matches 1,000; the bounding box matches 441 and returns at most 100. Missing IDs return 404. `limit=not-a-number` returns 400. The configured maximum is 100 returned features; this is not a total collection size limit. Use response `next` links or documented offset pagination to retrieve further pages. Do not assume every provider implements the same filters, sorting, transactions or CRS transformations.

The CSV API is read-only. CORS is enabled for this loopback learning exercise so the separate local viewer can fetch it; authentication, a public reverse proxy, rate limiting and a production CORS allowlist are not configured.

## 3. Open the web map and QGIS project

From the kit root, in another terminal:

```sh
python3 -m http.server 8850 --bind 127.0.0.1
```

Open `http://localhost:8850/viewer/`. The default loads all 10,000 local GeoJSON features. The API button loads only its first 100, which makes the response limit visible. Use the English/Spanish toggle and click a point to inspect its attributes. Green, amber and red represent good, inspect and repair. No external tile service is used. A blank background is intentional; geometry loading and identifiers are the subject of this exercise.

In QGIS 3.x, open `qgis/municipal-assets.qgs`. It references `../data/municipal-assets.gpkg|layername=assets`. If your QGIS version repairs project settings on load, save a copy and record its exact version. Confirm the layer count is 10,000, CRS is EPSG:4326, and stable IDs are preserved. Open the attribute form: `asset_id` should be non-editable and `condition` should offer the three valid values. Edit a COPY of the GeoPackage and verify persistence after reopening. The project XML is structurally checked but was not opened in QGIS for this release; this checklist is your desktop acceptance task.

For QField, this kit is a starting dataset/project, not a packaged and synchronized field deployment. First confirm QGIS forms, then package a copy using the QField workflow appropriate to your version. Test airplane mode, two-device conflicting edits, attachment paths, reconnection, identifier stability, and a rollback before approving a production field workflow.

## 4. Run the optional PostGIS + GeoServer + pygeoapi stack

Prerequisites: Docker Engine/Desktop with Compose v2, enough free memory for PostgreSQL and a Java server, and internet access to fetch images/build dependencies. PostGIS and the GeoServer image are pinned to Linux amd64; ARM machines may need emulation. Emulated timings are not comparable with the native measurements included here. All published ports bind to `127.0.0.1`.

Set distinct disposable secrets in this shell. Nothing is written to an `.env` file and no actual credentials are bundled:

```sh
export LAB_DB_PASSWORD="$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')"
export LAB_READER_PASSWORD="$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')"
export LAB_EDITOR_PASSWORD="$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')"
export LAB_GEOSERVER_PASSWORD="$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')"
docker compose config --quiet
docker compose up -d --build
docker compose ps
```

Stop the native CSV API first if it uses port 5000. Initial database setup runs only on an empty named volume. Keep this shell for the publishing step; regenerated passwords do not change an existing database. Compose errors explain missing variables. Do not print the expanded Compose configuration or copy container environment dumps into a ticket; they contain the disposable secrets.

Endpoints: PostgreSQL `127.0.0.1:55432`, database `geosat_lab`; GeoServer `http://localhost:8080/geoserver`; OGC API `http://localhost:5000`. Database owner is `lab_owner`; the API and GeoServer use `lab_reader`. `lab_editor` can update only `condition` and `inspected_on`; it cannot change geometry or delete/insert records. This narrow example demonstrates privileges; it is not a complete multi-user editing or conflict-resolution system.

Run database acceptance:

```sh
docker compose exec -T db psql -U lab_owner -d geosat_lab < sql/02-acceptance.sql
```

Expected: 10,000 rows, valid EPSG:4326 points, D01=1,000, spatial envelope=441, a query plan, and privilege flags `true,false,true,false`. The planner chooses the actual index strategy; do not claim an index was used without reading its plan. The editor update is inside a transaction that rolls back.

Publish the PostGIS datastore and style after GeoServer responds:

```sh
. .local/venv/bin/activate
python scripts/publish-geoserver.py --mode postgis
```

The publisher requires the explicit environment passwords, permits only a loopback GeoServer host, creates workspace `lab`, datastore `assets`, layer `lab:assets`, and the condition style. Existing matching resource names are reused; use a fresh lab volume if you previously published the Shapefile store with the same name. It does not configure SSO, WFS transactions, public hosting, or ArcGIS REST emulation.

Verify services:

```sh
curl --fail 'http://localhost:8080/geoserver/lab/wms?service=WMS&request=GetCapabilities'
curl --fail 'http://localhost:8080/geoserver/lab/wfs?service=WFS&version=2.0.0&request=GetFeature&typeNames=lab:assets&outputFormat=application/json&srsName=EPSG:4326&count=10'
curl --fail 'http://localhost:5000/collections/assets/items?f=json&limit=10'
docker compose exec -T db psql -U lab_owner -d geosat_lab < sql/03-export.sql > .local/export.geojson
```

WMS 1.1.1 example coordinates are longitude/latitude with `SRS=EPSG:4326`; WMS 1.3.0 may use the CRS-defined axis order. Copying a BBOX unchanged between versions can yield a blank map. A WFS endpoint or OGC API collection also does not make an application expecting ArcGIS `/FeatureServer` or `/MapServer` compatible; verify each actual consumer.

## 5. Native GeoServer alternative and actual measurements

The published GeoServer run used the official 2.28.5 platform-independent binary, Java 17.0.20, `-Xms128m -Xmx768m`, and loopback binding. The binary is not bundled. Download it from the official release page, use its own disposable data directory, bind `jetty.http.host=127.0.0.1`, and select port 8080. Follow the upstream setup instructions to set a dedicated administrator password, then export `GEOSERVER_USER` and `LAB_GEOSERVER_PASSWORD` in your shell. The kit does not embed or attempt to discover a password.

```sh
python scripts/make-shapefile.py
python scripts/publish-geoserver.py --mode shapefile
python scripts/measure-geoserver.py --store shapefile
python scripts/measure-pygeoapi.py
```

`measure-pygeoapi.py` starts and stops its own loopback HTTP server on an available port. `measure-geoserver.py` reads the GeoServer you started. Each command replaces only its corresponding result files in `measurements/`; keep a copy of the shipped results before running if you want to compare your machine.

The pygeoapi run has 11 contract checks and four workloads of 30 measured requests after five warmups each. GeoServer has eight checks and three workloads of 30 measured requests after five warmups each. Both use one sequential client, full-response timings, no TLS and no cache flush. p50 is the median; p95 is nearest-rank. Raw samples and response sizes are included. Dataset hashes identify the fixture. The GeoServer WMS PNG is an actual GetMap response.

A CSV bounding-box request must inspect records; page size alone does not explain its cost. WMS rendering and WFS/OGC feature retrieval produce different outputs. Neither observation establishes which product is faster. These local measurements do not estimate production concurrency, cloud latency, recovery capability, or savings.

## 6. Practice backup and restore in the disposable stack

This optional procedure has not been executed for the published kit. It creates a separate database inside the disposable lab container and leaves the original intact:

```sh
mkdir -p .local/backups
docker compose exec -T db pg_dump -U lab_owner -d geosat_lab --format=custom > .local/backups/geosat-lab.dump
docker compose exec -T db createdb -U lab_owner geosat_restore
docker compose exec -T db pg_restore -U lab_owner -d geosat_restore --no-owner < .local/backups/geosat-lab.dump
docker compose exec -T db psql -U lab_owner -d geosat_restore < sql/02-acceptance.sql
```

Compare counts, stable ID sets, geometry hashes and attributes between source and restored database. Measure restore duration and record where the dump lives; an export existing on the same failed disk is not a recovery plan. Roles are cluster-level and already exist in this lab; restoring into a separate server additionally requires a deliberate role/ownership procedure.

`docker compose stop` stops the exercise while retaining data. `docker compose down` removes its containers and network but retains named volumes. The separate command `docker compose down -v` deletes this lab's database and GeoServer volumes; use it only when you intend to discard those disposable lab records. Never substitute a production Compose project.

## 7. Turn the exercise into a migration decision

Fill `worksheets/migration-audit.csv` with actual workflows and their owners. Inventory service URLs, apps, scripts, identity, field devices, attachments, CRS transformations, licenses, recovery targets and support commitments. Keep an ArcGIS component when a required workflow has not passed its replacement test.

Fill `worksheets/tco-inputs.csv` with your quotes, hours and documented assumptions. Use one currency and horizon. Annual current TCO is the sum of current licenses, infrastructure, support and internal labor. Future TCO uses the corresponding future costs plus one-time migration, training and parallel operation. Undiscounted saving = current annual × years − future annual × years − transition. Simple payback months = transition / (current annual − future annual) × 12 only when the annual difference is positive. A negative saving or payback beyond the horizon is a valid result. Blank cells mean missing information, not zero. This worksheet has no prefilled vendor prices or automatic exchange rates.

Use `worksheets/acceptance.csv` to attach your own observed outputs. Do not mark the supplied unexecuted checks as passed. Extend the fixture with polygons, attachments, relationships, versioned editing or your own sanitized data only after documenting their data rights and compatibility requirements.

## Troubleshooting

| Symptom | Check |
|---|---|
| API cannot find CSV | Run from kit root; `data/municipal-assets.csv` is relative to the working directory |
| Port in use | Stop the earlier native process; do not silently publish to all interfaces |
| Missing CORS package | Install the provided requirements, including Flask-CORS |
| QGIS missing layer | Preserve the project/data relative paths; select the GPKG `assets` layer |
| GeoServer layer fails | Confirm database health, datastore type, reader password, schema and table |
| Dataset unchanged after editing init SQL | Initialization runs only once on an empty volume |
| API returns only 100 | That is the configured page cap; paginate |
| WMS looks blank | Check layer/style names, bounds, protocol version and axis order |
| SQL permissions fail | Confirm you connected to the intended disposable database and roles |
| ARM container starts slowly | The pinned amd64 images may run under emulation |
| Form or offline sync fails | Record exact QGIS/QField version and task; server availability is not a field acceptance test |

## Sources and versions

- [pygeoapi 0.24 documentation and provider capabilities](https://docs.pygeoapi.io/en/stable/publishing/ogcapi-features.html)
- [GeoServer 2.28.5 release](https://geoserver.org/release/2.28.5/)
- [Official GeoServer container](https://github.com/geoserver/docker)
- [Official PostGIS container](https://github.com/postgis/docker-postgis)
- [PostGIS documentation](https://postgis.net/documentation/)
- [QGIS user manual](https://docs.qgis.org/latest/en/docs/user_manual/)
- [QField documentation](https://docs.qfield.org/)
- [MapLibre GL JS](https://maplibre.org/maplibre-gl-js/docs/)
- [GeoPackage standard](https://www.geopackage.org/)

The container manifests are recorded in `measurements/container-images.json`; Compose and the Python base image use immutable digests. Top-level Python packages are pinned in requirements files; transitive dependency resolution can still change between installations. The native runtime versions in each result JSON describe the environment actually measured, which differs from the optional container Python base. Check current security advisories and supported releases when turning this learning example into a maintained system.