Geospatial Queries

Overview

TrailBase supports geospatial queries through SpatiaLite, a spatial extension for SQLite. Store and query geographic data including points, lines, polygons, and complex geometries.

Geospatial Data Types

TrailBase recognizes geometry columns with ST_IsValid check constraints:

CREATE TABLE locations (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  -- Geometry column with validation
  geom BLOB CHECK(ST_IsValid(geom))
) STRICT;

CREATE TABLE regions (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  boundary BLOB CHECK(ST_IsValid(boundary)) NOT NULL
) STRICT;

Creating Geometries

From Well-Known Text (WKT)

-- Point
INSERT INTO locations (name, geom)
VALUES (
  'Colosseum',
  ST_GeomFromText('POINT(12.4924 41.8902)', 4326)
);

-- LineString
INSERT INTO locations (name, geom)
VALUES (
  'Route 66',
  ST_GeomFromText('LINESTRING(-118.2437 34.0522, -87.6298 41.8781)', 4326)
);

-- Polygon
INSERT INTO regions (name, boundary)
VALUES (
  'Central Park',
  ST_GeomFromText(
    'POLYGON((-73.9812 40.7681, -73.9581 40.7681, -73.9581 40.7648, -73.9812 40.7648, -73.9812 40.7681))',
    4326
  )
);

-- MultiPolygon (for regions with multiple parts)
INSERT INTO regions (name, boundary)
VALUES (
  'Hawaii',
  ST_GeomFromText(
    'MULTIPOLYGON(((-155.5 19.5, -155.5 19.0, -155.0 19.0, -155.0 19.5, -155.5 19.5)),
                  ((-156.5 20.5, -156.5 20.0, -156.0 20.0, -156.0 20.5, -156.5 20.5)))',
    4326
  )
);

Using Helper Functions

-- Create point from coordinates
INSERT INTO locations (name, geom)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584, 4326));

-- Create envelope (bounding box)
INSERT INTO regions (name, boundary)
VALUES (
  'Bottom-right quadrant',
  ST_MakeEnvelope(0, -90, 180, 0, 4326)
);

-- Create line from points
INSERT INTO locations (name, geom)
VALUES (
  'Flight path',
  ST_MakeLine(
    ST_MakePoint(-74.0060, 40.7128, 4326),  -- New York
    ST_MakePoint(-0.1278, 51.5074, 4326)     -- London
  )
);

Coordinate Reference Systems

SRID 4326 (WGS 84): Standard latitude/longitude coordinates

Longitude: -180 to 180 (East/West)
Latitude: -90 to 90 (South/North)

-- Set SRID when creating geometry
SELECT ST_GeomFromText('POINT(-122.4194 37.7749)', 4326);

-- Transform between coordinate systems
SELECT ST_Transform(geom, 3857) FROM locations;  -- Convert to Web Mercator

Spatial Queries

Distance Calculations

-- Find locations within 10km of a point
SELECT 
  name,
  ST_Distance(
    geom,
    ST_GeomFromText('POINT(-122.4194 37.7749)', 4326),
    1  -- Use geodesic distance for accuracy
  ) / 1000 AS distance_km
FROM locations
WHERE ST_Distance(
  geom,
  ST_GeomFromText('POINT(-122.4194 37.7749)', 4326),
  1
) <= 10000
ORDER BY distance_km;

Spatial Relationships

-- Check if point is within polygon
SELECT name
FROM regions
WHERE ST_Within(
  ST_GeomFromText('POINT(-73.9712 40.7831)', 4326),
  boundary
);

-- Find overlapping regions
SELECT r1.name, r2.name
FROM regions r1
JOIN regions r2 ON r1.id < r2.id
WHERE ST_Intersects(r1.boundary, r2.boundary);

-- Check if geometry contains another
SELECT name
FROM regions
WHERE ST_Contains(
  boundary,
  ST_GeomFromText('POINT(-118.2437 34.0522)', 4326)
);

-- Find touching geometries
SELECT r1.name, r2.name
FROM regions r1
JOIN regions r2 ON r1.id != r2.id
WHERE ST_Touches(r1.boundary, r2.boundary);

Query Parameters

TrailBase supports geospatial query parameters:

Within Query

GET /api/records/v1/locations?geom:$within=POINT(-122.4194 37.7749)

Intersects Query

GET /api/records/v1/regions?boundary:$intersects=POLYGON(...)

Contains Query

GET /api/records/v1/regions?boundary:$contains=POINT(-118.2437 34.0522)

From the codebase:

// crates/qs/src/column_rel_value.rs
pub enum ColumnRelValue {
    StWithin,      // Geometry A is within B
    StIntersects,  // Geometries A and B intersect
    StContains,    // Geometry A contains B
}

impl ColumnRelValue {
    fn as_sql(&self, column: &str, param: &str) -> String {
        match self {
            Self::StWithin => format!("ST_Within({column}, {param})"),
            Self::StIntersects => format!("ST_Intersects({column}, {param})"),
            Self::StContains => format!("ST_Contains({column}, {param})"),
        }
    }
}

GeoJSON Integration

TrailBase automatically converts geometry columns to GeoJSON in API responses:

CREATE TABLE places (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  location BLOB CHECK(ST_IsValid(location))
);

INSERT INTO places (name, location)
VALUES (
  'Golden Gate Bridge',
  ST_GeomFromText('POINT(-122.4783 37.8199)', 4326)
);

API Response:

{
  "id": 1,
  "name": "Golden Gate Bridge",
  "_geojson_location": {
    "type": "Point",
    "coordinates": [-122.4783, 37.8199]
  }
}

Requesting GeoJSON

Specify which geometry column to return as GeoJSON:

GET /api/records/v1/places?geojson=location

WASM Component Integration

use trailbase_wasm::db::{query, execute, Value};
use trailbase_wasm::http::{HttpRoute, Request, Json, routing};
use serde::{Deserialize, Serialize};

#[derive(Deserialize)]
struct NearbyQuery {
    lat: f64,
    lon: f64,
    radius_km: f64,
}

#[derive(Serialize)]
struct Location {
    id: i64,
    name: String,
    distance_km: f64,
}

async fn find_nearby(req: Request) -> Result<Json<Vec<Location>>, HttpError> {
    let query: NearbyQuery = req.query_parse()?;
    
    let rows = query(
        r#"
        SELECT 
          id,
          name,
          ST_Distance(
            geom,
            ST_GeomFromText($1, 4326),
            1
          ) / 1000 AS distance_km
        FROM locations
        WHERE ST_Distance(geom, ST_GeomFromText($1, 4326), 1) <= $2
        ORDER BY distance_km
        LIMIT 50
        "#,
        [
            Value::Text(format!("POINT({} {})", query.lon, query.lat)),
            Value::Real(query.radius_km * 1000.0),
        ]
    ).await?;
    
    let locations = rows.into_iter().map(|row| Location {
        id: row[0].as_integer().unwrap(),
        name: row[1].as_text().unwrap().to_string(),
        distance_km: row[2].as_real().unwrap(),
    }).collect();
    
    Ok(Json(locations))
}

async fn create_location(mut req: Request) -> Result<Json<CreateResponse>, HttpError> {
    #[derive(Deserialize)]
    struct Input {
        name: String,
        lat: f64,
        lon: f64,
    }
    
    let input: Input = req.body().json().await?;
    
    let id = execute(
        "INSERT INTO locations (name, geom) VALUES ($1, ST_GeomFromText($2, 4326)) RETURNING id",
        [
            Value::Text(input.name),
            Value::Text(format!("POINT({} {})", input.lon, input.lat)),
        ]
    ).await? as i64;
    
    Ok(Json(CreateResponse { id }))
}

import { query, execute } from 'trailbase-wasm/db';
import { HttpHandler, HttpResponse } from 'trailbase-wasm/http';

HttpHandler.get('/nearby', async (req) => {
  const lat = parseFloat(req.getQueryParam('lat') || '0');
  const lon = parseFloat(req.getQueryParam('lon') || '0');
  const radiusKm = parseFloat(req.getQueryParam('radius') || '10');
  
  const rows = await query(
    `
      SELECT 
        id,
        name,
        ST_Distance(
          geom,
          ST_GeomFromText($1, 4326),
          1
        ) / 1000 AS distance_km
      FROM locations
      WHERE ST_Distance(geom, ST_GeomFromText($1, 4326), 1) <= $2
      ORDER BY distance_km
      LIMIT 50
    `,
    [`POINT(${lon} ${lat})`, radiusKm * 1000]
  );
  
  return HttpResponse.json(rows);
});

Advanced Queries

Bounding Box Search

-- Find all locations in a bounding box (faster than distance)
SELECT id, name
FROM locations
WHERE ST_Intersects(
  geom,
  ST_MakeEnvelope(
    -122.5,  -- min longitude
    37.7,    -- min latitude
    -122.3,  -- max longitude
    37.9,    -- max latitude
    4326
  )
);

Buffer Zones

-- Create a 5km buffer around a point
SELECT ST_Buffer(
  ST_GeomFromText('POINT(-122.4194 37.7749)', 4326),
  5000,  -- meters
  1      -- use geodesic
);

-- Find locations within buffer of a region
SELECT l.name
FROM locations l
JOIN regions r ON r.id = $region_id
WHERE ST_Within(
  l.geom,
  ST_Buffer(r.boundary, 1000, 1)
);

Nearest Neighbor

-- Find 10 nearest locations to a point
SELECT 
  id,
  name,
  ST_Distance(geom, ST_GeomFromText('POINT(-122.4194 37.7749)', 4326), 1) AS distance
FROM locations
ORDER BY distance
LIMIT 10;

Area and Length

-- Calculate area of polygon (in square meters)
SELECT 
  name,
  ST_Area(boundary, 1) / 1000000 AS area_km2
FROM regions
ORDER BY area_km2 DESC;

-- Calculate length of line (in meters)
SELECT 
  name,
  ST_Length(geom, 1) / 1000 AS length_km
FROM routes;

Centroid

-- Find center point of a polygon
SELECT 
  name,
  ST_AsText(ST_Centroid(boundary)) AS center
FROM regions;

Real-Time Location Tracking

use trailbase_wasm::http::{HttpRoute, Request, routing};
use trailbase_wasm::db::{execute, query, Value};

async fn update_location(mut req: Request) -> Result<String, HttpError> {
    #[derive(Deserialize)]
    struct LocationUpdate {
        user_id: i64,
        lat: f64,
        lon: f64,
    }
    
    let update: LocationUpdate = req.body().json().await?;
    
    // Update user's current location
    execute(
        r#"
        INSERT INTO user_locations (user_id, location, updated_at)
        VALUES ($1, ST_GeomFromText($2, 4326), CURRENT_TIMESTAMP)
        ON CONFLICT(user_id) DO UPDATE SET
          location = excluded.location,
          updated_at = excluded.updated_at
        "#,
        [
            Value::Integer(update.user_id),
            Value::Text(format!("POINT({} {})", update.lon, update.lat)),
        ]
    ).await?;
    
    Ok("Location updated".to_string())
}

async fn find_nearby_users(req: Request) -> Result<Json<Vec<NearbyUser>>, HttpError> {
    let user_id: i64 = req.path_param("user_id")
        .and_then(|s| s.parse().ok())
        .ok_or_else(|| HttpError::status(StatusCode::BAD_REQUEST))?;
    
    let rows = query(
        r#"
        SELECT 
          u.id,
          u.name,
          ST_Distance(
            ul1.location,
            ul2.location,
            1
          ) / 1000 AS distance_km
        FROM user_locations ul1
        JOIN user_locations ul2 ON ul1.user_id = $1
        JOIN users u ON u.id = ul2.user_id
        WHERE ul2.user_id != $1
          AND ST_Distance(ul1.location, ul2.location, 1) <= 5000
        ORDER BY distance_km
        LIMIT 20
        "#,
        [Value::Integer(user_id)]
    ).await?;
    
    let nearby_users = rows.into_iter().map(|row| NearbyUser {
        id: row[0].as_integer().unwrap(),
        name: row[1].as_text().unwrap().to_string(),
        distance_km: row[2].as_real().unwrap(),
    }).collect();
    
    Ok(Json(nearby_users))
}

Geofencing

-- Check if location is within any geofence
CREATE TABLE geofences (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  boundary BLOB CHECK(ST_IsValid(boundary)) NOT NULL,
  action TEXT NOT NULL
);

-- Trigger when entering geofence
SELECT 
  g.id,
  g.name,
  g.action
FROM geofences g
WHERE ST_Within(
  ST_GeomFromText($user_location, 4326),
  g.boundary
);

Performance Tips

Use spatial indexes

Create R-Tree indexes for geometry columns:

SELECT CreateSpatialIndex('locations', 'geom');

Bounding box first

Use bounding box for initial filtering, then precise geometry:

WHERE ST_Intersects(geom, ST_MakeEnvelope(...))  -- Fast
  AND ST_Distance(geom, point, 1) <= radius       -- Precise

Simplify geometries

Reduce complexity for large polygons:

SELECT ST_Simplify(boundary, 0.001)

Cache calculations

Store frequently accessed spatial calculations

Complete Example

-- Schema from coffee vector search example
CREATE TABLE geometry (
  id INTEGER PRIMARY KEY,
  name TEXT,
  geom BLOB CHECK(ST_IsValid(geom))
) STRICT;

INSERT INTO geometry (id, name, geom) VALUES
  (3, 'Colosseo', ST_GeomFromText('POINT(12.4924 41.8902)', 4326)),
  (7, 'A Line', ST_GeomFromText('LINESTRING(10 20, 20 30)', 4326)),
  (8, 'Bottom-right quadrant', ST_MakeEnvelope(0, -90, 180, 0, 4326));

-- Query with GeoJSON output
SELECT 
  id,
  name,
  ST_AsGeoJSON(geom) as geometry
FROM geometry
WHERE ST_Within(
  geom,
  ST_MakeEnvelope(-10, 35, 20, 50, 4326)
);

Next Steps

Vector Search

Combine with semantic search

Custom Endpoints

Build location APIs

Object Storage

Store map tiles

Jobs Scheduler

Periodic geo processing

WebAssembly Runtime

Features

Integration

Geospatial Queries

Overview

Geospatial Data Types

Creating Geometries

From Well-Known Text (WKT)

Using Helper Functions

Coordinate Reference Systems

Spatial Queries

Distance Calculations

Spatial Relationships

Query Parameters

Within Query

Intersects Query

Contains Query

GeoJSON Integration

Requesting GeoJSON

WASM Component Integration

Advanced Queries

Bounding Box Search

Buffer Zones

Nearest Neighbor

Area and Length

Centroid

Real-Time Location Tracking

Geofencing

Performance Tips

Complete Example

Next Steps

Vector Search

Custom Endpoints

Object Storage

Jobs Scheduler

​Overview

​Geospatial Data Types

​Creating Geometries

​From Well-Known Text (WKT)

​Using Helper Functions

​Coordinate Reference Systems

​Spatial Queries

​Distance Calculations

​Spatial Relationships

​Query Parameters

​Within Query

​Intersects Query

​Contains Query

​GeoJSON Integration

​Requesting GeoJSON

​WASM Component Integration

​Advanced Queries

​Bounding Box Search

​Buffer Zones

​Nearest Neighbor

​Area and Length

​Centroid

​Real-Time Location Tracking

​Geofencing

​Performance Tips

​Complete Example

​Next Steps

Vector Search

Custom Endpoints

Object Storage

Jobs Scheduler

Overview

Geospatial Data Types

Creating Geometries

From Well-Known Text (WKT)

Using Helper Functions

Coordinate Reference Systems

Spatial Queries

Distance Calculations

Spatial Relationships

Query Parameters

Within Query

Intersects Query

Contains Query

GeoJSON Integration

Requesting GeoJSON

WASM Component Integration

Advanced Queries

Bounding Box Search

Buffer Zones

Nearest Neighbor

Area and Length

Centroid

Real-Time Location Tracking

Geofencing

Performance Tips

Complete Example

Next Steps