Geo Data Types

Clickhouse supports data types for representing geographical objects — locations, lands, etc.

Warning

Currently geo data types are an experimental feature. To work with them you must set allow_experimental_geo_types = 1.

See Also
- Representing simple geographical features.
- allow_experimental_geo_types setting.

Point

Point is represented by its X and Y coordinates, stored as a Tuple(Float64, Float64).

Example

Query:

  1. SET allow_experimental_geo_types = 1;
  2. CREATE TABLE geo_point (p Point) ENGINE = Memory();
  3. INSERT INTO geo_point VALUES((10, 10));
  4. SELECT p, toTypeName(p) FROM geo_point;

Result:

  1. ┌─p─────┬─toTypeName(p)─┐
  2. │ (10,10) │ Point         │
  3. └───────┴───────────────┘

Ring

Ring is a simple polygon without holes stored as an array of points: Array(Point).

Example

Query:

  1. SET allow_experimental_geo_types = 1;
  2. CREATE TABLE geo_ring (r Ring) ENGINE = Memory();
  3. INSERT INTO geo_ring VALUES([(0, 0), (10, 0), (10, 10), (0, 10)]);
  4. SELECT r, toTypeName(r) FROM geo_ring;

Result:

  1. ┌─r─────────────────────────────┬─toTypeName(r)─┐
  2. │ [(0,0),(10,0),(10,10),(0,10)] │ Ring          │
  3. └───────────────────────────────┴───────────────┘

Polygon

Polygon is a polygon with holes stored as an array of rings: Array(Ring). First element of outer array is the outer shape of polygon and all the following elements are holes.

Example

This is a polygon with one hole:

  1. SET allow_experimental_geo_types = 1;
  2. CREATE TABLE geo_polygon (pg Polygon) ENGINE = Memory();
  3. INSERT INTO geo_polygon VALUES([[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]]);
  4. SELECT pg, toTypeName(pg) FROM geo_polygon;

Result:

  1. ┌─pg────────────────────────────────────────────────────────────┬─toTypeName(pg)─┐
  2. │ [[(20,20),(50,20),(50,50),(20,50)],[(30,30),(50,50),(50,30)]] │ Polygon        │
  3. └───────────────────────────────────────────────────────────────┴────────────────┘

MultiPolygon

MultiPolygon consists of multiple polygons and is stored as an array of polygons: Array(Polygon).

Example

This multipolygon consists of two separate polygons — the first one without holes, and the second with one hole:

  1. SET allow_experimental_geo_types = 1;
  2. CREATE TABLE geo_multipolygon (mpg MultiPolygon) ENGINE = Memory();
  3. INSERT INTO geo_multipolygon VALUES([[[(0, 0), (10, 0), (10, 10), (0, 10)]], [[(20, 20), (50, 20), (50, 50), (20, 50)],[(30, 30), (50, 50), (50, 30)]]]);
  4. SELECT mpg, toTypeName(mpg) FROM geo_multipolygon;

Result:

  1. ┌─mpg─────────────────────────────────────────────────────────────────────────────────────────────┬─toTypeName(mpg)─┐
  2. │ [[[(0,0),(10,0),(10,10),(0,10)]],[[(20,20),(50,20),(50,50),(20,50)],[(30,30),(50,50),(50,30)]]] │ MultiPolygon    │
  3. └─────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────┘

Original article