Propagate SRID from EWKB as PROJJSON CRS in geoarrow.wkb metadata

[jatorre]

The driver already emits geoarrow.wkb Arrow extension metadata (thanks! confirmed working in #1), but the ARROW:extension:metadata field has no CRS information. Since Redshift sends geometry as EWKB over the wire — which includes the SRID in the header — the driver could propagate it as PROJJSON CRS in the geoarrow metadata.

Why this matters

Without CRS metadata, consumers (DuckDB, GeoParquet writers, pyarrow) receive geometry with no coordinate reference system. When the data gets written to GeoParquet, the CRS field is empty. This means downstream tools can't distinguish EPSG:4326 from EPSG:3857 or any other projection — they have to guess or require the user to specify it out-of-band.

What the driver does today

1. Receives EWKB from Redshift (flag bit 0x20000000 + 4-byte SRID in header)
2. Strips the SRID, outputs plain WKB
3. Tags the Arrow column with ARROW:extension:name = "geoarrow.wkb"
4. ARROW:extension:metadata is empty (no CRS)

Proposed change

Extract the SRID from the first non-null EWKB value and encode it as PROJJSON in ARROW:extension:metadata:

{
  "crs": {
    "type": "ProjectedCRS" or "GeographicCRS",
    "name": "EPSG:4326",
    "id": { "authority": "EPSG", "code": 4326 }
  }
}

This is the same approach used in the Databricks ADBC driver: adbc-drivers/databricks#350. The Databricks case is more complex because it needs to flatten Struct<srid, wkb> first — here the SRID is already in the EWKB header, so it's just a matter of reading it during decode and attaching it to the schema.

For SRID 0 (unset), the metadata should remain empty per the geoarrow spec (no CRS = unknown).

Two-phase schema

Since the SRID isn't known until the first record batch is read, this likely needs the same two-phase approach as Databricks: emit the schema without CRS initially, then rebuild it with CRS after seeing the first non-null geometry. Alternatively, the driver could query SELECT ST_SRID(geom_col) FROM table LIMIT 1 upfront, but reading it from the EWKB bytes is simpler and doesn't require an extra round-trip.

Context

We're using the Redshift ADBC driver from DuckDB (adbc_scanner) to export geospatial data to GeoParquet. DuckDB picks up geoarrow.wkb as native GEOMETRY, which is great — but the missing CRS means the output GeoParquet has no coordinate reference system. We recently got this working end-to-end for Databricks via the PR linked above, and the Redshift case should be strictly simpler.

[lidavidm]

This is rather painful because you can't change the schema after sending it, so we would have to always read data to determine the schema (it would also mean that GetTableSchema would no longer be consistent with the actual schema).

[jatorre]

For the Databricks ADBC driver we proposed a two-phase approach in adbc-drivers/databricks#350:

1. Emit schema with geoarrow.wkb but no CRS before the first Next()
2. Read the SRID from the first non-null geometry in the first record batch
3. Rebuild the schema with PROJJSON CRS from that SRID

GetTableSchema returns the CRS-less schema (consistent with catalog), while query results get the CRS-enriched schema after peeking at the first batch. The SRID is already in the EWKB header that the driver is decoding anyway — it's just a matter of capturing it before discarding.

Would a similar pattern work here, or does the Redshift driver's architecture make that first-batch buffering harder?

[lidavidm]

What is "emit schema"? Sorry, but that's not quite how ArrowArrayStream works: there is get_schema, and get_next; for get_schema to have the correct schema, we would need to buffer some data. That's not necessarily impossible, just a bit of a kludge. (Changing the schema after reading data would be highly inadvisable.)

I'm not sure the approach in that PR works. Again, modifying the schema as a side effect of something else isn't allowed. We would have to buffer data up front to get the full schema.

[jatorre]

Let me zoom out. The driver currently works producing geoarrow, but incomplete. Geoarrow asks you provide the encoding of the geometry (WKB) and that is good, but also what SRID is used. Those 2 pieces encoding and srid is the required metadata in rhe schema. The encoding is fixed, always WKB, but the srid depends on the table or sql you are running. The reason is that redshift supports different srid per record and geoarrow not, has to be the same for the entire dataset. Redshift emits something called EWKB which effectively is the WKB + SRID in a single value. So the ask is to inspect one record coming and extract the SRID from it and use it as metadata for the entire dataset. Most applications do not accept multiple SRID per dataset, and that’s way geoarrow only support one defined at metadata level.

So I don’t know if this requires “changing the schema”, is it changing a piece of metadata on the fly.

So I guess I am saying set the schema, receive the first batch, inspect it and extract the SRID, produce the schema with the right metadata and add the batch of data you have received…

Sorry not an expert in arrow so I might be saying something stupid. Thabks

[lidavidm]

The metadata is part of the schema.

So I guess I am saying set the schema, receive the first batch, inspect it and extract the SRID, produce the schema with the right metadata and add the batch of data you have received…

This isn't allowed. (It may seem to work at the Go level but will not do what you want over FFI.)

We have to buffer data and get the schema right up front.

[paleolimbot]

Cool! I'm happy to peek at code if this looks like it can eventually merge...a few high level comments based on the above discussion:

So the ask is to inspect one record coming and extract the SRID from it and use it as metadata for the entire dataset

The row-level SRID vs. column level SRID problem is what's kept me from just inlining geo support into the Postgres driver...it's the same issue as Decimal precision/scale in Arrow (type level) vs. Postgres (item level). Usually all items that come out of a query have the same one and null geometries are somewhat rarer than for other data types, so peeking at the first item (and erroring for subsequent mismatches) is fine. I opened geoarrow/geoarrow#94 to track formalizing how to handle item level CRSes with more fidelity (we have a way to do this in SedonaDB already but there are a few options).

Rebuild the schema with PROJJSON CRS from that SRID

I think Redshift has very basic SRID support so you can just set the CRS to "EPSG:<SRID>" and it will get picked up by all the tools I know about (PROJJSON is great but resolving that is a pain and not necessary for DuckDB/GeoPandas/SedonaDB). PostGIS is trickier because you need a database call to resolve the SRID into something usable outside the database connection by querying the spatial_ref_sys table but I think Redshift/Snowflake/Databricks don't support custom SRIDs.