No Clocks

FlatGeobuf, which is ideal for making PMTiles with Tippecanoe due to its spatial index, and GeoParquet, which is compact and fast, are the primary formats I explored

Even cooler, with Parquet you can select different types of compression. The default compression for most of the tools is ‘snappy’, which I’m pretty sure means that it optimizes speed of compression / decompression over absolute size.

Parquet output is not valid GeoParquet, since it doesn’t have the proper metadata, but it is a ‘ compatible ’ parquet file (WKB in lat/long w/ column named ‘geometry’), so the awesome GPQ is able to convert it. For this particular file the conversion adds 3.64 seconds, so even with the conversion it’s still faster than Pandas. And DuckDB should support native GeoParquet output at some point, and writing the proper metadata shouldn’t add any performance overhead, so the headline times will be real in the future.

One issue I noticed is that when you want to get the summary of the layer, with ogrinfo -al -so the.gpkg, it was very slow. The reason is that this summary includes the feature count, and there's no way to get this metadata quickly, apart from running the "SELECT COUNT(*) FROM the_table" request, which causes a full scan of the table.

But getting the spatial extent of the layer, which is one of the other information displayed by the summary mode of ogrinfo, is fast since the gpkg_contents "system" table of a GeoPackage database includes the bounding box of the table. So my idea was to extend the definition of the gpkg_contents table to add a new column, ogr_feature_count, to store the feature count.

Anyway, that's only the theory and we don't want to break interoperability for just a nice-to-have feature... So I went to change the design a bit and created a new table, gpkg_ogr_contents, with a table_name and feature_count columns. I'm aware that I should not borrow the gpkg_ prefix, but I felt it was safer to do so since other implementations will probably ignore any unknown gpkg_ prefixed table.

And the addition of the ogr_ prefix makes collisions with future extension of the GeoPackage specification unlikely. The content of this table is also maintained in synchronization with the data table thanks to two triggers, and this makes the other software that rejected my first attempt happy. Problem solved.

building the spatial index took ages.

In the GeoPackage driver, the spatial index is indeed created after feature insertion, so that the feature table and spatial index tables are well separated in the file, and from previous experiment with the Spatialite driver, it proved to be the right strategy

Populating the SQLite R-Tree is done with a simple statement: INSERT INTO my_rtree SELECT fid, ST_MinX(geom), ST_MaxX(geom), ST_MinY(geom), ST_MaxY(geom) FROM the_table. Analyzing what happens in the SQLite code is not easy when you are not familiar with that code base, but my intuition is that there was constant back and forth between the geometry data area and the RTree area in the file, making the SQLite page cache inefficient.

Let's iterate over the feature table and collect the fid, minx, max, miny, maxy by chunks of 100 000 rows, and the insert those 100 000 bounding boxes into the R-Tree, and loop again unil we have completely read the feature table. With such a strategy, the spatial index can now be built in 4h30. The resulting GeoPackage file weights 31.6 GB, so twice at large than the FileGeoDatabase.

GeoPackage uses a uncompressed SQLite BLOB based on OGC WKB

Another experiment showed that increasing the SQLite page size from 1024 bytes (the default in SQLite 3.11 or earlier) to 4096 bytes (the default since SQLite 3.12) decreases the database size to 28.8 GB. This new page size of 4096 bytes is now used by default by the OGR SQLite and GPKG drivers (unless OGR_SQLITE_PRAGMA=page_size=xxxx is specified as a configuration option).

I also discovered that increasing the SQLite page cache from its 2 MB default to 2 GB (with --config OGR_SQLITE_CACHE 2000) significantly improved the time to build the spatial index, decreasing the total conversion time from 4h30 to 2h10. 2GB is just a value selected at random. It might be too large or perhaps a larger value would help further.

Expected metadata tables (gpkg_contents, gpkg_spatial_ref_sys, gpkg_geometry_columns)

Binary format encoding for geometries in spatial tables (basically a GPKG standard header object followed by ISO standard well-known binary (WKB))

Naming and conventions for extensions (extended feature types) and indexes (how to use SQLite r-tree in an interoperable manner)

This driver reads and writes SQLite files from the file system, so it must be run by a user with read/write access to the files it is working with.

GeoPackage version 1.0, 1.1, 1.2, 1.3 or 1.4 can be specified through the VERSION dataset creation option. Starting with GDAL 3.11, it defaults to 1.4. For earlier versions, it defaults to 1.2.

GeoPackage only supports one geometry column per table.

The driver supports OGR attribute filters, and users are expected to provide filters in the SQLite dialect, as they will be executed directly against the database.

SQL SELECT statements passed to ExecuteSQL() are also executed directly against the database.

If the SQLite dialect is used with the SpatiaLite functions, an explicit cast operator AsGPB() is required to transform SpatiaLite geometries into GeoPackage geometries.

ogrinfo -update points.gpkg -sql "INSERT INTO points (geom) values (AsGPB(ST_GeomFromText('POINT(3 54)', 4326)))"

The reverse conversion from GPKG geometries into SpatiaLite geometries is automatically done.

The "DROP TABLE layer_name" and "ALTER TABLE layer_name RENAME TO new_layer" statements can be used. They will update GeoPackage system tables.

The "HasSpatialIndex('table_name','geom_col_name')" statement can be used for checking if the table has spatial index on the named geometry column.

When dropping a table, or removing records from tables, the space they occupied is not immediately released and kept in the pool of file pages that SQLite may reuse later. If you need to shrink the file to its minimum size, you need to issue an explicit "VACUUM" SQL request. Note that this will result in a full rewrite of the file.

ST_MinX(geom Geometry): returns the minimum X coordinate of the geometry ST_MinY(geom Geometry): returns the minimum Y coordinate of the geometry ST_MaxX(geom Geometry): returns the maximum X coordinate of the geometry ST_MaxY(geom Geometry): returns the maximum Y coordinate of the geometry ST_IsEmpty(geom Geometry): returns 1 if the geometry is empty (but not null), e.g. a POINT EMPTY geometry ST_GeometryType(geom Geometry): returns the geometry type: 'POINT', 'LINESTRING', 'POLYGON', 'MULTIPOLYGON', 'MULTILINESTRING', 'MULTIPOINT', 'GEOMETRYCOLLECTION' ST_SRID(geom Geometry): returns the SRID of the geometry GPKG_IsAssignable(expected_geom_type String, actual_geom_type String): mainly, needed for the 'Geometry Type Triggers Extension'

Link with Spatialite

If GDAL has been compiled against Spatialite, it is also possible to use Spatialite SQL functions. Transformation from GPKG geometry binary to Spatialite geometry binary encoding is done implicitly

ogrinfo poly.gpkg -sql "SELECT ST_Buffer(geom,5) FROM poly"

Note that due to the loose typing mechanism of SQLite, if a geometry expression returns a NULL value for the first row, this will generally cause OGR not to recognize the column as a geometry column.

It might be then useful to sort the results by making sure that non-null geometries are returned first

ogrinfo poly.gpkg -sql "SELECT * FROM (SELECT ST_Buffer(geom,5) AS geom, * FROM poly) ORDER BY geom IS NULL ASC"

The driver implements transactions at the database level, per RFC 54: Dataset transactions

Many-to-many relationship retrieval is supported, respecting the OGC GeoPackage Related Tables Extension. One-to-many relationships will also be reported for tables which utilize FOREIGN KEY constraints.

Open options can be specified in command-line tools using the syntax -oo <NAME>=<VALUE> or by providing the appropriate arguments to GDALOpenEx() (C) or gdal.OpenEx (Python).

LIST_ALL_TABLES=[AUTO/YES/NO]: Defaults to AUTO. Whether all tables, including those not listed in gpkg_contents, should be listed. If AUTO, all tables including those not listed in gpkg_contents will be listed, except if the aspatial extension is found or a table is registered as 'attributes' in gpkg_contents. If YES, all tables including those not listed in gpkg_contents will be listed, in all cases. If NO, only tables registered as 'features', 'attributes' or 'aspatial' will be listed.

PRELUDE_STATEMENTS=value: (GDAL >= 3.2) SQL statement(s) to send on the SQLite3 connection before any other ones. In case of several statements, they must be separated with the semicolon (;) sign. This option may be useful to attach another database to the current one and issue cross-database requests.

NOLOCK=[YES/NO]: (GDAL >= 3.4.2) Defaults to NO. Whether the database should be used without doing any file locking. Setting it to YES will only be honoured when opening in read-only mode and if the journal mode is not WAL. This corresponds to the nolock=1 query parameter described at https://www.sqlite.org/uri.html

IMMUTABLE=[YES/NO]: (GDAL >= 3.5.3) Whether the database should be opened by assuming that the file cannot be modified by another process. This will skip any checks for change detection. This can be useful for WAL enabled files on read-only storage. GDAL will automatically try to turn it on when not being able to open in read-only mode a WAL enabled file. This corresponds to the immutable=1 query parameter described at https://www.sqlite.org/uri.html

Note: open options are typically specified with "-oo name=value" syntax in most OGR utilities, or with the GDALOpenEx() API call.

Note: configuration option OGR_SQLITE_JOURNAL can be used to set the journal mode of the GeoPackage (and thus SQLite) file, see also https://www.sqlite.org/pragma.html#pragma_journal_mode.

When creating a new GeoPackage file, the driver will attempt to force the database into a UTF-8 mode for text handling, satisfying the OGR strict UTF-8 capability. For pre-existing files, the driver will work with whatever it is given.

The driver updates the GeoPackage last_change timestamp when the file is created or modified. If consistent binary output is required for reproducibility, the timestamp can be forced to a specific value by setting the global configuration option. When setting the option, take care to meet the specific time format requirement of the GeoPackage standard, e.g. for version 1.2.

Dataset creation options can be specified in command-line tools using the syntax -dsco <NAME>=<VALUE> or by providing the appropriate arguments to GDALCreate() (C) or Driver.Create (Python).

VERSION=[AUTO/1.0/1.1/1.2/1.3/1.4]: Set GeoPackage version (for application_id and user_version fields). In AUTO mode, this will be equivalent to 1.4 starting with GDAL 3.11 (1.2 in prior versions) 1.3 is available starting with GDAL 3.3 1.4 is available starting with GDAL 3.7.1

ADD_GPKG_OGR_CONTENTS=[YES/NO]: Defaults to YES. Defines whether to add a gpkg_ogr_contents table to keep feature count, and associated triggers.

DATETIME_FORMAT=[WITH_TZ/UTC]: (GDAL >= 3.2) Defaults to WITH_TZ. Defines whether to keep the DateTime values in the time zones as used in the data source (WITH_TZ), or to convert the date and time expressions to UTC (Coordinated Universal Time). Pedantically, non-UTC time zones are not currently supported by GeoPackage v1.3 (see https://github.com/opengeospatial/geopackage/issues/530). When using UTC format, with a unspecified timezone, UTC will be assumed.

CRS_WKT_EXTENSION=[YES/NO]: (GDAL >= 3.8) Defaults to NO. Defines whether to add the definition_12_063 column to the gpkg_spatial_ref_sys system table, according to http://www.geopackage.org/spec/#extension_crs_wkt. The default is NO, unless the tile gridded coverage extension is used. With VERSION >= 1.4, a epoch column is also added. WKT strings in definition_12_063 will follow the WKT2:2015 standard when possible, but may use the WKT2:2019 standard for specific cases (dynamic CRS with coordinate epoch). This option generally does not need to be specified, as the driver will automatically update the gpkg_spatial_ref_sys table when needed, but it may be useful to create GeoPackage datasets matching the exceptions of other software or profiles (such as the DGIWG-GPKG profile).

Layer creation options can be specified in command-line tools using the syntax -lco <NAME>=<VALUE> or by providing the appropriate arguments to GDALDatasetCreateLayer() (C) or Dataset.CreateLayer (Python

OGR_SQLITE_JOURNAL=value: can be used to set the journal mode of the SQLite file, see also https://www.sqlite.org/pragma.html#pragma_journal_mode.

OGR_SQLITE_CACHE=value: see.

OGR_SQLITE_SYNCHRONOUS=value: see.

OGR_SQLITE_LOAD_EXTENSIONS=[<extension1,...,extensionN>/ENABLE_SQL_LOAD_EXTENSION]: (GDAL >= 3.5.0) Comma separated list of names of shared libraries containing extensions to load at database opening. If a file cannot be loaded directly, attempts are made to load with various operating-system specific extensions added. So for example, if "samplelib" cannot be loaded, then names like "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried also. The special value ENABLE_SQL_LOAD_EXTENSION can be used to enable the use of the SQL load_extension() function, which is normally disabled in standard builds of sqlite3. Loading extensions as a potential security impact if they are untrusted.

OGR_SQLITE_PRAGMA=value: with this option any SQLite pragma can be specified. The syntax is OGR_SQLITE_PRAGMA = "pragma_name=pragma_value[,pragma_name2=pragma_value2]*"

OGR_CURRENT_DATE=value: the driver updates the GeoPackage last_change timestamp when the file is created or modified. If consistent binary output is required for reproducibility, the timestamp can be forced to a specific value by setting this global configuration option. When setting the option, take care to meet the specific time format requirement of the GeoPackage standard, e.g. for version 1.2.

SQLITE_USE_OGR_VFS=[YES/NO]: YES enables extra buffering/caching by the GDAL/OGR I/O layer and can speed up I/O. More information here. Be aware that no file locking will occur if this option is activated, so concurrent edits may lead to database corruption.

OGR_GPKG_NUM_THREADS=value: (GDAL >= 3.8.3) Can be set to an integer or ALL_CPUS. This is the number of threads used when reading tables through the ArrowArray interface, when no filter is applied and when features have consecutive feature ID numbering. The default is the minimum of 4 and the number of CPUs. Note that setting this value too high is not recommended: a value of 4 is close to the optimal.

The core GeoPackage specification of GeoPackage 1.0 and 1.1 did not support non-spatial tables. This was added in GeoPackage 1.2 as the "attributes" data type.

The driver allows creating and reading non-spatial tables with the GeoPackage aspatial extension.

The driver will also, by default, list non spatial tables that are not registered through the gdal_aspatial extension, and support the GeoPackage 1.2 "attributes" data type as well. Non spatial tables are by default created following the GeoPackage 1.2 "attributes" data type (can be controlled with the layer creation option).

Views can be created and recognized as valid spatial layers if a corresponding record is inserted into the gpkg_contents and gpkg_geometry_columns table.

In the case of the columns in the SELECT clause of the view acts a integer primary key, then it can be recognized by OGR as the FID column of the view, provided it is renamed as OGC_FID. Selecting a feature id from a source table without renaming will not be sufficient, since due to joins this feature id could appear several times. Thus the user must explicitly acknowledge that the column is really a primary key.

CREATE VIEW my_view AS SELECT foo.fid AS OGC_FID, foo.geom FROM foo JOIN another_table ON foo.some_id = another_table.other_id; INSERT INTO gpkg_contents (table_name, identifier, data_type, srs_id) VALUES ( 'my_view', 'my_view', 'features', 4326); INSERT INTO gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m) values ('my_view', 'my_geom', 'GEOMETRY', 4326, 0, 0);

This requires GDAL to be compiled with the SQLITE_HAS_COLUMN_METADATA option and SQLite3 with the SQLITE_ENABLE_COLUMN_METADATA option. This can be easily verified if the SQLITE_HAS_COLUMN_METADATA=YES driver metadata item is declared (for example with "ogrinfo --format GPKG").

Starting with GDAL 3.7.1, it is possible to define a geometry column as the result of a Spatialite spatial function. Note however that this is an extension likely to be non-interoperable with other software that does not activate Spatialite for the SQLite3 database connection. Such geometry column should be registered into the gpkg_extensions using the gdal_spatialite_computed_geom_column extension name (cf GeoPackage Spatialite computed geometry column extension), like below:

libsqlite3

Package development workflows make much more sense if you understand the five states an R package can be in: source bundled binary installed in-memory

You already know some of the functions that put packages into these states. For example, install.packages() can move a package from the source, bundled, or binary states into the installed state. devtools::install_github() takes a source package on GitHub and moves it into the installed state. The library() function loads an installed package into memory, making it available for immediate and direct use.

A source package is just a directory of files with a specific structure. It includes particular components, such as a DESCRIPTION file, an R/ directory containing .R files, and so on. Most of the remaining chapters in this book are dedicated to detailing these components.

A bundled package is a package that’s been compressed into a single file. By convention (from Linux), package bundles in R use the extension .tar.gz and are sometimes referred to as “source tarballs”. This means that multiple files have been reduced to a single file (.tar) and then compressed using gzip (.gz). While a bundle is not that useful on its own, it’s a platform-agnostic, transportation-friendly intermediary between a source package and an installed package.

In the rare case that you need to make a bundle from a package you’re developing locally, use devtools::build(). Under the hood, this calls pkgbuild::build() and, ultimately, R CMD build, which is described further in the Building package tarballs section of Writing R Extensions.

This should tip you off that a package bundle or “source tarball” is not simply the result of making a tar archive of the source files, then compressing with gzip. By convention, in the R world, a few more operations are carried out when making the .tar.gz file and this is why we’ve elected to refer to this form as a package bundle, in this book.

Every CRAN package is available in bundled form, via the “Package source” field of its landing page.

The main differences between a source package and an uncompressed bundle are: Vignettes have been built, so rendered outputs, such as HTML, appear below inst/doc/ and a vignette index appears in the build/ directory. A local source package might contain temporary files used to save time during development, like compilation artefacts in src/. These are never found in a bundle. Any files listed in .Rbuildignore are not included in the bundle. These are typically files that facilitate your development process, but that should be excluded from the distributed product.

Each line of .Rbuildignore is a Perl-compatible regular expression that is matched, without regard to case, against the path to each file in the source package1. If the regular expression matches, that file or directory is excluded. Note there are some default exclusions implemented by R itself, mostly relating to classic version control systems and editors, such as SVN, Git, and Emacs.

We usually modify .Rbuildignore with the usethis::use_build_ignore() function, which takes care of easy-to-forget details, such as regular expression anchoring and escaping. To exclude a specific file or directory (the most common use case), you MUST anchor the regular expression. For example, to exclude a directory called “notes”, the .Rbuildignore entry must be ^notes$, whereas the unanchored regular expression notes will match any file name containing “notes”, e.g. R/notes.R, man/important-notes.R, data/endnotes.Rdata, etc. We find that use_build_ignore() helps us get more of our .Rbuildignore entries right the first time.

.Rbuildignore is a way to resolve some of the tension between the practices that support your development process and CRAN’s requirements for submission and distribution (Chapter 22). Even if you aren’t planning to release on CRAN, following these conventions will allow you to make the best use of R’s built-in tooling for package checking and installation. The files you should .Rbuildignore fall into two broad, semi-overlapping classes: Files that help you generate package contents programmatically. Examples: Using README.Rmd to generate an informative and current README.md (Section 18.1). Storing .R scripts to create and update internal or exported data (Section 7.1.1). Files that drive package development, checking, and documentation, outside of CRAN’s purview. Examples: Files relating to the RStudio IDE (Section 4.2). Using the pkgdown package to generate a website (Chapter 19). Configuration files related to continuous integration/deployment (Section 20.2).

Here is a non-exhaustive list of typical entries in the .Rbuildignore file for a package in the tidyverse: ^.*\.Rproj$ # Designates the directory as an RStudio Project ^\.Rproj\.user$ # Used by RStudio for temporary files ^README\.Rmd$ # An Rmd file used to generate README.md ^LICENSE\.md$ # Full text of the license ^cran-comments\.md$ # Comments for CRAN submission ^data-raw$ # Code used to create data included in the package ^pkgdown$ # Resources used for the package website ^_pkgdown\.yml$ # Configuration info for the package website ^\.github$ # GitHub Actions workflows

If you want to distribute your package to an R user who doesn’t have package development tools, you’ll need to provide a binary package. The primary maker and distributor of binary packages is CRAN, not individual maintainers. But even if you delegate the responsibility of distributing your package to CRAN, it’s still important for a maintainer to understand the nature of a binary package.

Binary packages for macOS are stored as .tgz, whereas Windows binary packages end in .zip. If you need to make a binary package, use devtools::build(binary = TRUE) on the relevant operating system. Under the hood, this calls pkgbuild::build(binary = TRUE) and, ultimately, R CMD INSTALL --build, which is described further in the Building binary packages section of Writing R Extensions. If you choose to release your package on CRAN (Chapter 22), you submit your package in bundled form, then CRAN creates and distributes the package binaries.

An installed package is a binary package that’s been decompressed into a package library (described in Section 3.7). Figure 3.2 illustrates the many ways a package can be installed, along with a few other functions for converting a package from one state to another. This diagram is complicated! In an ideal world, installing a package would involve stringing together a set of simple steps: source -> bundle, bundle -> binary, binary -> installed. In the real world, it’s not this simple because there are often (faster) shortcuts available.