Question

How to import Geopackages into Postgres/Postgis database?

Posted December 16, 2019 2.9k views
Linux BasicsPostgreSQLLinux Commands

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

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

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

  • 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

Submit an Answer