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.
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)
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.
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).
See also
EXPORT
- Migrate from Shapefiles
- Migrate from GeoJSON
- Migrate from GeoPackage
- Migrate from OpenStreetMap
- Spatial features
- Spatial indexes
- Working with Spatial Data
- Spatial and GIS Glossary of Terms
- Known Limitations
- Spatial functions
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRYCOLLECTION
- Well known text
- Well known binary
- GeoJSON
- SRID 4326 - longitude and latitude
ST_Contains
ST_ConvexHull
ST_CoveredBy
ST_Covers
ST_Disjoint
ST_Equals
ST_Intersects
ST_Overlaps
ST_Touches
ST_Union
ST_Within