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

# Migrate from Shapefiles

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 <InternalLink path="export-spatial-data">spatial data</InternalLink>.

This page has instructions for migrating data from ESRI <InternalLink path="architecture/glossary#shapefile">Shapefiles</InternalLink> into CockroachDB using [`shp2pgsql`](https://manpages.debian.org/stretch/postgis/shp2pgsql.1) and <InternalLink path="import">`IMPORT`</InternalLink>.

<Tip>
  We are using `shp2pgsql` in the example below, but [`ogr2ogr`](https://gdal.org/programs/ogr2ogr) could also be used, e.g. `ogr2ogr -f PGDUMP file.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF file.shp`
</Tip>

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

In the example below we will import a [tornadoes data set](http://web.archive.org/web/20201018170120/https:/www.spc.noaa.gov/gis/svrgis/zipped/1950-2018-torn-initpoint.zip) that is [available from the US National Weather Service](https://www.spc.noaa.gov/gis/svrgis) (NWS).

<Note>
  Please refer to the documentation of your GIS software for instructions on exporting GIS data to Shapefiles.
</Note>

## Before You Begin

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

* CockroachDB <InternalLink path="install-cockroachdb">installed</InternalLink> and <InternalLink path="start-a-local-cluster">running</InternalLink>
* [`shp2pgsql`](https://manpages.debian.org/stretch/postgis/shp2pgsql.1)
* [Python 3](https://www.python.org/)

## Step 1. Download the Shapefile data

1. Download the tornado data:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   curl -o 1950-2018-torn-initpoint.zip http://web.archive.org/web/20201018170120/https://www.spc.noaa.gov/gis/svrgis/zipped/1950-2018-torn-initpoint.zip
   ```
2. Unzip the data file:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   unzip 1950-2018-torn-initpoint.zip
   ```
3. Change to the data folder:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cd 1950-2018-torn-initpoint/
   ```

## Step 2. Convert the Shapefile data to SQL

To load the tornado Shapefile into CockroachDB, we must first convert it to SQL using the `shp2pgsql` tool:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
shp2pgsql 1950-2018-torn-initpoint.shp > tornado-points.sql &
```

## 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 <InternalLink path="import">`IMPORT`</InternalLink> can pull from, see <InternalLink path="import#import-file-location">import file locations</InternalLink>.

For local testing, you can <InternalLink path="use-a-local-file-server">start a local file server</InternalLink>. The following command will start a local file server listening on port 3000:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
python3 -m http.server 3000
```

## Step 4. Prepare the database

Next, create a `tornadoes` database to store the data in, and switch to it:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach sql --insecure
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE DATABASE IF NOT EXISTS tornadoes;
USE tornadoes;
```

## 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 <InternalLink path="import#import-a-postgresql-database-dump">`IMPORT PGDUMP`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT PGDUMP ('http://localhost:3000/tornado-points.sql') WITH ignore_unsupported_statements;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        job_id       |  status   | fraction_completed | rows  | index_entries |  bytes
---------------------+-----------+--------------------+-------+---------------+-----------
  584874782851497985 | succeeded |                  1 | 63645 |             0 | 18287213
(1 row)
```

## See also

* <InternalLink path="import">`IMPORT`</InternalLink>
* <InternalLink path="export-spatial-data">Export Spatial Data</InternalLink>
* <InternalLink path="spatial-tutorial">Spatial tutorial</InternalLink>
* <InternalLink path="spatial-indexes">Spatial indexes</InternalLink>
* <InternalLink path="geoserver">Using GeoServer with CockroachDB</InternalLink>
* <InternalLink path="migrate-from-openstreetmap">Migrate from OpenStreetMap</InternalLink>
* <InternalLink path="migrate-from-geojson">Migrate from GeoJSON</InternalLink>
* <InternalLink path="migrate-from-geopackage">Migrate from GeoPackage</InternalLink>
* <InternalLink version="molt" path="migration-overview">Migration Overview</InternalLink>
* <InternalLink version="molt" path="migrate-to-cockroachdb?filters=mysql">Migrate from MySQL</InternalLink>
* <InternalLink version="molt" path="migrate-to-cockroachdb">Migrate from PostgreSQL</InternalLink>
* <InternalLink path="take-full-and-incremental-backups">Back Up and Restore Data</InternalLink>
* <InternalLink path="cockroach-sql">Use the Built-in SQL Client</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>