0

I've tried to import GPKG file data into PostGIS using ogr2ogr, but can't seem to understand if it is possible to import the style symbology contained within the GPKG aswell.

I've noticed ogr2ogr adds a few new columns related to styling but all as null in the database:

f_table_catalog, f_table_schema, f_table_name, f_geometry_column, stylename, styleqml, stylesld, useasdefault, description, owner, ui

Is it possible?


Update #1: Additional Info

My GeoPackage: https://geo2.dgterritorio.gov.pt/cos/COS2018/COS2018v2-gpkg.zip

Command used to import data:

ogr2ogr -f "PostgreSQL" PG:"host=localhost dbname=postgres user=postgres password=postgres" -nln COS2018v2 -progress COS2018v2.gpkg

No layer_styles table in postgis:

ERROR: relation "public.layer_styles" does not exist 
LINE 1: SELECT * from public.layer_styles

orginfo output:

$>ogrinfo COS2018v2.gpkg 
INFO: Open of `COS2018v2.gpkg'
      using driver `GPKG' successful.
1: COS2018v2 (Multi Polygon)
2: layer_styles (None)

Update #2

Updated the ogr2ogr command to:

ogr2ogr -f "PostgreSQL" PG:"host=localhost dbname=postgres user=postgres password=postgres" -progress COS2018v2.gpkg

Specifying -nln COS2018v2 was causing ogr2ogr to join both layers into 1 table.

Additionally I can't seem to load PostGIS styles into QGIS 3.34 on Windows 10. Any help would be appreciated!

6
  • Did you have the GeoPackage styles saved into GeoPackage? I believe that it is not the default. If you have styles in GeoPackage then ogrinfo my_geopackage.gpkg layer_styles should list them. If you now have an empty layer_styles in PostGIS maybe the table was empty already in GeoPackage.
    – user30184
    Nov 9, 2023 at 18:29
  • @user30184 I updated my question with additional information based on your questions. Nov 10, 2023 at 11:18
  • So you do have the QGIS styles stored into GeoPackage, that is a good start. I think I would try to append the contents of layer_styles into PostGIS with something like ogr2ogr -append PG:.... my_geopackage.gpkg layer_styles. If it fails, use pgAdmin or whatever tool you have for inserting data into layer_styles on the PostGIS side. Perhaps QGIS can then load them blog.rustprooflabs.com/2019/08/postgis-qgis-create-layers.
    – user30184
    Nov 10, 2023 at 11:37
  • @user30184 Apparently the issue was specifying '-nln COS2018v2' in my ogr2ogr command. It was attempting to merge both layers under the same table. After re-running , it creates both tables. Now I have no idea how to load these styles from PostGIS to QGIS, I've tried several plugins with no success. I use QGIS 3.34 on Windows 10 and when I try to load style (Layer > Properties > Symbology > Style > Load Style) I only get these options: i.imgur.com/4sN2Hvd.png Nov 10, 2023 at 12:39
  • "From datasource database" should be the right option.
    – user30184
    Nov 10, 2023 at 13:01

1 Answer 1

0

I made some testing by comparing what was stored into layer_styles by ogr2ogr and by saving the styles into PostGIS with QGIS. It appeared that ogr2ogr left three important fields empty:

  • f_table_catalog (the name of the database)
  • f_table_schema (the name of the table)
  • owner (I am not sure what owner this is and the field did not fit into this screenshot)

enter image description here

I edited the row with pgAdmin and added data into these 3 fields. As an owner I used the same owner that QGIS itself inserted into the table. Now when I ask QGIS to load the styles it finds them automatically

enter image description here

EDIT

I made some more tests with the "owner". What QGIS inserted into the field is my Windows user name. I edited the value into the owner of the PostGIS table, and next into the owner of the PostgreSQL database, and both worked. But I also noticed that I could update the owner into "null", and I could still load the styles again into QGIS. So somehow the owner field was required for the first loading of styles, but not later. That is odd but not a critical bug.

Edit 2 Additional question 1: For loading just table "COS2018v2" of these two

1: COS2018v2 (Multi Polygon)
2: layer_styles (None)

add the layer name after the datasource name into ogr2ogr command ogr2ogr -f "PostgreSQL" PG:"..." COS2018v2.gpkg COS2018v2 or use SQL for selecting the data ...-sql "select * from COS2018v2"

Additional question 2: For creating a layer_styles insert that works in PostGIS without further editing, use SQL. A prototype command:

ogr2ogr -f "PostgreSQL" PG:"..." -sql "select 'database_name' as f_table_catalog,'schema_name' as f_table_schema,'table_name' as f_table_name,[rest of the fields] from layer_styles" COS2018v2.gpkg
3
  • This fixed the issue, you are a legend! making sure f_table_catalog, f_table_schema, f_table_name all have the correct values made it work :). Just 2 additional questions, if possible: 1. Can you load the table on QGIS but choose not to include the styles ? 2. Is there any way to load the layer_styles table correctly when using ogr2ogr ? Nov 12, 2023 at 15:21
  • Both are possible. The first one is easy, the second one seems more tricky but it is still just standard SQL. See the edited answer.
    – user30184
    Nov 12, 2023 at 18:17
  • I'm sorry I didn't explain the first question (1.) properly. When importing the PostGIS data to QGIS is it possible to not include the layer_styles information or to turn it off as it is performance heavy on the QGIS side? Nov 13, 2023 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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