Storing spatial data and spatial databases | Learn Geospatial Data Management & Engineering w/ BNHR

<img src="../static/img/bnhr-2024-fordarkbg-main-720.png" class="title-logo" alt="BNHR">
<p class="project-context">philippine statistics authority · geospatial data management & engineering</p>

<h1><span style="color: var(--bnhr-accent-green);">storing</span> spatial data</h1>

<p class="url">bnhr.xyz</p>

---

## the storage problem

> *"A field officer in Albay updates an aquafarm record..."*

- How long does it take before it becomes part of the official dataset?
- How many manual steps does that involve?
- What breaks if one step is skipped?

**Discuss within your group for 3-5 minutes.**

Note: Pose this to the room. Let participants discuss in pairs for 2 minutes. Do not answer yet — this question frames the motivation for the entire morning.

---

## Contents

1. **Lecture**: Beyond file-based spatial data management
2. **Demo**: PostGIS, DuckDB & GeoParquet, Data Lakes
3. **Workshop**: Which Storage for My Use Case?

Note: 45-minute lecture (9:10–9:55), feedback + break (9:55–10:10), demo (10:10–11:00), workshop (11:00–11:50), morning debrief (11:50–12:00).

---

<img src="../static/img/bnhr-2024-fordarkbg-main-720.png" class="title-logo" alt="BNHR">

# How are you<br>storing your data?

---

## where does your data live today?

| ArcGIS Online | Open Replacement |
|---|---|
| Hosted feature layers | **PostGIS** tables |
| WMS / WFS services | **GeoServer** |
| Data catalog / portal | **GeoNode** |
| AGOL dashboards | Custom web app or GeoNode |
| Cloud hosting (ESRI-managed) | Self-hosted **Docker** stack |

The conceptual mapping is 1:1. The migration is three steps: **export → load → publish**.

This week covers every step.

Note: Start from what participants already know. The goal is to make the open stack feel familiar rather than foreign — ArcGIS Online is being replaced with named open equivalents for every component they already use.

---

## (shape)file-based data management

<div class="two-column">
<div class="column">
<div class="column-header">Without a database</div>

- `palay_final.shp`
- `palay_final_v2.shp`
- `palay_2023.shp`
- `palay_2023_corrected.shp`
- `palay_region2_updated_jan.shp`
- `palay_region2_updated_jan_FINAL.shp`
- `palay_SUBMITTED_corrected.shp`

*Which one is current? Nobody knows for certain.*

</div>
<div class="column">
<div class="column-header">With a database</div>

One table. One authoritative dataset.

`psa.palay_distribution`

**14,382 rows**
Last updated: 2024-11-12
Updated by: coordinator_ramos

*No version archaeology.*

</div>
</div>

Note: Pause here and ask the room: "How many of you have a folder that looks like the left side?" Most hands will go up. This is the problem that motivates everything that follows. The database does not solve this by convention — it solves it by design. There is only one table name. One dataset. Everyone queries the same rows.

---

## what makes a database "spatial"?

- **Geometry column** — stores points, lines, and polygons as structured data, not as text
- **Spatial data types** — Point, LineString, Polygon, MultiPolygon (OGC WKT/WKB standard)
- **Spatial index (GIST)** — a tree structure that makes *"find everything near X"* fast at scale
- **Spatial functions** — `ST_Intersects`, `ST_Buffer`, `ST_Distance`, `ST_Within`, `ST_DWithin`

The critical difference from a spreadsheet with a latitude column:

**A spatial database can answer spatial questions efficiently.**

Note: A spreadsheet with lat/lon can store locations but cannot efficiently answer "which farms are within 10 km of a road?" without scanning every row. The GIST index is what makes spatial queries fast at scale. This is the "why" behind the whole morning.

---

## PostGIS: the production spatial database

PostgreSQL + the PostGIS extension.

| Strength | What it means for AFCD |
|---|---|
| **Single source of truth** | One canonical table replaces dozens of versioned shapefiles on shared drives |
| **Concurrent multi-user access** | Field officers and analysts can read and write simultaneously |
| **ACID transactions** | No partial updates — data is always consistent |
| **GeoServer as native client** | Publish tables directly as WMS / WFS layers |
| **Role-based access control** | Different permissions per user group |

</div>

This is similar to ArcGIS Online/Enterprise's managed database.

Note: Emphasize the GeoServer integration — this is what closes the loop from "data in a database" to "layer on a web map." PostGIS is the backend; GeoServer is the front door. Day 2 covers the GeoServer side.

---

## OLTP vs OLAP: the fundamental split

<div class="two-column">
<div class="column">
<div class="column-header">OLTP — Transactional</div>

**Online Transaction Processing**

Many small reads and writes.
Concurrent users.
Low latency per query.
Data changes frequently.

*Field data entry, web map updates, role-managed edits*

**→ PostGIS**

</div>
<div class="column">
<div class="column-header">OLAP — Analytical</div>

**Online Analytical Processing**

Few large reads.
Scan millions of rows.
Compute aggregates and joins.
Data is mostly static.

*Annual census analysis, cross-dataset reports, ad-hoc exploration*

**→ DuckDB**

</div>
</div>

Note: This distinction drives every storage decision in the morning. If you can identify whether a use case is OLTP or OLAP, you can pick the right tool. Most confusion about "which database should I use?" comes from mixing these two modes in one system.

---

## when to use PostGIS — and when not to

<div class="two-column">
<div class="column">
<div class="column-header">Use PostGIS when...</div>

- Multiple users read and write concurrently
- The team needs one authoritative source — no more "which shapefile is current?" debates
- Data is updated regularly by field staff or coordinators
- GeoServer WMS / WFS needs a live backend
- Complex spatial joins across multiple layers
- Role-based access control is required

</div>
<div class="column">
<div class="column-header">Do NOT use PostGIS when...</div>

- One analyst runs a query on a large static dataset
- Local personal analysis by a single person
- Columnar aggregate queries across millions of rows
- Ad-hoc exploration where server overhead is not justified

</div>
</div>

Note: The "do not use" column is as important as the "use" column. PostGIS has real overhead: a server to maintain, a schema to design, connections to manage. For many analytical workflows, that overhead is not justified. DuckDB removes it entirely.

---

## DuckDB: in-process analytical queries

No server. No configuration. No schema migration.
Runs inside Python, the CLI, or a Jupyter notebook.

| Feature | What it means |
|---|---|
| **Columnar storage** | Reads only the columns a query needs — fast on wide tables |
| **Vectorized execution** | Processes batches of rows together — very fast aggregates |
| **Native file formats** | Reads GeoParquet, GeoPackage, CSV, GeoJSON without import |
| **In-process** | No connection overhead — the engine runs in your process |

PostGIS and DuckDB are **complementary**, not competing.

Note: Demonstrate that DuckDB can be started in 30 seconds with no setup — contrast this with PostGIS which requires a running server, a schema, and a connection. The complementary framing is critical: these tools often appear together in the same workflow.

---

## PostGIS & DuckDB

| | PostGIS | DuckDB |
|---|---|---|
| **Architecture** | Client-server | In-process |
| **Workload** | OLTP — transactional | OLAP — analytical |
| **Setup** | Server required | No setup |
| **GeoServer-ready** | Yes | No |
| **Concurrent writes** | Yes | No |
| **File formats** | PostgreSQL tables | GeoParquet, GeoPackage, CSV, GeoJSON, etc. |
| **Best for** | Live data, web map backends, multi-user | Analysis, data exploration, big data |

Note: These tools often work together. PostGIS holds the live operational data; analysts periodically export to GeoParquet for DuckDB analysis. One does not replace the other — they cover different parts of the workflow.

---

## GeoParquet: cloud-native + columnar

**OGC GeoParquet** — Apache Parquet extended with spatial metadata.

- Geometry stored as **WKB** in a binary Parquet column
- File footer carries **CRS, geometry types, and bounding box** per column
- Bounding box enables **partition pruning** — queries skip non-intersecting file sections

| Format | Storage | Max size | Cloud-native | Query without server |
|---|---|---|---|---|
| Shapefile | Row, 7 files | 2 GB | No | Yes |
| GeoPackage | SQLite row | Depends | No | Yes |
| PostGIS table | Row, server | No hard limit | Via container | No |
| **GeoParquet** | **Columnar, 1 file** | **Hundreds of millions** | **Yes** | **Yes** |

</div>

Note: GeoParquet is the export / archive / sharing format — not a PostGIS replacement. Typical workflow: PostGIS holds live data → export to GeoParquet for analyst use or public distribution → partners query with DuckDB without needing a PostGIS connection.

---

## Apache Sedona: distributed spatial SQL

Extends Apache Spark with spatial data types and functions — the same vocabulary (`ST_DWithin`, `ST_Distance`), executed across a cluster.

```python
from sedona.spark import SedonaContext

sedona = SedonaContext.create(spark)

farms = sedona.read.format("geoparquet").load("s3://psa-data/palay/")
roads = sedona.read.format("geoparquet").load("s3://psa-data/roads/")

farms.createOrReplaceTempView("farms")
roads.createOrReplaceTempView("roads")

result = sedona.sql("""
  SELECT f.farm_id, f.province,
         ST_Distance(f.geom, r.geom) AS dist_m
  FROM farms f, roads r
  WHERE ST_DWithin(f.geom, r.geom, 10000)
""")
result.write.format("geoparquet").save("s3://psa-data/output/")
```

Note: Ask the room: "Does AFCD need this today?" Expected answer: no. Then reframe: "When would you need it? Think about a national crop monitoring program pulling from Sentinel-2 every 10 days — 7,000 tiles per pass, multiple years. That is when you reach for Sedona."

---

## the three-tier storage hierarchy

| Tier | Tool | When to use |
|---|---|---|
| **Operational** | PostGIS | Field data, web map backend, daily edits, multi-user |
| **Analytical** | DuckDB + GeoParquet | Census analysis, reports, ad-hoc queries, single analyst |
| **Archival/BigData/AI/ML** | Data lake + Apache Sedona | National datasets, satellite products, distributed processing |

**Three decision questions:**

1. Are multiple users writing to this data? → **PostGIS**
2. Large static dataset for analytical queries? → **DuckDB / GeoParquet**
3. Distributed processing at national scale? → **Data Lake + Apache Sedona**

Note: This is the framework participants should leave the lecture with. Most AFCD workflows live in the first two tiers. The data lake tier is context — for when datasets outgrow a single machine or when AFCD interfaces with national PSA data infrastructure.

---

## the migration path

| ArcGIS Online | Open Replacement | How |
|---|---|---|
| Hosted feature layers | **PostGIS** tables | Export → `ogr2ogr` → PostGIS |
| WMS / WFS services | **GeoServer** | Publish PostGIS table (Day 2) |
| Portal / catalog | **GeoNode** | Register GeoServer layer (Day 3) |
| AGOL dashboards | **Custom web app** | MapLibre + GeoServer API (Days 4–5) |
| Cloud hosting | **Docker stack** | `docker compose up` (all week) |

ArcGIS Online Feature Layer = **PostGIS table + GeoServer WFS endpoint**.

Note: Return to the opening question here — "how does a field officer update reach the web map?" The answer is now visible: field data → PostGIS → GeoServer → web map. This is also the first time the full week arc becomes visible to participants.

---

<img src="../static/img/bnhr-2024-fordarkbg-main-720.png" class="title-logo" alt="BNHR">

# demo

---

<img src="../static/img/bnhr-2024-fordarkbg-main-720.png" class="title-logo" alt="BNHR">

# workshop

---

<img src="../static/img/bnhr-2024-fordarkbg-main-720.png" class="title-logo" alt="BNHR">

<h1 style="color: #ffa500">Questions?</h1>