RS_ raster predicates: reconsider assuming WGS84 for a missing CRS

[james-willis]

RS_Intersects/RS_Contains/RS_Within default a missing CRS on either operand to WGS84. Now that the GeoParquet reader attaches CRSes, CRS-less-but-actually-WGS84 values are much rarer, so this default may do more harm than good — a one-sided-CRS error would surface the mismatch instead of silently assuming a frame.

This tracks the Sedona (Spark) side. The SedonaDB side: apache/sedona-db#1075.

[jiayuasu]

@james-willis

After exploring the implementation in #3194, I think the alignment direction should be reversed: Sedona Spark should retain its established behavior, and SedonaDB should align with it.

Sedona Spark currently applies a simple rule when raster CRS or geometry SRID metadata is missing:

• A missing raster CRS is interpreted as WGS84.
• Geometry SRID 0 is interpreted as WGS84.
• Operands with known, different CRSs are transformed to a common CRS before comparison.

This rule is imperfect because missing metadata may describe data that is not actually WGS84. However, it gives every operand an independent coordinate interpretation. That property is particularly important for optimized spatial joins.

Raster CRS is stored per value in Spark, so one raster column may contain rows using EPSG:4326, EPSG:3857, custom non-EPSG CRSs, and no CRS. A strict “exactly one operand has a CRS must error” rule cannot generally be validated once at planning time. If validation occurs only after envelope filtering, the error becomes data-dependent because the index may discard the incompatible pair before the scalar predicate evaluates it. Guaranteeing the error would require a preflight scan and classification of both inputs.

We also considered treating an SRID-0 geometry as native raster coordinates when the raster has a custom non-EPSG CRS. This keeps custom rasters usable, but introduces pair-dependent semantics: the same geometry row can be interpreted in a different CRS for every raster row. It also prevents the geometry from having one independently computable envelope for the spatial index. Supporting this correctly requires conservative filtering, multiple join paths, or significant loss of pruning.

The existing WGS84 default avoids these problems:

• Every operand can be normalized independently.
• Mixed per-row raster CRSs are supported naturally.
• Scalar predicates, range joins, and broadcast-index joins use the same interpretation.
• No preflight validation job is required.
• Join optimization does not change whether a query returns a result or raises a CRS error.

The strict one-sided-CRS behavior and optimized raster–vector join are relatively recent in SedonaDB, including the work in apache/sedona-db#1073. Sedona Spark’s behavior is older and has a much larger compatibility surface across existing raster functions and applications. Changing Spark would therefore introduce substantially more risk than aligning the newer SedonaDB behavior with Spark.

I propose that both engines use the established Sedona Spark rule:

1. Missing CRS or SRID metadata is interpreted as WGS84.
2. Known CRSs are respected and transformed when necessary.
3. Scalar predicates and optimized joins must use the same interpretation.
4. Documentation should strongly recommend assigning explicit CRS/SRID metadata whenever it is known.

This intentionally favors backward compatibility and consistent query execution over strict detection of missing metadata. A strict mode could be reconsidered later as an opt-in or coordinated breaking change, ideally with schema-level CRS information and a migration path.

[james-willis]

@paleolimbot

[paleolimbot]

I don't see this as an alignment issue...Sedona users migrating to SedonaDB may see an error and can make their CRS explicit with ST_SetCrs() or ST_SetSRID(). They should be doing this anyway, particular for raster inputs. The resulting code will run on both engines.

We could also adopt type-level CRS tracking for Rasters, too (which would allow this error to be caught at planning time). The issue with the silent assumption is that it can produce silently incorrect results rather than an error.

As a migration path for Sedona Spark, you could have an opt in warning, followed by an opt out warning on the first instance of the encountered behaviour, followed by a behaviour change. If you don't get any feedback by the second stage, we will know this was behaviour that wasn't used anyway (do we have evidence that it's being used today?)

[jiayuasu]

@paleolimbot @james-willis Thanks. I agree that silently interpreting missing CRS metadata can produce incorrect results, and I agree that type-level CRS tracking is worth exploring.

My concern is specifically about enforcing a pairwise one-sided-CRS error when RS_Intersects, RS_Contains, or RS_Within is used as an optimized spatial join condition.

SedonaDB and Sedona Spark both allow CRS information to exist at the item level. A raster or geometry column may therefore contain values with different CRSs, including a mixture of values with and without CRS metadata.

For scalar predicate evaluation, the proposed rule is straightforward:

Throw an error when exactly one operand in the evaluated pair has CRS metadata.

For an optimized spatial join, however, the engine normally uses operand envelopes to eliminate most row pairs before evaluating the exact predicate at the physical planning phase (Cost Base Optimization). If two operands do not have independently identifiable coordinate systems, their envelopes cannot be safely compared before checking their CRS compatibility.

To guarantee the pairwise error before CRS-dependent spatial pruning, the engine must first generate the row pairs to which the predicate applies and validate their CRS metadata. With no selective non-spatial join condition, this can degenerate to the N*M Cartesian product that the spatial index is intended to avoid.

Checking compatibility only after spatial pruning does not enforce the rule consistently:

1. An incompatible pair may have numerically disjoint envelopes.
2. The spatial index removes the pair.
3. The exact predicate is never evaluated.
4. The expected CRS error is never raised.

The result is that error visibility can depend on whether the optimizer selects a broadcast-index join, distributed spatial join, or nested-loop execution.

Here is my new proposal. The key is to not perform pairwise CRS compatibility validation for optimized raster–vector joins.

Instead, each join input can be inspected independently during its normal scan at the physical planning phase (CBO phase):

• Record whether any raster value has no CRS object.
• Record whether any geometry value has no CRS metadata, such as SRID 0 in Sedona Spark.
• If either condition is encountered, emit one warning for the query or spatial operator.
• Preserve the existing behavior of interpreting the missing CRS as WGS84.

This requires no pairwise comparison and can be incorporated into the normal scan used to build or probe the join.

I do not recommend turning this input-level check into an error. That would be substantially stricter than the proposed pairwise rule.

A warning is more appropriate (but I am open to it if you guys want to make this an error):

Raster or geometry input is missing CRS metadata. For compatibility, Sedona will interpret the missing CRS as WGS84. Set the raster CRS or geometry SRID explicitly if this is not intended.

[paleolimbot]

The CRS comparison issue seems orthogonal to the implicit treating of a None CRS as lon/lat. Lon/Lat is not a likely CRS for a raster and so this will usually be masking an error. For vector, it is functionally difficult to get a column with a None CRS (when it's missing this is usually an error in internal CRS propagation). This behaviour is probably never used intentionally (and if it is, it is trivially fixable with a ST_SetSRID).

CRS comparison in a join is indeed a problem. Probably the best solution here is to validate that all geometries on the build side have the same CRS, which can be done on the way in when building the join (this is also an issue for Sedona's regular spatial join, not limited to RS_Intersects). SedonaDB doesn't support joins for item level CRS yet, so probably our best solution is to force the build side to be the geometry, where we can prove identical CRS at planning time (or where it's more likely all items have the same CRS).

For RS_Intersects, you could also force the index to be constructed in lon/lat space, since that is the spirit of the comparison. Or if we want to keep existing behaviour, make a new function RS_DWithin (this would let a user set some tolerance to account for any error due to reprojection).

[jiayuasu]

@paleolimbot

Based on this discussion, I think the simplest and most deterministic policy is to validate each join input independently, rather than attempting pairwise CRS validation.

In SedonaSpark, spatial partitioning occurs before optional index construction and probing. Therefore, at the beginning of optimized spatial-join execution—and before spatial partitioning or index construction—SedonaSpark and SedonaDB should validate both inputs and fail if any non-null spatial value lacks identifiable CRS metadata:

• A geometry is missing CRS metadata when ST_SRID(geom) = 0.
• A raster is missing CRS metadata when RS_CRS(rast) IS NULL.

For rasters, we should not use RS_SRID(rast) = 0 as the test. A raster with a custom non-EPSG CRS can have SRID 0 while still carrying valid CRS metadata.

This policy is intentionally strict: the join fails even if both inputs have missing CRS metadata, or if a record with missing CRS would never match anything. I think this is preferable to an error that depends on which candidate pairs happen to survive spatial partitioning or index pruning.

A follow-up question is how users should locate and repair the affected records after receiving this error. For example, the problematic records can be found with:

SELECT *
FROM geometry_table
WHERE geom IS NOT NULL
  AND ST_SRID(geom) = 0;

SELECT *
FROM raster_table
WHERE rast IS NOT NULL
  AND RS_CRS(rast) IS NULL;

If the correct CRS is known, users can conditionally assign it:

SELECT
  CASE
    WHEN geom IS NOT NULL AND ST_SRID(geom) = 0
      THEN ST_SetSRID(geom, 4326)
    ELSE geom
  END AS geom
FROM geometry_table;

SELECT
  CASE
    WHEN rast IS NOT NULL AND RS_CRS(rast) IS NULL
      THEN RS_SetCRS(rast, 'EPSG:4326')
    ELSE rast
  END AS rast
FROM raster_table;

Agree?

[paleolimbot]

I am definitely missing something about the specialness of a missing CRS (I think of it as a completely valid CRS that just happens to lack transforms into any CRS except itself), but refusing an RS_Intersects call between to SRID 0 sides is fine with me (the specific piece that I think is problematic is the assumption that an SRID 0 CRS can be implicitly assigned or compared to something else).

I'm also not quite following how spatial partitionings / indexes can be built when there's more than one CRS involved on the same side (although I can see how it's possible to validate that a partitioning / index only contains one SRID since you've scanned all the geometries at that point or at least sampled them to know what each SRID should be). No need to block this discussion on my understanding of that, though.

[jiayuasu]

@paleolimbot

We agree on erroring out the missing CRS situation. Done.

Another follow-up question:

In the raster case, when there's more than one CRS involved on the same side (which is quite common), we transform them all to WGS84. This is the current behavior of SedonaSpark for in-db raster parquet stats (for spatial filter pushdown), and vector - raster join. Are you fine with this behavior?

[paleolimbot]

Yes, that makes sense...RS_Intersects explicitly compares in lon/lat space, so you transform the envelopes to wgs84 (densifying edges to minimize inconsistency) to do pruning/indexing. Users who want something different can call RS_Envelope themselves and do what they need to do.

[jiayuasu]

@james-willis Can you execute based on the decisions we made here?