delete layer from geopackage?


Ari Meyer
 

It appears that Fiona doesn't expose an API to delete a layer -- is that correct?  It seems there is no option to open a geopackage in read/write mode, and it won't even let me open an existing geopackage in write mode.  From the docs: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona.  https://fiona.readthedocs.io/en/stable/manual.html#writing-vector-data

As expected, I had similar problems trying this with OGR/GDAL.  It would seem that this is a common enough operation that there should be an API that can do this.  Right now, we have to execute a bunch of SQL DELETE calls, which is ugly and potentially error-prone.  Some have suggested a convoluted use of Geopandas: https://stackoverflow.com/questions/49291740/delete-rows-if-there-are-null-values-in-a-specific-column-in-pandas-dataframe/49291779#49291779 , but this is likewise not clean, IMHO.

Any suggestions?


Loïc Dutrieux
 

I don't know about deleting a layer, but opening an existing geopackage layer in write or append mode does work. See example:

import string
import tempfile, os

import fiona
from fiona.crs import from_epsg
from shapely.geometry import Point, mapping


gpkg = os.path.join(tempfile.gettempdir(), 'fiona_wa_test.gpkg')
sc0 = [Point(1,2), Point(3,4), Point(5,6)]
fc0 = [{'geometry': mapping(s),
'properties': {'letter': string.ascii_lowercase[idx]}}
for idx,s in enumerate(sc0)]
sc1 = [Point(7,8), Point(9,10), Point(11,12)]
fc1 = [{'geometry': mapping(s),
'properties': {'letter': string.ascii_lowercase[idx]}}
for idx,s in enumerate(sc1)]
schema = {'geometry': 'Point',
'properties': {'letter': 'str'}}
# Initial creation of geopackage file and layer + write fc0
with fiona.open(gpkg, 'w', driver='GPKG', schema=schema, crs=from_epsg(4326),
layer='test') as dst:
dst.writerecords(fc0)
# Append fc1 to existing layer
with fiona.open(gpkg, 'a', driver='GPKG', layer='test') as dst:
dst.writerecords(fc1)

________________________________________
From: main@fiona.groups.io <main@fiona.groups.io> on behalf of Ari Meyer <ari.meyer@gmail.com>
Sent: 26 July 2021 23:33:38
To: main@fiona.groups.io
Subject: [fiona] delete layer from geopackage?

It appears that Fiona doesn't expose an API to delete a layer -- is that correct? It seems there is no option to open a geopackage in read/write mode, and it won't even let me open an existing geopackage in write mode. From the docs: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona. https://fiona.readthedocs.io/en/stable/manual.html#writing-vector-data<https://urldefense.com/v3/__https://fiona.readthedocs.io/en/stable/manual.html*writing-vector-data__;Iw!!DOxrgLBm!UrEkP-JHQ-cFu4XmX_M6HcSiXvZQwNKGRNK6Q_V4yE3sCWM2M1FdArQ6J1Vkn3hxrkKUTzM$>

As expected, I had similar problems trying this with OGR/GDAL. It would seem that this is a common enough operation that there should be an API that can do this. Right now, we have to execute a bunch of SQL DELETE calls, which is ugly and potentially error-prone. Some have suggested a convoluted use of Geopandas: https://stackoverflow.com/questions/49291740/delete-rows-if-there-are-null-values-in-a-specific-column-in-pandas-dataframe/49291779#49291779<https://urldefense.com/v3/__https://stackoverflow.com/questions/49291740/delete-rows-if-there-are-null-values-in-a-specific-column-in-pandas-dataframe/49291779*49291779__;Iw!!DOxrgLBm!UrEkP-JHQ-cFu4XmX_M6HcSiXvZQwNKGRNK6Q_V4yE3sCWM2M1FdArQ6J1Vkn3hxXWmSdF0$> , but this is likewise not clean, IMHO.

Any suggestions?


Ari Meyer
 

Thank you, Loic.  I should have said that I wasn't able to open my geopackage in write mode without having to specify a schema -- I don't want to overwrite it.  I am able to open it in append mode as you're doing, though.  In any case, I was really hoping I could open one in a "read/write" mode whereby I could examine it and make modifications, but I guess that's not possible (as the docs say: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona.).  It looks like if I want to do something like that I have to open it in read mode, parse it, then write to a new geopackage.  Is that correct?

In any case, my main question is about deleting layers from a geopackage.  Does anyone know a way to do this without hand-coding a bunch of SQL DELETEs?
Ari

On Tue, Jul 27, 2021 at 3:58 AM Loïc Dutrieux <loic.dutrieux@...> wrote:
I don't know about deleting a layer, but opening an existing geopackage layer in write or append mode does work. See example:

import string
import tempfile, os

import fiona
from fiona.crs import from_epsg
from shapely.geometry import Point, mapping


gpkg = os.path.join(tempfile.gettempdir(), 'fiona_wa_test.gpkg')
sc0 = [Point(1,2), Point(3,4), Point(5,6)]
fc0 = [{'geometry': mapping(s),
       'properties': {'letter': string.ascii_lowercase[idx]}}
     for idx,s in enumerate(sc0)]
sc1 = [Point(7,8), Point(9,10), Point(11,12)]
fc1 = [{'geometry': mapping(s),
       'properties': {'letter': string.ascii_lowercase[idx]}}
     for idx,s in enumerate(sc1)]
schema = {'geometry': 'Point',
          'properties': {'letter': 'str'}}
# Initial creation of geopackage file and layer + write fc0
with fiona.open(gpkg, 'w', driver='GPKG', schema=schema, crs=from_epsg(4326),
                layer='test') as dst:
    dst.writerecords(fc0)
# Append fc1 to existing layer
with fiona.open(gpkg, 'a', driver='GPKG', layer='test') as dst:
    dst.writerecords(fc1)

________________________________________
From: main@fiona.groups.io <main@fiona.groups.io> on behalf of Ari Meyer <ari.meyer@...>
Sent: 26 July 2021 23:33:38
To: main@fiona.groups.io
Subject: [fiona] delete layer from geopackage?

It appears that Fiona doesn't expose an API to delete a layer -- is that correct?  It seems there is no option to open a geopackage in read/write mode, and it won't even let me open an existing geopackage in write mode.  From the docs: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona.  https://fiona.readthedocs.io/en/stable/manual.html#writing-vector-data<https://urldefense.com/v3/__https://fiona.readthedocs.io/en/stable/manual.html*writing-vector-data__;Iw!!DOxrgLBm!UrEkP-JHQ-cFu4XmX_M6HcSiXvZQwNKGRNK6Q_V4yE3sCWM2M1FdArQ6J1Vkn3hxrkKUTzM$>

As expected, I had similar problems trying this with OGR/GDAL.  It would seem that this is a common enough operation that there should be an API that can do this.  Right now, we have to execute a bunch of SQL DELETE calls, which is ugly and potentially error-prone.  Some have suggested a convoluted use of Geopandas: https://stackoverflow.com/questions/49291740/delete-rows-if-there-are-null-values-in-a-specific-column-in-pandas-dataframe/49291779#49291779<https://urldefense.com/v3/__https://stackoverflow.com/questions/49291740/delete-rows-if-there-are-null-values-in-a-specific-column-in-pandas-dataframe/49291779*49291779__;Iw!!DOxrgLBm!UrEkP-JHQ-cFu4XmX_M6HcSiXvZQwNKGRNK6Q_V4yE3sCWM2M1FdArQ6J1Vkn3hxXWmSdF0$> , but this is likewise not clean, IMHO.

Any suggestions?







Sean Gillies
 

Hi Ari,

On Tue, Jul 27, 2021 at 4:49 PM Ari Meyer <ari.meyer@...> wrote:
Thank you, Loic.  I should have said that I wasn't able to open my geopackage in write mode without having to specify a schema -- I don't want to overwrite it.  I am able to open it in append mode as you're doing, though.  In any case, I was really hoping I could open one in a "read/write" mode whereby I could examine it and make modifications, but I guess that's not possible (as the docs say: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona.).  It looks like if I want to do something like that I have to open it in read mode, parse it, then write to a new geopackage.  Is that correct?

In any case, my main question is about deleting layers from a geopackage.  Does anyone know a way to do this without hand-coding a bunch of SQL DELETEs?
Ari

Thanks for the question. It turns out that fiona's API for removing a dataset or layer hasn't surfaced in our documentation due to an oversight. Until I fix that, please see


--
Sean Gillies


Ari Meyer
 

That's great, Sean -- thanks!

On Wed, Aug 11, 2021 at 7:04 PM Sean Gillies <sean.gillies@...> wrote:
Hi Ari,

On Tue, Jul 27, 2021 at 4:49 PM Ari Meyer <ari.meyer@...> wrote:
Thank you, Loic.  I should have said that I wasn't able to open my geopackage in write mode without having to specify a schema -- I don't want to overwrite it.  I am able to open it in append mode as you're doing, though.  In any case, I was really hoping I could open one in a "read/write" mode whereby I could examine it and make modifications, but I guess that's not possible (as the docs say: The in situ “update” mode of OGR is quite format dependent and is therefore not supported by Fiona.).  It looks like if I want to do something like that I have to open it in read mode, parse it, then write to a new geopackage.  Is that correct?

In any case, my main question is about deleting layers from a geopackage.  Does anyone know a way to do this without hand-coding a bunch of SQL DELETEs?
Ari

Thanks for the question. It turns out that fiona's API for removing a dataset or layer hasn't surfaced in our documentation due to an oversight. Until I fix that, please see


--
Sean Gillies


Ari Meyer
 

Hi Sean,

I wasn't sure if you meant that this function was not currently working properly, or if it was just missing from the documentation.  In any case, I am able to execute it, but it fails:

fiona.remove('C:/SiteSelection/Senegal_test.gpkg', driver='GPKG', layer='AOI_PnLQzVP6')

Traceback (most recent call last):

  File "C:\OSGeo4W\apps\Python39\lib\code.py", line 90, in runcode

    exec(code, self.locals)

  File "<input>", line 1, in <module>

  File "C:\OSGeo4W\apps\Python39\lib\site-packages\fiona\__init__.py", line 310, in remove

    _remove_layer(path, layer, driver)

  File "fiona/ogrext.pyx", line 1640, in fiona.ogrext._remove_layer

fiona.errors.DatasetDeleteError: Failed to remove layer "AOI_PnLQzVP6" from datasource: C:/SiteSelection/Senegal_test.gpkg

I'm using QGIS LTR, which is bundling fiona 1.8.20.

FYI, if I try to run it from outside QGIS, I get the same error but more info:

ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0

 

Do I need to anything particular prior to calling the function?

Thanks,
Ari