pygeoapi with PostGIS: filters, pagination and CRS

A PostGIS-backed API must preserve identifiers, query meaning and coordinate order across pages. Connecting successfully is the beginning of the test. This guide takes the assets fixture from the reference lab, whose table is lab.assets, primary key asset_id and geometry geom in EPSG:4326.

The lab's CSV route and its PostgreSQL route are separate verification surfaces. Check the lab page for what has actually run before describing the database-backed configuration as tested. The instructions below target the documented pygeoapi 0.24.0 provider, not every historical release.

Prepare a deliberate publication table

Confirm that each identifier is non-null and unique, geometry has the declared CRS, and only intended public attributes are available to the service role. On a disposable copy, inspect counts and geometry before starting the API:

SELECT count(*) AS rows, count(DISTINCT asset_id) AS unique_ids,
       count(*) FILTER (WHERE geom IS NULL) AS missing_geometry,
       min(ST_SRID(geom)) AS min_srid,
       max(ST_SRID(geom)) AS max_srid
FROM lab.assets;

The generated lab has 10,000 records and stable text identifiers. For your real data, replace that expected count with the import manifest rather than hard-coding a tutorial number. Keep the database role read-only for a public reading endpoint. If the installed provider supports transactions, that is not a reason to enable writes without a separate workflow and authorization design.

Configure the provider explicitly

The 0.24.0 provider reference names PostgreSQL dependencies and settings. Inside the existing collection's providers section, use the database's actual connection values, with secrets injected by the deployment mechanism. This is a structural example; the lab includes its complete configuration.

providers:
  - type: feature
    name: PostgreSQL
    data:
      host: postgis
      port: 5432
      dbname: geosat_lab
      user: api_reader
      password: ${PG_API_PASSWORD}
      search_path: [lab, public]
    id_field: asset_id
    table: assets
    geom_field: geom
    properties: [asset_id, asset_type, condition, district, inspected_on]

Supply the referenced environment variable through your process configuration; do not paste an actual password into the file. The properties list expresses the intended public schema. A restricted database view gives an additional boundary and avoids relying only on API presentation settings. Regenerate OpenAPI and restart after changing the collection.

Test discovery, exact items and bounded pages

Begin with /collections/assets?f=json, then /collections/assets/queryables?f=json if exposed. Queryable names and types tell consumers what can actually be filtered. Request a known identifier such as asset-00001, and a nonexistent one. Check that text IDs retain leading zeros and are not converted into row numbers.

curl --fail --get 'http://localhost:5000/collections/assets/items' \
  --data-urlencode 'f=json' \
  --data-urlencode 'limit=10' \
  --data-urlencode 'district=D01'

Verify every returned record belongs to D01. Try a value absent from the fixture and expect an empty feature set rather than an unfiltered fallback. Test an unsupported parameter deliberately and document whether the server rejects or ignores it. Silent acceptance can mislead an application into believing a filter was applied.

Follow response pagination links and compare the collected identifier set to an equivalent SQL query. Avoid assuming that default database order is stable. Under concurrent changes, repeated offset pages can duplicate or omit records; choose a snapshot/export mechanism when complete, repeatable extraction is required.

Verify CRS with known coordinates

GeoJSON consumers normally expect longitude/latitude. Do not relabel projected coordinates as geographic coordinates. Request a small bbox around known synthetic points and compare its result with SQL. If you expose additional CRSs, verify the collection's advertised CRS, response representation and axis order using the CRS documentation.

Keep expensive queries bounded

Separate page size from query cost. Counting all matching rows can still be expensive even when only ten are returned. Review the provider's count setting, database indexes and query plan for representative filters. Limit database connections per process and account for the number of API workers; multiplying worker count can multiply pools.

Do not expose arbitrary SQL through query parameters. Restrict filterable properties and validate dates, enumerations and geometry inputs at the intended boundary. Measure realistic conjunctions such as district plus date interval; an index useful for one field may not make every combination inexpensive.

Acceptance and next step

Accept the provider when schema, stable IDs, missing-item errors, filters, pagination and CRS work for the actual consumer, including the negative cases above. Record version, fixture revision and expected ID sets. Then add production controls: database access, TLS, timeouts, monitoring and recovery remain necessary even when the API's JSON is correct.