Polygon dictionaries

Configure polygon dictionaries for point-in-polygon lookups.

The polygon (POLYGON) dictionary is optimized for point-in-polygon queries, essentially “reverse geocoding” lookups. Given a coordinate (latitude/longitude), it efficiently finds which polygon/region (from a set of many polygons, such as country or region boundaries) contains that point. It’s well-suited for mapping location coordinates to their containing region.

Example of configuring a polygon dictionary:

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

DDL

CREATE DICTIONARY polygon_dict_name (
    key Array(Array(Array(Array(Float64)))),
    name String,
    value UInt64
)
PRIMARY KEY key
LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))
...

Configuration file

<dictionary>
    <structure>
        <key>
            <attribute>
                <name>key</name>
                <type>Array(Array(Array(Array(Float64))))</type>
            </attribute>
        </key>

        <attribute>
            <name>name</name>
            <type>String</type>
            <null_value></null_value>
        </attribute>

        <attribute>
            <name>value</name>
            <type>UInt64</type>
            <null_value>0</null_value>
        </attribute>
    </structure>

    <layout>
        <polygon>
            <store_polygon_key_column>1</store_polygon_key_column>
        </polygon>
    </layout>

    ...
</dictionary>

When configuring the polygon dictionary, the key must have one of two types:

• A simple polygon. It is an array of points.
• MultiPolygon. It is an array of polygons. Each polygon is a two-dimensional array of points. The first element of this array is the outer boundary of the polygon, and subsequent elements specify areas to be excluded from it.

Points can be specified as an array or a tuple of their coordinates. In the current implementation, only two-dimensional points are supported.

The user can upload their own data in all formats supported by ClickHouse.

There are 3 types of in-memory storage available:

Layout	Description
POLYGON_SIMPLE	Naive implementation. A linear pass through all polygons is made for each query, checking membership without additional indexes.
POLYGON_INDEX_EACH	A separate index is built for each polygon, allowing fast membership checks in most cases (optimized for geographical regions). A grid is superimposed on the area, recursively dividing cells into 16 equal parts. Division stops when recursion depth reaches MAX_DEPTH or a cell crosses no more than MIN_INTERSECTIONS polygons.
POLYGON_INDEX_CELL	Also creates the grid described above with the same options. For each leaf cell, an index is built on all polygon pieces that fall into it, allowing fast query responses.
POLYGON	Synonym for POLYGON_INDEX_CELL.

Dictionary queries are carried out using standard functions for working with dictionaries. An important difference is that here the keys will be the points for which you want to find the polygon containing them.

Example

Example of working with the dictionary defined above:

CREATE TABLE points (
    x Float64,
    y Float64
)
...
SELECT tuple(x, y) AS key, dictGet(dict_name, 'name', key), dictGet(dict_name, 'value', key) FROM points ORDER BY x, y;

As a result of executing the last command for each point in the ‘points’ table, a minimum area polygon containing this point will be found, and the requested attributes will be output.

Example

You can read columns from polygon dictionaries via SELECT query, just turn on the store_polygon_key_column = 1 in the dictionary configuration or corresponding DDL-query.

Query

CREATE TABLE polygons_test_table
(
    key Array(Array(Array(Tuple(Float64, Float64)))),
    name String
) ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO polygons_test_table VALUES ([[[(3, 1), (0, 1), (0, -1), (3, -1)]]], 'Value');

CREATE DICTIONARY polygons_test_dictionary
(
    key Array(Array(Array(Tuple(Float64, Float64)))),
    name String
)
PRIMARY KEY key
SOURCE(CLICKHOUSE(TABLE 'polygons_test_table'))
LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))
LIFETIME(0);

SELECT * FROM polygons_test_dictionary;

Response

┌─key─────────────────────────────┬─name──┐
│ [[[(3,1),(0,1),(0,-1),(3,-1)]]] │ Value │
└─────────────────────────────────┴───────┘