convez.ai
Agente
MatheusConstantino

poc-geoserver-sqlserver

MatheusConstantino/poc-geoserver-sqlserver

Ver no GitHub
InicialPluginScore · de 0 a 100

Portfolio POC: GeoServer + SQL Server 2022 + PostGIS spatial engineering, AI-powered analysis (Claude API), multi-agent Claude Code workflow, spec-driven development

Instalação

$npx convez-ai MatheusConstantino/poc-geoserver-sqlserver

1 comando no terminal: detecta o formato do repositório (marketplace ou plugin avulso) e instala via Claude Code. Requer Node 18+ e o Claude Code instalado.

Métricas

Estrelas
0
Forks
0
Acompanhando
0
Saúde
57%
Último commit
há 5 meses
Versão
v0.5.4
Issues abertas
0
Contribuidores
1

README

A tradução para o português deste README ainda está na fila.

POC GeoServer + SQL Server

CI License: MIT Docker Node.js Release

Educational POC demonstrating production-grade geospatial engineering: GeoServer, SQL Server 2022, PostGIS 3.4, spatial validation, AI-powered analysis, and benchmark-driven decision making — all containerized and reproducible.


Architecture

graph TB
    Client["Client / Browser"]

    subgraph Docker["Docker Compose Stack"]
        API["Node.js API\n(Fastify + TypeScript)\n:3000"]
        GEO["GeoServer 2.25\n(WMS / WFS)\n:8080"]
        SQL["SQL Server 2022\n(Primary spatial DB)\n:1433"]
        PG["PostgreSQL 16\n+ PostGIS 3.4\n:5432"]
        AI["Claude API\n(Anthropic)\nAI Analysis Layer"]
    end

    Client -->|"REST"| API
    Client -->|"WMS/WFS"| GEO
    API -->|"mssql"| SQL
    API -->|"pg"| PG
    API -->|"HTTPS"| AI
    GEO -->|"JDBC"| SQL

Quickstart

# 1. Clone
git clone https://github.com/MatheusConstantino/poc-geoserver-sqlserver.git
cd poc-geoserver-sqlserver

# 2. Configure environment
cp .env.example .env
# Edit .env — set SA_PASSWORD and ANTHROPIC_API_KEY

# 3. Start the full stack (first run pulls images + seeds data)
docker compose up --build

# 4. Run spatial inconsistency validation
curl http://localhost:3000/inconsistencias

# 5. Run all 15 benchmark scenarios
curl -X POST http://localhost:3000/benchmark/run

GeoServer Layer Preview: http://localhost:8080/geoserver/web → Layer Preview


REST API

MethodEndpointDescription
GET/healthStack health — SQL Server + PostGIS ping, latency, version. Returns 200 or 503.
GET/inconsistenciasSpatial validation results (VR-001–005). Query params: rule, limit, include_samples.
POST/benchmark/runRun up to 15 spatial benchmark scenarios. Body: { scenarios, databases, options }.
POST/ai/analyze-inconsistenciasClaude API analysis of validation results (Sprint 4.5)
POST/ai/benchmark-insightsClaude API interpretation of benchmark results (Sprint 4.5)
POST/ai/query-suggestionsClaude API spatial query optimisation suggestions (Sprint 4.5)

Full contract: specs/02-api-spec.md · PT-BR reference: docs/04-api.md


What This POC Demonstrates

Geospatial Engineering

  • Spatial indexing strategies (R-tree / GIST) and their performance implications at different volumetries
  • Geographic inconsistency detection using SQL spatial functions (STIntersects, STDistance, STIsValid)
  • GeoServer WMS/WFS configuration including resolution of the undocumented bbox bug with SQL Server JDBC (see docs/03-setup-geoserver.md)
  • Schema design for 50k+ points, 100k+ lines, and 2k polygons with spatial indexes

AI-Driven Engineering

  • Claude API integration for intelligent spatial anomaly analysis (structured JSON output)
  • Prompt engineering with typed schemas — production-ready, not just demos
  • AI-assisted benchmark interpretation and query optimization suggestions
  • Automated PR review via GitHub Actions using Claude API
  • Full .claude/ setup: multi-agent workflow (PO, QA, Infra, Reviewer, GIS Expert)

Architecture & Decision-Making

  • 5 Architecture Decision Records (ADRs) documenting non-obvious tradeoffs
  • Volumetry analysis: how design decisions evolve from 50k to 50M features
  • Cost modeling: when open-source beats proprietary at scale
  • Spec-driven development: every feature starts with a written spec

Observability & Benchmarking

  • 15 parameterized benchmark scenarios with p50/p95/p99 and throughput metrics
  • Side-by-side SQL Server vs PostGIS comparison on identical datasets
  • AI-generated insights layered on raw benchmark data

Benchmark Results

Full methodology: docs/05-benchmark.md

The table below shows real measurements from a GeoServer WFS comparison on equivalent volumetry (50k points / 100k lines / 2k polygons). Numbers marked with ² include ~50–100 ms of network overhead. Run cd benchmark && npm run run with the stack up to get precise direct-DB numbers for all 15 scenarios.

IDScenarioSQL Server p95PostGIS p95¹Winner
B01Bbox dense region (points)737 ms~650 msTIE (~1.5 %)
B02Bbox sparse region (points)578 ms~600 msTIE (~4 %)
B03Bbox medium region (lines)710 ms~724 msSQL Server
B04Bbox full extent (polygons)709 ms~603 msPostGIS²
B09Mixed filter (attribute + spatial)818 ms~818 msTIE
B14Cross-schema JOIN (validation)561 msN/ASQL Server
B15Full validation summary view580 msN/ASQL Server

¹ PostGIS ran on a remote DEV server; deduct ~100 ms network overhead for an apples-to-apples comparison — results converge to TIE. ² With network overhead removed, B04 is also effectively a TIE.

Overall verdict: SQL Server and PostGIS are equivalent for this workload at 50k–100k features. Scenarios B05–B08 / B10–B13 (spatial JOINs, KNN, self-join) require a live stack — run the benchmark runner to fill them in. See docs/05-benchmark.md for the full comparative analysis and validated hypotheses.


Project Structure

poc-geoserver-sqlserver/
├── .claude/                 # Claude Code: agents, commands, hooks
│   ├── CLAUDE.md            # Project context loaded in every AI session
│   ├── agents/              # Role-based AI personas (PO, QA, Infra, ...)
│   └── commands/            # Slash commands (/project:review, /project:deploy, ...)
├── .github/                 # GitHub Actions, PR template, issue templates
├── specs/                   # All specifications (written before implementation)
├── docs/
│   ├── adr/                 # Architecture Decision Records
│   └── *.md                 # Detailed technical docs (PT-BR)
├── sqlserver/               # SQL Server schema, seed scripts, views
├── postgis/                 # PostGIS equivalent schemas
├── geoserver/data_dir/      # GeoServer workspace pre-configuration
├── api/                     # Node.js 20 / Fastify API + AI layer
├── benchmark/               # Scenarios, runner, results
├── docker-compose.yml
└── .env.example

AI-Driven Development Workflow

This project uses Claude Code with a custom multi-agent setup simulating a real engineering team. Each agent has domain-specific instructions and is invoked at the right moment in the workflow.

AgentRoleTriggered When
PO AgentScope, acceptance criteria, issue trackingSprint planning, spec review
QA AgentEdge cases, idempotency, API contractsBefore any PR merge
Infra AgentDocker, CI/CD, secrets, health checksInfrastructure changes
Reviewer AgentSecurity, performance, conventionsEvery code PR
GIS ExpertSpatial correctness, SRID, GeoServerSpatial SQL, config changes
/project:review          # Full code review before PR
/project:deploy          # Interactive deploy checklist
/project:benchmark       # Run all 15 benchmark scenarios
/project:validate        # Spatial inconsistency report + AI analysis
/project:new-adr         # Scaffold a new Architecture Decision Record
/project:sprint-status   # Check progress against open GitHub issues
/project:qa              # Full QA checklist before release

Documentation

Specs (written before implementation)

DocDescription
specs/00-prd.mdProduct Requirements Document
specs/01-technical-spec.mdTechnical architecture spec
specs/02-api-spec.mdREST API contract
specs/03-data-model-spec.mdSchema + index design
specs/04-validation-rules-spec.mdVR-001–005 spatial rules
specs/05-benchmark-spec.md15 benchmark scenarios
specs/06-ai-integration-spec.mdClaude API endpoints
specs/07-infrastructure-spec.mdDocker, CI, health checks

Architecture Decision Records

ADRDecision
ADR-001Why both SQL Server and PostGIS?
ADR-002GeoServer vs direct API geometry serving
ADR-003GEOMETRY_AUTO_GRID vs GIST indexing
ADR-004Volumetry roadmap 50k → 50M features
ADR-005AI at two levels: product + engineering process

Technical Docs (PT-BR)

DocDescription
docs/01-arquitetura.mdSystem architecture — layers, data flow, design decisions
docs/03-setup-geoserver.mdGeoServer setup + bbox bug fix
docs/04-api.mdREST API reference — all endpoints, examples, error codes
docs/05-benchmark.mdBenchmark methodology + how to generate results
docs/06-ia.mdAI integration — prompts, Zod schemas, error handling

Origin

This is an educational POC inspired by real-world geospatial engineering challenges encountered when working with utility network data. All data is 100% synthetic — no proprietary or client data is included. The project exists to demonstrate technical competencies, document real gotchas (like the GeoServer bbox bug), and serve as a reference for teams evaluating SQL Server vs PostGIS for spatial workloads.


License

MIT — free to use, fork, and learn from.

Ver o original no GitHub

Bom para

Relacionados