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 }))
}

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

Documentation Index

​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