skaidb v0.234.0

Geospatial search

skaidb stores geographic points as ordinary document fields and queries them with two scalar functions — geo_distance and geo_bbox — that work anywhere in a WHERE or ORDER BY. A geo index makes those predicates prune to a neighborhood instead of scanning the table, transparently: the same query runs against the index when one exists on the column, and against a scan when it doesn't.

Geo indexes are distributed (sharded scatter-gather), on-disk (durable entries — nothing rebuilds on open), self-maintaining on writes, and backfilled in the background like a secondary index.

Points

A point is a document field shaped as a {lat, lon} object (lng is also accepted) or a [lat, lon] array:

CREATE TABLE places (PRIMARY KEY (id));
INSERT INTO places (id, name, loc) VALUES
  (1, 'HQ',   {lat: 40.7128, lon: -74.0060}),
  (2, 'Depot', [40.73, -73.99]);

The store is schema-less: a field that is not a readable point (absent, NULL, or the wrong shape) simply never matches a geo predicate and is never indexed — one bad row can't fail a query.

Querying (works with or without an index)

-- Everything within 5 km of a point (metres, or a distance('…') literal):
SELECT id FROM places WHERE geo_distance(loc, 40.71, -74.0) <= 5000;
SELECT id FROM places WHERE geo_distance(loc, 40.71, -74.0) <= distance('5km');

-- Nearest-first, bounded:
SELECT id, geo_distance(loc, 40.71, -74.0) AS m
FROM places
WHERE geo_distance(loc, 40.71, -74.0) <= 5000
ORDER BY geo_distance(loc, 40.71, -74.0) LIMIT 10;

-- Inside a bounding box (min_lon > max_lon crosses the antimeridian):
SELECT id FROM places WHERE geo_bbox(loc, 40.4, -74.3, 40.9, -73.7);

geo_distance(point, lat, lon) is the great-circle (haversine) distance in metres; geo_bbox(point, min_lat, min_lon, max_lat, max_lon) is a boolean point-in-rectangle test; distance('<n><unit>') converts a unit-suffixed distance literal (m/km/mi/yd/ft/NM/…) to metres — constant, so the geo index prunes through it. Full grammar in QUERY_SYNTAX.md.

ES clients get the same predicates through the _search DSL — geo_distance and geo_bounding_box queries map onto these functions (with ES unit suffixes like "5km" converted to metres, and object/GeoJSON-array/string/WKT point shapes accepted); see SEARCH.md.

From an application

Geo predicates are ordinary SQL functions, so every driver runs them through its normal query call. The centre and the radius bind as parameters — distance('5km') is a literal for hand-written SQL, but from code you pass metres as a number. Points bind as parameters too: a document ({lat, lon}) or a two-element array, exactly as they are stored. Placeholders differ per driver (? everywhere except Node.js and Ruby, which use $1) — see the matrix in HOWDOI.md.

SELECT id, name, geo_distance(loc, ?, ?) AS metres
FROM places
WHERE geo_distance(loc, ?, ?) <= ?
ORDER BY metres LIMIT 10;

lat, lon, radius = 40.71, -74.0, 5000
cur = conn.cursor()
cur.execute("SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places "
            "WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10",
            (lat, lon, lat, lon, radius))
for id_, name, metres in cur.fetchall():
    print(name, round(metres))

const res = await client.query(
  `SELECT id, name, geo_distance(loc, $1, $2) AS metres FROM places
   WHERE geo_distance(loc, $1, $2) <= $3 ORDER BY metres LIMIT 10`,
  [40.71, -74.0, 5000]);            // $1/$2 reused — the driver duplicates them
for (const row of res.rows) console.log(row.name, row.metres);

lat, lon, radius := 40.71, -74.0, 5000.0
rows, err := db.Query(`SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places
    WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10`,
    lat, lon, lat, lon, radius)
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    var metres float64
    rows.Scan(&id, &name, &metres)
}

Skaidb.ResultSet rs = conn.prepare(
        "SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places "
      + "WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10")
    .setDouble(1, 40.71).setDouble(2, -74.0)
    .setDouble(3, 40.71).setDouble(4, -74.0)
    .setDouble(5, 5000)
    .executeQuery();
while (rs.next()) System.out.println(rs.getString("name") + " " + rs.getDouble("metres"));

res = conn.exec_params(<<~SQL, [40.71, -74.0, 5000])
  SELECT id, name, geo_distance(loc, $1, $2) AS metres FROM places
  WHERE geo_distance(loc, $1, $2) <= $3 ORDER BY metres LIMIT 10
SQL
res.each { |row| puts "#{row['name']} #{row['metres']}" }

$stmt = $db->prepare('SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places
    WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10');
$stmt->execute([40.71, -74.0, 40.71, -74.0, 5000]);
foreach ($stmt->fetchAll() as $row) { echo $row['name'], ' ', $row['metres'], PHP_EOL; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places " +
                  "WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10";
foreach (var p in new object[] { 40.71, -74.0, 40.71, -74.0, 5000.0 }) cmd.Parameters.Add(p);
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetString(1)} {reader.GetDouble(2)}");

use skaidb_proto::Response;
use skaidb_types::Value;
let mut q = client.prepare(
    "SELECT id, name, geo_distance(loc, ?, ?) AS metres FROM places \
     WHERE geo_distance(loc, ?, ?) <= ? ORDER BY metres LIMIT 10")?;
let args = [Value::Float(40.71), Value::Float(-74.0),
            Value::Float(40.71), Value::Float(-74.0), Value::Float(5000.0)];
if let Response::Rows { rows, .. } = client.execute_prepared(&mut q, &args)? {
    for row in rows { println!("{} {}", row[1], row[2]); }
}

Note the repeated centre: geo_distance appears in both the projection and the predicate, so a positional-placeholder driver binds it twice. Node.js and Ruby reuse $1/$2 and let the driver duplicate them.

Creating a geo index (SQL — works cluster-wide)

CREATE GEO INDEX places_geo ON places (loc);
DROP   GEO INDEX places_geo;

Nothing to configure. This is broadcast DDL: every node builds and maintains an index over its own shard. Existing rows are backfilled in paged background work (like secondary indexes) — while a node backfills, SHOW INDEXES reports its local state as building; on a single-node/embedded database the backfill completes before the DDL returns. Once created, the index is used automatically — no query change. EXPLAIN SELECT … WHERE geo_distance(…) shows the geo index scan access path when the index is engaged.

The index maintains itself on INSERT/UPDATE/DELETE: a move updates the point's position, a delete removes it, and (because it lives in an on-disk index engine) entries persist across restarts — no rebuild, unlike the in-memory vector index.

How it works

  • Morton (Z-order) codes. Each point's latitude and longitude are quantized to 32-bit fixed-point values and bit-interleaved into a single 64-bit code (latitude on the even bits, longitude on the odd). The index stores one entry per row keyed by morton_be(point) ++ row_key, so entries sort by Z-order.
  • A query is a range cover. A geo_distance <= r radius is turned into its bounding box (with a cos φ longitude correction); a geo_bbox is the box directly. The box is covered by a small, bounded set of contiguous Morton-code ranges (a Z-order quadtree descent, capped so a fringe never explodes the scan count). Each range is a candidate scan.
  • Superset + exact re-read. The range cover is always a superset of the true matches (the Z-curve wanders outside the box between corners), so every candidate row is re-read and re-checked with the exact geo_distance / geo_bbox predicate — no false negatives, and false positives are filtered out. Ordering by distance is applied by the executor after the gather (Z-order is not distance order).
  • Distributed. A geo scan is just a multi-range secondary-index scan, so it reuses the existing scatter: each shard scans the code ranges over its local index, the coordinator unions the candidate keys, re-reads each at the read quorum (authoritative last-writer-wins point), and applies the exact filter.

Antimeridian

A geo_bbox with min_lon > max_lon crosses the antimeridian (±180°); the planner splits it into two non-wrapping halves and the index serves both. A geo_distance radius straddling ±180° likewise wraps its envelope, so an index-served radius query near the antimeridian sees far-side rows.

Limits

  • Points only — geo_shape polygons are not supported.
  • No geo aggregations (geohash-grid / geo-bounds facets).