streams/crossings extracts - column type incompatible

[smnorris]

Pulling from the posted .gdb is a nice workaround vs trying to page through the api. Unfortunately esri file geodatabases appear not to support a boolean type and ogr is translating the habitat columns to an integer. This is not compatible with existing map layer definitions in qgis. Maybe a -sql statement in the ogr2ogr command can fix?

$ ogrinfo bcfishpass.gdb streams -so
INFO: Open of `bcfishpass.gdb'
      using driver `OpenFileGDB' successful.

Layer name: streams
Geometry: Multi Line String
....<etc>
spawning_model_ch: Integer (0.0)
spawning_model_co: Integer (0.0)
spawning_model_sk: Integer (0.0)
spawning_model_st: Integer (0.0)
spawning_model_wct: Integer (0.0)
rearing_model_ch: Integer (0.0)

https://github.com/NewGraphEnvironment/dff-2022/blob/master/scripts/background_layers.sh#L61

[smnorris]

I'd post as geopackage but user feedback about accessing that format (especially for large layers like streams) has been poor.

[smnorris]

Interestingly, sqlite/geopackage boolean support is much the same as file geodatabase: https://www.sqlite.org/datatype3.html#:~:text=SQLite%20does%20not%20have%20a,literals%201%20and%200%20respectively

But ogr2ogr does a better job at encoding them than with the file geodatabase driver - this is from an existing "clipped_streams.gpkg"

spawning_model_ch: Integer(Boolean) (0.0)
spawning_model_co: Integer(Boolean) (0.0)
spawning_model_sk: Integer(Boolean) (0.0)
spawning_model_st: Integer(Boolean) (0.0)
spawning_model_wct: Integer(Boolean) (0.0)
rearing_model_ch: Integer(Boolean) (0.0)
rearing_model_co: Integer(Boolean) (0.0)
rearing_model_sk: Integer(Boolean) (0.0)
rearing_model_st: Integer(Boolean) (0.0)
rearing_model_wct: Integer(Boolean) (0.0)

[smnorris]

Using the -sql option and wrapping the habitat columns like cast(spawning_model_ch as boolean) as spawning_model_ch, works fine.

But:

• this requires spelling out the required columns in this query, creating yet another place where column names will have to be updated with model changes
• dumping streams to flatgeobuf on the web server avoids having to make the casts - and makes for much faster downloads

I'll host flatgeobuf versions of the data. It is more redundant data but the format is really nice for quick downloads. Also handy as a fast input to tippecanoe if we move to storing the streams as vector tiles in the project.

[smnorris]

Test of extracting VICT from the two file formats:

• fgb: 9s
• zipped .gdb: 12m4s

I think the redundant data is ok

[smnorris]

fixed with 065b288a16ddfb684092d206ae4e54df473e3a18. Extracts are now pretty quick. I wonder how pgfs compares to requests from a flatgeobuf.