1

I create an example file, example.gpkg using Python:

import geopandas as gpd
import shapely.geometry

gdf = gpd.GeoDataFrame.from_dict(dict(
        geometry=[shapely.geometry.Point([0, 0]), shapely.geometry.Point([1, 1])],
        col0=["a","b"]))

gdf.to_file("example.gpkg",driver="GPKG")

I would like to add another property to each geometry using ogrinfo. To do this, I try creating another column, col1 & try setting the col1 value of each geometry to 1234 as an example. (This thread explains how to do this on a SpatiaLite database using ogr2ogr - now I'm working with a GPKG file.) I do:

sql="
ALTER TABLE example ADD COLUMN col1 INTEGER;
UPDATE example SET col1=1234;
"

ogrinfo -dialect SQLite -sql "${sql}" example.gpkg

However, when I read back the GPKG into a GeoDataFrame:

gdf = gpd.read_file("example.gpkg")

and print(gdf), I get:

  col0  col1                 geometry
0    a   NaN  POINT (0.00000 0.00000)
1    b   NaN  POINT (1.00000 1.00000)

ie all the col1 values are NaN, instead of 1234, so this approach failed.

How do I add a new column to a GPKG file using ogrinfo?

5
  • 2
    I believe that ogrinfo can send only one SQL statement. Run ogrinfo separately for ALTER TABLE and for UPDATE.
    – user30184
    Commented Dec 11, 2023 at 13:05
  • Wow, that worked! Would you like to add that as an answer? If you know documentation where one could have learnt this, it would be awesome to reference that.
    – zabop
    Commented Dec 11, 2023 at 13:07
  • Maybe not as explicit as can be, but in the documentation of ogrinfo it is stated like this: Execute the indicated SQL statement and return the result. SQL statement is singular, so you can only run one SQL statement at a time.
    – Pieter
    Commented Dec 11, 2023 at 17:32
  • Not sure what exactly you want to accomplish, but just for info: SQLite also supports specyfing a default value for a column, so you can also do the following: ALTER TABLE example ADD COLUMN col1 INTEGER DEFAULT 1234. This will fill up all rows automatically with 1234, but if you insert new rows without specifying a value for column col1 it will also automatically get 1234 as value.
    – Pieter
    Commented Dec 11, 2023 at 17:54
  • The -sql option in ogr2ogr or ogrinfo is basically launching the ExecuteSQL() gdal.org/doxygen/… function for the datastore. Feel free to write the answer yourself.
    – user30184
    Commented Dec 11, 2023 at 21:23

1 Answer 1

3

We need to split up the query to 2 SQL statements, as ogrinfo can send 1 SQL statement at a time.

sql0="ALTER TABLE example ADD COLUMN col1 INTEGER;"
sql1="UPDATE example SET col1=1234;"

ogrinfo -dialect SQLite -sql "${sql0}" example.gpkg
ogrinfo -dialect SQLite -sql "${sql1}" example.gpkg

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.