> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Geospatial Queries

> Location-based features and GIS support with SpatiaLite

## 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:

```sql theme={null}
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)

```sql theme={null}
-- 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

```sql theme={null}
-- 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)

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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:

```rust theme={null}
// 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:

```sql theme={null}
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:

```json theme={null}
{
  "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

<CodeGroup>
  ```rust Rust theme={null}
  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 }))
  }
  ```

  ```typescript TypeScript theme={null}
  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);
  });
  ```
</CodeGroup>

## Advanced Queries

### Bounding Box Search

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- Find center point of a polygon
SELECT 
  name,
  ST_AsText(ST_Centroid(boundary)) AS center
FROM regions;
```

## Real-Time Location Tracking

```rust theme={null}
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

```sql theme={null}
-- 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

<Steps>
  <Step title="Use spatial indexes">
    Create R-Tree indexes for geometry columns:

    ```sql theme={null}
    SELECT CreateSpatialIndex('locations', 'geom');
    ```
  </Step>

  <Step title="Bounding box first">
    Use bounding box for initial filtering, then precise geometry:

    ```sql theme={null}
    WHERE ST_Intersects(geom, ST_MakeEnvelope(...))  -- Fast
      AND ST_Distance(geom, point, 1) <= radius       -- Precise
    ```
  </Step>

  <Step title="Simplify geometries">
    Reduce complexity for large polygons:

    ```sql theme={null}
    SELECT ST_Simplify(boundary, 0.001)
    ```
  </Step>

  <Step title="Cache calculations">
    Store frequently accessed spatial calculations
  </Step>
</Steps>

## Complete Example

```sql theme={null}
-- 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

<CardGroup cols={2}>
  <Card title="Vector Search" href="/advanced/vector-search" icon="magnifying-glass">
    Combine with semantic search
  </Card>

  <Card title="Custom Endpoints" href="/advanced/custom-endpoints" icon="route">
    Build location APIs
  </Card>

  <Card title="Object Storage" href="/advanced/object-storage" icon="database">
    Store map tiles
  </Card>

  <Card title="Jobs Scheduler" href="/advanced/jobs-scheduler" icon="clock">
    Periodic geo processing
  </Card>
</CardGroup>