MultiPolygon in ClickHouse

  

In ClickHouse, a MultiPolygon is a collection of multiple Polygon shapes, where each polygon can optionally have holes.

 

It is stored as:

Array(Polygon) → Array(Array(Ring))`

   

That means:

Each Polygon = Outer Ring + Zero or more Inner Rings (holes)

MultiPolygon = Multiple such Polygons grouped together

 

For example, a city might consist of:

·      The main urban area

·      A separate industrial zone

·      A farmland owned by the city, all geographically disconnected

 

Each of these zones can be represented as separate Polygons, but together they form a MultiPolygon (one logical city).

 

Let’s model the above city example.

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.cities (
    city_id UInt32,
    city_name String,
    zones MultiPolygon
) ENGINE = MergeTree
ORDER BY city_id;

   

Sample City with three disconnected zones.

·      First Polygon has 1 hole

·      Second Polygon has two holes.

·      Third Polygon has no holes.

INSERT INTO demo_db.cities (city_id, city_name, zones)
VALUES
(
    1,
    'SampleCity',
    [
        [  -- Polygon 1: Main urban area with one hole
            [ (10.0, 20.0), (11.0, 20.0), (11.0, 21.0), (10.0, 21.0), (10.0, 20.0) ], -- outer ring
            [ (10.2, 20.2), (10.8, 20.2), (10.8, 20.8), (10.2, 20.8), (10.2, 20.2) ]  -- hole
        ],
        [  -- Polygon 2: Industrial zone with two holes
            [ (30.0, 40.0), (31.0, 40.0), (31.0, 41.0), (30.0, 41.0), (30.0, 40.0) ], -- outer
            [ (30.2, 40.2), (30.4, 40.2), (30.4, 40.4), (30.2, 40.4), (30.2, 40.2) ], -- hole 1
            [ (30.6, 40.6), (30.8, 40.6), (30.8, 40.8), (30.6, 40.8), (30.6, 40.6) ]  -- hole 2
        ],
        [  -- Polygon 3: Farmland (no holes)
            [ (50.0, 60.0), (51.0, 60.0), (51.0, 61.0), (50.0, 61.0), (50.0, 60.0) ]
        ]
    ]
);

   

Get the City1 data.

 

SELECT *
FROM demo_db.cities
WHERE city_id=1;

krishna :) SELECT *
FROM demo_db.cities
WHERE city_id=1;

SELECT *
FROM demo_db.cities
WHERE city_id = 1

Query id: cdf75a38-cee6-4a72-8daa-66581d412781

   ┌─city_id─┬─city_name──┬─zones──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │       1 │ SampleCity │ [[[(10,20),(11,20),(11,21),(10,21),(10,20)],[(10.2,20.2),(10.8,20.2),(10.8,20.8),(10.2,20.8),(10.2,20.2)]],[[(30,40),(31,40),(31,41),(30,41),(30,40)],[(30.2,40.2),(30.4,40.2),(30.4,40.4),(30.2,40.4),(30.2,40.2)],[(30.6,40.6),(30.8,40.6),(30.8,40.8),(30.6,40.8),(30.6,40.6)]],[[(50,60),(51,60),(51,61),(50,61),(50,60)]]] │
   └─────────┴────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.013 sec.

 

  

Previous                                                    Next                                                    Home