Add Planet initial database schema #114

Merged

ned merged 1 commit from feature/planet-schema into main

2026-07-15 19:25:08 +00:00

ned commented

2026-07-15 19:24:03 +00:00

Member

Closes #113

Implements the planet database schema for tracking purchased Planet Labs satellite imagery.

Tables

Table	Purpose
`planet.item`	Individual satellite image scenes with full metadata, PostGIS geometry, cloud/quality metrics, sun/sensor angles, band counts, and available assets
`planet.sensor`	Normalized sensor/satellite platform catalog
`planet.item_asset`	Per-item purchased asset tracking (download URLs, status)
`planet.order`	Planet Orders API purchase records
`planet.order_item`	Junction linking items to the orders they were acquired through

Key design decisions

PostGIS geometry (geometry(Polygon,4326)) for spatial queries — matches the existing municipal.parcel pattern
Computed columns (capture_date, capture_hour) for common temporal access patterns without application overhead
TEXT arrays for available_assets and permissions — PostgreSQL native array operations are more efficient than JSONB for simple string lists
Sun/sensor angles (sun_azimuth, sun_elevation, view_angle, satellite_azimuth) captured per the nidus-reveal design doc requirements for lighting normalization between satellite passes
JSONB for delivery, hosting, error_hints on orders — these are configuration blobs that don't need to be individually queryable

Nidus Reveal alignment

All metadata fields specified in the nidus-reveal design doc are present on planet.item: solar_azimuth, solar_elevation, off_nadir_angle (view_angle), and cloud_cover. The geometry and GSD fields support the high-res (~0.3m) and medium-res (~3m) layer classification.

Code generation

db/bobgen.yaml updated to include the planet schema. After migration, run db/jetgen.sh to produce Go types in db/gen/nidus-sync/planet/.

Closes #113 Implements the `planet` database schema for tracking purchased Planet Labs satellite imagery. ### Tables | Table | Purpose | |---|---| | `planet.item` | Individual satellite image scenes with full metadata, PostGIS geometry, cloud/quality metrics, sun/sensor angles, band counts, and available assets | | `planet.sensor` | Normalized sensor/satellite platform catalog | | `planet.item_asset` | Per-item purchased asset tracking (download URLs, status) | | `planet.order` | Planet Orders API purchase records | | `planet.order_item` | Junction linking items to the orders they were acquired through | ### Key design decisions - **PostGIS geometry** (`geometry(Polygon,4326)`) for spatial queries — matches the existing `municipal.parcel` pattern - **Computed columns** (`capture_date`, `capture_hour`) for common temporal access patterns without application overhead - **TEXT arrays** for `available_assets` and `permissions` — PostgreSQL native array operations are more efficient than JSONB for simple string lists - **Sun/sensor angles** (`sun_azimuth`, `sun_elevation`, `view_angle`, `satellite_azimuth`) captured per the nidus-reveal design doc requirements for lighting normalization between satellite passes - **JSONB** for `delivery`, `hosting`, `error_hints` on orders — these are configuration blobs that don't need to be individually queryable ### Nidus Reveal alignment All metadata fields specified in the nidus-reveal design doc are present on `planet.item`: solar_azimuth, solar_elevation, off_nadir_angle (view_angle), and cloud_cover. The geometry and GSD fields support the high-res (~0.3m) and medium-res (~3m) layer classification. ### Code generation `db/bobgen.yaml` updated to include the `planet` schema. After migration, run `db/jetgen.sh` to produce Go types in `db/gen/nidus-sync/planet/`.

ned added 1 commit

2026-07-15 19:24:03 +00:00

Add initial planet database schema

/ golint (push) Successful in 1m5s

Details

/ pnpm-build (push) Successful in 30s

Details

36735ad7e4

Create the planet schema with tables for satellite image metadata
from Planet Labs imagery purchases. The schema mirrors the data
structures available from the Planet Data API and Orders API,
as documented in the go-planet submodule.

Tables:
- planet.sensor: Normalized catalog of satellite/sensor platforms
- planet.item: Individual satellite image scenes with full
  metadata including PostGIS geometry, acquisition timestamps,
  cloud/quality metrics, sun/sensor angles, band counts, and
  available asset types
- planet.item_asset: Per-item asset tracking (purchased asset
  types, download URLs, status)
- planet.order: Order records from the Planet Orders API
- planet.order_item: Junction table linking items to the orders
  they were acquired through

Key design decisions:
- Store geometry as geometry(Polygon,4326) for spatial queries
- Use generated columns (capture_date, capture_hour) for
  common temporal access patterns
- Store available_assets and permissions as TEXT arrays
  for efficient PostgreSQL array operations
- Capture sun/sensor angles needed by nidus-reveal for
  lighting normalization between passes

Updates db/bobgen.yaml to include planet in code generation.

ned referenced this pull request

2026-07-15 19:24:10 +00:00

Planet integration: Initial database schema #113

ned merged commit 72fb83f25a into main

2026-07-15 19:25:08 +00:00

ned referenced this pull request from a commit

2026-07-15 19:25:09 +00:00

Add Planet initial database schema