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).
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"
ogrinfo multiple_vector_data.gpkg
.