Migrate from GeoPackage

CockroachDB supports efficiently storing and querying spatial data. This page has instructions for migrating data from the GeoPackage format into CockroachDB using ogr2ogr and IMPORT. In the example below we will import a data set with [the locations of natural springs in the state of Minnesota (USA) that is made available via gisdata.mn.gov.](https://gisdata.mn.gov/dataset/env-mn-springs-inventory)

Before you begin

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

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

Step 1. Download the GeoPackage data

Download the zip file containing the spring location data:

curl -o gpkg_env_mn_springs_inventory.zip https://resources.gisdata.mn.gov/pub/gdrs/data/pub/us_mn_state_dnr/env_mn_springs_inventory/gpkg_env_mn_springs_inventory.zip

Unzip the file:

unzip gpkg_env_mn_springs_inventory.zip

Step 2. Convert the GeoPackage data to SQL

To load the GeoPackage into CockroachDB, we must first convert it to SQL using the ogr2ogr tool.

ogr2ogr -f PGDUMP springs.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF env_mn_springs_inventory.gpkg

This particular data set emits a warning due to some date formatting.

Warning 1: Non-conformant content for record 1 in column field_ch_1, 2017/05/04, successfully parsed

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:

python3 -m http.server 3000

Step 4. Prepare the database

Next, create a database to hold the natural spring location data:

cockroach sql --insecure

CREATE DATABASE springs;
USE springs;

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:

IMPORT PGDUMP ('http://localhost:3000/springs.sql');

        job_id       |  status   | fraction_completed | rows | index_entries |  bytes
---------------------+-----------+--------------------+------+---------------+----------
  589053379352526849 | succeeded |                  1 | 5124 |          5124 | 2449139
(1 row)

Migration Considerations

MOLT Tools

Common Migration Approaches

Third-Party Migration Tools

Migrate Data Types

Migrate from GeoPackage

Before you begin

Step 1. Download the GeoPackage data

Step 2. Convert the GeoPackage data to SQL

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

Step 4. Prepare the database

Step 5. Import the SQL

See also

​Before you begin

​Step 1. Download the GeoPackage data

​Step 2. Convert the GeoPackage data to SQL

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

​Step 4. Prepare the database

​Step 5. Import the SQL

​See also

Before you begin

Step 1. Download the GeoPackage data

Step 2. Convert the GeoPackage data to SQL

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

Step 4. Prepare the database

Step 5. Import the SQL

See also