3

Context

I have a single GeoPackage file containing several layers (tables) with different geometry types that I want to push to a PostgreSQL (+PostGIS) database. Each table in the GPKG has a unique geometry type.
But the geometry type varies between tables.

What I've tried so far

Here is a simple example with two layers in the GPKG file (containing POINT and LINESTRING geometries only, see figure 1), but the question can be extended to multiples situations with the same idea in mind.
It could be especially interesting to extend it to mixture of geometry types in a single GPKG layer or geometry collections, and to multiples geometry columns in a single table (even if they are of the same type).

Importing the GPKG file in QGIS shows two layers
Fig.1: Importing the GPKG file in QGIS shows two layers.

The content of the GPKG file looks something like that (I just decided to focus on the two first layers here, notice lines 12 and 13 are not yet implemented on my side, but I want them in the future):

$ ogrinfo multiple_vector_data.gpkg 
INFO: Open of `multiple_vector_data.gpkg'
      using driver `GPKG' successful.
1: el_points (Point)
2: el_lines (Line String)
3: ec_project1_pts (Point)
4: ec_project1_lin (Line String)
5: ep_project2_ply (Polygon)
6: ep_project2_lin (Line String)
7: atc_buildings (Multi Polygon)
8: atc_contours (Multi Line String)
9: atc_centers (Multi Point)
10: atc_head_1 (Line String)
11: atc_head_2 (Multi Point)
12: atc_head_3 (Geometry Collection)
13: atc_head_4 (Geometry Collection)
14: ekp_1 (Line String)
15: ekp_2 (Point)
16: ekp_3 (Polygon)
17: ekp_4 (Polygon)
18: ekp_5 (Multi Polygon)
19: ekp_6 (Multi Polygon)
20: ekp_7 (Multi Polygon)

This command where I specify two target (PostGIS) tables with the -nln option fails miserably:

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_lines \
-nln vector_data.el_points \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" 

by returning this error:

Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Multi Point.
Insertion is likely to fail
ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiLineString) does not match column type (MultiPoint)
CONTEXT:  COPY el_points, line 1222, column wkb_geometry: "010500...B30AFC8..."

ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiLineString) does not match column type (MultiPoint)
CONTEXT:  COPY el_points, line 1, column wkb_geometry: "010500...40390E1714..."

And if I revert the order of these options:

-nln vector_data.el_lines \
-nln vector_data.el_points \

to:

-nln vector_data.el_points \
-nln vector_data.el_lines \

The error is somewhat the opposite:

Warning 1: Geometry to be inserted is of type Multi Point, whereas the layer geometry type is Multi Line String.
Insertion is likely to fail
ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiPoint) does not match column type (MultiLineString)
CONTEXT:  COPY el_lines, line 1, column wkb_geometry: "010400...AFC854674740"

ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiPoint) does not match column type (MultiLineString)
CONTEXT:  COPY el_lines, line 1, column wkb_geometry: "010400...3DF5A3634740"

Question

How could I achieve that?
Could we specify a kind of layers reading order (if this makes sense)?
Or specifying a mapping between the GPKG layers and the PostGIS tables?



Related question

Uploading Geopackage contents to PostgreSQL


Environment

$ ogr2ogr --version
GDAL 2.4.0, released 2018/12/14

psql    

select version();
"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

select postgis_version();
"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
  • 1
    It will not work that way. Run a separate ogr2ogr command for each layer. You can use only one -nln in the command so renaming many layers is not possible. Please show what layers you really have in your gpkg by running ogrinfo multiple_vector_data.gpkg. – user30184 Apr 3 at 11:47
4

You must run two separate commands

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_lines \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" "el_lines" 

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_points \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" "el_points" 

You wrote:

It could be especially interesting to extend it to mixture of geometry types in a single GPKG layer or geometry collections, and to multiples geometry columns in a single table (even if they are of the same type).

Mixture of geometry types is supported in GeoPackage when the geometry type is set to generic "geometry". With ogr2ogr it means -nlt geometry. QGIS does not support multiple geometry types on the same layer so it will split the layer into layer group. Some other clients like OpenJUMP do support mixed geometry types on the same layer.

Multiple geometry columns in the same table are not allowed in GeoPackage. For SpatiaLite they are OK and you can have for example native and simplified geometries in the same table. If you create a layer from such table with QGIS you must select which geometry to use.

| improve this answer | |
  • By appending the layer name to the GPKG file name, I've got a failure: Unable to open datasource '/path/to/multiple_vector_data.gpkg el_lines' with the following drivers. (followed by a long list of OGR drivers). – s.k Apr 3 at 13:47
  • Can you read data with ogrinfo multiple_vector_data.gpkg el_lines? If not it may be that underscore requires escaping in the command line. You can also try alternative way ogrinfo -sql "select * from el_line" multiple_vector_data.gpkg or ogrinfo -sql "select * from \"el_line\"" multiple_vector_data.gpkg. Or write options into a text file and use --optfile options.txt in the ogrinfo/ogr2ogr command. That prevents some problems with command line. – user30184 Apr 3 at 14:00
  • 1
    Or maybe separate the layer from the dataset "${gpkgdir}/multiple_vector_data.gpkg" "el_lines". Anyway, make the syntax to work with ogrinfo first. – user30184 Apr 3 at 14:07
  • 1
    You're on the right track, without any quote (double or single) it works. With some quotes, it doesn't, even after escaping underscore with a backslash \ . What is working on the other hand is a quoted filename followed by a space, and then by a quoted layer name, such as "${gpkgdir}/multiple_vector_data.gpkg" "el_lines". – s.k Apr 3 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.