decision-labs/geobase-skills/geobase-project-db-data-import

Project DB: Local Data Import

When to use this skill

• Load a local file into the Geobase project database
• GeoParquet, GeoJSON, GPKG, Shapefile, CSV, Parquet → Postgres
• Needs DATABASE_URI in the user's local environment (human in the loop — e.g. gitignored .env.db, direnv, or exported shell vars; not in chat)
• Not for OSM worker import (@geobase-worker-osm-import) or Overture Maps (@geobase-worker-overture-import)

Required Inputs

• @geobase → Private beta (CLI not shipped) if geobase-cli is unavailable
• authenticated CLI session (geobase-cli login) when CLI is installed
• project_ref
• local file path (for example .parquet, .geojson, .gpkg, .shp, .csv)
• target table name

Secrets (human in the loop)

DATABASE_URI / GEOBASE_DATABASE_URI and the DB password are not available to agents or geobase-cli in usable form. projects env --persona postgres prints placeholders only.

Before import:

1. Run geobase-cli projects env <project-ref> --persona postgres --format dotenv for host, port, database, user (non-secret).
2. Ask the user to set the real DB password and a complete DATABASE_URI / GEOBASE_DATABASE_URI locally (replace [PASSWORD] / <db-password>). Common patterns: gitignored `.env.db`, direnv, or exporting vars in the shell — whatever they already use.
3. User loads secrets in their environment; do not ask them to paste the password or URI in chat.

See @geobase → Secrets (human in the loop).

Core Rule

Use ogr2ogr as the default import tool for geospatial vector datasets. For non-geospatial tabular files, use COPY/\copy or Python loaders.

Procedure

1. Validate auth/session and project:

• geobase-cli whoami
• geobase-cli projects refs
• geobase-cli projects env <project-ref> --persona postgres --format dotenv

1. Source DB credentials from the user's local environment (after they configure it):

• non-secret fields may come from projects env: GEOBASE_PGHOST, GEOBASE_PGPORT, GEOBASE_PGDATABASE, GEOBASE_PGUSER
• password and DATABASE_URI / GEOBASE_DATABASE_URI must come from the user's side only — never from CLI placeholders or chat

1. Validate target table name:

• keep it short;
• use letters/digits/underscores only;
• max length 63.
• strict preflight (required): verify target table does not already exist.

Example SQL check: SELECT to_regclass(format('public.%I', '<target_table>')) IS NULL AS table_available;

1. Inspect local file first:

• geospatial: ogrinfo "<path>"
• parquet tabular: inspect schema with duckdb/Python as needed

1. Decide import path:

• geospatial vector data -> ogr2ogr
• plain tabular data -> \copy / Python

1. Import geospatial vector data (recommended path):

PGPASSWORD="$GEOBASE_PGPASSWORD" ogr2ogr \
  -f PostgreSQL \
  PG:"host=$GEOBASE_PGHOST port=$GEOBASE_PGPORT dbname=$GEOBASE_PGDATABASE user=$GEOBASE_PGUSER sslmode=require" \
  "<input_file>" \
  -nln public.<target_table> \
  -lco GEOMETRY_NAME=geom \
  -nlt PROMOTE_TO_MULTI

Note: ogr2ogr automatically creates a spatial index on the geometry column during import.

1. Optional import modes:

• replace table: add -overwrite
• append rows: add -append
• reproject: add -t_srs EPSG:4326 (or project target SRID)

1. Validate import:

• row count query
• geometry validity and SRID checks
• sample data query

Failure Handling

• If auth fails: run geobase-cli login, then retry.
• If project/db connection fails: re-check postgres env output and credentials.
• If geometry import fails:
• run ogrinfo again to confirm layer/geometry types;
• try -nlt PROMOTE_TO_MULTI;
• set explicit -t_srs if CRS is missing/mismatched.
• If table name errors occur: shorten/sanitize table name.

Security Rules

• Never paste DB passwords in chat.
• Never commit secrets/connection strings to repo files.
• Prefer environment variables or secure local credential storage.