moto-adv-website/MAP_SYSTEM.md

Database-Driven Map System

This document describes the new database-driven map system that provides efficient and reliable loading of GPX routes on the community map.

Overview

The map system has been redesigned to use pre-processed route data stored in the database instead of real-time GPX file processing. This approach provides:

• Faster Loading: Routes load instantly from the database
• Better Performance: No real-time file parsing during map display
• Simplified Coordinates: Routes are automatically simplified for overview maps
• Reliable Display: Eliminates container sizing and timing issues
• Cached Statistics: Route statistics are pre-calculated and stored

Database Schema

MapRoute Table

The map_routes table stores pre-processed route data:

CREATE TABLE map_routes (
    id INTEGER PRIMARY KEY,
    coordinates TEXT NOT NULL,           -- JSON array of [lat, lng] points
    simplified_coordinates TEXT,         -- Simplified version for overview
    start_latitude FLOAT NOT NULL,       -- Route start point
    start_longitude FLOAT NOT NULL,
    end_latitude FLOAT NOT NULL,         -- Route end point  
    end_longitude FLOAT NOT NULL,
    bounds_north FLOAT NOT NULL,         -- Bounding box
    bounds_south FLOAT NOT NULL,
    bounds_east FLOAT NOT NULL,
    bounds_west FLOAT NOT NULL,
    total_distance FLOAT DEFAULT 0.0,    -- Distance in kilometers
    elevation_gain FLOAT DEFAULT 0.0,    -- Elevation gain in meters
    max_elevation FLOAT DEFAULT 0.0,     -- Maximum elevation
    min_elevation FLOAT DEFAULT 0.0,     -- Minimum elevation
    total_points INTEGER DEFAULT 0,      -- Original point count
    simplified_points INTEGER DEFAULT 0, -- Simplified point count
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    post_id INTEGER NOT NULL UNIQUE,     -- Foreign key to posts
    gpx_file_id INTEGER NOT NULL         -- Foreign key to gpx_files
);

How It Works

1. Route Creation Process

When a post is published by an admin:

1. Admin publishes post → admin.publish_post() function
2. GPX processing triggered → process_post_approval() function
3. Route data extracted → Parse GPX file with gpxpy
4. Coordinates simplified → Douglas-Peucker algorithm reduces points
5. Statistics calculated → Distance, elevation, bounds computed
6. Data stored → All information saved to map_routes table

2. Map Display Process

When the community map loads:

1. API called → /community/api/routes endpoint
2. Database queried → Only published posts with routes
3. Data returned → Pre-processed coordinates and metadata
4. Map rendered → Leaflet displays routes instantly

3. Coordinate Simplification

Routes are simplified using the Douglas-Peucker algorithm:

• Original points: Full GPX track (e.g., 1,849 points)
• Simplified points: Reduced set maintaining shape (e.g., 74 points)
• Compression ratio: Typically 95-98% reduction
• Visual quality: Preserved for map display

API Endpoints

Get All Routes

GET /community/api/routes

Returns simplified route data for all published posts:

[
  {
    "id": 1,
    "title": "Transalpina",
    "author": "ske087", 
    "coordinates": [[45.409, 23.794], ...],
    "distance": 41.26,
    "elevation_gain": 2244,
    "max_elevation": 1994,
    "start_point": {"latitude": 45.409, "longitude": 23.794},
    "end_point": {"latitude": 45.426, "longitude": 23.799},
    "bounds": {"north": 45.426, "south": 45.378, ...}
  }
]

Get Route Details

GET /community/api/route/<post_id>

Returns detailed route data including full coordinates for individual post views.

Management Tools

Route Management Script

Use manage_routes.py for route management:

# Show all routes
python manage_routes.py list

# Show statistics
python manage_routes.py stats

# Create route for specific post
python manage_routes.py create 1

# Recreate all routes
python manage_routes.py recreate-all

# Clean up unpublished routes
python manage_routes.py cleanup

Migration Script

The migrations/create_map_routes.py script:

• Creates the map_routes table
• Processes existing published posts with GPX files
• Generates route data for all current content

Files Modified

Core Files

• app/models.py - Added MapRoute model
• app/utils/gpx_processor.py - Extended with route creation functions
• app/routes/community.py - Updated API endpoints
• app/routes/admin.py - Added route creation on post publish

Management Files

• migrations/create_map_routes.py - Database migration
• manage_routes.py - Route management utility
• static/test_db_map.html - Test page for verifying functionality

Benefits Over Previous System

Performance

• Before: Real-time GPX parsing (1-2 second delays)
• After: Instant database lookup (<100ms)

Reliability

• Before: Container sizing issues, timing problems
• After: Consistent loading, no container dependencies

Scalability

• Before: Processing time increases with file size
• After: Constant time regardless of original GPX size

Maintenance

• Before: Debug JavaScript timing and CSS conflicts
• After: Simple database queries with management tools

Troubleshooting

No Routes Displayed

1. Check if posts are published: python manage_routes.py list
2. Verify API response: curl http://localhost:5000/community/api/routes
3. Check browser console for JavaScript errors

Missing Route Data

1. Re-create routes: python manage_routes.py recreate-all
2. Check GPX file exists and is valid
3. Review server logs for processing errors

Performance Issues

1. Check simplified point counts: python manage_routes.py stats
2. Verify database indexes on post_id and published fields
3. Monitor API response times

Future Enhancements

Possible Improvements

• Route Clustering: Group nearby routes on overview map
• Elevation Profiles: Store elevation data for profile charts
• Route Segments: Support for multi-segment routes
• Cache Invalidation: Automatic route updates when GPX files change
• Batch Processing: Background job processing for large GPX files

Configuration Options

• Simplification Tolerance: Adjustable coordinate reduction level
• Update Triggers: Automatic processing on file upload
• Storage Options: Consider storing coordinates in PostGIS for spatial queries

This system provides a solid foundation for reliable map display while maintaining flexibility for future enhancements.