This page has instructions for configuring GeoServer to use CockroachDB as the underlying database.
The instructions here reuse parts of the data set described in the Spatial Data tutorial, specifically the tutorial.roads
table, which contains the U.S. National Atlas data set.
Many of the instructions on this page come from the following GeoServer documentation pages:
Before you begin
You must have the following set up before proceeding with this tutorial:
- CockroachDB installed on the local machine
- GeoServer installed on the local machine.
These instructions assume you are running on a UNIX-like system.
Step 1. Start CockroachDB and connect to your cluster
Start a CockroachDB cluster by following the instructions in Start a Local Cluster.
Step 2. Load spatial data
Connect to the running cluster from the SQL client and enter the statements below.
Create the
tutorial
database:CREATE DATABASE tutorial;
Switch to the
tutorial
database:USE tutorial;
In a separate terminal, download the spatial data set:
curl -o bookstores-and-roads.sql https://spatial-tutorial.s3.us-east-2.amazonaws.com/bookstores-and-roads-20210125.sql
Pipe the data set directly into cockroach sql, specifying the connection string of your local cluster:
cockroach sql --url {"CONNECTION STRING"} < bookstores-and-roads.sql
The SQL execution will take some time to complete.
Step 3. Turn on CockroachDB's experimental box comparison operators
CockroachDB's support for GeoServer is still in development. To use CockroachDB with GeoServer, you will need to enable the use of certain experimental box2d comparison operators by changing the following cluster setting:
SET CLUSTER SETTING sql.spatial.experimental_box2d_comparison_operators.enabled = ON;
The reasons the box2d comparison operators are experimental in CockroachDB are as follows:
- PostGIS uses the
&&
,~
, and@
operators to do bounding box comparisons. These comparisons can always be index-accelerated by PostgreSQL since it uses R-tree based indexing to generate its coverings. - CockroachDB uses a different indexing strategy based on space-filling curves since this is necessary for scaling horizontally.
- This means that the coverings generated by CockroachDB's
&&
,~
, and@
operators for index-accelerated lookups are not the same as the bounding box coverings generated by PostGIS. - In practice, CockroachDB may return a smaller set of results for the same query, as the space-filling curve covering is often more exact than a bounding box covering, and will exclude from the result set any geometries that have an intersecting bounding box but where no part of the geometry hits the actual bounding box.
- Note that the behavior described above only applies to index-accelerated lookups.
- This means that the coverings generated by CockroachDB's
Step 4. Start GeoServer
The easiest place to create the GeoServer data directory is in your user's home directory.
In the UNIX shell, run the following command:
mkdir -p $HOME/geoserver
Start GeoServer by running the following command:
geoserver $HOME/geoserver
You should see some log output that looks like the following:
2021-06-23 11:44:22.617:INFO::main: Logging initialized @721ms to org.eclipse.jetty.util.log.StdErrLog
2021-06-23 11:44:22.944:INFO:oejs.Server:main: jetty-9.4.36.v20210114; built: 2021-01-14T16:44:28.689Z; git: 238ec6997c7806b055319a6d11f8ae7564adc0de; jvm 1.8.0_282-b08
2021-06-23 11:44:22.969:INFO:oejdp.ScanningAppProvider:main: Deployment monitor [file:///usr/local/Cellar/geoserver/2.19.1/libexec/webapps/] at interval 1
2021-06-23 11:44:23.732:INFO:oejw.StandardDescriptorProcessor:main: NO JSP Support for /geoserver, did not find org.eclipse.jetty.jsp.JettyJspServlet
2021-06-23 11:44:24.687:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2021-06-23 11:44:24.687:INFO:oejs.session:main: No SessionScavenger set, using defaults
...
Step 5. Log in to GeoServer
In this and the following steps we will set up GeoServer so it can access the spatial data we loaded in Step 2.
Open your web browser and navigate to your locally running GeoServer instance. Log in using the default credentials: username admin
, password geoserver
.
Step 6. Set up a GeoServer Workspace
In the left-hand navigation menu, click Data > Workspaces. The Workspaces page will load. Click the Add new workspace button.
On the New Workspace page, enter the following information:
- In the Name field, enter the text "spatial-tutorial".
- In the Namespace URI field, enter the URL for the spatial tutorial where this data set is used: https://www.cockroachlabs.com/docs/stable/spatial-data.html.
Press the Save button.
You will be redirected to the Workspaces page, and you should see a workspace called spatial-tutorial in the list.
Step 7. Configure GeoServer to use CockroachDB
In the left-hand navigation menu, click Data > Stores. The Stores page will load. Click the Add new Store button.
You will be taken to the New data source page. Under the list of Vector Data Sources, click PostGIS.
This opens the New Vector Data Source page, where you need to enter the following information:
Under Basic Store Info, fill in the Data Source Name field with the text:
CockroachDB
Under Connection Parameters, edit port to the default CockroachDB port:
26257
Edit the database field to add the text:
tutorial
Fill in the user field with the text:
root
Delete the contents of the passwd field, if any
Click Save, and you will be redirected to the New Layer page, with the following unpublished layers:
- bookstore_routes
- bookstores
- roads
Click the Publish button to the right of the roads
layer.
This will bring you to the Edit Layer page, where you need to enter the following information:
- In the Bounding Boxes section, for the Native Bounding Box settings, click the Compute from data button, which will fill in the form fields.
- Also in the Bounding Boxes section, for the Lat/Lon Bounding Box setting, click the Compute from native bounds button, which will fill in the form fields.
Click Save, and you will be redirected to the Layers page.
Step 8. View the roads
layer
In the left-hand navigation menu, click Data > Layer Preview.
You will be redirected to the Layer Preview page.
In the row for the roads
layer, click the OpenLayers button under the Common Formats column.
Your browser should open a new tab with the title OpenLayers map preview. It should show a map view that looks like the following:
See also
- Install CockroachDB
- Spatial Data Overview
- Spatial Indexes
- Spatial & GIS Glossary of Terms
- Export 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
- Troubleshooting Overview
- Support Resources