PostGIS spatial operators not available in CI test environment

[kbighorse]

Summary

PostGIS spatial operator tests fail in the GitHub Actions CI environment with the error:

no spatial operator found for 'st_within': opfamily 23841 type 23725

Affected Tests

The following tests are currently skipped due to this issue:

• tests/test_ogc.py::test_ogc_locations_items_bbox - Uses bbox filtering
• tests/test_ogc.py::test_ogc_polygon_within_filter - Uses ST_Within via CQL2 filter

Root Cause

The PostGIS container used in CI (postgis/postgis:17-3.5) may not have spatial indexes properly initialized, or there's a version mismatch between the PostGIS operators and the spatial reference system.

Potential Solutions

1. Update CI PostGIS initialization - Ensure spatial indexes are properly created after PostGIS extension installation
2. Use a different PostGIS image - Try an older stable version that doesn't have this issue
3. Add explicit GIST index creation - Create spatial indexes on the geometry columns during test database setup
4. Investigate opfamily/type mismatch - The error suggests a catalog mismatch that may require SELECT PostGIS_Extensions_Upgrade();

References

• PR Chemistry relationships should enforce parent-child integrity #416 - Tests were skipped as a workaround
• PostGIS issue tracker may have similar reports for version 3.5

[jirhiker]

running the following command on the ocotillo-staging db

SELECT PostGIS_Full_Version();

yields

POSTGIS="3.6.0 3.6.0" [EXTENSION] PGSQL="170" GEOS="3.11.4-CAPI-1.17.4" PROJ="6.3.2" (compiled against PROJ 0.0.63) LIBXML="2.9.10" LIBJSON="0.17" LIBPROTOBUF="1.5.2" WAGYU="0.5.0 (Internal)"

[jirhiker]

- name: Debug PostGIS schema/search_path
        run: |
          PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d ocotilloapi_test -c "SHOW search_path;"
          PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d ocotilloapi_test -c "SELECT postgis_full_version();"
          PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d ocotilloapi_test -c "SELECT n.nspname AS geometry_schema FROM pg_type t JOIN pg_namespace n ON n.oid=t.typnamespace WHERE t.typname='geometry';"
          PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d ocotilloapi_test -c "SELECT n.nspname AS opfamily_schema, o.opfname FROM pg_opfamily o JOIN pg_namespace n ON n.oid=o.opfnamespace WHERE o.opfname LIKE 'gist_geometry_ops%';"