Understanding the Polygon Type in ClickHouse

  

When working with geospatial data in ClickHouse, shapes like points and lines are easy to grasp. But how do you model complex areas, like a city boundary that has lakes inside it?

That’s where the Polygon type shines, it allows you to define areas with or without holes, such as:

 

·      A park with a pond in the middle

·      A city excluding restricted zones

·      A farmland area with structures in the center

 

ClickHouse makes this possible with the Polygon type, which is built from rings.

 

1. What is a Polygon?

In ClickHouse, a Polygon is an Array(Ring). A Ring is an Array(Point) (a closed shape with no holes)

 

The Polygon has:

·      The first Ring is the outer boundary

·      The subsequent Rings are the holes

 

2. Real World Example:

Imagine you're modeling a city park that includes:

 

·      The outer boundary of the park

·      A few buildings inside the park

·      One or more ponds in the park

 

In this case:

·      The outer ring defines the full area of the park.

·      The holes (inner rings) represent the buildings and ponds, which are excluded areas (no trees, no lawns, etc.,)

 

This is a perfect use case for a Polygon with holes, because:

·      You want to model the space accurately.

·      You need to subtract internal non-park areas from the overall polygon area.

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.city_parks (
    park_id UInt32,
    name String,
    area Polygon
) ENGINE = MergeTree ORDER BY park_id;

-- Outer ring is the park boundary, inner rings are building and pond exclusions
INSERT INTO demo_db.city_parks VALUES (
    1, 'Central Park',
    [[(0,0), (100,0), (100,100), (0,100)],       -- outer boundary
     [(20,20), (30,20), (30,30), (20,30)],       -- building
     [(60,60), (70,60), (70,70), (60,70)]]       -- pond
);

SELECT name, area FROM demo_db.city_parks WHERE park_id = 1;

krishna :) SELECT name, area FROM demo_db.city_parks WHERE park_id = 1;

SELECT
    name,
    area
FROM demo_db.city_parks
WHERE park_id = 1

Query id: 8db4a7b3-aa87-4e23-8bba-7fa55cf0543f

   ┌─name─────────┬─area────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Central Park │ [[(0,0),(100,0),(100,100),(0,100)],[(20,20),(30,20),(30,30),(20,30)],[(60,60),(70,60),(70,70),(60,70)]] │
   └──────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

  

Polygons with holes make spatial data far more precise, supporting use cases like urban planning, disaster management, and autonomous navigation.

 

Previous                                                    Next                                                    Home