Migrate from Shapefiles

On this page

New in v20.2: CockroachDB supports efficiently storing and querying spatial data.

This page has instructions for migrating data from ESRI Shapefiles into CockroachDB using shp2pgsql and IMPORT.

In the example below we will import a tornadoes data set that is available from the US National Weather Service (NWS).

Before You Begin

To follow along with the example below, you will need the following prerequisites:

• CockroachDB installed and running
• shp2pgsql
• Python 3

Step 1. Download the Shapefile data

First, download and unzip the tornado data:

wget 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

cd 1950-2018-torn-initpoint/

Step 2. Convert the Shapefile data to SQL

To load the tornado Shapefile into CockroachDB, we must first convert it to SQL using the shp2pgsql tool:

shp2pgsql 1950-2018-torn-initpoint.shp > tornado-points.sql &

Step 3. Host the files where the cluster can access them

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 can pull from, see import file locations.

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 4. Prepare the database

Next, create a tornadoes database to store the data in, and switch to it:

cockroach sql --insecure

CREATE DATABASE IF NOT EXISTS tornadoes;
USE tornadoes;

Step 5. Import the SQL

Since the file is being served from a local server and is formatted as Postgres-compatible SQL, we can import the data using the following IMPORT PGDUMP statement:

IMPORT PGDUMP ('http://localhost:3000/tornado-points.sql');

        job_id       |  status   | fraction_completed | rows  | index_entries |  bytes
---------------------+-----------+--------------------+-------+---------------+-----------
  584874782851497985 | succeeded |                  1 | 63645 |             0 | 18287213
(1 row)

See also

• IMPORT
• Export Spatial Data
• Working with Spatial Data
• Spatial indexes
• Migrate from OpenStreetMap
• Migrate from GeoJSON
• Migrate from GeoPackage
• Introducing Distributed Spatial Data in Free, Open Source CockroachDB (blog post)
• Migration Overview
• Migrate from MySQL
• Migrate from Postgres
• SQL Dump (Export)
• Back Up and Restore Data
• Use the Built-in SQL Client
• Other Cockroach Commands

Was this helpful?