> ## 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 GeoPackage

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

This page has instructions for migrating data from the [GeoPackage](https://www.geopackage.org/) format into CockroachDB using [`ogr2ogr`](https://gdal.org/programs/ogr2ogr) and <InternalLink path="import-into">`IMPORT INTO`</InternalLink>.

In the following example, you will import a data set with the locations of bus stops in the cities of Minneapolis and St. Paul, MN (USA) that is made available via [gisdata.mn.gov](https://gisdata.mn.gov/dataset/us-mn-state-metc-trans-better-bus-stops).

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

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

* [Python 3](https://www.python.org/)
* The bus-stop GeoPackage data:

  ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  curl -o gpkg_trans_better_bus_stops.zip https://resources.gisdata.mn.gov/pub/gdrs/data/pub/us_mn_state_metc/trans_better_bus_stops/gpkg_trans_better_bus_stops.zip && unzip gpkg_trans_better_bus_stops.zip
  ```

## Step 1. Convert the GeoPackage data to CSV

Convert the GeoPackage data to CSV using the following `ogr2ogr` command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ogr2ogr -f CSV busstops.CSV -lco GEOMETRY=AS_WKT trans_better_bus_stops.gpkg
```

You will import the CSV data into a CockroachDB table.

## Step 2. Host the file where the cluster can access it

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-into">`IMPORT INTO`</InternalLink> can pull from, see <InternalLink path="import-into#import-file-location">Import file location</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 3. Prepare the CockroachDB database

Create a database to hold the bus-stop data:

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE DATABASE busstops;
USE busstops;
```

## Step 4. Create a CockroachDB table

To import the CSV data, you need to create a table with the necessary columns and data types.

Convert the GeoPackage data to SQL using the following `ogr2ogr` command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ogr2ogr -f PGDUMP busstops.sql -lco LAUNDER=NO -lco DROP_TABLE=OFF trans_better_bus_stops.gpkg
```

Create a CockroachDB table that corresponds to the DDL statements in `busstops.sql`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE busstops (
    geom GEOMETRY(POINT) NULL,
    site_ID INT8 NULL,
    site_on VARCHAR NULL,
    site_at VARCHAR NULL,
    corn_desc VARCHAR NULL,
    Completion INT8 NULL,
    Improvemen VARCHAR NULL,
    Improvem_1 VARCHAR NULL,
    Public_Com VARCHAR NULL,
    Project_Na VARCHAR NULL
);
```

## Step 5. Import the CSV

Since the file is being served from a local server and is formatted as CSV, you can import the data using the following <InternalLink path="import-into">`IMPORT INTO`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT INTO busstops CSV DATA ('http://localhost:3000/busstops.csv') WITH skip = '1';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  980669141570682881 | succeeded |                  1 |  945 |             0 | 173351
```

## See also

* <InternalLink path="import-into">`IMPORT INTO`</InternalLink>
* <InternalLink path="spatial-indexes">Spatial indexes</InternalLink>
* <InternalLink path="migrate-from-openstreetmap">Migrate from OpenStreetMap</InternalLink>
* <InternalLink path="migrate-from-shapefiles">Migrate from Shapefiles</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>

[postgres]: migrate-from-postgres.html

[mysql]: migrate-from-mysql.html