Geo Data Types in ClickHouse

ClickHouse has an in-built data type to store geographical objects. The following data types to store and work with geographical objects are currently supported in ClickHouse (23.8 LTS).

Point

A point is a primitive notion that models an exact location in space and has no length, width, or thickness. A point is represented as a tuple containing X and Y coordinates. The only supported data type for X and Y coordinates is Float64.

Ring

A ring is a polygon without holes in it and consists of an array of points.

Polygon

A polygon consists of a polygon with one or more holes (rings). It is stored as an array of rings.

MultiPolygon

As the name suggests, a multi polygon has multiple polygons and is stored as an array of polygons.

Example

Let us create a table with these data types and see how to insert the data.

CREATE TABLE geo_example
(
point_type Point,
ring_type Ring,
polygon_type Polygon,
multipolygon_type MultiPolygon
)
Engine = Log;

Let us insert some data into the table.

INSERT INTO geo_example VALUES(
(10, 10),
[(1,1), (1,2), (2,1), (2,2)],
[[(10,10), (10,20), (20,20), (20,10)], [(1,1), (1,2), (2,2), (2,1)]],
[[[(10,10), (10,20), (20,20), (20,10)], [(1,1), (1,2), (2,2), (2,1)]],
 [[(100,100), (100,200), (200,200), (200,100)], [(10,10), (10,20), (20,20), (20,10)]]]
);

Let us query the data after it is inserted.

SELECT *
FROM geo_example
FORMAT Vertical

Query id: 639d64b6-265b-4b05-bd4e-16c2c2c82eb3

Row 1:
──────
point_type:        (10,10)
ring_type:         [(1,1),(1,2),(2,1),(2,2)]
polygon_type:      [[(10,10),(10,20),(20,20),(20,10)],[(1,1),(1,2),(2,2),(2,1)]]
multipolygon_type: [[[(10,10),(10,20),(20,20),(20,10)],[(1,1),(1,2),(2,2),(2,1)]],[[(100,100),(100,200),(200,200),(200,100)],[(10,10),(10,20),(20,20),(20,10)]]]

1 row in set. Elapsed: 0.004 sec. 

References

https://clickhouse.com/docs/en/sql-reference/data-types/geo#polygon

https://clickhouse.com/docs/en/sql-reference/functions/geo/coordinates

https://carto.com/blog/geospatial-processing-with-clickhouse