How to import Geopackages into Postgres/Postgis database?

Question

Hi everyone,

I would like to know how to import a Geopackage file containing raster into a Postgis database. I tried with gdal library “ogr2ogr”.

This is the command line I used:

ogr2ogr -f PostgreSQL PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" path_name\geopackage_name.gpkg

I use a server Ubuntu 18.04 LAMP with postgres/postgis and I would like to import geopackage from my PC (Ubuntu 19.10 + Qgis + Postgresql 10 + gdal library installed).

Greeting,
Giuseppe

Answer 1

bobbyiliev December 20, 2019

Hello,

I think that your firewall might be blocking your connections on port 5432, so I would suggest running the command locally from your Droplet.

What you should usually do is:

SSH to your Droplet
Download data for the whole world or by country. You can download it from here:

https://gadm.org/download_world.html

To download the package you could run:

wget https://biogeo.ucdavis.edu/data/gadm3.6/gadm36_levels_gpkg.zip
unzip gadm36_levels_gpkg.zip

Note: the URL might change so it is best to go to the gadam.org website and get the latest version from there.

Create a PostgreSQL database if you don’t already have one.
Install the PostGIS extension:

CREATE EXTENSION postgis;

Import the data with ogr2ogr with the command that you’ve provided:

ogr2ogr -f PostgreSQL PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" path_name\geopackage_name.gpkg

Note: You might have to install the ogr2org command if you don’t have it installed already:

npm install ogr2ogr

Hope that this helps!

Regards,
Bobby

Reply Report

Heryx December 23, 2019

Thanks for the answer.

Yes, I work already with putty (from windows).
I downloaded the gdam.gpkg and before I start the ogr2ogr command I needed to install gdal-bin, then I put the command

ogr2ogr -f PostgreSQL PG:“dbname=‘databasename’ host='addr’ port='5432’ user='x’ password='y’” pathname\geopackagename.gpkg

but I always receive this error:

FAILURE:
Unable to open datasource `mapItalia.gpkg' with the following drivers.
  -> `PCIDSK'
  -> `netCDF'
  -> `JP2OpenJPEG'
  -> `PDF'
  -> `ESRI Shapefile'
  -> `MapInfo File'
  -> `UK .NTF'
  -> `OGR_SDTS'
  -> `S57'
  -> `DGN'
  -> `OGR_VRT'
  -> `REC'
  -> `Memory'
  -> `BNA'
  -> `CSV'
  -> `NAS'
  -> `GML'
  -> `GPX'
  -> `LIBKML'
  -> `KML'
  -> `GeoJSON'
  -> `Interlis 1'
  -> `Interlis 2'
  -> `OGR_GMT'
  -> `GPKG'
  -> `SQLite'
  -> `OGR_DODS'
  -> `ODBC'
  -> `WAsP'
  -> `PGeo'
  -> `MSSQLSpatial'
  -> `OGR_OGDI'
  -> `PostgreSQL'
  -> `MySQL'
  -> `OpenFileGDB'
  -> `XPlane'
  -> `DXF'
  -> `CAD'
  -> `Geoconcept'
  -> `GeoRSS'
  -> `GPSTrackMaker'
  -> `VFK'
  -> `PGDUMP'
  -> `OSM'
  -> `GPSBabel'
  -> `SUA'
  -> `OpenAir'
  -> `OGR_PDS'
  -> `WFS'
  -> `SOSI'
  -> `HTF'
  -> `AeronavFAA'
  -> `Geomedia'
  -> `EDIGEO'
  -> `GFT'
  -> `SVG'
  -> `CouchDB'
  -> `Cloudant'
  -> `Idrisi'
  -> `ARCGEN'
  -> `SEGUKOOA'
  -> `SEGY'
  -> `XLS'
  -> `ODS'
  -> `XLSX'
  -> `ElasticSearch'
  -> `Walk'
  -> `Carto'
  -> `AmigoCloud'
  -> `SXF'
  -> `Selafin'
  -> `JML'
  -> `PLSCENES'
  -> `CSW'
  -> `VDV'
  -> `GMLAS'
  -> `TIGER'
  -> `AVCBin'
  -> `AVCE00'
  -> `HTTP'

Regards,
Giuseppe

Reply Report

Answer 2

Heryx December 23, 2019

Thanks for the answer.

Yes, I work already with putty (from windows).
I downloaded the gdam.gpkg and before I start the ogr2ogr command I needed to install gdal-bin, then I put the command

ogr2ogr -f PostgreSQL PG:“dbname=‘databasename’ host='addr’ port='5432’ user='x’ password='y’” pathname\geopackagename.gpkg

but I always receive this error:

FAILURE:
Unable to open datasource `mapItalia.gpkg' with the following drivers.
  -> `PCIDSK'
  -> `netCDF'
  -> `JP2OpenJPEG'
  -> `PDF'
  -> `ESRI Shapefile'
  -> `MapInfo File'
  -> `UK .NTF'
  -> `OGR_SDTS'
  -> `S57'
  -> `DGN'
  -> `OGR_VRT'
  -> `REC'
  -> `Memory'
  -> `BNA'
  -> `CSV'
  -> `NAS'
  -> `GML'
  -> `GPX'
  -> `LIBKML'
  -> `KML'
  -> `GeoJSON'
  -> `Interlis 1'
  -> `Interlis 2'
  -> `OGR_GMT'
  -> `GPKG'
  -> `SQLite'
  -> `OGR_DODS'
  -> `ODBC'
  -> `WAsP'
  -> `PGeo'
  -> `MSSQLSpatial'
  -> `OGR_OGDI'
  -> `PostgreSQL'
  -> `MySQL'
  -> `OpenFileGDB'
  -> `XPlane'
  -> `DXF'
  -> `CAD'
  -> `Geoconcept'
  -> `GeoRSS'
  -> `GPSTrackMaker'
  -> `VFK'
  -> `PGDUMP'
  -> `OSM'
  -> `GPSBabel'
  -> `SUA'
  -> `OpenAir'
  -> `OGR_PDS'
  -> `WFS'
  -> `SOSI'
  -> `HTF'
  -> `AeronavFAA'
  -> `Geomedia'
  -> `EDIGEO'
  -> `GFT'
  -> `SVG'
  -> `CouchDB'
  -> `Cloudant'
  -> `Idrisi'
  -> `ARCGEN'
  -> `SEGUKOOA'
  -> `SEGY'
  -> `XLS'
  -> `ODS'
  -> `XLSX'
  -> `ElasticSearch'
  -> `Walk'
  -> `Carto'
  -> `AmigoCloud'
  -> `SXF'
  -> `Selafin'
  -> `JML'
  -> `PLSCENES'
  -> `CSW'
  -> `VDV'
  -> `GMLAS'
  -> `TIGER'
  -> `AVCBin'
  -> `AVCE00'
  -> `HTTP'

Regards,
Giuseppe

Reply Report

Related

Question

How to import Geopackages into Postgres/Postgis database?

Related

Leave a comment

Looking for something else?

Sign up for our newsletter

Related

Question

How to import Geopackages into Postgres/Postgis database?

Related

Leave a comment

Related

question

Restrict the new user

question

[Help Needed] SSH Key Error

question

How to check the disk usage of all running Docker containers?

question

Is possible to connect a DO K8s cluster with a DO Droplet private network?

Looking for something else?