Skip to content

pgstac integration

stac_geoparquet.pgstac_reader has some helpers for working with items coming from a pgstac.items table. It takes care of

  • Rehydrating the dehydrated items
  • Partitioning by time
  • Injecting dynamic links and assets from a STAC API

pgstac_to_parquet and pgstac_to_arrow convert the items chunk_size at a time, unless schema is "FullFile". Each chunk becomes a record batch, and a row group in Parquet.

Changing items with row_func

Pass a function as row_func to change items before they are written, for example to add the links and assets a STAC API would add:

def add_self_link(item):
    item.setdefault("links", []).append({
        "rel": "self",
        "href": f"https://example.com/collections/{item['collection']}/items/{item['id']}",
    })
    return item

pgstac_to_parquet(conninfo, "items.parquet", collection="naip", row_func=add_self_link)

A row_func gets each item as pgstac_to_iter would yield it with the same geometry. pgstac_to_iter gives GeoJSON geometries by default, as STAC items have them. pgstac_to_arrow, pgstac_to_parquet and sync_pgstac_to_parquet give ISO WKB bytes by default, as GeoParquet stores them, so a row_func that adds links or assets doesn't convert every geometry to GeoJSON and back. If a row_func reads or changes geometries as GeoJSON, pass geometry="geojson". For 100,000 NAIP-like items with large footprints, an export with geometry="geojson" took more than four times as long as with "wkb", with about the same peak memory. Without a row_func, geometries are never converted to GeoJSON.

With geometry="wkb", pgstac_schema and schema="PreScan" don't read the geometries, and the items a row_func gets there have an empty geometry. If what a row_func adds depends on the geometry, pass geometry="geojson", which reads every item's geometry.

Memory

Memory use grows with chunk_size, as a chunk's items are all held as Python dicts while it is converted.

With the default chunk_size of 65536, exporting 100,000 NAIP-like items peaked at 2.0 GB, and 100,000 Sentinel-2-like items, with more assets, at 2.8 GB. With chunk_size=8192 they peaked at 0.31 GB and 0.39 GB, and took about as long. The files were 414 MB and 3.0 MB at 65536, against 415 MB and 4.0 MB at 8192.

To use less memory, pass a smaller chunk_size, or set STAC_GEOPARQUET_CHUNK_SIZE to change the default for every call:

pgstac_to_parquet(conninfo, "items.parquet", collection="naip", chunk_size=8192)

A chunk ends early, with an INFO log message, if it wouldn't fit in the memory the process had left when the export started: the system's available memory, or a container (cgroup) limit if that is lower. This is an estimate, so items with very many distinct assets can still need more. Set STAC_GEOPARQUET_MEMORY_CAP to false to keep every row group at chunk_size.

Connection poolers

Each export sets search_path and statement_timeout with set_config inside its transaction, on a callable conninfo's connection too, not as startup parameters: PgBouncer rejects statement_timeout as one, and RDS Proxy doesn't list options among the startup parameters it accepts. Through PgBouncer, pass statement_timeout as an argument or set STAC_GEOPARQUET_PGSTAC_STATEMENT_TIMEOUT, not in the connection string. The transaction is read-only, so an export can't change the database.

Exports don't prepare their queries. PgBouncer before 1.24, or with max_prepared_statements = 0, can run the next transaction on a server connection that lacks a prepared statement.

Base items are looked up on a second connection while the first reads the items. It comes from a pool shared by every export, from a connection string or a callable conninfo, to the same database as the same user. The pool connects with the latest export's password, and a password that changes, such as an AWS IAM token, doesn't need a new pool. The pool closes its connection once it has gone unused for a whole max_idle period, so the connection can stay open for up to twice max_idle after the last export, and the pool keeps two threads until the process exits. Its min_size, max_size and max_idle can be set with the pool settings.

Schema options

schema Reads the items Memory Schema covers
"FirstBatch" (default) once one chunk the first chunk only, which is shorter than chunk_size when memory is short: a later chunk whose schema differs in any way fails
"PreScan" twice one chunk, plus one item of each structure every item, except that a row_func only runs with one item of each structure
"ChunksToDisk" once, then again from a copy on disk one chunk every item
"FullFile" once every item every item
a pa.Schema or InferredSchema once one chunk what the schema has: fields it lacks are dropped

For collections whose items don't all have the same fields, use "ChunksToDisk". It writes each chunk to an uncompressed Arrow file and reads them back with the schema of all of them. The files take about as much space as the items do in Arrow. pgstac_to_arrow needs a tmpdir, writes them in a new directory inside it, and leaves them there; pgstac_to_parquet uses a temporary directory and deletes it unless you pass a tmpdir. If the directory is in memory (tmpfs, as /tmp is on some systems), the files take memory too.

"PreScan" also covers every item without writing files. It calls a row_func with one item of each structure, so if the fields a row_func adds depend on an item's values rather than its structure, use "ChunksToDisk" or pass a schema. It reads each item's stored content twice, which nearly doubled the data sent from the database for Sentinel-2-like items, so on a slow connection "ChunksToDisk" is faster.

To give several files the same schema, for example the partitions written by sync_pgstac_to_parquet, infer it once with pgstac_schema and pass it as schema.

stac_geoparquet.pgstac_reader

pgstac_to_parquet

pgstac_to_parquet(
    conninfo: str | Callable[[], Connection] | None,
    output_path: str | Path,
    collection: str | None = None,
    start_datetime: datetime | None = None,
    end_datetime: datetime | None = None,
    search: dict[str, Any] | None = None,
    chunk_size: int | None = None,
    schema: PGSTAC_SCHEMA_OPTIONS = "FirstBatch",
    statement_timeout: int | None = None,
    row_func: Callable | None = None,
    schema_version: SUPPORTED_PARQUET_SCHEMA_VERSIONS = DEFAULT_PARQUET_SCHEMA_VERSION,
    filesystem: FileSystem | None = None,
    geometry: GEOMETRY_OPTIONS = "wkb",
    cursor_itersize: int | None = None,
    stream: bool | None = None,
    **kwargs: Any,
) -> str

Convert pgstac items to a parquet file.

See pgstac_to_arrow for search, statement_timeout, row_func, geometry, schema="PreScan", cursor_itersize and stream. Other keyword arguments are passed on to parse_stac_items_to_parquet.

pgstac_to_arrow

pgstac_to_arrow(
    conninfo: str | Callable[[], Connection] | None,
    collection: str | None = None,
    start_datetime: datetime | None = None,
    end_datetime: datetime | None = None,
    search: dict[str, Any] | None = None,
    chunk_size: int | None = None,
    schema: PGSTAC_SCHEMA_OPTIONS = "FirstBatch",
    statement_timeout: int | None = None,
    row_func: Callable | None = None,
    tmpdir: str | Path | None = None,
    drop_invalid_properties: bool = True,
    geometry: GEOMETRY_OPTIONS = "wkb",
    cursor_itersize: int | None = None,
    stream: bool | None = None,
) -> RecordBatchReader

Convert pgstac items to an arrow record batch reader.

search selects items with a STAC API search body. Its limit is the most items to export. They're sorted by its sortby, then by datetime, collection and id; fields and token aren't supported.

If row_func is given, it is called with each item dict, and what it returns is converted in place of the item. Its items' geometries are ISO WKB bytes, or GeoJSON with geometry="geojson", which is slower.

schema can also be "PreScan", which reads the items twice: once to infer the schema from all of them with pgstac_schema, which says how it calls row_func, and once to convert them.

cursor_itersize is the number of rows read from the database at a time. It defaults to the STAC_GEOPARQUET_PGSTAC_CURSOR_ITERSIZE environment variable, or to 100 rows when streaming and 1000 when fetching from a server-side cursor, where each fetch waits a round trip to the database.

If stream is True, the rows are streamed from a single query, so statement_timeout limits the whole export, including converting and writing the items. If it's False, they're fetched from a server-side cursor, and it limits each fetch. It defaults to the STAC_GEOPARQUET_PGSTAC_STREAM environment variable, or to streaming with libpq 17 or later, which streams rows in batches; before that, streamed rows arrive one at a time. statement_timeout is in milliseconds, and defaults to the STAC_GEOPARQUET_PGSTAC_STATEMENT_TIMEOUT environment variable.

pgstac_to_iter

pgstac_to_iter(
    conninfo: str | Callable[[], Connection] | None,
    collection: str | None = None,
    start_datetime: datetime | None = None,
    end_datetime: datetime | None = None,
    search: dict[str, Any] | None = None,
    statement_timeout: int | None = None,
    cursor_itersize: int | None = None,
    row_func: Callable | None = None,
    geometry: GEOMETRY_OPTIONS = "geojson",
    stream: bool | None = None,
) -> Iterator[dict[str, Any]]

Iterate over pgstac items as STAC item dicts.

Their geometries are GeoJSON, or ISO WKB bytes with geometry="wkb", and row_func gets them the same way. See pgstac_to_arrow for search, statement_timeout, cursor_itersize, row_func and stream.

pgstac_schema

pgstac_schema(
    conninfo: str | Callable[[], Connection] | None,
    collection: str | None = None,
    start_datetime: datetime | None = None,
    end_datetime: datetime | None = None,
    search: dict[str, Any] | None = None,
    statement_timeout: int | None = None,
    cursor_itersize: int | None = None,
    row_func: Callable | None = None,
    geometry: GEOMETRY_OPTIONS = "wkb",
    stream: bool | None = None,
) -> InferredSchema

Infer the schema of pgstac items, after row_func if one is given.

The schema only depends on the keys and types of values in the items, so this reads each item's stored content and converts just one item of each structure. A row_func is called with that one item of each structure, so if the fields it adds depend on an item's values rather than its structure, use schema="ChunksToDisk" or pass a schema instead. With geometry="wkb", the geometries aren't read, and the items a row_func gets have an empty one; if what it adds depends on the geometry, pass geometry="geojson". The result's count is the number of items converted.

Pass the result as schema to pgstac_to_parquet or sync_pgstac_to_parquet, for example to give several files the same schema. It describes the items as they were when it ran: a field that an item gains later is dropped. See pgstac_to_arrow for the other arguments.

sync_pgstac_to_parquet

sync_pgstac_to_parquet(
    conninfo: str | Callable[[], Connection] | None,
    output_path: str | Path,
    updated_after: datetime | None = None,
    chunk_size: int | None = None,
    schema: PGSTAC_SCHEMA_OPTIONS = "FirstBatch",
    statement_timeout: int | None = None,
    row_func: Callable | None = None,
    schema_version: SUPPORTED_PARQUET_SCHEMA_VERSIONS = DEFAULT_PARQUET_SCHEMA_VERSION,
    filesystem: FileSystem | None = None,
    geometry: GEOMETRY_OPTIONS = "wkb",
    **kwargs: Any,
) -> str

Use the last_updated partition metadata in pgstac to sync only changed items to parquet.