This page provides information about working with spatial data in CockroachDB.
Supported data types
Supported spatial data types include:
Geometric objects such as points, lines, and polygons in 2-dimensional space. These are projected onto the flat surface of a plane and are represented in SQL by the
GEOMETRY
data type.Geographic objects, which are also made up of points, lines, polygons, etc., in 2-dimensional space. They are projected onto the surface of a sphere and are represented in SQL by the
GEOGRAPHY
data type. (Technically, they are projected onto a spheroid: "a sphere with a bulge"). The spheroid projection means that:- The X and Y coordinates of 2-dimensional points are longitude and latitude values.
- The paths between geographic objects are not straight lines; they are curves, and so the distances between objects are calculated using great circle math.
Compatibility
Just as CockroachDB strives for PostgreSQL compatibility, our spatial data support is designed to be as compatible as possible with the functionality provided by the PostGIS extension. CockroachDB is compatible with PostGIS Version 3.0 and up.
CockroachDB does not implement the full list of PostGIS built-in functions and operators. Also, spatial indexing works differently (see the Performance section below). For a list of the spatial functions CockroachDB supports, see Geospatial functions.
If your application needs support for functions that are not yet implemented, check the meta-issue for built-in function support on GitHub, which describes how to find an issue for the built-in function(s) you need.
For a list of other known limitations, see Known Limitations.
ORM compatibility
The following ORM spatial libraries are fully compatible with CockroachDB's spatial features:
-
Hibernate 5.4.30.Final added the
CockroachDB202SpatialDialect
dialect to thehibernate-spatial
module. TheCockroachDB202SpatialDialect
dialect supports spatial features available in CockroachDB v20.2 and later. -
CockroachDB's ActiveRecord adapter (
activerecord-cockroachdb-adapter
) uses RGeo and RGeo-ActiveRecord for spatial support with ActiveRecord v5.2.2+ and v6.0.0+. For information about using CockroachDB spatial features with ActiveRecord, see theactiverecord-cockroachdb-adapter
README. -
Starting with CockroachDB 20.2.x and
django-cockroachdb
3.1.3, CockroachDB is compatible with GeoDjango. For information about using CockroachDB spatial features with GeoDjango, see thedjango-cockroachdb
README.
Most PostGIS-compatible client libraries are incompatible with CockroachDB's spatial features without an adapter.
Troubleshooting
For general CockroachDB troubleshooting information, see Troubleshooting Overview.
If you need help troubleshooting an issue with our spatial support, please get in touch using our Support resources.
Performance
In order to avoid full table scans, make sure to add indexes to any columns that are accessed as part of a predicate in the WHERE
clause. For geospatial columns, the index will only be used if the column is accessed using an index-accelerated geospatial function from the list below (all of these functions work on GEOMETRY
data types; a *
means that a function also works on GEOGRAPHY
data types):
ST_Covers
(*)ST_CoveredBy
(*)ST_Contains
ST_ContainsProperly
ST_Crosses
ST_DWithin
(*)ST_DFullyWithin
ST_Equals
ST_Intersects
(*)ST_Overlaps
ST_Touches
ST_Within
To use a version of a function from the list above that will explicitly not use the index, add an underscore (_
) to the beginning of the function name, e.g., _ST_Covers
.
You can check which queries are using which indexes using the EXPLAIN
statement. For more information about general query tuning (including index usage), see Optimize Statement Performance.
The syntax for adding an index to a geometry column is CREATE INDEX index_name ON table_name USING GIST (column_name)
.
For example, to add an index to the geom
column of the sample tornadoes
table:
CREATE INDEX tornado_geom_idx ON tornadoes USING GIST (geom);
This creates a (spatial) GIN index on the geom
column.
Because CockroachDB is a scale-out, multi-node database, our spatial indexing strategy is based on a space-filling curve/quad-tree design (also known as "divide the space"), rather than the R-Tree data structure used by some other spatial databases (also known as "divide the objects"). Other databases that use a "divide the space" strategy include Microsoft SQL Server and MongoDB.
For more detailed information about how CockroachDB's spatial indexes work, see Spatial indexes.
If you encounter behavior that you think is due to a performance issue, please get in touch using our Support resources.
Example: Load NYC data for the PostGIS tutorial
Follow the steps below to load the SQL for the NYC data used in the Introduction to PostGIS tutorial.
CockroachDB can work with the tutorial up to Chapter 22, with the following exceptions:
- Do not try to load Shapefiles via the GUI as shown in the tutorial. Instead, follow the steps below to load the SQL data directly into CockroachDB. (We have already converted the tutorial Shapefiles to SQL for you.)
- CockroachDB does not support GML or KML data.
- CockroachDB does not support SVG.
Before you begin
Install a build of CockroachDB with support for spatial data by following the instructions at Install CockroachDB.
Start a local insecure cluster and connect to that cluster from a SQL client:
cockroach sql --insecure --host=localhost --port=26257
Leave this shell open for use in the examples below.
Step 1. Load the NYC data
Clone the data set:
git clone https://github.com/otan-cockroach/otan-scripts
Load the SQL files into your CockroachDB cluster:
cat otan-scripts/geospatial_sql/*.sql | cockroach sql --insecure --host=localhost --port=26257
The command above will take a few minutes to run.
Step 2. Follow the PostGIS tutorial
When the cluster is finished loading the data, open a SQL shell and start working through the Introduction to PostGIS tutorial:
cockroach sql --insecure --host=localhost --port=26257
See also
- Install CockroachDB
- Spatial Features
- Spatial indexes
- Spatial & GIS Glossary of Terms
- Working with Spatial Data
- Migrate from Shapefiles
- Migrate from GeoJSON
- Migrate from GeoPackage
- Migrate from OpenStreetMap
- Spatial functions
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRYCOLLECTION
- Well known text
- Well known binary
- GeoJSON
- SRID 4326 - longitude and latitude
ST_Contains
ST_ConvexHull
ST_CoveredBy
ST_Covers
ST_Disjoint
ST_Equals
ST_Intersects
ST_Overlaps
ST_Touches
ST_Union
ST_Within
- Introducing Distributed Spatial Data in Free, Open Source CockroachDB (blog post)
- Troubleshooting overview
- Support resources
- Using GeoServer with CockroachDB