Geospatial Search

CrateDB supports location data for efficiently storing and querying geographic and spatial/geospatial data.

Overview

CrateDB can be used as a database to conduct geospatial search operations building upon the Prefix Tree and BKD-tree index structures of Apache Lucene.

About

Using spatial search, you can:

• Index points or other shapes.

• Filter search results by a bounding box, circle, donut, or other shape.

• Sort or boost scoring by distance between points, or relative area between rectangles.

Details

CrateDB’s GEO_POINT and GEO_SHAPE geographic data types represent points or shapes in a 2D world.

GEO_POINT:

A geographic data type used to store latitude and longitude coordinates.

GEO_SHAPE:

A geographic data type used to store 2D shapes defined as GeoJSON geometry objects. It supports Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, and GeometryCollection.

When inserting spatial data, you can use GeoJSON or WKT formats.

• Geographic points can be inserted as a double precision array with longitude and latitude values, or by using a WKT string.

• Geographic shapes can be inserted as GeoJSON object literal or parameter as seen above and as WKT string.

Reference Manual

• Geometric points

• Geometric shapes

• Geo search

• distance()

• within()

• intersects()

• latitude() and longitude()

• geohash()

• area()

SQLAlchemy

• Geopoint and Geoshape types

• Working with geospatial types

Related

• SQL

• Full-Text Search

• Vector Search

• Hybrid Search

• Advanced Querying

SQL Geospatial GeoJSON WKT

About Lucene

CrateDB uses Lucene as a storage layer, so it inherits the implementation and concepts of Lucene, in the same spirit like Solr and Elasticsearch.

• Geospatial Indexing & Search at Scale with Lucene

• Geospatial Indexing with Apache Lucene and OpenSearch

• Apache Solr Spatial Search

While Elasticsearch uses a query DSL based on JSON, in CrateDB, you can work with geospatial data using SQL.

Synopsis

Select data points by distance.

/**
 * Based on the location of the International Space Station,
 * this query returns the 10 closest capital cities from
 * the last known position.
**/

SELECT
  city AS "City Name",
  country AS "Country",
  DISTANCE(i.position, c.location)::LONG / 1000
    AS "Distance [km]"
FROM demo.iss i
CROSS JOIN demo.world_cities c
WHERE capital = 'primary'
  AND ts = (SELECT MAX(ts) FROM demo.iss)
ORDER BY 3 ASC
LIMIT 10;

Usage

Using geographic search in CrateDB.

Index Structure Type

Computations on very complex polygons and geometry collections are exact but very expensive. To provide fast queries even on complex shapes, CrateDB uses a different approach to store, analyze and query geo shapes. The available geo shape indexing strategies are based on two primary data structures: Prefix and BKD trees, which are described below.

There are three geographic index types: geohash (default), quadtree, and bkdtree, described in more detail at Geo shape index structure.

Column Definition

Learn how to define a GEO_SHAPE column, and how to adjust parameters of the index structure in the documentation section about Geo shape column definition.

MATCH predicate

CrateDB’s MATCH predicate for geographical search can be used to query geographic indices for relations between geographical shapes and points. It supports the intersects, disjoint, and within operations.

Learn

Learn how to use CrateDB’s geospatial data types through ORM adapters, tutorials, or example applications.

Articles

• Geometric Shapes Indexing with BKD-trees

Applications

• Spatial data demo application using CrateDB and the Express framework

• Plane Spotting with Software Defined Radio (SDN), CrateDB and Node.js

Tutorials

• Geospatial Queries with CrateDB

• Berlin and Geo Shapes in CrateDB

Videos

Getting Started with Geospatial Data in CrateDB

Discover how to effortlessly create a table and seamlessly import weather data into CrateDB in this video. Witness the power of CrateDB’s time series query capabilities in action with a weather dataset, showcasing the dynamic schema flexibility.

CrateDB: Querying Multi-Model Heterogeneous Time Series Data with SQL

Dive deeper into CrateDB’s multi-modal features with demonstrations on handling JSON, geospatial data, and conducting full-text searches.

 

Fundamentals
Geospatial Data SQL

Let’s Go Plane Spotting with Software Defined Radio, CrateDB and Node.js!

Did you know that passing aircraft can be a rich source of real time data? This talk will teach you how to receive and make sense of messages from aircraft in real time using an ADS-B receiver / software defined radio.

You’ll see how to decode the messages, store them in a CrateDB database, and make sense of them. Finally, the talk demonstrates a system that alerts you when specific types of aircraft are passing by so you can run outside and see that 747 go past.

The hardware involved is a Raspberry Pi, a Radarbox flight stick, and a flip dot sign. The software is written in JavaScript and runs on Node.js.

 

Fundamentals
Geospatial Data SQL

Testimonials

• GolfNow chooses CrateDB for location analytics and commerce.

• Spatially Health chooses CrateDB for location analytics.

See also

Product: Multi-model Database • Geospatial Database • Geospatial Data Model • Dynamic Database Schemas • Nested Data Structure • Relational Database