> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Export Spatial Data

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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`](https://gdal.org/programs/ogr2ogr) command.

<Note>
  An `ogr2ogr` version of 3.1.0 or higher is required to generate data that can be imported into CockroachDB.
</Note>

## Step 1. Export data to CSV

First, use the <InternalLink path="export">`EXPORT`</InternalLink> statement to export your data to a CSV file.

In the example statement below, we export the tornadoes database used in <InternalLink path="query-spatial-data#use-a-sample-shapefile-dataset">Query Spatial Data</InternalLink>.

The statement will place the CSV file in the node's <InternalLink path="cockroach-start#store">store directory</InternalLink>, in a subdirectory named `extern/tornadoes`. The file's name is automatically generated, and will be displayed as output in the <InternalLink path="cockroach-sql">SQL shell</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPORT INTO CSV 'nodelocal://self/tornadoes' WITH nullas = '' FROM SELECT * from "1950-2018-torn-initpoint";
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                     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 <InternalLink path="export">`EXPORT`</InternalLink>.
</Note>

## 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 <InternalLink path="cockroach-start#store">store directory</InternalLink>. Depending on the size of the data set, there may be more than one CSV file.

To combine multiple CSVs into one file:

1. 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:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   WITH x AS (SHOW COLUMNS FROM "1950-2018-torn-initpoint") SELECT string_agg(column_name, ',') FROM x;
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                                                 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
   ```
2. 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:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   gid, om, yr, mo, dy, date, time, tz, st, stf, stn, mag, inj, fat, loss, closs, slat, slon, elat, elon, len, wid, fc, geom
   ```
3. 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`.

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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`](https://gdal.org/programs/ogr2ogr).

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

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](https://gdal.org/programs/ogr2ogr).

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 <InternalLink path="string">`VARCHAR`</InternalLink>.

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 <InternalLink path="alter-type">`ALTER TYPE`</InternalLink> statements to restore the data's SQL types after loading this data into another database (including another CockroachDB instance).

## See also

* <InternalLink path="export">`EXPORT`</InternalLink>
* <InternalLink path="migrate-from-shapefiles">Migrate from Shapefiles</InternalLink>
* <InternalLink path="migrate-from-geojson">Migrate from GeoJSON</InternalLink>
* <InternalLink path="migrate-from-geopackage">Migrate from GeoPackage</InternalLink>
* <InternalLink path="migrate-from-openstreetmap">Migrate from OpenStreetMap</InternalLink>
* <InternalLink path="spatial-data-overview">Spatial Data Overview</InternalLink>
* <InternalLink path="spatial-indexes">Spatial indexes</InternalLink>
* <InternalLink path="architecture/glossary">Spatial and GIS Glossary of Terms</InternalLink>
* <InternalLink path="spatial-data-overview#known-limitations">Known Limitations</InternalLink>
* <InternalLink path="functions-and-operators#spatial-functions">Spatial functions</InternalLink>
* <InternalLink path="point">POINT</InternalLink>
* <InternalLink path="linestring">LINESTRING</InternalLink>
* <InternalLink path="polygon">POLYGON</InternalLink>
* <InternalLink path="multipoint">MULTIPOINT</InternalLink>
* <InternalLink path="multilinestring">MULTILINESTRING</InternalLink>
* <InternalLink path="multipolygon">MULTIPOLYGON</InternalLink>
* <InternalLink path="geometrycollection">GEOMETRYCOLLECTION</InternalLink>
* <InternalLink path="well-known-text">Well known text</InternalLink>
* <InternalLink path="well-known-binary">Well known binary</InternalLink>
* <InternalLink path="geojson">GeoJSON</InternalLink>
* <InternalLink path="srid-4326">SRID 4326 - longitude and latitude</InternalLink>
* <InternalLink path="st_contains">`ST_Contains`</InternalLink>
* <InternalLink path="st_convexhull">`ST_ConvexHull`</InternalLink>
* <InternalLink path="st_coveredby">`ST_CoveredBy`</InternalLink>
* <InternalLink path="st_covers">`ST_Covers`</InternalLink>
* <InternalLink path="st_disjoint">`ST_Disjoint`</InternalLink>
* <InternalLink path="st_equals">`ST_Equals`</InternalLink>
* <InternalLink path="st_intersects">`ST_Intersects`</InternalLink>
* <InternalLink path="st_overlaps">`ST_Overlaps`</InternalLink>
* <InternalLink path="st_touches">`ST_Touches`</InternalLink>
* <InternalLink path="st_union">`ST_Union`</InternalLink>
* <InternalLink path="st_within">`ST_Within`</InternalLink>