Spatial Distance Queries in SQLite on Android

10.1.2026

Spatial Distance Queries in SQLite on Android

Introdiuction

SQLite on Android (especially older and stock builds) does not provide trigonometric functions such as SIN(), COS(), or ACOS() — and you can’t load extensions.

Goal

Efficiently sort points of interest (POIs) by distance inside SQLite, without pulling all rows into memory.

This article explains a proven workaround and shows a Kotlin Multiplatform–friendly implementation (no Android- or Java-only dependencies).

The Problem

A common task in mobile apps is:

“Give me the nearest 30 locations to the user.”

In “full” SQL databases (PostgreSQL, MySQL, etc.), you might write:

SELECT *,
ACOS(
SIN(lat1)*SIN(lat2) +
COS(lat1)*COS(lat2)*COS(lon1-lon2)
) AS distance
FROM pois
ORDER BY distance
LIMIT 30;

Why this fails on Android SQLite

  • Android’s SQLite is compiled without math functions
  • SIN, COS, ACOS do not exist
  • Extensions cannot be loaded
  • This is true even on many modern Android devices

So this perfectly valid SQL simply crashes.

The Key Insight

SQLite does support:

  • addition (+)
  • multiplication (*)
  • comparison and sorting (ORDER BY)

So the solution is:

Move all trigonometry into application code and store the results in the database.

Mathematical Background (Short Version)

The spherical law of cosines:

cos(Δσ) =
sin φ1 sin φ2 +
cos φ1 cos φ2 cos(Δλ)

We can rewrite:

cos(Δλ) = cos λ1 cos λ2 + sin λ1 sin λ2

So the full expression becomes:

sin φ1 sin φ2 +
cos φ1 cos φ2 (cos λ1 cos λ2 + sin λ1 sin λ2)

✔ No trig functions needed in SQL ✔ Result is cos(angle), not distance ✔ Larger value = closer point

We only need ordering, so this is perfect.

Database Schema

We store latitude/longitude and their precomputed trig values.

CREATE TABLE pois (
id INTEGER PRIMARY KEY,
name TEXT,lat REAL,
lon REAL,sin_lat REAL,
cos_lat REAL,
sin_lon REAL,
cos_lon REAL
);

Kotlin Multiplatform: Precomputing Trig Values

This code runs in commonMain.

import kotlin.math.*data class GeoPoint(
val latDeg: Double,
val lonDeg: Double
) {
val latRad = Math.toRadians(latDeg)
val lonRad = Math.toRadians(lonDeg)val sinLat = sin(latRad)
val cosLat = cos(latRad)
val sinLon = sin(lonRad)
val cosLon = cos(lonRad)
}

Inserting a POI

fun insertPoi(
name: String,
point: GeoPoint
): Map<String, Any> {
return mapOf(
"name" to name,
"lat" to point.latDeg,
"lon" to point.lonDeg,
"sin_lat" to point.sinLat,
"cos_lat" to point.cosLat,
"sin_lon" to point.sinLon,
"cos_lon" to point.cosLon
)
}

This map can be bound to SQL on Android, iOS, or desktop.

Querying Nearest POIs (Trig-Free SQL)

User location is computed in Kotlin:

val user = GeoPoint(
latDeg = 52.519842,
lonDeg = 13.439484
)

SQL query:

SELECT *,
(
? * sin_lat +
? * cos_lat * (
? * cos_lon +
? * sin_lon
)
) AS proximity
FROM pois
ORDER BY proximity DESC
LIMIT 30;

Bind parameters in this order:

  1. user.sinLat
  2. user.cosLat
  3. user.cosLon
  4. user.sinLon

Why This Works

  • SQLite only evaluates arithmetic
  • No trigonometric SQL functions required
  • Sorting happens in the database
  • Only the nearest rows are returned
  • Works on all Android versions

Important Notes

This is NOT a real distance

The computed value is:

cos(central_angle)

  • Range: [-1, 1]

  • Higher = closer

  • Perfect for sorting

  • If you need meters/kilometers:

    • Compute acos(value) * EarthRadiusin Kotlin

Performance

  • Add LIMIT
  • Optionally add a bounding box filter
  • For large datasets, consider R-tree indexes

When to Use This Approach

✔ You need distance sorting ✔ SQLite has no trig support ✔ Dataset is moderate (hundreds–thousands of rows) ✔ You want database-side ordering

❌ You need geofencing ❌ You need exact distances for all rows ❌ Dataset is huge (millions)

Final Thoughts

This pattern is old — but still relevant.

Even today, Android SQLite lacks trigonometric functions, and Kotlin Multiplatform makes this solution portable across platforms.

By precomputing trig values once and storing them, you get:

  • correctness
  • speed
  • compatibility
  • clean separation of concerns

Sometimes, the best optimization is just moving math to the right place.

NOTE: this is an idea based on my implementaion in LocationProvider in my contribution to Wheelamp.org