Implement Full MongoDB GeoSpatial Query Compatibility Using SpatiaLite

[cwt]

Summary

Implement complete MongoDB geospatial query compatibility in NeoSQLite using the SpatiaLite extension, employing a trigger-based architecture for synchronizing document and spatial data tables.

---

Rationale

• MongoDB's geospatial API ($near, $geoWithin, $geoIntersects, $nearSphere) is feature-rich and widely adopted.
• PyMongo compatibility is a core goal of NeoSQLite, and adding geospatial support is frequently requested.
• SQLite + SpatiaLite provides a mature spatial engine with all required functions for 100% operator parity.

---

High-Level Design

Architecture:

• Use a dedicated geospatial companion table for each document collection.
• Maintain table synchronization with SQLite triggers on insert/update/delete.
• R-Tree and SpatiaLite spatial indexes for optimal query performance.
• Convert MongoDB GeoJSON input to SpatiaLite WKT for storage/indexing.

---

Implementation Phases:

1. SpatiaLite Integration

   • Load SpatiaLite in the Connection class (neosqlite/connection.py).
   • Error handling and feature detection if SpatiaLite is unavailable.

2. Geospatial Table & Index Setup

   • Extend collection creation to build spatial companion tables and indexes (neosqlite/collection/__init__.py).
   • Support various geometry types (Point, LineString, Polygon, MultiPoint, etc).

3. GeoJSON Utilities

   • Utilities to extract geometry from JSON documents, convert GeoJSON → WKT (neosqlite/collection/geo_helper.py).
   • Coordinate system transformations and validation.

4. Trigger-Based Synchronization

   • Implement triggers to maintain spatial table with inserts, updates, deletes.

5. Operator Translation Layer

   • SQL translation for $near, $geoWithin, $geoIntersects, $nearSphere using SpatiaLite functions (neosqlite/collection/sql_translator_unified.py).
   • Support for $maxDistance, $minDistance, complex shapes.

6. Spatial Index and Management APIs

   • Methods for creating, dropping, listing spatial indexes (neosqlite/collection/index_manager.py).
   • Compound index and index introspection support.

7. Comprehensive Testing

   • Unit and integration tests for each operator (compared to MongoDB).
   • Edge cases, invalid geometries, non-SpatiaLite environments.
   • Performance benchmarks.

---

Potential Challenges

• Ensuring SpatiaLite is available by default in user environments.
• Accurate GeoJSON → WKT conversion (including edge/corner cases).
• Maintaining performance parity with MongoDB for large spatial datasets.

---

References

• See documents/TODO/GEOSPATIAL_IMPLEMENTATION_PLAN.md for full technical breakdown and tasks.

---

Deliverable:
A robust implementation providing 100% PyMongo geospatial API compatibility in NeoSQLite for SQLite installations with SpatiaLite enabled.