openSenseMap Edu

An interactive, web-based SQL learning tool that leverages real-world environmental sensor data from openSenseMap. Built as part of a Master's thesis in Geoinformatics and Spatial Data Science at the Institute for Geoinformatics (ifgi), University of Münster, this project serves as a functional scaffolding that extends the NRW DatabaseConnector framework used across schools in North Rhine-Westphalia.

Students interact with SQL through structured, narrative-driven scenarios where they explore real environmental sensor networks. By querying device locations, filtering by exposure configurations, joining station metadata with measurement logs, and examining spatial air quality distributions, learners practice foundational relational mechanics on a live-synced, authentic dataset.

---

Features

SQL Learning Environment

• Narrative-Driven Scenarios: Progressive tasks that guide students from basic SELECT projections to structured JOIN conditions, GROUP BY aggregations, and subqueries.
• Integrated Workspace: An in-browser SQL editor powered by CodeMirror, complete with syntax highlighting, automatic completion triggers, and Ctrl+Enter code execution.
• Automated Query Validation: A dual-layer safety validation system that parses student schemas against sample database solution profiles (cross-checking row count, column dimensions, and exact cell values).
• Targeted Feedback: Provides automated scaffolding hints and semantic corrections when an executed query encounters syntax anomalies or data mismatches.
• SQL Playground: A sandboxed environment allowing for unguided, free-form query experimentation outside the rigid scenario paths.

Geographic and Spatial Interpretation

• Interactive Leaflet Map: Automatically parses and evaluates latitude/longitude coordinate matrices present within query result rows to generate dynamic geographical overlays.
• Color-Coded Exposure Markers: Visually classifies senseBox markers based on station environment configurations (indoor, outdoor, mobile).
• Cluster Management: Handles dense spatial result sets natively through Leaflet.MarkerCluster configurations, incorporating clean drop-in rendering paths.
• Contextual Popups: Renders responsive data popups showing all retrieved database fields directly on the targeted map coordinate.
• Dynamic View Bounds: Auto-centers and maps the viewport camera to perfectly fit the geometric boundaries of the retrieved query set.

Progress and Session Tracking

• Role-Based Authentication: Clean user management isolation partitioning pathways for Students, Teachers, and Administrators.
• Granular Progress Checkmarks: Real-time per-task tracking indicated by automated checklist states in the workspace sidebar.
• Linear Workspace Navigation: Prompts students with structured transition paths to the next curricular milestone immediately upon target validation.
• Scenario Metrics: Employs progress gauges on the main dashboard indicating structural completion percentages.

Administration

• Unified Control Dashboard: Admin tools for real-time user provision, scenario publishing, and schema task management.
• Integrated Scenario Builder: An administrative workspace interface for modifying learning objectives, narrative arcs, and sample solution keys.
• Classroom Monitoring Logs: Provides instructors with descriptive progress overviews across all active student cohorts.

---

Tech Stack

Architecture Layer	Core Technology Component
Backend Framework	Java 21, Spring Boot 3.x
Security Layer	Spring Security (Role-Based Access Controls)
Relational Database	PostgreSQL 17 with PostGIS Extensions
Persistence / ORM	Spring Data JPA / Hibernate Core
Template Engine	Thymeleaf, Bootstrap 5 UI, CodeMirror Editor
Mapping Engine	Leaflet.js, Leaflet.MarkerCluster
Data Engine Feed	openSenseMap Production API (Automated Daily Synchronization)

---

Database Architecture

The system coordinates a single PostgreSQL database instance integrating two distinct operational schemas:

openSenseMap Production Mirror Tables (Daily Sync Pipeline)

• device — Real senseBox deployment profiles containing unique identifiers, metadata tags, model configurations, and physical exposure parameters.
• sensor — Distinct hardware sensor profiles mapped to their parent device.
• measurement — Time-series logging records containing environmental sensor readings captured over the previous 24 hours.
• location & device_to_location — Structural positioning registries tracking spatial and historical tracking coordinates.

Educational Management Tables (Application Domain)

• edu_user — Core user authentication records isolating student, instructor, and system admin accounts.
• scenario — Narrative learning modules tracking contextual story plots, educational objectives, and target difficulty scales.
• task — Distinct task nodes encapsulating standard SQL instructions, correct sample validation keys, and targeted hints.
• query_submission — Analytical logging tables tracking student query history and validation outcomes.
• user_progress — Consolidated progress tracking logs monitoring completion metrics and execution success parameters.

---

Getting Started

Prerequisites

• Java Development Kit (JDK) 21 or higher
• Apache Maven 3.8+
• PostgreSQL 17+ (configured with PostGIS components)
• A target relational database instance initialized as opensensemap_edu

Configuration and Execution

1. Clone the Source Tree:

   git clone https://github.com/JerryVincent/opensensemap-edu.git
   cd opensensemap-edu

2. Configure Database Target Strings: Adjust your structural connection lines inside src/main/resources/application.properties to target your local PostgreSQL credentials:

   spring.datasource.url=jdbc:postgresql://localhost:5432/opensensemap_edu
   spring.datasource.username=your_database_username
   spring.datasource.password=your_secure_password

3. Database Migrations: Database schema structures are version-controlled via Flyway. SQL migration assets inside src/main/resources/db/migration/ automatically build out tables on system startup.

4. Compile and Run via Maven:

   mvn spring-boot:run

5. Access the Local Workspace: Open a browser window and navigate to: http://localhost:8080

Default Authentication Credentials

Username	Password	Assigned System Role
admin	admin123	ADMIN (Full Dashboard Privileges)
student	student123	STUDENT (Standard Workspace Access)

---

Data Synchronization Workflow

The system updates its real-world database tables daily by running an automated synchronization script against the openSenseMap production environment:

• Structural Metadata Tables (user, profile, device, sensor): Completes a full clean refresh of structural states.
• Time-Series Tracking (measurement): Curation window isolated to the preceding 24 hours of live collection cycles (~2.6M rows).
• Spatial Positioning Coordinates (location): Filters and processes the single latest coordinate profile tracked per mobile or static device.

Note: Operational script components reside inside C:\osm_sync\ and are omitted from public version control to protect credentials.

---

Core Learning Scenarios

The system deploys natively with three curriculum-aligned learning tracks:

1. Getting Started with SQL (Beginner Level): Targets foundational projections (SELECT), structural column filtering, and conditional statements (WHERE).
2. Air Quality Detective (Intermediate Level): Explores pattern filters (LIKE), multi-table relational mappings (JOIN), and temporal evaluation conditions.
3. Sensor Network Analyst (Advanced Level): Deepens skills across row metrics (COUNT, AVG), aggregation buckets (GROUP BY), array rankings (ORDER BY), and nested subqueries.

---

Thesis Research Context

This platform forms the technical artifact of an M.Sc. thesis completed within the Geoinformatics and Spatial Data Science framework at the Institute for Geoinformatics (ifgi), University of Münster.

The research evaluates the feasibility and student reception of using authentic spatial data streams to fulfill standard secondary database curriculum requirements. Student motivational feedback and workspace experiences were collected during an empirical user study conducted at the Ratsgymnasium Münster.

The evaluation framework leverages an adapted ICODE_MS questionnaire instrument, grounded firmly in psychological goal-orientation theory and styled after German educational motivation tracking standards (SELLMO).

---

License & Acknowledgments

• Development Context: This project was engineered strictly as academic master's thesis work. Contact the author directly for operational deployment permissions or licensing requests.
• openSenseMap Platform: Core environmental datasets are provided via open API models by the openSenseLab gGMBH and re:edu / Reedu GmbH & Co. KG.
• Institutional Supervision: Academic engineering direction and research facilities were provided by the Institute for Geoinformatics (ifgi), University of Münster.
• Curricular Foundation: Technical core designs extend the standard pedagogical Java logic formulated by the NRW DatabaseConnector framework distributed by QUA-LiS NRW.