Migrate from GeoJSON

On this page Carat arrow pointing down

CockroachDB supports efficiently storing and querying spatial data.

This page has instructions for migrating data from the GeoJSON format into CockroachDB using ogr2ogr and IMPORT.

In the example below we will import a data set with the locations of underground storage tanks in the state of Vermont (USA) that is made available via data.gov.

Before You Begin

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

Note:

An ogr2ogr version of 3.1.0 or higher is required to generate data that can be imported into CockroachDB.

Step 1. Download the GeoJSON data

First, download the storage tank GeoJSON data:

icon/buttons/copy
wget -O tanks.geojson https://geodata.vermont.gov/datasets/986155613c5743239e7b1980b45bbf36_162.geojson

Step 2. Convert the GeoJSON data to SQL

Next, convert the GeoJSON data to SQL using the following ogr2ogr command:

icon/buttons/copy
ogr2ogr -f PGDUMP tanks.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF tanks.geojson
Note:

An ogr2ogr version of 3.1.0 or higher is required to generate data that can be imported into CockroachDB.

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:

icon/buttons/copy
python3 -m http.server 3000

Step 4. Prepare the database

Next, create a database to hold the storage tank data:

icon/buttons/copy
cockroach sql --insecure
icon/buttons/copy
CREATE DATABASE IF NOT EXISTS tanks;
USE tanks;

Step 5. Import the SQL

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

icon/buttons/copy
IMPORT PGDUMP ('http://localhost:3000/tanks.sql');
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  588555793549328385 | succeeded |                  1 | 2709 |          2709 | 822504
(1 row)

See also


Yes No
On this page

Yes No