Smart Retail Siting & Data Engine

A production-grade, local implementation of an AI-powered retail location intelligence system. The system runs completely locally with zero cloud billing dependencies, using open-source infrastructure to simulate enterprise spatial data capabilities.

🏗️ Architecture

Technical Stack

• Backend: Python 3.11+ with FastAPI (asynchronous execution)

• AI Orchestration: LangChain for LLM integration

• Database: SQLite with SpatiaLite extension for spatial operations

• MCP Server: Local Model Context Protocol implementation for database tool contracts

• LLM Core: Ollama (local) or Google Gemini API (free tier)

• Frontend: Responsive dark-mode dashboard with Tailwind CSS and Chart.js

• Real-time: WebSocket/SSE for live log streaming

Core Components

1. Local Database & Seed Pipeline

• SQLite database with SpatiaLite extension for geospatial operations

• Automated ingestion pipeline for mock spatial datasets

• Tables: transit stations, commerce points, storefront locations

• Spatial queries using Haversine formula for distance calculations

2. Local MCP Server

• get_schema: Returns database schema and table structures

• execute_query: Executes SQL queries with security validation

• get_spatial_examples: Provides example spatial analysis queries

• get_table_schema: Detailed schema for specific tables

3. Agentic Execution Controller

Four-phase execution loop:

• Phase A: Schema inspection and database learning

• Phase B: LLM generates optimized SQL queries

• Phase C: Query execution with raw data capture

• Phase D: LLM generates strategic business insights

4. Real-time Frontend

• Live WebSocket connection for streaming logs

• Interactive dashboard with phase progress tracking

• Chart.js visualization of spatial analysis results

• Database statistics and monitoring

Related MCP server: PostGIS Yukon MCP

🚀 Quick Start

Prerequisites

• Python 3.11 or higher

• Ollama (recommended) or Google Gemini API key

• Git

Installation

Windows

# Run the setup script
setup.bat

# Or manually
python -m venv venv
venv\Scripts\activate
pip install -r requirements.txt
python run.py setup

Linux/Mac

# Make setup script executable
chmod +x setup.sh

# Run setup
./setup.sh

# Or manually
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
python3 run.py setup

Configuration

Edit the .env file to configure your settings:

# LLM Configuration
LLM_PROVIDER=OLLAMA  # or GEMINI

# Ollama Configuration (recommended)
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3

# Google Gemini Configuration (alternative)
GEMINI_API_KEY=your_api_key_here
GEMINI_MODEL=gemini-pro

# Database Configuration
DATABASE_PATH=data/retail_siting.db

# Server Configuration
HOST=0.0.0.0
PORT=8000
LOG_LEVEL=INFO

Running Ollama (Recommended)

1. Install Ollama from ollama.ai

2. Start the Ollama service:

   ollama serve

3. Pull the model:

   ollama pull llama3

Starting the Application

# Development mode (setup + run)
python run.py dev

# Or run directly
python run.py run

The application will be available at:

• Backend API: http://localhost:8000

• Frontend Dashboard: http://localhost:8000/static/index.html

• API Documentation: http://localhost:8000/docs

📊 Usage

Running Analysis

1. Open the frontend dashboard in your browser

2. Enter your analysis request in the text area, for example:

   "Identify the top 3 high-volume cyclist transit points for a new coffee shop store layout expansion"

3. Select your LLM provider (Ollama or Gemini)

4. Click "Run Analysis"

5. Watch the real-time execution progress in the console

6. View the results and business insights

API Endpoints

• GET /health - Health check

• GET /api/schema - Get database schema

• POST /api/analyze - Run analysis request

• GET /api/status - Get execution status

• POST /api/ingest-data - Trigger data ingestion

• WS /ws - WebSocket for real-time logs

• GET /api/logs - Get recent logs

• GET /api/logs/stream - SSE log streaming

Example API Usage

# Get database schema
curl http://localhost:8000/api/schema

# Run analysis
curl -X POST http://localhost:8000/api/analyze \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Find top 5 high-traffic transit stations", "llm_provider": "ollama"}'

# Check execution status
curl http://localhost:8000/api/status

🧪 Testing

Run the test suite:

# Run all tests
pytest tests/

# Run specific test file
pytest tests/test_database.py

# Run with coverage
pytest tests/ --cov=backend --cov-report=html

📁 Project Structure

Retail-MCP-Siting-Engine/
├── backend/
│   ├── app.py              # FastAPI server with WebSocket
│   ├── database.py         # SQLite + SpatiaLite database
│   ├── data_ingestion.py   # Mock data generation pipeline
│   ├── mcp_server.py       # MCP server implementation
│   └── agent_controller.py # Agentic execution controller
├── data/
│   └── retail_siting.db    # Local database (created on init)
├── frontend/
│   ├── index.html          # Main dashboard
│   └── app.js              # Frontend JavaScript
├── tests/
│   ├── test_database.py    # Database tests
│   └── test_mcp_server.py  # MCP server tests
├── requirements.txt        # Python dependencies
├── run.py                 # Main setup and run script
├── setup.bat              # Windows setup script
├── setup.sh               # Linux/Mac setup script
├── .env.example           # Environment configuration template
└── README.md              # This file

� Features

Database Capabilities

• Transit Stations: Bike transit routes with capacity scores

• Commerce Points: Business locations with revenue metrics

• Storefronts: Potential retail locations with accessibility scores

• Spatial Queries: Distance calculations, nearby points, spatial joins

AI/LLM Integration

• Multi-Provider Support: Ollama (local) or Google Gemini

• Dynamic SQL Generation: LLM writes optimized queries based on schema

• Business Intelligence: Strategic insights from query results

• Error Handling: Graceful failure with informative messages

Real-time Monitoring

• Live Logs: WebSocket streaming of application logs

• Phase Tracking: Visual progress through execution phases

• Status Updates: Real-time execution status and progress

• Error Reporting: Immediate feedback on failures

🛡️ Security

• SQL injection protection via query validation

• Dangerous SQL keyword blocking (DROP, DELETE, etc.)

• Environment variable configuration for sensitive data

• No cloud dependencies - everything runs locally

• No data leaves your machine

⚙️ Configuration Options

LLM Providers

Ollama (Recommended)

• Completely free and local

• Requires Ollama installation

• Supports multiple models (llama3, mistral, etc.)

• No API keys needed

Google Gemini

• Free tier available

• Requires API key

• Good alternative if Ollama is not available

• Configure via .env file

Database Options

SQLite + SpatiaLite (Default)

• Lightweight, serverless

• Built-in spatial extensions

• Portable database file

• No additional setup required

DuckDB (Optional)

• Alternative database engine

• Included in requirements

• Can be configured in code

🐛 Troubleshooting

Ollama Connection Issues

# Check if Ollama is running
curl http://localhost:11434/api/tags

# Start Ollama service
ollama serve

# Pull required model
ollama pull llama3

Database Issues

# Reinitialize database
rm data/retail_siting.db
python run.py setup

Port Already in Use

# Change port in .env
PORT=8001

# Or kill the process using the port
# Windows
netstat -ano | findstr :8000
taskkill /PID <PID> /F

# Linux/Mac
lsof -ti:8000 | xargs kill -9

Dependency Issues

# Create fresh virtual environment
python -m venv venv
source venv/bin/activate  # or venv\Scripts\activate on Windows
pip install --upgrade pip
pip install -r requirements.txt

📈 Performance

• Database Query: < 100ms for typical spatial queries

• LLM Generation: 2-10 seconds depending on query complexity

• Total Analysis: 5-30 seconds for complete 4-phase execution

• Memory Usage: ~500MB for Python + LLM

• Disk Usage: ~10MB for database with mock data

🤝 Contributing

This is a demonstration project for local AI-powered spatial analysis. Feel free to extend it with:

• Additional spatial analysis functions

• More sophisticated mock data generation

• Support for other LLM providers

• Enhanced frontend visualizations

• Additional database backends

📄 License

This project is provided as-is for educational and demonstration purposes.

🙏 Acknowledgments

• FastAPI - Modern, fast web framework

• LangChain - LLM orchestration framework

• Ollama - Local LLM runtime

• SpatiaLite - Spatial extensions for SQLite

• Chart.js - Data visualization library

• Tailwind CSS - Utility-first CSS framework

📞 Support

For issues and questions:

1. Check the troubleshooting section

2. Review the logs in the frontend console

3. Ensure all prerequisites are installed

4. Verify configuration in .env file

Built with ❤️ for local, privacy-first AI applications