ArchiTools/geoportal/skill-pmtiles-pipeline.md

# Skill: PMTiles Generation Pipeline from PostGIS

## When to Use

When you need to pre-generate vector tiles from PostGIS data for fast static serving. Ideal for overview/boundary layers that change infrequently, serving from S3/MinIO/CDN without a tile server, or eliminating database load for tile serving.

---

## Complete Pipeline

### Prerequisites

| Tool | Purpose | Install |
|---|---|---|
| ogr2ogr (GDAL) | PostGIS export + reprojection | `apt install gdal-bin` or Docker |
| tippecanoe | MVT tile generation → PMTiles | `ghcr.io/felt/tippecanoe` Docker image |
| mc (MinIO client) | Upload to MinIO/S3 | `brew install minio/stable/mc` |

### Step 1: Export from PostGIS

```bash
# Single layer — FlatGeobuf is fastest for tippecanoe input
ogr2ogr -f FlatGeobuf \
  -s_srs EPSG:3844 \        # source SRID (your data)
  -t_srs EPSG:4326 \        # tippecanoe REQUIRES WGS84
  parcels.fgb \
  "PG:host=10.10.10.166 dbname=mydb user=myuser password=mypass" \
  -sql "SELECT id, name, area, geom FROM my_view WHERE geom IS NOT NULL"

# Multiple layers in parallel
ogr2ogr -f FlatGeobuf -s_srs EPSG:3844 -t_srs EPSG:4326 \
  parcels.fgb "PG:..." -sql "SELECT ... FROM gis_terenuri" &
ogr2ogr -f FlatGeobuf -s_srs EPSG:3844 -t_srs EPSG:4326 \
  buildings.fgb "PG:..." -sql "SELECT ... FROM gis_cladiri" &
ogr2ogr -f FlatGeobuf -s_srs EPSG:3844 -t_srs EPSG:4326 \
  uats.fgb "PG:..." -sql "SELECT ... FROM gis_uats_z12" &
wait
```

**Why FlatGeobuf over GeoJSON:**
- Binary columnar format — tippecanoe reads it 3-5x faster
- No JSON parsing overhead
- Streaming read (no need to load entire file in memory)
- tippecanoe native support since v2.17+

### Step 2: Generate PMTiles with tippecanoe

```bash
# Single layer
tippecanoe \
  -o parcels.pmtiles \
  --name="Parcels" \
  --layer="parcels" \
  --minimum-zoom=6 \
  --maximum-zoom=15 \
  --base-zoom=15 \
  --drop-densest-as-needed \
  --extend-zooms-if-still-dropping \
  --detect-shared-borders \
  --simplification=10 \
  --hilbert \
  --force \
  parcels.fgb

# Multi-layer (combined file)
tippecanoe \
  -o combined.pmtiles \
  --named-layer=parcels:parcels.fgb \
  --named-layer=buildings:buildings.fgb \
  --named-layer=uats:uats.fgb \
  --minimum-zoom=0 \
  --maximum-zoom=15 \
  --drop-densest-as-needed \
  --detect-shared-borders \
  --hilbert \
  --force
```

#### Key tippecanoe Flags

| Flag | Purpose | When to Use |
|---|---|---|
| `--minimum-zoom=N` | Lowest zoom level | Always set |
| `--maximum-zoom=N` | Highest zoom level (full detail) | Always set |
| `--base-zoom=N` | Zoom where ALL features kept (no dropping) | Set to max-zoom |
| `--drop-densest-as-needed` | Drop features in dense areas at low zoom | Large polygon datasets |
| `--extend-zooms-if-still-dropping` | Auto-increase max zoom if needed | Safety net |
| `--detect-shared-borders` | Prevent gaps between adjacent polygons | **Critical for parcels/admin boundaries** |
| `--coalesce-densest-as-needed` | Merge small features at low zoom | Building footprints |
| `--simplification=N` | Pixel tolerance for geometry simplification | Reduce tile size at low zoom |
| `--hilbert` | Hilbert curve ordering | Better compression, always use |
| `-y col1 -y col2` | Include ONLY these properties | Reduce tile size |
| `-x col1 -x col2` | Exclude these properties | Remove large/unnecessary fields |
| `--force` | Overwrite existing output | Scripts |
| `--no-feature-limit` | No limit per tile | When density matters |
| `--no-tile-size-limit` | No tile byte limit | When completeness matters |

#### Property Control

```bash
# Include only specific properties (whitelist)
tippecanoe -o out.pmtiles -y name -y area -y type parcels.fgb

# Exclude specific properties (blacklist)
tippecanoe -o out.pmtiles -x raw_json -x internal_id parcels.fgb

# Zoom-dependent properties (different attributes per zoom)
# Use tippecanoe-json format with per-feature "tippecanoe" key
```

### Step 3: Upload to MinIO (Atomic Swap)

```bash
# Upload to temp name first
mc cp combined.pmtiles myminio/tiles/combined_new.pmtiles

# Atomic rename (zero-downtime swap)
mc mv myminio/tiles/combined_new.pmtiles myminio/tiles/combined.pmtiles
```

### Step 4: MinIO CORS Configuration

```bash
# Required for browser-direct Range Requests
mc admin config set myminio api cors_allow_origin="https://tools.beletage.ro"

# Or bucket policy for public read
mc anonymous set download myminio/tiles
```

MinIO CORS must expose Range/Content-Range headers:
```json
{
  "CORSRules": [{
    "AllowedOrigins": ["https://your-domain.com"],
    "AllowedMethods": ["GET", "HEAD"],
    "AllowedHeaders": ["Range", "If-None-Match"],
    "ExposeHeaders": ["Content-Range", "Content-Length", "ETag"],
    "MaxAgeSeconds": 3600
  }]
}
```

---

## MapLibre GL JS Integration

```bash
npm install pmtiles
```

```typescript
import maplibregl from 'maplibre-gl';
import { Protocol } from 'pmtiles';

// Register ONCE at app initialization
const protocol = new Protocol();
maplibregl.addProtocol('pmtiles', protocol.tile);

// Add source to map
map.addSource('my-tiles', {
  type: 'vector',
  url: 'pmtiles://https://minio.example.com/tiles/combined.pmtiles',
});

// Add layers
map.addLayer({
  id: 'parcels-fill',
  type: 'fill',
  source: 'my-tiles',
  'source-layer': 'parcels',  // layer name from tippecanoe --layer or --named-layer
  minzoom: 10,
  maxzoom: 16,
  paint: { 'fill-color': '#22c55e', 'fill-opacity': 0.15 },
});

// Cleanup on unmount
maplibregl.removeProtocol('pmtiles');
```

---

## Hybrid Architecture (PMTiles + Live Tile Server)

```
Zoom 0-14:  PMTiles from MinIO (pre-generated, ~5ms, zero DB load)
Zoom 14+:   Martin from PostGIS (live, always-current, ~50-200ms)
```

```typescript
// PMTiles for overview
map.addSource('overview', {
  type: 'vector',
  url: 'pmtiles://https://minio/tiles/overview.pmtiles',
});

// Martin for detail
map.addSource('detail', {
  type: 'vector',
  tiles: ['https://tiles.example.com/{source}/{z}/{x}/{y}'],
  minzoom: 14,
  maxzoom: 18,
});

// Layers with zoom handoff
map.addLayer({
  id: 'parcels-overview', source: 'overview', 'source-layer': 'parcels',
  minzoom: 6, maxzoom: 14,  // PMTiles handles low zoom
  ...
});
map.addLayer({
  id: 'parcels-detail', source: 'detail', 'source-layer': 'gis_terenuri',
  minzoom: 14,  // Martin handles high zoom
  ...
});
```

---

## Rebuild Strategies

### Nightly Cron

```bash
# crontab -e
0 2 * * * /opt/scripts/rebuild-tiles.sh >> /var/log/tile-rebuild.log 2>&1
```

### After Data Sync (webhook/API trigger)

```bash
# Call from sync completion handler
curl -X POST http://n8n:5678/webhook/rebuild-tiles
```

### Partial Rebuild (single layer update)

```bash
# Rebuild just parcels, then merge with existing layers
tippecanoe -o parcels_new.pmtiles ... parcels.fgb
tile-join -o combined_new.pmtiles --force \
  parcels_new.pmtiles \
  buildings_existing.pmtiles \
  uats_existing.pmtiles
mc cp combined_new.pmtiles myminio/tiles/combined.pmtiles
```

---

## Build Time Estimates

| Features | Type | Zoom Range | Time | Output Size |
|---|---|---|---|---|
| 500 | Polygons (UAT) | z0-z12 | <5s | 10-30 MB |
| 100K | Polygons (buildings) | z12-z15 | 30-90s | 100-200 MB |
| 330K | Polygons (parcels) | z6-z15 | 2-5 min | 200-400 MB |
| 1M | Polygons (mixed) | z0-z15 | 8-15 min | 500 MB-1 GB |

tippecanoe is highly optimized and uses parallel processing.

---

## Common Pitfalls

1. **tippecanoe only accepts WGS84 (EPSG:4326)** — always reproject with ogr2ogr first
2. **`--detect-shared-borders` is critical for parcels** — without it, gaps appear between adjacent polygons
3. **GeoJSON input is slow** — use FlatGeobuf for 3-5x faster reads
4. **No incremental updates** — must rebuild entire file (use `tile-join` for layer-level replacement)
5. **MinIO needs CORS for browser-direct access** — Range + Content-Range headers must be exposed
6. **Large properties bloat tile size** — use `-y`/`-x` flags to control what goes into tiles
7. **`--no-tile-size-limit` can produce huge tiles** — use with `--drop-densest-as-needed` safety valve
8. **Atomic upload prevents serving partial files** — always upload as temp name then rename