This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 1eae69306450 [SPARK-54091][GEO][SQL] Implement the ST_Srid expression
in SQL
1eae69306450 is described below
commit 1eae6930645048be6cd0df6ef11d81e34cd86581
Author: Uros Bojanic <[email protected]>
AuthorDate: Fri Oct 31 12:42:14 2025 +0800
[SPARK-54091][GEO][SQL] Implement the ST_Srid expression in SQL
### What changes were proposed in this pull request?
Implement the `ST_Srid` expression in Catalyst, and add the corresponding
`st_srid` SQL function.
#### Description
Returns the SRID of the input `GEOGRAPHY` or `GEOMETRY` value.
#### Syntax
```
st_srid(geo)
```
#### Arguments
- `geo`: A `GEOGRAPHY` or `GEOMETRY` value.
#### Returns
- A value of type `INTEGER`.
#### Notes
- The function returns `NULL` if the input is `NULL`.
#### Examples
```
> SELECT
st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'));
4326
```
```
> SELECT
st_srid(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040'));
0
```
```
> SELECT st_srid(NULL);
NULL
```
### Why are the changes needed?
### Does this PR introduce _any_ user-facing change?
Yes, a new SQL function is added: `st_srid`.
### How was this patch tested?
New unit tests in Java/Scala suites:
- `STExpressionsSuite.scala`
- `StUtilsSuite.java`
New end-to-end SQL tests:
- `st-functions.sql`
Finally, manually testing using Spark shell.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #52795 from uros-db/geo-ST_Srid.
Authored-by: Uros Bojanic <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../apache/spark/sql/catalyst/util/STUtils.java | 9 +++
.../sql/catalyst/analysis/FunctionRegistry.scala | 1 +
.../catalyst/expressions/st/stExpressions.scala | 50 +++++++++++++++
.../spark/sql/catalyst/util/StUtilsSuite.java | 32 ++++++++--
.../sql-functions/sql-expression-schema.md | 1 +
.../analyzer-results/nonansi/st-functions.sql.out | 69 ++++++++++++++++++++
.../analyzer-results/st-functions.sql.out | 69 ++++++++++++++++++++
.../resources/sql-tests/inputs/st-functions.sql | 25 ++++++++
.../sql-tests/results/nonansi/st-functions.sql.out | 74 ++++++++++++++++++++++
.../sql-tests/results/st-functions.sql.out | 74 ++++++++++++++++++++++
.../org/apache/spark/sql/STExpressionsSuite.scala | 38 +++++++++--
11 files changed, 432 insertions(+), 10 deletions(-)
diff --git
a/sql/catalyst/src/main/java/org/apache/spark/sql/catalyst/util/STUtils.java
b/sql/catalyst/src/main/java/org/apache/spark/sql/catalyst/util/STUtils.java
index 641382a0f959..aca3fdf1f100 100644
--- a/sql/catalyst/src/main/java/org/apache/spark/sql/catalyst/util/STUtils.java
+++ b/sql/catalyst/src/main/java/org/apache/spark/sql/catalyst/util/STUtils.java
@@ -67,4 +67,13 @@ public final class STUtils {
return toPhysVal(Geometry.fromWkb(wkb));
}
+ // ST_Srid
+ public static int stSrid(GeographyVal geog) {
+ return fromPhysVal(geog).srid();
+ }
+
+ public static int stSrid(GeometryVal geom) {
+ return fromPhysVal(geom).srid();
+ }
+
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
index 97f8cbc23b7a..bb98e5fa02ed 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
@@ -877,6 +877,7 @@ object FunctionRegistry {
expression[ST_AsBinary]("st_asbinary"),
expression[ST_GeogFromWKB]("st_geogfromwkb"),
expression[ST_GeomFromWKB]("st_geomfromwkb"),
+ expression[ST_Srid]("st_srid"),
// cast
expression[Cast]("cast"),
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/st/stExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/st/stExpressions.scala
index 12f3d13746d5..a08f88b67952 100755
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/st/stExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/st/stExpressions.scala
@@ -172,3 +172,53 @@ case class ST_GeomFromWKB(wkb: Expression)
override protected def withNewChildInternal(newChild: Expression):
ST_GeomFromWKB =
copy(wkb = newChild)
}
+
+/** ST accessor expressions. */
+
+/**
+ * Returns the SRID of the input GEOGRAPHY or GEOMETRY value. Returns NULL if
the input is NULL.
+ * See https://en.wikipedia.org/wiki/Spatial_reference_system#Identifier for
more details on the
+ * Spatial Reference System Identifier (SRID).
+ */
+@ExpressionDescription(
+ usage = "_FUNC_(geo) - Returns the SRID of the input GEOGRAPHY or GEOMETRY
value.",
+ arguments = """
+ Arguments:
+ * geo - A GEOGRAPHY or GEOMETRY value.
+ """,
+ examples = """
+ Examples:
+ > SELECT
_FUNC_(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'));
+ 4326
+ > SELECT
_FUNC_(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040'));
+ 0
+ > SELECT _FUNC_(NULL);
+ NULL
+ """,
+ since = "4.1.0",
+ group = "st_funcs"
+)
+case class ST_Srid(geo: Expression)
+ extends RuntimeReplaceable
+ with ImplicitCastInputTypes
+ with UnaryLike[Expression] {
+
+ override def inputTypes: Seq[AbstractDataType] = Seq(
+ TypeCollection(GeographyType, GeometryType)
+ )
+
+ override lazy val replacement: Expression = StaticInvoke(
+ classOf[STUtils],
+ IntegerType,
+ "stSrid",
+ Seq(geo),
+ returnNullable = false
+ )
+
+ override def prettyName: String = "st_srid"
+
+ override def child: Expression = geo
+
+ override protected def withNewChildInternal(newChild: Expression): ST_Srid =
+ copy(geo = newChild)
+}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/StUtilsSuite.java
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/StUtilsSuite.java
index 425abac2efed..8ad4d4c36e45 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/StUtilsSuite.java
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/StUtilsSuite.java
@@ -21,6 +21,9 @@ import org.apache.spark.unsafe.types.GeographyVal;
import org.apache.spark.unsafe.types.GeometryVal;
import org.junit.jupiter.api.Test;
+import java.nio.ByteBuffer;
+import java.nio.ByteOrder;
+
import static org.junit.jupiter.api.Assertions.*;
@@ -35,23 +38,27 @@ class STUtilsSuite {
0x00, 0x00, 0x00, (byte)0xF0, 0x3F, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x40};
// A sample Geography byte array for testing purposes, representing a
POINT(1 2) with SRID 4326.
- private final byte[] testGeographySrid = new byte[] {(byte)0xE6, 0x10, 0x00,
0x00};
+ private final int testGeographySrid = 4326;
private final byte[] testGeographyBytes;
// A sample Geometry byte array for testing purposes, representing a POINT(1
2) with SRID 0.
- private final byte[] testGeometrySrid = new byte[] {0x00, 0x00, 0x00, 0x00};
+ private final int testGeometrySrid = 0;
private final byte[] testGeometryBytes;
{
- int sridLen = testGeographySrid.length;
- int wkbLen = testWkb.length;
+ // Initialize headers.
+ ByteOrder end = Geo.DEFAULT_ENDIANNESS;
+ int sridLen = Geo.HEADER_SIZE;
+ byte[] geogSrid =
ByteBuffer.allocate(sridLen).order(end).putInt(testGeographySrid).array();
+ byte[] geomSrid =
ByteBuffer.allocate(sridLen).order(end).putInt(testGeometrySrid).array();
// Initialize GEOGRAPHY.
+ int wkbLen = testWkb.length;
testGeographyBytes = new byte[sridLen + wkbLen];
- System.arraycopy(testGeographySrid, 0, testGeographyBytes, 0, sridLen);
+ System.arraycopy(geogSrid, 0, testGeographyBytes, 0, sridLen);
System.arraycopy(testWkb, 0, testGeographyBytes, sridLen, wkbLen);
// Initialize GEOMETRY.
testGeometryBytes = new byte[sridLen + wkbLen];
- System.arraycopy(testGeometrySrid, 0, testGeometryBytes, 0, sridLen);
+ System.arraycopy(geomSrid, 0, testGeometryBytes, 0, sridLen);
System.arraycopy(testWkb, 0, testGeometryBytes, sridLen, wkbLen);
}
@@ -90,4 +97,17 @@ class STUtilsSuite {
assertArrayEquals(testGeometryBytes, geometryVal.getBytes());
}
+ // ST_Srid
+ @Test
+ void testStSridGeography() {
+ GeographyVal geographyVal = GeographyVal.fromBytes(testGeographyBytes);
+ assertEquals(testGeographySrid, STUtils.stSrid(geographyVal));
+ }
+
+ @Test
+ void testStSridGeometry() {
+ GeometryVal geometryVal = GeometryVal.fromBytes(testGeometryBytes);
+ assertEquals(testGeometrySrid, STUtils.stSrid(geometryVal));
+ }
+
}
diff --git a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
index 56146237a98b..29d194f5715e 100644
--- a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
+++ b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
@@ -290,6 +290,7 @@
| org.apache.spark.sql.catalyst.expressions.ST_AsBinary | st_asbinary | SELECT
hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')))
|
struct<hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'))):string>
|
| org.apache.spark.sql.catalyst.expressions.ST_GeogFromWKB | st_geogfromwkb |
SELECT
hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')))
|
struct<hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'))):string>
|
| org.apache.spark.sql.catalyst.expressions.ST_GeomFromWKB | st_geomfromwkb |
SELECT
hex(st_asbinary(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040')))
|
struct<hex(st_asbinary(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040'))):string>
|
+| org.apache.spark.sql.catalyst.expressions.ST_Srid | st_srid | SELECT
st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')) |
struct<st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')):int>
|
| org.apache.spark.sql.catalyst.expressions.SchemaOfCsv | schema_of_csv |
SELECT schema_of_csv('1,abc') | struct<schema_of_csv(1,abc):string> |
| org.apache.spark.sql.catalyst.expressions.SchemaOfJson | schema_of_json |
SELECT schema_of_json('[{"col":0}]') |
struct<schema_of_json([{"col":0}]):string> |
| org.apache.spark.sql.catalyst.expressions.SchemaOfXml | schema_of_xml |
SELECT schema_of_xml('<p><a>1</a></p>') |
struct<schema_of_xml(<p><a>1</a></p>):string> |
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
index 465f27b3c06f..fe15f3b3a9a9 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
@@ -1,4 +1,27 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+DROP TABLE IF EXISTS geodata
+-- !query analysis
+DropTable true, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.geodata
+
+
+-- !query
+CREATE TABLE geodata(wkb BINARY) USING parquet
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`geodata`, false
+
+
+-- !query
+INSERT INTO geodata VALUES
+(NULL),
+(X'0101000000000000000000F03F0000000000000040')
+-- !query analysis
+InsertIntoHadoopFsRelationCommand file:[not included in
comparison]/{warehouse_dir}/geodata, false, Parquet, [path=file:[not included
in comparison]/{warehouse_dir}/geodata], Append,
`spark_catalog`.`default`.`geodata`,
org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included
in comparison]/{warehouse_dir}/geodata), [wkb]
++- Project [col1#x AS wkb#x]
+ +- LocalRelation [col1#x]
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query analysis
@@ -11,3 +34,49 @@ SELECT
hex(ST_AsBinary(ST_GeomFromWKB(X'0101000000000000000000f03f00000000000000
-- !query analysis
Project
[hex(st_asbinary(st_geomfromwkb(0x0101000000000000000000F03F0000000000000040)))
AS result#x]
+- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(NULL)
+-- !query analysis
+Project [st_srid(cast(null as geography(any))) AS st_srid(NULL)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(ST_GeogFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query analysis
+Project [st_srid(st_geogfromwkb(0x0101000000000000000000F03F0000000000000040))
AS st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(ST_GeomFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query analysis
+Project [st_srid(st_geomfromwkb(0x0101000000000000000000F03F0000000000000040))
AS st_srid(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040'))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeogFromWKB(wkb)) <> 4326
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter NOT (st_srid(st_geogfromwkb(wkb#x)) = 4326)
+ +- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeomFromWKB(wkb)) <> 0
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter NOT (st_srid(st_geomfromwkb(wkb#x)) = 0)
+ +- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
+-- !query
+DROP TABLE geodata
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.geodata
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
index 465f27b3c06f..fe15f3b3a9a9 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
@@ -1,4 +1,27 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+DROP TABLE IF EXISTS geodata
+-- !query analysis
+DropTable true, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.geodata
+
+
+-- !query
+CREATE TABLE geodata(wkb BINARY) USING parquet
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`geodata`, false
+
+
+-- !query
+INSERT INTO geodata VALUES
+(NULL),
+(X'0101000000000000000000F03F0000000000000040')
+-- !query analysis
+InsertIntoHadoopFsRelationCommand file:[not included in
comparison]/{warehouse_dir}/geodata, false, Parquet, [path=file:[not included
in comparison]/{warehouse_dir}/geodata], Append,
`spark_catalog`.`default`.`geodata`,
org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included
in comparison]/{warehouse_dir}/geodata), [wkb]
++- Project [col1#x AS wkb#x]
+ +- LocalRelation [col1#x]
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query analysis
@@ -11,3 +34,49 @@ SELECT
hex(ST_AsBinary(ST_GeomFromWKB(X'0101000000000000000000f03f00000000000000
-- !query analysis
Project
[hex(st_asbinary(st_geomfromwkb(0x0101000000000000000000F03F0000000000000040)))
AS result#x]
+- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(NULL)
+-- !query analysis
+Project [st_srid(cast(null as geography(any))) AS st_srid(NULL)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(ST_GeogFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query analysis
+Project [st_srid(st_geogfromwkb(0x0101000000000000000000F03F0000000000000040))
AS st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040'))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ST_Srid(ST_GeomFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query analysis
+Project [st_srid(st_geomfromwkb(0x0101000000000000000000F03F0000000000000040))
AS st_srid(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040'))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeogFromWKB(wkb)) <> 4326
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter NOT (st_srid(st_geogfromwkb(wkb#x)) = 4326)
+ +- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeomFromWKB(wkb)) <> 0
+-- !query analysis
+Aggregate [count(1) AS count(1)#xL]
++- Filter NOT (st_srid(st_geomfromwkb(wkb#x)) = 0)
+ +- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
+-- !query
+DROP TABLE geodata
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.geodata
diff --git a/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
b/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
index 02bb526bee25..ecffe55231e7 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
@@ -1,5 +1,30 @@
+-- Create the table of WKB values and insert test values.
+DROP TABLE IF EXISTS geodata;
+CREATE TABLE geodata(wkb BINARY) USING parquet;
+-- See:
https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary
+-- to understand the formatting/layout of the input Well-Known Binary (WKB)
values.
+INSERT INTO geodata VALUES
+(NULL),
+(X'0101000000000000000000F03F0000000000000040');
+
---- ST reader/writer expressions
-- WKB (Well-Known Binary) round-trip tests for GEOGRAPHY and GEOMETRY types.
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result;
SELECT
hex(ST_AsBinary(ST_GeomFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result;
+
+------ ST accessor expressions
+
+---- ST_Srid
+
+-- 1. Driver-level queries.
+SELECT ST_Srid(NULL);
+SELECT ST_Srid(ST_GeogFromWKB(X'0101000000000000000000F03F0000000000000040'));
+SELECT ST_Srid(ST_GeomFromWKB(X'0101000000000000000000F03F0000000000000040'));
+
+-- 2. Table-level queries.
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeogFromWKB(wkb)) <> 4326;
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeomFromWKB(wkb)) <> 0;
+
+-- Drop the test table.
+DROP TABLE geodata;
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
index b77c11f0dd1d..5787d02e29c2 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
@@ -1,4 +1,30 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+DROP TABLE IF EXISTS geodata
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE geodata(wkb BINARY) USING parquet
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+INSERT INTO geodata VALUES
+(NULL),
+(X'0101000000000000000000F03F0000000000000040')
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query schema
@@ -13,3 +39,51 @@ SELECT
hex(ST_AsBinary(ST_GeomFromWKB(X'0101000000000000000000f03f00000000000000
struct<result:string>
-- !query output
0101000000000000000000F03F0000000000000040
+
+
+-- !query
+SELECT ST_Srid(NULL)
+-- !query schema
+struct<st_srid(NULL):int>
+-- !query output
+NULL
+
+
+-- !query
+SELECT ST_Srid(ST_GeogFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query schema
+struct<st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')):int>
+-- !query output
+4326
+
+
+-- !query
+SELECT ST_Srid(ST_GeomFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query schema
+struct<st_srid(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040')):int>
+-- !query output
+0
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeogFromWKB(wkb)) <> 4326
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeomFromWKB(wkb)) <> 0
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+0
+
+
+-- !query
+DROP TABLE geodata
+-- !query schema
+struct<>
+-- !query output
+
diff --git a/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
index b77c11f0dd1d..5787d02e29c2 100644
--- a/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
@@ -1,4 +1,30 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+DROP TABLE IF EXISTS geodata
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE geodata(wkb BINARY) USING parquet
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+INSERT INTO geodata VALUES
+(NULL),
+(X'0101000000000000000000F03F0000000000000040')
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query schema
@@ -13,3 +39,51 @@ SELECT
hex(ST_AsBinary(ST_GeomFromWKB(X'0101000000000000000000f03f00000000000000
struct<result:string>
-- !query output
0101000000000000000000F03F0000000000000040
+
+
+-- !query
+SELECT ST_Srid(NULL)
+-- !query schema
+struct<st_srid(NULL):int>
+-- !query output
+NULL
+
+
+-- !query
+SELECT ST_Srid(ST_GeogFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query schema
+struct<st_srid(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')):int>
+-- !query output
+4326
+
+
+-- !query
+SELECT ST_Srid(ST_GeomFromWKB(X'0101000000000000000000F03F0000000000000040'))
+-- !query schema
+struct<st_srid(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040')):int>
+-- !query output
+0
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeogFromWKB(wkb)) <> 4326
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT COUNT(*) FROM geodata WHERE ST_Srid(ST_GeomFromWKB(wkb)) <> 0
+-- !query schema
+struct<count(1):bigint>
+-- !query output
+0
+
+
+-- !query
+DROP TABLE geodata
+-- !query schema
+struct<>
+-- !query output
+
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
index 46c59bbd6d0c..71353e1bc29b 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
@@ -27,10 +27,10 @@ class STExpressionsSuite
with ExpressionEvalHelper {
// Private common constants used across several tests.
- private val defaultGeographyType: DataType =
- GeographyType(ExpressionDefaults.DEFAULT_GEOGRAPHY_SRID)
- private val defaultGeometryType: DataType =
- GeometryType(ExpressionDefaults.DEFAULT_GEOMETRY_SRID)
+ private final val defaultGeographySrid: Int =
ExpressionDefaults.DEFAULT_GEOGRAPHY_SRID
+ private final val defaultGeographyType: DataType =
GeographyType(defaultGeographySrid)
+ private final val defaultGeometrySrid: Int =
ExpressionDefaults.DEFAULT_GEOMETRY_SRID
+ private final val defaultGeometryType: DataType =
GeometryType(defaultGeometrySrid)
/** ST reader/writer expressions. */
@@ -48,4 +48,34 @@ class STExpressionsSuite
checkEvaluation(ST_AsBinary(geometryExpression), wkb)
}
+ /** ST accessor expressions. */
+
+ test("ST_Srid") {
+ // Test data: WKB representation of POINT(1 2).
+ val wkb =
Hex.unhex("0101000000000000000000F03F0000000000000040".getBytes())
+ val wkbLiteral = Literal.create(wkb, BinaryType)
+
+ // ST_Srid with GEOGRAPHY.
+ val geographyExpression = ST_GeogFromWKB(wkbLiteral)
+ val stSridGeography = ST_Srid(geographyExpression)
+ assert(stSridGeography.dataType.sameType(IntegerType))
+ checkEvaluation(stSridGeography, defaultGeographySrid)
+ // Test NULL handling.
+ val nullGeographyLiteral = Literal.create(null, defaultGeographyType)
+ val stSridGeographyNull = ST_Srid(nullGeographyLiteral)
+ assert(stSridGeographyNull.dataType.sameType(IntegerType))
+ checkEvaluation(stSridGeographyNull, null)
+
+ // ST_Srid with GEOMETRY.
+ val geometryExpression = ST_GeomFromWKB(wkbLiteral)
+ val stSridGeometry = ST_Srid(geometryExpression)
+ assert(stSridGeometry.dataType.sameType(IntegerType))
+ checkEvaluation(stSridGeometry, defaultGeometrySrid)
+ // Test NULL handling.
+ val nullGeometryLiteral = Literal.create(null, defaultGeometryType)
+ val stSridGeometryNull = ST_Srid(nullGeometryLiteral)
+ assert(stSridGeometryNull.dataType.sameType(IntegerType))
+ checkEvaluation(stSridGeometryNull, null)
+ }
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
