ClickHouse® Geospatial Functions for Location Data

Introduction

Location data has become an essential part of modern applications, powering services such as ride-sharing, logistics, food delivery, mapping, fleet management, and location-based analytics. Analyzing geographical coordinates efficiently is crucial when working with large datasets.

ClickHouse® provides built-in geospatial functions that enable you to calculate distances, work with latitude and longitude coordinates, generate geohashes, and perform location-based analysis directly in SQL.

In this guide, you'll learn the most commonly used ClickHouse® geospatial functions with practical examples.

What Are Geospatial Functions?

Geospatial functions perform calculations using geographical coordinates such as latitude and longitude.

These functions help answer questions like:

• How far apart are two locations?
• Which customers are within a specific radius?
• Which delivery driver is nearest?
• How can nearby locations be grouped?
• How can location data be indexed efficiently?

These functions are widely used in mapping, transportation, logistics, and business intelligence.

Sample Table

We'll use the following table throughout this guide:

CREATE TABLE delivery_locations
(
    DriverID    UInt32,
    DriverName  String,
    Latitude    Float64,
    Longitude   Float64
)
ENGINE = MergeTree
ORDER BY DriverID;

Sample data:

Why Use Geospatial Functions?

Instead of exporting location data to GIS tools, ClickHouse® allows you to analyze geographical information directly within SQL, reducing data movement and improving query performance.

Benefits include:

• Faster analytics
• Real-time location processing
• Reduced data movement
• Simpler analytical workflows

Common ClickHouse® Geospatial Functions

1. greatCircleDistance()

Calculates the shortest distance between two points on Earth's surface.

For example, suppose a customer is located in Bengaluru. The following query calculates the distance between each driver's location and the customer's location:

SELECT
    DriverName,
    greatCircleDistance(
        Longitude,   -- Driver's Longitude (from table)
        Latitude,    -- Driver's Latitude (from table)
        77.5946,     -- Bengaluru Longitude
        12.9716      -- Bengaluru Latitude
    ) AS DistanceInMeters
FROM delivery_locations;

Output:

Use cases:

• Find the nearest driver
• Delivery distance calculation
• Fleet management
• Nearby store search

2. geoDistance()

Calculates a highly accurate distance between two geographical points using Earth's ellipsoid model.

SELECT
    DriverName,
    geoDistance(
        Longitude,
        Latitude,
        77.5946,
        12.9716
    ) AS Distance
FROM delivery_locations;

Output:

Use cases:

• GPS applications
• Navigation systems
• Logistics

Compared to greatCircleDistance(), geoDistance() provides slightly higher accuracy and is commonly used in GPS and navigation applications.

3. geohashEncode()

Encodes latitude and longitude into a Geohash string.

SELECT
    DriverName,
    geohashEncode(
        Longitude,
        Latitude,
        8
    ) AS Geohash
FROM delivery_locations;

Output:

Note: Nearby locations often produce similar Geohash values, making them useful for location grouping and indexing.

Use cases:

• Spatial indexing
• Nearby location searches
• Location clustering

4. geohashDecode()

Converts a Geohash back into latitude and longitude:

SELECT geohashDecode('tdr1v9qh');

Output:

5. pointInPolygon()

Determines whether a geographical point lies inside a specified polygon. It returns 1 if the point is inside the polygon and 0 if it is outside.

Suppose we want to check whether a driver's location falls within a predefined delivery zone:

SELECT
    DriverName,
    pointInPolygon(
        (Longitude, Latitude),
        [
            (80.20, 13.00),
            (80.35, 13.00),
            (80.35, 13.15),
            (80.20, 13.15)
        ]
    ) AS IsInsideZone
FROM delivery_locations;

Output:

Explanation:

• (Longitude, Latitude) - Driver's current location
• The list of coordinate pairs defines a polygon (delivery zone)
• 1 indicates the driver is inside the delivery zone
• 0 indicates the driver is outside the delivery zone

Use cases:

• Geofencing
• Delivery zone validation
• Store coverage analysis
• Service area verification
• Asset tracking

6. Finding Nearby Drivers

Find drivers within a 300 km radius:

SELECT *
FROM
(
    SELECT
        DriverName,
        greatCircleDistance(
            Longitude,
            Latitude,
            77.5946,
            12.9716
        ) AS Distance
    FROM delivery_locations
)
WHERE Distance <= 300000;

Output:

7. Sorting by Distance

Order locations from nearest to farthest:

SELECT
    DriverName,
    greatCircleDistance(
        Longitude,
        Latitude,
        77.5946,
        12.9716
    ) AS Distance
FROM delivery_locations
ORDER BY Distance;

Output:

Real-World Applications

Geospatial functions are widely used across many industries:

Performance Tips

For better performance:

• Store coordinates as Float64
• Filter records before calculating distances
• Use ORDER BY efficiently in MergeTree tables
• Use Geohashes for location grouping
• Avoid unnecessary distance calculations on very large datasets

Final Thoughts

ClickHouse® geospatial functions make it easy to analyze location data without relying on external GIS tools. Whether you're calculating distances, locating nearby drivers, generating geohashes, or building location-based analytics, these built-in SQL functions provide both simplicity and performance.

By combining geospatial functions with ClickHouse®'s columnar storage and high-speed query engine, you can build scalable applications for logistics, fleet management, IoT, ride-sharing, retail, and many other location-aware use cases.

References

• ClickHouse® Documentation - Geo Functions
• ClickHouse® Documentation - greatCircleDistance
• ClickHouse® Documentation - geohashEncode
• ClickHouse® Documentation - pointInPolygon