Implementing Geospatial Queries in SQLite

How to Store, Search, and Analyze Location Data Using SQLite

Location-based analytics has become a major part of modern applications, whether you’re tracking delivery routes, analyzing store performance, or powering a “find-nearby” feature in a mobile app. The good news? SQLite can support geospatial data and queries surprisingly well, even without heavyweight GIS engines.

In this blog, you’ll learn how to store location data, run spatial queries, calculate distances, and build simple geospatial analytics using SQLite. We’ll walk through real-world examples like searching for nearby restaurants, analyzing delivery heatmaps, and tracking movement over time.

Before we dive in, if you’re new to handling synced data in distributed or offline systems, check out our blog on Ensuring Data Integrity in SQLite Across Devices - especially helpful if you’re storing geospatial data across multiple user devices.

1. Understanding Geospatial Data in SQLite

SQLite does not ship with a built-in spatial extension like PostGIS for PostgreSQL. But you can still:

• Store latitude & longitude in standard columns

• Perform distance calculations (Haversine formula)

• Run bounding-box searches

• Integrate with extensions like SpatiaLite for advanced GIS workloads

For most apps (food delivery, fitness tracking, property search, ride-hailing), basic spatial queries are more than enough.

Example: Create a table for locations

CREATE TABLE places (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL
);

This stores any point on Earth using decimal degrees.

2. Real-World Scenario: Finding Nearby Restaurants

Imagine you’re building a “Find Nearby Restaurants” feature.

A user shares their current location:

• Latitude: 1.3002

• Longitude: 103.8455

Before running distance-heavy queries, we first reduce the search space using a bounding box.

Bounding Box Query

SELECT *
FROM places
WHERE latitude  BETWEEN 1.20 AND 1.40
  AND longitude BETWEEN 103.70 AND 104.00;

This filters places roughly around the user.

Precise Distance Using Haversine

SELECT
    name,
    (
        6371 * acos(
            cos(radians(1.3002)) *
            cos(radians(latitude)) *
            cos(radians(longitude) - radians(103.8455)) +
            sin(radians(1.3002)) *
            sin(radians(latitude))
        )
    ) AS distance_km
FROM places
ORDER BY distance_km ASC
LIMIT 10;

What this does:

• Computes the real geographic distance

• Returns the closest 10 places

This is the heart of many location-based apps.

3. Heatmaps & Location Analytics

Location-based analytics helps businesses make smarter decisions:

• Retail chains: Which areas see the most customer activity?

• Delivery companies: Where are the busiest delivery clusters?

• Transport planners: Which regions need more pickup density?

To generate such insights, we often group coordinates into regions.

Example: Count visits within a grid tile

SELECT
    ROUND(latitude, 2) AS lat_tile,
    ROUND(longitude, 2) AS lng_tile,
    COUNT(*) AS visit_count
FROM user_visits
GROUP BY lat_tile, lng_tile
ORDER BY visit_count DESC;

This helps us visualize “hot regions” on a map.

4. Tracking Movement Over Time (Time-Series + Geo)

If your system tracks movement (fitness app, fleet management, IoT trackers), you’ll combine geospatial and time-series data.

Create a tracking table

CREATE TABLE location_history (
    user_id INTEGER,
    timestamp TEXT,
    latitude REAL,
    longitude REAL
);

Example: Calculate distance between consecutive points

SELECT
    user_id,
    timestamp,
    latitude,
    longitude,
    (
        6371 * acos(
            cos(radians(latitude)) *
            cos(radians(LAG(latitude) OVER (PARTITION BY user_id ORDER BY timestamp))) *
            cos(radians(LAG(longitude) OVER (PARTITION BY user_id ORDER BY timestamp)) - radians(longitude)) +
            sin(radians(latitude)) *
            sin(radians(LAG(latitude) OVER (PARTITION BY user_id ORDER BY timestamp)))
        )
    ) AS distance_from_previous_km
FROM location_history
WHERE user_id = 1;

This is the basis of:

• Running routes

• Distance travelled in a day

• Delivery route optimization

• IoT asset movement tracking

By the way, if you want to learn how to handle real-time data in SQLite, read our blog on Real-Time Analytics with SQLite. It pairs beautifully with location history tracking.

5. Adding Geospatial Indexing for Faster Queries

Searching across thousands of coordinates can get slow.

Use Expression Indexes

SQLite allows indexing computed expressions.

Index on latitude grid

CREATE INDEX idx_places_lat_grid
ON places (ROUND(latitude, 2));

Index on longitude grid

CREATE INDEX idx_places_lng_grid
ON places (ROUND(longitude, 2));

This speeds up bounding-box and grid analytics dramatically.

6. Using SpatiaLite for Advanced GIS Features (Optional)

If you need real GIS capabilities like:

• Polygons

• Intersections

• Buffer zones

• Spatial joins

• Geofencing

Then SpatiaLite is the SQLite version of PostGIS.

Example: Points within a polygon

SELECT name
FROM places
WHERE ST_Contains(
    ST_GeomFromText(’POLYGON((...))’),
    MakePoint(longitude, latitude)
);

This is useful for:

• District-based analytics

• Serviceable area mapping

• Geofencing alerts

If you’re working across distributed devices and syncing geospatial datasets, our in-depth post Integrating SQLite with Cloud Databases will help you manage complex sync workflows.

7. Building a Location-Based Report Dashboard (Case Study)

Scenario: Food Delivery Company

You want to build:

• A heatmap of where orders originate

• A “top busy zones” panel

• A courier distance tracker

• A “restaurants nearby” recommendation engine

Sample Query: Delivery hotspots

SELECT
    ROUND(pickup_lat, 3) AS lat_bin,
    ROUND(pickup_lng, 3) AS lng_bin,
    COUNT(*) AS orders
FROM deliveries
GROUP BY lat_bin, lng_bin
ORDER BY orders DESC
LIMIT 20;

Sample Query: Fast routing suggestion

Use shortest-distance ordering:

SELECT
    restaurant_id,
    (
        6371 * acos(
            cos(radians(:user_lat)) *
            cos(radians(latitude)) *
            cos(radians(longitude) - radians(:user_lng)) +
            sin(radians(:user_lat)) *
            sin(radians(latitude))
        )
    ) AS distance
FROM restaurants
ORDER BY distance ASC
LIMIT 5;

This builds the foundation for a lightweight location-based dashboard in SQLite.

Conclusion

SQLite may not be a full GIS engine, but it is surprisingly powerful for real-world geospatial workloads:

• Storing GPS points

• Searching nearby locations

• Generating heatmaps

• Running analytics

• Tracking movement

• Building recommendation engines

• Performing routing-like queries

By combining bounding-box filters, Haversine distance, binning, indexing, and (optionally) SpatiaLite, you can build fast, scalable location-based features right inside SQLite.

As your application grows and needs to sync data across phones or edge devices, you’ll also want strong syncing logic. Our blog on Building Offline-First Applications with SQLite and Sync Strategies is a great follow-up read.

SQLite continues to prove it’s more than a local database. It’s a powerful engine capable of real analytics, even when handling geospatial data.

Subscribe Now

Stay ahead with practical SQLite tutorials, edge computing insights, and real-world IoT project examples. Join the SQLite Forum and be part of a growing global community of developers building smarter, faster applications.