Export Spatial Data

On this page

Warning:

CockroachDB v20.2 is no longer supported. For more details, see the Release Support Policy.

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

This page has instructions for exporting spatial data from CockroachDB and converting it to other spatial formats using the ogr2ogr command.

Note:

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

Step 1. Export data to CSV

First, use the EXPORT statement to export your data to a CSV file.

In the example statement below, we export the tornadoes database used in Working with spatial data.

The statement will place the CSV file in the node's store directory, in a subdirectory named extern/tornadoes. The file's name is automatically generated, and will be displayed as output in the SQL shell.

EXPORT INTO CSV 'nodelocal://self/tornadoes' WITH nullas = '' FROM SELECT * from "1950-2018-torn-initpoint";

                     filename                     | rows  |  bytes
--------------------------------------------------+-------+-----------
  export16467a35d30d25700000000000000001-n1.0.csv | 63645 | 16557064
(1 row)

Note:

This example uses local file storage. For more information about other locations where you can export your data (such as cloud storage), see EXPORT.

Step 2. Combine multiple CSV files into one, as needed

You should now have one or more CSV files in the extern/tornadoes subdirectory of your node's store directory. Depending on the size of the data set, there may be more than one CSV file.

To combine multiple CSVs into one file:

Open the CSV file where you will be storing the combined output in a text editor. You will need to manually add the CSV header columns to that file so that the ogr2ogr output we generate below will have the proper column names. Start by running the statement below on the table you are exporting to get the necessary column names:

SELECT string_agg(column_name, ',') FROM [SHOW COLUMNS FROM "1950-2018-torn-initpoint"];

                                              string_agg
------------------------------------------------------------------------------------------------------
  gid,om,yr,mo,dy,date,time,tz,st,stf,stn,mag,inj,fat,loss,closs,slat,slon,elat,elon,len,wid,fc,geom

Add the column names output above to your target output CSV file (e.g., tornadoes.csv) as header columns. For the tornadoes database, they should look like the following:
```
gid, om, yr, mo, dy, date, time, tz, st, stf, stn, mag, inj, fat, loss, closs, slat, slon, elat, elon, len, wid, fc, geom
```
Concatenate the non-header data from all of the exported CSV files, and append the output to the target CSV file as shown below. The node's store directory on this machine is /tmp/node0.
```
cat /tmp/node0/extern/tornadoes/*.csv >> tornadoes.csv
```

Step 3. Convert CSV to other formats using `ogr2ogr`

Now that you have your data in CSV format, you can convert it to other spatial formats using ogr2ogr.

For example, to convert the data to SQL, run the following command:

ogr2ogr -f PGDUMP tornadoes.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF -oo GEOM_POSSIBLE_NAMES=geom -oo KEEP_GEOM_COLUMNS=off tornadoes.csv

Note that the options -oo GEOM_POSSIBLE_NAMES=<geom_column_name> -oo KEEP_GEOM_COLUMNS=off are required no matter what output format you are converting into.

For more information about the formats supported by ogr2ogr, see the ogr2ogr documentation.

Note:

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

Finally, note that SQL type information is lost in the conversion to CSV, such that the tornadoes.sql file output by the ogr2ogr command above lists every non-geometry field as a VARCHAR.

This can be addressed in one of the following ways:

Modify the data definitions in the SQL output file to use the correct types.
Run ALTER TYPE statements to restore the data's SQL types after loading this data into another database (including another CockroachDB instance).

Cockroach
University

Docs Hub

Export Spatial Data

Step 1. Export data to CSV

Step 2. Combine multiple CSV files into one, as needed

Step 3. Convert CSV to other formats using `ogr2ogr`

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Export Spatial Data

Step 1. Export data to CSV

Step 2. Combine multiple CSV files into one, as needed

Step 3. Convert CSV to other formats using ogr2ogr

See also

Cockroach
University

Cockroach
University

Step 3. Convert CSV to other formats using `ogr2ogr`