jiayuasu commented on issue #3153:
URL: https://github.com/apache/sedona/issues/3153#issuecomment-5183148221
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:
```sql
SELECT *
FROM geometry_table
WHERE geom IS NOT NULL
AND ST_SRID(geom) = 0;
```
```sql
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:
```sql
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;
```
```sql
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
