Understanding ClickHouse Ring Type: Storing Simple Polygons as Arrays of Points

  

ClickHouse is a high-performance column-oriented database, often used for analytics. With the increasing demand for handling geospatial data, ClickHouse offers several geometric types. One such type is Ring, which is used to represent simple polygons (without holes). This post introduces the Ring type to beginners using clear examples.

 

What is a Ring in ClickHouse?

A Ring in ClickHouse represents a simple polygon, a closed shape made by connecting a series of straight lines (edges) between points, without any holes.

 

Think of a square or a triangle, a continuous boundary drawn by connecting dots in a specific order. That’s what a Ring represents internally, a list of points (x, y) connected in sequence.

 

What does “without holes” mean in the context of a Ring?

Imagine you're drawing a shape like a square or triangle on paper. If you just draw the outline, that's a simple shape, and that's what a Ring represents: the outer boundary of a shape.

 

Now imagine you draw a donut a circle with a hole in the middle. That’s a polygon with a hole.

 

·      Ring = only the outer boundary.

·      Polygons with holes need a different data structure, like Polygon, which supports both an outer ring and one or more inner rings (holes).

In summary, a Ring is like drawing a simple, solid shape with just one boundary, no cutouts or empty areas inside.

 

Example: Storing Outer Boundary Of a City

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.city_boundaries (
    city_name String,
    boundary Ring
) ENGINE = Memory();

   

Here:

·      city_name: Name of the city.

·      boundary: A Ring that defines the outer boundary (polygon without holes).

 

Let's insert a square boundary for the city 'Amaravathi' with corners at

 

(0,0) → (0,10) → (10,10) → (10,0)

 

INSERT INTO demo_db.city_boundaries VALUES
(
    'Amaravathi',
    [(0, 0), (0, 10), (10, 10), (10, 0)]
);

   

This polygon is closed automatically; we don’t need to repeat (0,0).

 

Let's get the city boundary.

 

SELECT
    city_name,
    boundary,
    toTypeName(boundary) AS type
FROM demo_db.city_boundaries;

krishna :) SELECT
    city_name,
    boundary,
    toTypeName(boundary) AS type
FROM demo_db.city_boundaries;

SELECT
    city_name,
    boundary,
    toTypeName(boundary) AS type
FROM demo_db.city_boundaries

Query id: 487043e3-1327-4ecb-9ac4-6cd0f4055b77

   ┌─city_name──┬─boundary──────────────────────┬─type─┐
1. │ Amaravathi │ [(0,0),(0,10),(10,10),(10,0)] │ Ring │
   └────────────┴───────────────────────────────┴──────┘

1 row in set. Elapsed: 0.004 sec.

  

Previous                                                    Next                                                    Home