Getting Started with the Geometric Point Type in ClickHouse

  

ClickHouse, a high-performance columnar OLAP database, supports various data types to handle different kinds of data efficiently. One of the lesser-known but powerful features is its support for geometric data types, such as the Point type, which is especially useful for geospatial analysis and location-based queries. In this post, I am going to explain the Point type, show you how it’s represented, and demonstrate how to use it in practice.

 

What is the Point Type in ClickHouse?

The Point type in ClickHouse is used to represent a two-dimensional point in space. Internally, it is stored as a Tuple(Float64, Float64), where the two floating-point numbers represent the X and Y coordinates respectively.

 

This structure makes it easy to store, query, and manipulate spatial data using familiar SQL syntax and ClickHouse functions.

 

Since Point is just a wrapper over Tuple(Float64, Float64), it's worth understanding how tuples behave in ClickHouse:

 

·      A Tuple can contain multiple elements of different types.

·      Tuples can be used for  column grouping, especially useful with IN expressions and lambda functions.

·      When used in SELECT queries, tuple values are shown in parentheses, comma-separated.

·      In JSON-based formats, tuples are output as arrays in square brackets.

 

So, a Point is functionally equivalent to a tuple with two Float64 values, but it gains semantic meaning as a spatial data type.

 

Let's take a real-world example using the Point type in ClickHouse, with DDLs (table creation), sample inserts, and selects. These scenarios show how you might use Point for things like store locations, user check-ins, vehicle tracking, or IoT devices.

 

Example 1: Track the store locations.

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.store_locations (
    store_id UInt32,
    store_name String,
    location Point
) ENGINE = MergeTree()
ORDER BY store_id;

INSERT INTO demo_db.store_locations VALUES
(1, 'Downtown', (40.7128, -74.0060)),
(2, 'Target Uptown', (34.0522, -118.2437)),
(3, 'Best Buy Central', (41.8781, -87.6298));

   

Let’s print the latitude and longitude locations of the stores.

 

krishna :) SELECT store_name, location, location.1 AS latitude, location.2 AS longitude
FROM demo_db.store_locations;

SELECT
    store_name,
    location,
    location.1 AS latitude,
    location.2 AS longitude
FROM demo_db.store_locations

Query id: 3046d365-669d-4cc3-a742-df414fa61f87

   ┌─store_name───────┬─location────────────┬─latitude─┬─longitude─┐
1. │ Downtown         │ (40.7128,-74.006)   │  40.7128 │   -74.006 │
2. │ Target Uptown    │ (34.0522,-118.2437) │  34.0522 │ -118.2437 │
3. │ Best Buy Central │ (41.8781,-87.6298)  │  41.8781 │  -87.6298 │
   └──────────────────┴─────────────────────┴──────────┴───────────┘

3 rows in set. Elapsed: 0.004 sec.

   

Example 2: User Check-ins (Social App)

 

CREATE TABLE demo_db.user_checkins (
    user_id UInt64,
    username String,
    checkin_time DateTime,
    location Point
) ENGINE = MergeTree()
ORDER BY (user_id, checkin_time);

INSERT INTO demo_db.user_checkins VALUES
(101, 'ram', now(), (37.7749, -122.4194)),
(102, 'krishna', now(), (47.6062, -122.3321)),
(103, 'chamu', now(), (29.7604, -95.3698));

   

This query filters users who checked in somewhere between given latitude and longitude.

 

SELECT username, checkin_time, location.1 AS lat, location.2 AS lon
FROM demo_db.user_checkins
WHERE location.1 > 30 AND location.2 < -90;

krishna :) SELECT username, checkin_time, location.1 AS lat, location.2 AS lon
FROM demo_db.user_checkins
WHERE location.1 > 30 AND location.2 < -90;

SELECT
    username,
    checkin_time,
    location.1 AS lat,
    location.2 AS lon
FROM demo_db.user_checkins
WHERE ((location.1) > 30) AND ((location.2) < -90)

Query id: 2e2e73f4-1b27-462d-916c-1f0d5388680d

   ┌─username─┬────────checkin_time─┬─────lat─┬───────lon─┐
1. │ ram      │ 2025-05-18 21:37:12 │ 37.7749 │ -122.4194 │
2. │ krishna  │ 2025-05-18 21:37:12 │ 47.6062 │ -122.3321 │
   └──────────┴─────────────────────┴─────────┴───────────┘

2 rows in set. Elapsed: 0.007 sec.

 

 

 

Previous                                                    Next                                                    Home