Geospatial data (Kenneth Live)

The content has been updated since!

CrateDB supports real-time geospatial analytics at scale, enabling you to store, query, and analyze 2D location-based data using standard SQL over two dedicated types: GEO_POINT and GEO_SHAPE. You can seamlessly combine spatial data with full-text, vector, JSON, or time-series in the same SQL queries.

The strength of CrateDB's support for geospatial data includes:

• Designed for real-time geospatial tracking and analytics (e.g., fleet tracking, mapping, location-layered apps)

• Unified SQL platform: spatial data can be combined with full-text search, JSON, vectors, time-series — in the same table or query

• High ingest and query throughput, suitable for large-scale location-based workloads

Geospatial Data Types

CrateDB has two geospatial data types:

geo_point

• Stores a single location via latitude/longitude.

• Insert using

  • coordinate array [lon, lat]

  • Well-Known Text (WKT) string 'POINT (lon lat)'.

• Must be declared explicitly; dynamic schema inference will not detect geo_point type.

geo_shape

• Represents more complex 2D shapes defined via GeoJSON or WKT formats.

• Supported geometry types:

  • Point, MultiPoint

  • LineString, MultiLineString

  • Polygon, MultiPolygon

  • GeometryCollection

• Indexed using geohash, quadtree, or BKD-tree, with configurable precision (e.g. 50m) and error threshold. The indexes are described in the reference manual. You can choose and configure the indexing method when defining your table schema.

Defining a Geospatial Column

Here’s an example of how to define a GEO_SHAPE column with a specific index:

CREATE TABLE parks (
  name TEXT,
  area GEO_SHAPE INDEX USING quadtree
);

Inserting Geospatial Data

You can insert geospatial values using either GeoJSON or WKT formats.

-- Insert a shape (WKT format)
INSERT INTO parks (name, area)
VALUES ('My Park', 'POLYGON ((5 5, 30 5, 30 30, 5 30, 5 5))');

Querying with spacial operations

It is e.g. possible to check if a point is within a park in the table:

SELECT name FROM parks
WHERE within('POINT(10 10)'::geo_shape, area);

CrateDB provides key scalar functions for spatial operations like, distance(), within(), intersects(), area(), geohash() and lattitude()/longitude().

Furthermore, it is possible to use the match predicate with geospatial data in queries.

See the section about searching geospatial data (!!! add link) for details on this.

Further Learning & Resources

• Reference manual:

  • Geo Search

  • Geo functions: distance, within, intersects, lattitude, longitude, geohash, area

• CrateDB Academy Hands-on: Geospatial Data modules, with sample datasets (Chicago 311 calls, taxi rides, community zones) and example queries.

• CrateDB Blog: Geospatial Queries with CrateDB – outlines capabilities, limitations, and practical use cases.