Fiona: Spatialite support

[jamesmyatt]

I'm not clear about why Fiona doesn't support Spatialite files, especially since it does support GeoPackage file and OGR also supports Spatialite.

I assume that all SQLite files fall under the "RDBMS" category in https://fiona.readthedocs.io/en/latest/manual.html#rules-of-thumb, and Spatialite uses SQLite, but so does GeoPackage. Also https://github.com/Toblerity/Fiona/issues/121. This rule-of-thumb makes sense for a generic SQLite file where there are no standards for the tables and columns, but I think it's good for Fiona to support specific formats like Spatialite and GeoPackage, where SQLite is merely the container.

In particular, I am able to read Spatialite by patching fiona.supported_drivers. So the following code works exactly the same for both GeoPackage and Spatialite files (in fact, it should work the same for any multi-layer file supported by Fiona).

import fiona
import geopandas

# Force SQLite support(!?)
fiona.supported_drivers["SQLite"] = "r"

# Read layers
layers = fiona.listlayers(data_file)

# Load file using Fiona
dfs = {name: geopandas.read_file(data_file, layer=name) for name in layers}

Here's my "read_postgis" version of this code for Spatialite only and Windows only, as well as lots of other assumptions that I don't even know. I'm sure this can be improved, but it's the limit of my SQL and GIS knowledge/googling.

import contextlib
import sqlite3
import geopandas

dfs = {}
with contextlib.closing(sqlite3.connect(data_file)) as con:
    con.enable_load_extension(True)
    con.load_extension("mod_spatialite.dll")

    with contextlib.closing(con.cursor()) as c:
        layers = c.execute("SELECT f_table_name, f_geometry_column FROM geometry_columns").fetchall()

    for name, geom_col in layers:
        dfs[name] = pd.concat(
            [
                pd.read_sql_query(f"SELECT * FROM {name};", con).drop(
                    columns=["GEOMETRY", "OGC_FID"]
                ),
                geopandas.GeoDataFrame.from_postgis(
                    f"SELECT ST_AsBinary(GEOMETRY) as {geom_col} FROM {name};",
                    con,
                    geom_col=geom_col,
                ),
            ],
            axis=1,
        )

I think we can all agree that the read_file version of this code is far better, even if the read_postgis version is 3x faster.

I suppose, my main questions are:

• Am I way off base here?
• Have I misunderstood the parallels between GeoPackage and Spatialite?
• Am I lucky that the read_file version works?
• Is the correct approach actually one based on read_postgis?

I have fiona, geopandas, libspatialite, etc all installed via conda / conda-forge.

[sgillies]

Yes, we'll enable spatialite for 1.9.0.

[jamesmyatt]

Thanks. I thought I might be missing something.

In the meantime, is it OK to use the workaround above?

fiona.supported_drivers["SQLite"] = "r"

[sgillies]

@jamesmyatt yes, that's the thing to do.

[jamesmyatt]

Great. Thanks.