Skip to content

SpatiaLite Geospatial

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

SpatiaLite Geospatial

Last Updated: September 23, 2025 1:48 PM EST

The SQLite MCP Server provides comprehensive geospatial capabilities through SpatiaLite integration, enabling advanced geographic information systems (GIS) functionality directly within SQLite.

🌍 Geospatial Capabilities

When SpatiaLite is available, the SQLite MCP Server provides:

  • Spatial Data Types: Points, lines, polygons, multi-geometries
  • Coordinate Reference Systems: Full CRS support with transformations
  • Spatial Indexes: R-Tree spatial indexing for performance
  • Geometric Operations: Distance, intersection, buffering, and more
  • Import/Export: Shapefile, GeoJSON, KML, and other formats
  • Spatial Analysis: Complex geospatial queries and analysis

🔧 Available Geospatial Tools

Tool Description
spatialite_init Initialize SpatiaLite extension and create spatial metadata
create_spatial_table Create tables with geometry columns
spatial_index Create spatial indexes for performance
import_shapefile Import Shapefile data into spatial tables
export_geojson Export spatial data to GeoJSON format
spatial_query Execute spatial queries with geometric operations

🚀 Getting Started

Initialize SpatiaLite

// Initialize SpatiaLite extension
spatialite_init({
  "create_metadata": true,
  "init_spatial_ref_sys": true
})

Create Spatial Tables

// Create a table for storing points of interest
create_spatial_table({
  "table_name": "points_of_interest",
  "geometry_column": "geom",
  "geometry_type": "POINT",
  "srid": 4326,  // WGS84 geographic coordinates
  "columns": {
    "id": "INTEGER PRIMARY KEY",
    "name": "TEXT",
    "category": "TEXT",
    "description": "TEXT"
  }
})

// Create a table for administrative boundaries
create_spatial_table({
  "table_name": "admin_boundaries",
  "geometry_column": "boundary",
  "geometry_type": "POLYGON",
  "srid": 4326,
  "columns": {
    "id": "INTEGER PRIMARY KEY",
    "name": "TEXT",
    "level": "INTEGER",
    "population": "INTEGER"
  }
})

📍 Working with Spatial Data

Insert Spatial Data

// Insert point data
write_query({
  "query": `
    INSERT INTO points_of_interest (name, category, geom)
    VALUES (?, ?, GeomFromText('POINT(-122.4194 37.7749)', 4326))
  `,
  "params": ["San Francisco City Hall", "government"]
})

// Insert polygon data
write_query({
  "query": `
    INSERT INTO admin_boundaries (name, level, boundary)
    VALUES (?, ?, GeomFromText(?, 4326))
  `,
  "params": [
    "San Francisco County",
    2,
    "POLYGON((-122.5 37.7, -122.3 37.7, -122.3 37.8, -122.5 37.8, -122.5 37.7))"
  ]
})

Spatial Queries

// Find points within a distance
spatial_query({
  "query": `
    SELECT name, category, 
           Distance(geom, GeomFromText('POINT(-122.4194 37.7749)', 4326)) as distance_meters
    FROM points_of_interest
    WHERE Distance(geom, GeomFromText('POINT(-122.4194 37.7749)', 4326)) < 1000
    ORDER BY distance_meters
  `
})

// Find polygons that contain a point
spatial_query({
  "query": `
    SELECT name, level
    FROM admin_boundaries
    WHERE Contains(boundary, GeomFromText('POINT(-122.4194 37.7749)', 4326))
  `
})

🗺️ Advanced Geospatial Operations

Buffering and Analysis

// Create buffer zones around points
spatial_query({
  "query": `
    SELECT name,
           AsText(Buffer(geom, 0.01)) as buffer_geometry
    FROM points_of_interest
    WHERE category = 'school'
  `
})

// Find intersections between geometries
spatial_query({
  "query": `
    SELECT p.name as poi_name, a.name as boundary_name
    FROM points_of_interest p, admin_boundaries a
    WHERE Intersects(p.geom, a.boundary)
  `
})

Coordinate System Transformations

// Transform coordinates between systems
spatial_query({
  "query": `
    SELECT name,
           AsText(Transform(geom, 3857)) as web_mercator_coords,
           AsText(geom) as wgs84_coords
    FROM points_of_interest
    LIMIT 5
  `
})

📊 Spatial Indexing

Create Spatial Indexes

// Create spatial index for performance
spatial_index({
  "table_name": "points_of_interest",
  "geometry_column": "geom"
})

// Verify spatial index usage
read_query({
  "query": `
    EXPLAIN QUERY PLAN
    SELECT name FROM points_of_interest
    WHERE MbrWithin(geom, BuildMbr(-122.5, 37.7, -122.3, 37.8))
  `
})

📥 Data Import/Export

Import Shapefile Data

// Import shapefile
import_shapefile({
  "shapefile_path": "./data/boundaries.shp",
  "table_name": "imported_boundaries",
  "srid": 4326,
  "encoding": "UTF-8"
})

Export to GeoJSON

// Export spatial data to GeoJSON
export_geojson({
  "table_name": "points_of_interest",
  "geometry_column": "geom",
  "output_file": "./exports/poi.geojson",
  "where_clause": "category = 'restaurant'"
})

💡 Real-World Use Cases

Location-Based Services

// Find nearby restaurants
spatial_query({
  "query": `
    SELECT name, category,
           Distance(geom, GeomFromText(?, 4326)) as distance
    FROM points_of_interest
    WHERE category = 'restaurant'
    AND Distance(geom, GeomFromText(?, 4326)) < 500
    ORDER BY distance
    LIMIT 10
  `,
  "params": [user_location_wkt, user_location_wkt]
})

Urban Planning Analysis

// Analyze population density
spatial_query({
  "query": `
    SELECT b.name,
           b.population / Area(b.boundary) as density,
           COUNT(p.id) as poi_count
    FROM admin_boundaries b
    LEFT JOIN points_of_interest p ON Contains(b.boundary, p.geom)
    WHERE b.level = 3
    GROUP BY b.id, b.name, b.population, b.boundary
    ORDER BY density DESC
  `
})

Environmental Monitoring

// Find monitoring stations within affected areas
spatial_query({
  "query": `
    SELECT m.station_name, m.sensor_type,
           Distance(m.location, p.incident_location) as distance
    FROM monitoring_stations m, pollution_incidents p
    WHERE Distance(m.location, p.incident_location) < 5000
    AND p.incident_date > date('now', '-7 days')
  `
})

🎯 Best Practices

1. Choose Appropriate Coordinate Systems

// Use geographic coordinates (WGS84) for global data
create_spatial_table({
  "table_name": "global_locations",
  "geometry_type": "POINT",
  "srid": 4326  // WGS84
})

// Use projected coordinates for local analysis
create_spatial_table({
  "table_name": "local_analysis",
  "geometry_type": "POLYGON", 
  "srid": 3857  // Web Mercator for web mapping
})

2. Always Create Spatial Indexes

// Create spatial indexes for performance
spatial_index({
  "table_name": "large_polygon_table",
  "geometry_column": "geom"
})

3. Use Appropriate Geometry Types

// Use specific geometry types when possible
create_spatial_table({
  "geometry_type": "POINT",      // Not GEOMETRY
  "srid": 4326
})

4. Validate Spatial Data

// Check for invalid geometries
spatial_query({
  "query": `
    SELECT id, name
    FROM spatial_table
    WHERE NOT IsValid(geom)
  `
})

📚 Related Pages

🌍 Geospatial Tip: SpatiaLite transforms SQLite into a powerful GIS database. Always create spatial indexes for large datasets and choose appropriate coordinate reference systems for your use case.

Clone this wiki locally