map-services / gps-routes-api / 38819920945

54%

push

github

refactor(sqlite): replace Spatialite with R*Tree and Haversine function (#192)

Fixes #191 

Removes the Spatialite extension dependency entirely. All three
Spatialite usage sites are replaced with built-in SQLite features:

1. **bbox filtering** — R*Tree virtual table (routes_rtree) replaces
ST_Within(_geoloc, BuildMbr(...))
2. **radius filtering** — Go-registered haversine_distance() SQL
function (via sqlite3_haversine driver's ConnectHook) replaces
Euclidean-approx queries
3. **_geoloc writes** — _geoloc BLOB column fully dropped from schema;
lat/lng columns + R*Tree triggers handle all spatial indexing

**Key Decisions:**
- Build tags: -tags "sqlite,sqlite_fts5" (no sqlite_math_functions
needed)
- Haversine registered via ConnectHook → conn.RegisterFunc — no
extension or extra build tag
- Default Limit: SearchHits now defaults to 10 when unset (was LIMIT 0 →
no rows)
- JSON field name _geoloc retained in domain models (GeoJSON field name,
not DB column)

All tests pass: CGO_ENABLED=1 go test -tags "sqlite,sqlite_fts5"
-count=1 ./...

41 of 46 new or added lines in 5 files covered. (89.13%)

596 of 1144 relevant lines covered (52.1%)

5.91 hits per line