← Back to Home

Government Project 2

Geospatial Analytics Backend

The backend of a provincial government platform for tracking village development: a spatial data API that ingests the multi-indicator Village Development Index (IDM) from bulk spreadsheets, serves village profiles, the IDM score breakdown, and village enterprises statistics, and publishes Mapbox Vector Tile map layers from PostGIS. (Built as a government client project; my role was backend engineering.)

#Geospatial / GIS#PostGIS#Mapbox Vector Tiles (MVT)#FastAPI#Async PostgreSQL#Data Ingestion (Excel/IDM)#Public-Sector Analytics#JWT Auth#Backend API

// Overview

What it does (backend). The platform lets a provincial village-empowerment authority monitor how each village is developing. The backend powers that: it serves village profiles (administrative hierarchy down to province → regency → district → village, plus location and head-of-village data), the IDM — Village Development Index with its component sub-indices (social, economic, ecological, etc.) and the resulting status (Tertinggal / Berkembang / …), Village Enterprise statistics by regency and year, and interactive map layers of the region. Bulk IDM data arrives as per-village Excel files and is ingested into the database for query and ranking.

Background

Village-development monitoring is data-heavy, spatial, and multi-year:

  • IDM lives in spreadsheets. The index ships as many per-village Excel files per year; it had to be ingested into a queryable, rankable store instead of sitting as loose files.
  • Many indicators, one index. IDM aggregates several sub-indices into a status; the backend had to expose both the headline score/status and the indicator breakdown.
  • Inherently geographic. Villages, districts, and regencies are map features; users expect to pan/zoom a regional map, which needs efficient vector tiles, not heavy GeoJSON dumps.
  • Comparisons need flexible queries. Ranking and comparing villages by year, region, and indicator demanded safe dynamic sorting/filtering over large tables.
  • Multiple data domains. Village identity, IDM, Village Enterprise, and downloadable artifacts all had to share one backend and auth.

Solution

  1. Async API on PostGIS. A FastAPI + asyncpg service over PostgreSQL/PostGIS exposes the platform's data with the database doing the spatial work.
  2. Vector-tile layers. A reusable MVT builder renders z/x/y tiles per layer directly from PostGIS (ST_AsMVT/ST_AsMVTGeom/ST_TileEnvelope, Find_SRID, reprojection to 3857) for fast regional maps.
  3. Village profiles. Endpoints return the full administrative hierarchy (province → regency → district → village), coordinates, and village-head info.
  4. IDM domain. Indicator tables (social/education, economy, demographics, ecology, settlement, social capital, health) keyed by village + year, with filterable, sortable, paginated query endpoints for scores, status, and per-indicator breakdowns — inputs guarded by regex constraints for safe dynamic SQL.
  5. Village Enterprise analytics. Aggregated village-enterprise statistics by regency and by year.
  6. IDM ingestion. Bulk per-village Excel files are parsed and loaded into the IDM tables.
  7. Auth & downloads. JWT auth with per-user access scopes, plus file/download endpoints.

Impact

  • Spreadsheets became a queryable index. Hundreds of per-village Excel files turned into a structured, rankable IDM store.
  • Headline + breakdown. Consumers get both the IDM status and the underlying sub-indices from one API.
  • Fast regional maps. Database-generated vector tiles keep the map responsive across thousands of village features.
  • Flexible, safe comparisons. Sort/filter/paginate across years, regions, and indicators with validated inputs.
  • One backbone, many domains. Village identity, IDM, Village Enterprise, and downloads share a single Postgres and auth layer.

// Tech Stack

LayerTechnologies
APIFastAPI, Uvicorn, Pydantic, CORS, session middleware
DatabasePostgreSQL + PostGIS, asyncpg async pool
Geospatialreusable MVT builder — ST_AsMVT / ST_AsMVTGeom / ST_TileEnvelope, Find_SRID, reprojection (3857)
Data ingestionper-village Excel (.xls) IDM parsing/loading
AuthJWT (PyJWT / python-jose), passlib, per-user access scopes
ValidationPydantic + regex constr guards for safe dynamic ordering/filtering
Filesaiofiles, multipart, download endpoints
InfraDocker / docker-compose, GitHub Actions CI/CD

// Architecture & Diagrams

1 / 3System Architecture — Simple (high level)
rendering…

Disclaimer: sample visuals may contain anonymized, simulated, or non-production values for presentation purposes.

// Demo

No demo configured yet.