CockroachDB supports efficiently storing and querying spatial data.
This page has instructions for migrating data from ESRI Shapefiles into CockroachDB using ogr2ogr
and IMPORT INTO
.
In the example below we will import a tornadoes data set that is available from the US National Weather Service (NWS).
Please refer to the documentation of your GIS software for instructions on exporting GIS data to Shapefiles.
Before you begin
To follow along with the example below, you will need the following prerequisites:
- CockroachDB installed and running
- Note:An
ogr2ogr
version of 3.1.0 or higher is required to generate data that can be imported into CockroachDB. The tornado data:
curl -o 1950-2018-torn-initpoint.zip http://web.archive.org/web/20201018170120/https://www.spc.noaa.gov/gis/svrgis/zipped/1950-2018-torn-initpoint.zip && unzip 1950-2018-torn-initpoint.zip
Step 1. Convert the Shapefile data to CSV
Navigate to the folder containing the data:
cd 1950-2018-torn-initpoint/
Convert the Shapefile data to CSV using the following ogr2ogr
command:
ogr2ogr -f CSV tornadoes.CSV -lco GEOMETRY=AS_WKT 1950-2018-torn-initpoint.shp
You will import the CSV data into a CockroachDB table.
Step 2. Host the file where the cluster can access it
Each node in the CockroachDB cluster needs to have access to the files being imported. There are several ways for the cluster to access the data; for a complete list of the types of storage IMPORT INTO
can pull from, see Import file location.
For local testing, you can start a local file server. The following command will start a local file server listening on port 3000:
python3 -m http.server 3000
Step 3. Prepare the CockroachDB database
Create a database to hold the bus-stop data:
cockroach sql --insecure
CREATE DATABASE tornadoes;
USE tornadoes;
Step 4. Create a CockroachDB table
To import the CSV data, you need to create a table with the necessary columns and data types.
Convert the Shapefile data to SQL using the following ogr2ogr
command:
ogr2ogr -f PGDUMP tornadoes.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF 1950-2018-torn-initpoint.shp
Create a CockroachDB table that corresponds to the DDL statements in tornadoes.sql
:
CREATE TABLE tornadoes (
wkb_geometry GEOMETRY(POINT) NULL,
om NUMERIC NULL,
yr NUMERIC NULL,
mo NUMERIC NULL,
dy NUMERIC NULL,
date VARCHAR NULL,
time VARCHAR NULL,
tz NUMERIC NULL,
st VARCHAR NULL,
stf NUMERIC NULL,
stn NUMERIC NULL,
mag NUMERIC NULL,
inj NUMERIC NULL,
fat NUMERIC NULL,
loss NUMERIC NULL,
closs NUMERIC NULL,
slat NUMERIC NULL,
slon NUMERIC NULL,
elat NUMERIC NULL,
elon NUMERIC NULL,
len NUMERIC NULL,
wid NUMERIC NULL,
fc NUMERIC NULL
);
Step 5. Import the CSV
Since the file is being served from a local server and is formatted as CSV, you can import the data using the following IMPORT INTO
statement:
IMPORT INTO tornadoes CSV DATA ('http://localhost:3000/tornadoes.csv') WITH skip = '1';
job_id | status | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+-------+---------------+-----------
981219367971323905 | succeeded | 1 | 63645 | 0 | 15681599
See also
IMPORT INTO
- Export Spatial Data
- Spatial tutorial
- Spatial indexes
- Using GeoServer with CockroachDB
- Migrate from OpenStreetMap
- Migrate from GeoJSON
- Migrate from GeoPackage
- Migration Overview
- Migrate from MySQL
- Migrate from PostgreSQL
- Back Up and Restore Data
- Use the Built-in SQL Client
cockroach
Commands Overview