Question

How to import Geopackages into Postgres/Postgis database?

Posted December 16, 2019 856 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

Add a comment
C41840ec c920 4467 bae8 2bdc8f84a8b9
By:
Heryx
Add a comment

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
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
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help