The day after GIS Day each year has become the de facto day to celebrate all things PostgreSQL and the spatial database extender, PostGIS Day. The folks at CrunchyData host this virtual space to showcase ways that PostgreSQL/PostGIS are being used. We gave two presentations on the latest tooling we’ve built that extends PostgreSQL databases. In case you missed the live presentations, here’s a summary of what Vincent and David shared.

Simple and Fast OGC API Servers for PostGIS with TiFeatures and TiMVT

TiMVT and TiFeatures are two open source projects we’ve recently released. TiMVT is for vector tiles and TiFeatures is for OGC features.

TiMVT is a Postgres-based vector tile server and TiFeatures is an OGC Features API. These tools/libraries can be placed in front of any PostgreSQL instance, automatically exposing tables, views and functions through multiple endpoints. TiMVT/TiFeatures can be used directly as an application or be incorporated within any FastAPI application giving the ability to leverage the full ecosystem for functionality such as authentication or to extend the API with business-specific needs.

Summary of TiMVT & TiFeatures key attributes:

TiMVT and TiFeatures, along with TiTiler for raster services, complete a trio of python-FastAPI services. The current roadmap includes merging TiFeatures & TiMVT, the vector-based services, into a single project to reduce code redundancy.

Vincent’s slides from PostGIS Day. Recording

Intro to PgSTAC

PgSTAC is a set of schema, functions and triggers for a PostgreSQL database to provide for the storage and discovery of Items in a STAC Catalog. It focuses on the storage and search parts of creating a STAC API entirely at the database level with the goal of keeping core functionality as close to the data as possible.

The SpatioTemporal Asset Catalog (STAC) specification provides a common structure for describing and cataloging spatiotemporal assets. A spatiotemporal asset is any file that represents information about the earth captured in a certain space and time.

STAC basics

• Assets: Any spatiotemporal file that represents information about the earth captured in a certain space and time. This can be anything that has a URL: Cloud Optimized Geotiffs (COGs), Thumbnails, xml/text.json metadata, JPEG, GeoJSON, Parquet, ZARR, KML…the list goes on.
• Items: Assets that are related to the same time and place -collected in the same way- are part of an Item. An Item is a valid GeoJSON Feature representing the extent of the Assets. A Landsat scene, DEM product, LULC mosaic.
• Collections: A grouping of Items that share common metadata. Collection examples could be Landsat, NAIP, 3DEP LiDAR, US Census.

PgSTAC Basics

PgSTAC was built to power the STAC API for the Microsoft Planetary Computer - the largest public STAC Catalog with 95 Collections, 87 million Items and 1.7 billion Assets.

PgSTAC takes care of data ingestion, partition management, index creation and search. It exposes functions to provide search and filtering that conforms to the latest release of the STAC API including the following extensions: Fields, Sort, Context, Filter and Query. The filter extension allows the use of OGC CQL2-JSON expressions to search Items using spatial, temporal and other operators with a high level of expressiveness.

PgSTAC relies heavily on the JSONB datatype and functionalities of PostgreSQL to provide flexible schema/storage of STAC Items. It does not provide validation to the STAC specification or STAC Extensions.

STAC Collections and Items should be constructed and validated by libraries such as PySTAC or STAC Lint prior to being ingested into PgSTAC.

In order to create a STAC API, a PgSTAC database must be exposed through an API. This can be done through any language that has libraries to PostgreSQL such as STAC-FastAPI (Python) or Franklin (Java).

PgSTAC also has a few tricks up its sleeves

When the item-assets extension is used at the Collection level, PgSTAC will reduce the size of stored Assets in Items by stripping data fields that are the same across a Collection while maintaining full Asset data upon retrieval.

Search is optimized to return Items sorted by datetime leveraging PostgreSQL partitions to search in a chunked manner avoiding expensive full table scans.

PgSTAC also provides a geometrysearch function (used by TiTiler-PgSTAC) that assists in creating dynamic tiles from the results of a STAC search. The geometrysearch function allows the ability to register a search to obtain a hash of the filter json. This hash can then be used along with a passed-in geometry to return only the Items that (based on their geometry) are visible when displayed within the geometry. The geometrysearch function can be useful to create things like, for example, a Z/X/Y tile service from search results to make a mosaic of the most recent imagery with less than 10% cloud cover.

David’s slides from PostGIS Day. Recording