You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ja...@apache.org on 2026/08/21 17:13:02 UTC
(impala) branch master updated: IMPALA-15166: Improve test coverage for geospatial functions
This is an automated email from the ASF dual-hosted git repository.
asf-gitbox-commits pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
The following commit(s) were added to refs/heads/master by this push:
new 7227d81fb IMPALA-15166: Improve test coverage for geospatial functions
7227d81fb is described below
commit 7227d81fb9d78c61c8e80112545d7862b9e07dbc
Author: Csaba Ringhofer <cs...@cloudera.com>
AuthorDate: Thu Jul 2 21:17:09 2026 +0200
IMPALA-15166: Improve test coverage for geospatial functions
Add more function test cases to geospatial-esri-extra.test and
geospatial-esri-srid.test to cover all st_ functions.
Add the geospatial-wkb-serialization.test (WKB round-trip and malformed
input cases) to the default HIVE_ESRI test run, and the
functional.geom_relations_wkt test table (WKT strings in a text table)
with its data file. The table is used by the geospatial relation predicate
tests to check all geometry type combinations for all relations. Getting
data from tables also allow checking things like executing with codegen,
which will be important for the c++ implementation.
This revealed bug in Hive ESRI lib: HIVE-29726
Change-Id: Ic155e691cdb93842502e52c0a09807dd08e4a4f3
Assisted-by: Claude Opus 4.8
Reviewed-on: http://gerrit.cloudera.org:8080/24551
Reviewed-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Jason Fehr <jf...@cloudera.com>
Tested-by: Jason Fehr <jf...@cloudera.com>
---
testdata/data/README | 8 +
testdata/data/geom_relations_wkt/data.csv | 8 +
.../functional/functional_schema_template.sql | 50 ++
.../datasets/functional/schema_constraints.csv | 4 +
.../queries/QueryTest/geospatial-esri-extra.test | 180 ++++-
.../queries/QueryTest/geospatial-esri-srid.test | 22 +
.../QueryTest/geospatial-relations-table.test | 847 +++++++++++++++++++++
.../QueryTest/geospatial-wkb-serialization.test | 177 +++++
tests/custom_cluster/test_geospatial_library.py | 20 +-
tests/query_test/test_geospatial_functions.py | 7 +
10 files changed, 1311 insertions(+), 12 deletions(-)
diff --git a/testdata/data/README b/testdata/data/README
index 8e5a9b4ce..0f40f6bea 100644
--- a/testdata/data/README
+++ b/testdata/data/README
@@ -1425,3 +1425,11 @@ Generated by ORC C++ library using the following code
invalid_binary_data.txt:
Hand-written file where BINARY values are not Base64 encoded.
+
+geom_relations_wkt/data.csv:
+Includes WKT geometries to cover each ST_ relation function with all (6*6)
+geometry type combinations. Each row has 2*6 geometries, for example in id = 'intersects'
+row each <geomtype>1 column intersects with each <geomtype>2 (e.g point1 with polygon2).
+In 'disjoint_near' and 'disjoint_far' all relations (besides st_disjoint) must be false,
+the difference is whether the bounding rectangles intersect. Some values are left NULL,
+for example point1/point2 in overlaps case as st_overlaps is not meaningful for points.
\ No newline at end of file
diff --git a/testdata/data/geom_relations_wkt/data.csv b/testdata/data/geom_relations_wkt/data.csv
new file mode 100644
index 000000000..024be4953
--- /dev/null
+++ b/testdata/data/geom_relations_wkt/data.csv
@@ -0,0 +1,8 @@
+contains type1 contains type2 POINT(5 5) POINT(5 5) LINESTRING(1 5, 9 5) LINESTRING(4 5, 6 5) POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) POLYGON((3 3, 7 3, 7 7, 3 7, 3 3)) MULTIPOINT((4 5), (5 5), (6 5)) MULTIPOINT((5 5)) MULTILINESTRING((1 5, 9 5), (5 1, 5 9)) MULTILINESTRING((4 5, 6 5)) MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0))) MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3)))
+touches pairs touch at shared boundary \N \N LINESTRING(0 0, 5 0) LINESTRING(5 0, 10 0) POLYGON((0 0, 5 0, 5 5, 0 5, 0 0)) POLYGON((5 0, 10 0, 10 5, 5 5, 5 0)) \N \N MULTILINESTRING((0 0, 5 0)) MULTILINESTRING((5 0, 10 0)) MULTIPOLYGON(((0 0, 5 0, 5 5, 0 5, 0 0))) MULTIPOLYGON(((5 0, 10 0, 10 5, 5 5, 5 0)))
+intersects all pass through (5 5) POINT(5 5) POINT(5 5) LINESTRING(0 0, 10 10) LINESTRING(0 10, 10 0) POLYGON((3 3, 7 3, 7 7, 3 7, 3 3)) POLYGON((3 3, 7 3, 7 7, 3 7, 3 3)) MULTIPOINT((5 5), (1 1)) MULTIPOINT((5 5), (9 9)) MULTILINESTRING((0 0, 10 10)) MULTILINESTRING((0 10, 10 0)) MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3))) MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3)))
+crosses lines cross at (5 5) and cross polygons \N \N LINESTRING(0 5, 10 5) LINESTRING(5 0, 5 10) POLYGON((3 3, 7 3, 7 7, 3 7, 3 3)) POLYGON((3 3, 7 3, 7 7, 3 7, 3 3)) \N \N MULTILINESTRING((0 5, 10 5)) MULTILINESTRING((5 0, 5 10)) MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3))) MULTIPOLYGON(((3 3, 7 3, 7 7, 3 7, 3 3)))
+overlaps same-dim partial overlap \N \N LINESTRING(0 0, 6 0) LINESTRING(4 0, 10 0) POLYGON((0 0, 6 0, 6 6, 0 6, 0 0)) POLYGON((4 4, 10 4, 10 10, 4 10, 4 4)) MULTIPOINT((0 0), (1 1), (2 2)) MULTIPOINT((1 1), (2 2), (3 3)) MULTILINESTRING((0 0, 6 0)) MULTILINESTRING((4 0, 10 0)) MULTIPOLYGON(((0 0, 6 0, 6 6, 0 6, 0 0))) MULTIPOLYGON(((4 4, 10 4, 10 10, 4 10, 4 4)))
+equals geometrically equal pairs POINT(5 5) POINT(5 5) LINESTRING(0 0, 5 5, 10 0) LINESTRING(10 0, 5 5, 0 0) POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) POLYGON((10 0, 10 10, 0 10, 0 0, 10 0)) MULTIPOINT((5 5)) MULTIPOINT((5 5)) MULTILINESTRING((0 0, 5 5, 10 0)) MULTILINESTRING((10 0, 5 5, 0 0)) MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0))) MULTIPOLYGON(((10 0, 10 10, 0 10, 0 0, 10 0)))
+disjoint_near disjoint but bounding boxes overlap \N \N LINESTRING(0 0, 9 0, 0 9) LINESTRING(1 10, 10 10, 10 1) POLYGON((0 0, 9 0, 0 9, 0 0)) POLYGON((10 10, 1 10, 10 1, 10 10)) MULTIPOINT((1 1), (8 1), (1 8)) MULTIPOINT((9 9), (2 9), (9 2)) MULTILINESTRING((0 0, 9 0), (0 0, 0 9)) MULTILINESTRING((1 10, 10 10), (10 10, 10 1)) MULTIPOLYGON(((0 0, 9 0, 0 9, 0 0))) MULTIPOLYGON(((10 10, 1 10, 10 1, 10 10)))
+disjoint_far disjoint with non-overlapping bboxes POINT(0 0) POINT(100 100) LINESTRING(0 0, 1 1) LINESTRING(100 100, 101 101) POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)) POLYGON((100 100, 101 100, 101 101, 100 101, 100 100)) MULTIPOINT((0 0), (1 1)) MULTIPOINT((100 100), (101 101)) MULTILINESTRING((0 0, 1 1)) MULTILINESTRING((100 100, 101 101)) MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0))) MULTIPOLYGON(((100 100, 101 100, 101 101, 100 101, 100 100)))
diff --git a/testdata/datasets/functional/functional_schema_template.sql b/testdata/datasets/functional/functional_schema_template.sql
index 371a0c554..2371fdcf7 100644
--- a/testdata/datasets/functional/functional_schema_template.sql
+++ b/testdata/datasets/functional/functional_schema_template.sql
@@ -5003,4 +5003,54 @@ LOCATION '/test-warehouse/paimon_test/paimon_catalog/warehouse/functional.db/all
---- DEPENDENT_LOAD
`hadoop fs -mkdir -p /test-warehouse/paimon_test/paimon_catalog/warehouse/functional.db && \
hadoop fs -put -f ${IMPALA_HOME}/testdata/data/paimon_test/paimon_catalog/warehouse/functional.db/alltypes_structs_paimon /test-warehouse/paimon_test/paimon_catalog/warehouse/functional.db
+====
+---- DATASET
+functional
+---- BASE_TABLE_NAME
+geom_relations_wkt
+---- COLUMNS
+id STRING
+relation STRING
+point1 STRING
+point2 STRING
+linestring1 STRING
+linestring2 STRING
+polygon1 STRING
+polygon2 STRING
+multipoint1 STRING
+multipoint2 STRING
+multilinestring1 STRING
+multilinestring2 STRING
+multipolygon1 STRING
+multipolygon2 STRING
+---- ROW_FORMAT
+delimited fields terminated by '\t'
+---- LOAD
+LOAD DATA LOCAL INPATH '{impala_home}/testdata/data/geom_relations_wkt/data.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
+---- DEPENDENT_LOAD
+INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM functional.{table_name};
+====
+---- DATASET
+functional
+---- BASE_TABLE_NAME
+geom_relations
+---- CREATE
+CREATE VIEW IF NOT EXISTS {db_name}{db_suffix}.{table_name} AS
+SELECT
+ id,
+ relation,
+ st_GeomFromText(point1) AS point1,
+ st_GeomFromText(point2) AS point2,
+ st_GeomFromText(linestring1) AS linestring1,
+ st_GeomFromText(linestring2) AS linestring2,
+ st_GeomFromText(polygon1) AS polygon1,
+ st_GeomFromText(polygon2) AS polygon2,
+ st_GeomFromText(multipoint1) AS multipoint1,
+ st_GeomFromText(multipoint2) AS multipoint2,
+ st_GeomFromText(multilinestring1) AS multilinestring1,
+ st_GeomFromText(multilinestring2) AS multilinestring2,
+ st_GeomFromText(multipolygon1) AS multipolygon1,
+ st_GeomFromText(multipolygon2) AS multipolygon2
+FROM {db_name}{db_suffix}.geom_relations_wkt;
+---- LOAD
diff --git a/testdata/datasets/functional/schema_constraints.csv b/testdata/datasets/functional/schema_constraints.csv
index 7c75e12d1..ad9586f52 100644
--- a/testdata/datasets/functional/schema_constraints.csv
+++ b/testdata/datasets/functional/schema_constraints.csv
@@ -455,3 +455,7 @@ table_name:alltypes_structs_paimon, constraint:restrict_to, table_format:parquet
table_name:alltypes_nonpartitioned, constraint:restrict_to, table_format:text/none/none
table_name:alltypes_nonpartitioned, constraint:restrict_to, table_format:parquet/none/none
+
+# Geospatial relation test table - text (WKT) only
+table_name:geom_relations_wkt, constraint:restrict_to, table_format:text/none/none
+table_name:geom_relations, constraint:restrict_to, table_format:text/none/none
diff --git a/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-extra.test b/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-extra.test
index 694bc17e4..0257b6aee 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-extra.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-extra.test
@@ -153,19 +153,129 @@ STRING
'POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))'
====
---- QUERY
-# ST_DistanceSphere - great circle distance between points in meters
-select cast(ST_DistanceSphere(ST_Point(0, 0), ST_Point(0, 1)) as bigint);
+# ST_StartPoint / ST_EndPoint
+select ST_AsText(ST_StartPoint(ST_LineString(1,2, 3,4, 5,6)));
---- TYPES
-BIGINT
+STRING
---- RESULTS
-111195
+'POINT (1 2)'
====
---- QUERY
-select cast(ST_DistanceSphere(ST_Point(113.914603, 22.308919), ST_Point(151.177222, -33.946111)) as bigint);
+select ST_AsText(ST_EndPoint(ST_LineString(1,2, 3,4, 5,6)));
---- TYPES
-BIGINT
+STRING
---- RESULTS
-7393893
+'POINT (5 6)'
+====
+---- QUERY
+# Other types than LINESTRING return NULL.
+select ST_StartPoint(ST_Point(1, 2));
+---- TYPES
+BINARY
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# Other types than LINESTRING return NULL.
+select ST_EndPoint(ST_Point(1, 2));
+---- TYPES
+BINARY
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# ST_IsRing - true if linestring is closed and simple
+select ST_IsRing(ST_LineString(0,0, 3,4, 0,4, 0,0));
+---- TYPES
+BOOLEAN
+---- RESULTS
+true
+====
+---- QUERY
+select ST_IsRing(ST_LineString(0,0, 3,4));
+---- TYPES
+BOOLEAN
+---- RESULTS
+false
+====
+---- QUERY
+# Self-intersecting closed linestring is not a ring
+select ST_IsRing(ST_LineString(0,0, 1,1, 1,2, 2,1, 1,1, 0,0));
+---- TYPES
+BOOLEAN
+---- RESULTS
+false
+====
+---- QUERY
+# ST_NumInteriorRing
+select ST_NumInteriorRing(ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))'));
+---- TYPES
+INT
+---- RESULTS
+0
+====
+---- QUERY
+select ST_NumInteriorRing(
+ ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0), (2 2, 2 4, 4 4, 4 2, 2 2))'));
+---- TYPES
+INT
+---- RESULTS
+1
+====
+---- QUERY
+select ST_NumInteriorRing(
+ ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 3, 3 3, 3 1, 1 1), (5 5, 5 7, 7 7, 7 5, 5 5))'));
+---- TYPES
+INT
+---- RESULTS
+2
+====
+---- QUERY
+# ST_GeomCollection - only supports multi-part geometries of the same type.
+# True GEOMETRYCOLLECTION with mixed types returns NULL.
+select ST_AsText(ST_GeomCollection('GEOMETRYCOLLECTION (POINT (1 2), LINESTRING (0 0, 1 1))'));
+---- TYPES
+STRING
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# ST_GeomCollection with MULTIPOINT (homogeneous collection) works.
+select ST_AsText(ST_GeomCollection('MULTIPOINT ((1 2), (3 4))'));
+---- TYPES
+STRING
+---- RESULTS
+'MULTIPOINT ((1 2), (3 4))'
+====
+---- QUERY
+select ST_NumGeometries(ST_GeomCollection('MULTIPOINT ((1 2), (3 4), (5 6))'));
+---- TYPES
+INT
+---- RESULTS
+3
+====
+---- QUERY
+# ST_Union - union of two geometries
+select ST_AsText(ST_Union(ST_Point(1, 2), ST_Point(3, 4)));
+---- TYPES
+STRING
+---- RESULTS
+'MULTIPOINT ((1 2), (3 4))'
+====
+---- QUERY
+select ST_AsText(ST_Union(ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'), ST_GeomFromText('POLYGON ((1 1, 1 3, 3 3, 3 1, 1 1))')));
+---- TYPES
+STRING
+---- RESULTS
+'POLYGON ((0 0, 2 0, 2 1, 3 1, 3 3, 1 3, 1 2, 0 2, 0 0))'
+====
+---- QUERY
+# ST_Union with 3 arguments
+select ST_AsText(ST_Union(ST_Point(1, 1), ST_Point(2, 2), ST_Point(3, 3)));
+---- TYPES
+STRING
+---- RESULTS
+'MULTIPOINT ((1 1), (2 2), (3 3))'
====
---- QUERY
select st_intersects(st_geomfromtext('LINESTRING(1 0, 1 1)'), st_geomfromtext('MULTILINESTRING((0 0, 0 1), (2 0, 2 1))'));
@@ -222,4 +332,60 @@ select st_disjoint(st_geomfromtext('MULTILINESTRING((2 0, 2 2),(1 0, 1 1))'), st
BOOLEAN
---- RESULTS
false
+====
+---- QUERY
+# ST_DistanceSphere - great circle distance between points in meters
+select cast(ST_DistanceSphere(ST_Point(0, 0), ST_Point(0, 1)) as bigint);
+---- TYPES
+BIGINT
+---- RESULTS
+111195
+====
+---- QUERY
+# HK to Sydney
+select cast(ST_DistanceSphere(ST_Point(113.914603, 22.308919), ST_Point(151.177222, -33.946111)) as bigint);
+---- TYPES
+BIGINT
+---- RESULTS
+7393893
+====
+---- QUERY
+# HK to Toronto
+select cast(ST_DistanceSphere(ST_Point(113.914603, 22.308919), ST_Point(-79.630556, 43.677223)) as bigint);
+---- TYPES
+BIGINT
+---- RESULTS
+12548548
+====
+---- QUERY
+# Budapest Parliament to Buda Castle (~1.25 km)
+select cast(ST_DistanceSphere(ST_Point(19.04572641312447, 47.50745742600276), ST_Point(19.04125284394088, 47.49660323441422)) as bigint);
+---- TYPES
+BIGINT
+---- RESULTS
+1252
+====
+---- QUERY
+# Anti-meridian crossing: points near 180/-180 longitude should be close
+select ST_DistanceSphere(ST_Point(179.999, 0), ST_Point(-179.999, 0)) < 300;
+---- TYPES
+BOOLEAN
+---- RESULTS
+true
+====
+---- QUERY
+# Near the North Pole: opposite longitudes but both near 90 lat
+select ST_DistanceSphere(ST_Point(-60, 89.999), ST_Point(120, 89.999)) < 300;
+---- TYPES
+BOOLEAN
+---- RESULTS
+true
+====
+---- QUERY
+# Same point should give 0
+select cast(ST_DistanceSphere(ST_Point(10, 20), ST_Point(10, 20)) as bigint);
+---- TYPES
+BIGINT
+---- RESULTS
+0
====
\ No newline at end of file
diff --git a/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-srid.test b/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-srid.test
index 72088a098..d04ddf228 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-srid.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/geospatial-esri-srid.test
@@ -85,3 +85,25 @@ select ST_AsGeoJson(ST_GeomFromJson('{"x":1.2,"y":2.4,"spatialReference":{"wkid"
---- RESULTS
'{"type":"Point","coordinates":[1.2,2.4],"crs":null}'
====
+---- QUERY
+# ST_SRID - get spatial reference ID
+select ST_SRID(ST_SetSRID(ST_Point(1, 2), 4326));
+---- TYPES
+INT
+---- RESULTS
+4326
+====
+---- QUERY
+select ST_SRID(ST_GeomFromText('POINT (1 2)', 4326));
+---- TYPES
+INT
+---- RESULTS
+4326
+====
+---- QUERY
+select ST_SRID(ST_Point(1, 2));
+---- TYPES
+INT
+---- RESULTS
+0
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/geospatial-relations-table.test b/testdata/workloads/functional-query/queries/QueryTest/geospatial-relations-table.test
new file mode 100644
index 000000000..e26bca602
--- /dev/null
+++ b/testdata/workloads/functional-query/queries/QueryTest/geospatial-relations-table.test
@@ -0,0 +1,847 @@
+====
+---- QUERY
+-- contains: point1 vs all type2 columns
+SELECT
+ st_Contains(point1, point2),
+ st_Contains(point1, linestring2),
+ st_Contains(point1, polygon2),
+ st_Contains(point1, multipoint2),
+ st_Contains(point1, multilinestring2),
+ st_Contains(point1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false,false
+====
+---- QUERY
+-- contains: linestring1 vs all type2 columns
+SELECT
+ st_Contains(linestring1, point2),
+ st_Contains(linestring1, linestring2),
+ st_Contains(linestring1, polygon2),
+ st_Contains(linestring1, multipoint2),
+ st_Contains(linestring1, multilinestring2),
+ st_Contains(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,false,true,true,false
+====
+---- QUERY
+-- contains: polygon1 vs all type2 columns
+SELECT
+ st_Contains(polygon1, point2),
+ st_Contains(polygon1, linestring2),
+ st_Contains(polygon1, polygon2),
+ st_Contains(polygon1, multipoint2),
+ st_Contains(polygon1, multilinestring2),
+ st_Contains(polygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- contains: multipoint1 vs all type2 columns
+SELECT
+ st_Contains(multipoint1, point2),
+ st_Contains(multipoint1, linestring2),
+ st_Contains(multipoint1, polygon2),
+ st_Contains(multipoint1, multipoint2),
+ st_Contains(multipoint1, multilinestring2),
+ st_Contains(multipoint1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false,false
+====
+---- QUERY
+-- contains: multilinestring1 vs all type2 columns
+SELECT
+ st_Contains(multilinestring1, point2),
+ st_Contains(multilinestring1, linestring2),
+ st_Contains(multilinestring1, polygon2),
+ st_Contains(multilinestring1, multipoint2),
+ st_Contains(multilinestring1, multilinestring2),
+ st_Contains(multilinestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,false,true,true,false
+====
+---- QUERY
+-- contains: multipolygon1 vs all type2 columns
+SELECT
+ st_Contains(multipolygon1, point2),
+ st_Contains(multipolygon1, linestring2),
+ st_Contains(multipolygon1, polygon2),
+ st_Contains(multipolygon1, multipoint2),
+ st_Contains(multipolygon1, multilinestring2),
+ st_Contains(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- contains reversed: point2 vs all type1 columns
+SELECT
+ st_Contains(point2, point1),
+ st_Contains(point2, linestring1),
+ st_Contains(point2, polygon1),
+ st_Contains(point2, multipoint1),
+ st_Contains(point2, multilinestring1),
+ st_Contains(point2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,false,false,false
+====
+---- QUERY
+-- contains reversed: linestring2 vs all type1 columns
+SELECT
+ st_Contains(linestring2, point1),
+ st_Contains(linestring2, linestring1),
+ st_Contains(linestring2, polygon1),
+ st_Contains(linestring2, multipoint1),
+ st_Contains(linestring2, multilinestring1),
+ st_Contains(linestring2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,false,false,false
+====
+---- QUERY
+-- contains reversed: polygon2 vs all type1 columns
+SELECT
+ st_Contains(polygon2, point1),
+ st_Contains(polygon2, linestring1),
+ st_Contains(polygon2, polygon1),
+ st_Contains(polygon2, multipoint1),
+ st_Contains(polygon2, multilinestring1),
+ st_Contains(polygon2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false,false
+====
+---- QUERY
+-- contains reversed: multipoint2 vs all type1 columns
+SELECT
+ st_Contains(multipoint2, point1),
+ st_Contains(multipoint2, linestring1),
+ st_Contains(multipoint2, polygon1),
+ st_Contains(multipoint2, multipoint1),
+ st_Contains(multipoint2, multilinestring1),
+ st_Contains(multipoint2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,false,false,false
+====
+---- QUERY
+-- contains reversed: multilinestring2 vs all type1 columns
+SELECT
+ st_Contains(multilinestring2, point1),
+ st_Contains(multilinestring2, linestring1),
+ st_Contains(multilinestring2, polygon1),
+ st_Contains(multilinestring2, multipoint1),
+ st_Contains(multilinestring2, multilinestring1),
+ st_Contains(multilinestring2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,false,false,false
+====
+---- QUERY
+-- contains reversed: multipolygon2 vs all type1 columns
+SELECT
+ st_Contains(multipolygon2, point1),
+ st_Contains(multipolygon2, linestring1),
+ st_Contains(multipolygon2, polygon1),
+ st_Contains(multipolygon2, multipoint1),
+ st_Contains(multipolygon2, multilinestring1),
+ st_Contains(multipolygon2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false,false
+====
+---- QUERY
+-- touches: linestring1 vs all non-NULL type2 columns (symmetric)
+SELECT
+ st_Touches(linestring1, linestring2),
+ st_Touches(linestring1, polygon2),
+ st_Touches(linestring1, multilinestring2),
+ st_Touches(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- touches: polygon1 vs all non-NULL type2 columns
+SELECT
+ st_Touches(polygon1, linestring2),
+ st_Touches(polygon1, polygon2),
+ st_Touches(polygon1, multilinestring2),
+ st_Touches(polygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- touches: multilinestring1 vs all non-NULL type2 columns
+SELECT
+ st_Touches(multilinestring1, linestring2),
+ st_Touches(multilinestring1, polygon2),
+ st_Touches(multilinestring1, multilinestring2),
+ st_Touches(multilinestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- touches: multipolygon1 vs all non-NULL type2 columns
+SELECT
+ st_Touches(multipolygon1, linestring2),
+ st_Touches(multipolygon1, polygon2),
+ st_Touches(multipolygon1, multilinestring2),
+ st_Touches(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- touches reversed: type2 vs all type1 (symmetric, should match)
+SELECT
+ st_Touches(linestring2, linestring1),
+ st_Touches(linestring2, polygon1),
+ st_Touches(linestring2, multilinestring1),
+ st_Touches(linestring2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- touches reversed: polygon2 vs all type1
+SELECT
+ st_Touches(polygon2, linestring1),
+ st_Touches(polygon2, polygon1),
+ st_Touches(polygon2, multilinestring1),
+ st_Touches(polygon2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'touches';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- intersects: all type1 vs all type2 (all pass through (5,5), all true)
+SELECT
+ st_Intersects(point1, point2),
+ st_Intersects(point1, linestring2),
+ st_Intersects(point1, polygon2),
+ st_Intersects(point1, multipoint2),
+ st_Intersects(point1, multilinestring2),
+ st_Intersects(point1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- intersects: linestring1 vs all type2
+SELECT
+ st_Intersects(linestring1, point2),
+ st_Intersects(linestring1, linestring2),
+ st_Intersects(linestring1, polygon2),
+ st_Intersects(linestring1, multipoint2),
+ st_Intersects(linestring1, multilinestring2),
+ st_Intersects(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- intersects: polygon1 vs all type2
+SELECT
+ st_Intersects(polygon1, point2),
+ st_Intersects(polygon1, linestring2),
+ st_Intersects(polygon1, polygon2),
+ st_Intersects(polygon1, multipoint2),
+ st_Intersects(polygon1, multilinestring2),
+ st_Intersects(polygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- intersects: multipoint1 vs all type2
+SELECT
+ st_Intersects(multipoint1, point2),
+ st_Intersects(multipoint1, linestring2),
+ st_Intersects(multipoint1, polygon2),
+ st_Intersects(multipoint1, multipoint2),
+ st_Intersects(multipoint1, multilinestring2),
+ st_Intersects(multipoint1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- intersects: multilinestring1 vs all type2
+SELECT
+ st_Intersects(multilinestring1, point2),
+ st_Intersects(multilinestring1, linestring2),
+ st_Intersects(multilinestring1, polygon2),
+ st_Intersects(multilinestring1, multipoint2),
+ st_Intersects(multilinestring1, multilinestring2),
+ st_Intersects(multilinestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- intersects: multipolygon1 vs all type2
+SELECT
+ st_Intersects(multipolygon1, point2),
+ st_Intersects(multipolygon1, linestring2),
+ st_Intersects(multipolygon1, polygon2),
+ st_Intersects(multipolygon1, multipoint2),
+ st_Intersects(multipolygon1, multilinestring2),
+ st_Intersects(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- crosses: linestring1 vs all non-NULL type2 (lines cross lines and polygons)
+SELECT
+ st_Crosses(linestring1, linestring2),
+ st_Crosses(linestring1, polygon2),
+ st_Crosses(linestring1, multilinestring2),
+ st_Crosses(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- crosses: polygon1 vs all non-NULL type2 (area crosses lines, not areas)
+SELECT
+ st_Crosses(polygon1, linestring2),
+ st_Crosses(polygon1, polygon2),
+ st_Crosses(polygon1, multilinestring2),
+ st_Crosses(polygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,true,false
+====
+---- QUERY
+-- crosses: multilinestring1 vs all non-NULL type2
+SELECT
+ st_Crosses(multilinestring1, linestring2),
+ st_Crosses(multilinestring1, polygon2),
+ st_Crosses(multilinestring1, multilinestring2),
+ st_Crosses(multilinestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- crosses: multipolygon1 vs all non-NULL type2 (area crosses lines, not areas)
+SELECT
+ st_Crosses(multipolygon1, linestring2),
+ st_Crosses(multipolygon1, polygon2),
+ st_Crosses(multipolygon1, multilinestring2),
+ st_Crosses(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,true,false
+====
+---- QUERY
+-- crosses reversed: type2 vs type1 (symmetric)
+SELECT
+ st_Crosses(linestring2, linestring1),
+ st_Crosses(linestring2, polygon1),
+ st_Crosses(linestring2, multilinestring1),
+ st_Crosses(linestring2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- overlaps: linestring1 vs all non-NULL type2 (same-dim=true, cross-dim=false)
+SELECT
+ st_Overlaps(linestring1, linestring2),
+ st_Overlaps(linestring1, polygon2),
+ st_Overlaps(linestring1, multipoint2),
+ st_Overlaps(linestring1, multilinestring2),
+ st_Overlaps(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false
+====
+---- QUERY
+-- overlaps: polygon1 vs all non-NULL type2
+SELECT
+ st_Overlaps(polygon1, linestring2),
+ st_Overlaps(polygon1, polygon2),
+ st_Overlaps(polygon1, multipoint2),
+ st_Overlaps(polygon1, multilinestring2),
+ st_Overlaps(polygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,true,false,false,true
+====
+---- QUERY
+-- overlaps: multipoint1 vs all non-NULL type2
+SELECT
+ st_Overlaps(multipoint1, linestring2),
+ st_Overlaps(multipoint1, polygon2),
+ st_Overlaps(multipoint1, multipoint2),
+ st_Overlaps(multipoint1, multilinestring2),
+ st_Overlaps(multipoint1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,true,false,false
+====
+---- QUERY
+-- overlaps: multilinestring1 vs all non-NULL type2
+SELECT
+ st_Overlaps(multilinestring1, linestring2),
+ st_Overlaps(multilinestring1, polygon2),
+ st_Overlaps(multilinestring1, multipoint2),
+ st_Overlaps(multilinestring1, multilinestring2),
+ st_Overlaps(multilinestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,false,false,true,false
+====
+---- QUERY
+-- overlaps: multipolygon1 vs all non-NULL type2
+SELECT
+ st_Overlaps(multipolygon1, linestring2),
+ st_Overlaps(multipolygon1, polygon2),
+ st_Overlaps(multipolygon1, multipoint2),
+ st_Overlaps(multipolygon1, multilinestring2),
+ st_Overlaps(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,true,false,false,true
+====
+---- QUERY
+-- overlaps reversed: type2 vs type1 (symmetric, should match)
+SELECT
+ st_Overlaps(linestring2, linestring1),
+ st_Overlaps(polygon2, polygon1),
+ st_Overlaps(multipoint2, multipoint1),
+ st_Overlaps(multilinestring2, multilinestring1),
+ st_Overlaps(multipolygon2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'overlaps';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true
+====
+---- QUERY
+-- equals: same-type pairs (all true)
+SELECT
+ st_Equals(point1, point2),
+ st_Equals(linestring1, linestring2),
+ st_Equals(polygon1, polygon2),
+ st_Equals(multipoint1, multipoint2),
+ st_Equals(multilinestring1, multilinestring2),
+ st_Equals(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'equals';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- equals: point1 vs single-component multi types (same point set)
+SELECT
+ st_Equals(point1, multipoint2),
+ st_Equals(multipoint1, point2)
+FROM functional.geom_relations WHERE id = 'equals';
+---- TYPES
+BOOLEAN,BOOLEAN
+---- RESULTS
+true,true
+====
+---- QUERY
+-- equals: linestring1 vs multilinestring (same point set)
+SELECT
+ st_Equals(linestring1, multilinestring2),
+ st_Equals(multilinestring1, linestring2)
+FROM functional.geom_relations WHERE id = 'equals';
+---- TYPES
+BOOLEAN,BOOLEAN
+---- RESULTS
+true,true
+====
+---- QUERY
+-- equals: polygon1 vs multipolygon (same point set)
+SELECT
+ st_Equals(polygon1, multipolygon2),
+ st_Equals(multipolygon1, polygon2)
+FROM functional.geom_relations WHERE id = 'equals';
+---- TYPES
+BOOLEAN,BOOLEAN
+---- RESULTS
+true,true
+====
+---- QUERY
+-- equals: cross-dimension pairs (all false)
+SELECT
+ st_Equals(point1, linestring2),
+ st_Equals(point1, polygon2),
+ st_Equals(linestring1, polygon2),
+ st_Equals(linestring1, multipoint2),
+ st_Equals(polygon1, point2),
+ st_Equals(polygon1, multilinestring2)
+FROM functional.geom_relations WHERE id = 'equals';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- disjoint_near: all non-NULL cross-type pairs are disjoint (st_Intersects=false)
+SELECT
+ st_Intersects(linestring1, polygon2),
+ st_Intersects(linestring1, multipoint2),
+ st_Intersects(linestring1, multilinestring2),
+ st_Intersects(linestring1, multipolygon2),
+ st_Intersects(polygon1, linestring2),
+ st_Intersects(polygon1, multipoint2)
+FROM functional.geom_relations WHERE id = 'disjoint_near';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- disjoint_near: more cross-type pairs
+SELECT
+ st_Intersects(polygon1, multilinestring2),
+ st_Intersects(polygon1, multipolygon2),
+ st_Intersects(multipoint1, linestring2),
+ st_Intersects(multipoint1, polygon2),
+ st_Intersects(multipoint1, multipoint2),
+ st_Intersects(multipoint1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'disjoint_near';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- disjoint_near: same-type pairs also disjoint
+SELECT
+ st_Intersects(linestring1, linestring2),
+ st_Intersects(polygon1, polygon2),
+ st_Intersects(multipoint1, multipoint2),
+ st_Intersects(multilinestring1, multilinestring2),
+ st_Intersects(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'disjoint_near';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false
+====
+---- QUERY
+-- disjoint_far: all cross-type pairs disjoint
+SELECT
+ st_Intersects(point1, linestring2),
+ st_Intersects(point1, polygon2),
+ st_Intersects(point1, multipoint2),
+ st_Intersects(linestring1, point2),
+ st_Intersects(linestring1, polygon2),
+ st_Intersects(linestring1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'disjoint_far';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- disjoint_far: all same-type pairs disjoint
+SELECT
+ st_Intersects(point1, point2),
+ st_Intersects(linestring1, linestring2),
+ st_Intersects(polygon1, polygon2),
+ st_Intersects(multipoint1, multipoint2),
+ st_Intersects(multilinestring1, multilinestring2),
+ st_Intersects(multipolygon1, multipolygon2)
+FROM functional.geom_relations WHERE id = 'disjoint_far';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- disjoint_far reversed: type2 vs type1 (symmetric)
+SELECT
+ st_Intersects(polygon2, point1),
+ st_Intersects(polygon2, linestring1),
+ st_Intersects(polygon2, multipoint1),
+ st_Intersects(multipolygon2, polygon1),
+ st_Intersects(multipolygon2, multilinestring1),
+ st_Intersects(multipolygon2, multipolygon1)
+FROM functional.geom_relations WHERE id = 'disjoint_far';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false,false,false
+====
+---- QUERY
+-- constant LHS: polygon literal contains table column geometries (exercises PrepareLhs)
+SELECT
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ point2),
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ linestring2),
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ polygon2),
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ multipoint2),
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ multilinestring2),
+ st_Contains(st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
+ multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- constant RHS: table column geometries contain point literal (exercises PrepareRhs)
+SELECT
+ st_Contains(point1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Contains(linestring1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Contains(polygon1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Contains(multipoint1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Contains(multilinestring1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Contains(multipolygon1,
+ st_GeomFromText('POINT(5 5)'))
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- constant RHS: table column geometries contain multipoint literal
+SELECT
+ st_Contains(polygon1,
+ st_GeomFromText('MULTIPOINT((5 5))')),
+ st_Contains(multipoint1,
+ st_GeomFromText('MULTIPOINT((5 5))')),
+ st_Contains(multipolygon1,
+ st_GeomFromText('MULTIPOINT((5 5))'))
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true
+====
+---- QUERY
+-- constant LHS: within with constant polygon (exercises PrepareLhs)
+SELECT
+ st_Within(st_GeomFromText('POINT(5 5)'),
+ polygon1),
+ st_Within(st_GeomFromText('POINT(5 5)'),
+ multipolygon1)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN
+---- RESULTS
+true,true
+====
+---- QUERY
+-- constant RHS: within with constant polygon (exercises PrepareRhs)
+SELECT
+ st_Within(point2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
+ st_Within(linestring2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
+ st_Within(polygon2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
+ st_Within(multipoint2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
+ st_Within(multilinestring2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
+ st_Within(multipolygon2,
+ st_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'))
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- constant LHS: intersects with constant point (exercises PrepareLhs)
+SELECT
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ point2),
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ linestring2),
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ polygon2),
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ multipoint2),
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ multilinestring2),
+ st_Intersects(st_GeomFromText('POINT(5 5)'),
+ multipolygon2)
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- constant RHS: intersects with constant point (exercises PrepareRhs)
+SELECT
+ st_Intersects(point1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Intersects(linestring1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Intersects(polygon1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Intersects(multipoint1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Intersects(multilinestring1,
+ st_GeomFromText('POINT(5 5)')),
+ st_Intersects(multipolygon1,
+ st_GeomFromText('POINT(5 5)'))
+FROM functional.geom_relations WHERE id = 'intersects';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true,true,true
+====
+---- QUERY
+-- constant LHS: crosses with constant linestring (exercises PrepareLhs)
+SELECT
+ st_Crosses(st_GeomFromText('LINESTRING(0 5, 10 5)'),
+ linestring2),
+ st_Crosses(st_GeomFromText('LINESTRING(0 5, 10 5)'),
+ polygon2),
+ st_Crosses(st_GeomFromText('LINESTRING(0 5, 10 5)'),
+ multilinestring2),
+ st_Crosses(st_GeomFromText('LINESTRING(0 5, 10 5)'),
+ multipolygon2)
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- constant RHS: crosses with constant linestring (exercises PrepareRhs)
+SELECT
+ st_Crosses(linestring1,
+ st_GeomFromText('LINESTRING(5 0, 5 10)')),
+ st_Crosses(polygon1,
+ st_GeomFromText('LINESTRING(5 0, 5 10)')),
+ st_Crosses(multilinestring1,
+ st_GeomFromText('LINESTRING(5 0, 5 10)')),
+ st_Crosses(multipolygon1,
+ st_GeomFromText('LINESTRING(5 0, 5 10)'))
+FROM functional.geom_relations WHERE id = 'crosses';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+true,true,true,true
+====
+---- QUERY
+-- constant LHS false case: disjoint polygon does not intersect table geometries
+SELECT
+ st_Intersects(st_GeomFromText('POLYGON((50 50, 51 50, 51 51, 50 51, 50 50))'),
+ point2),
+ st_Intersects(st_GeomFromText('POLYGON((50 50, 51 50, 51 51, 50 51, 50 50))'),
+ linestring2),
+ st_Intersects(st_GeomFromText('POLYGON((50 50, 51 50, 51 51, 50 51, 50 50))'),
+ polygon2),
+ st_Intersects(st_GeomFromText('POLYGON((50 50, 51 50, 51 51, 50 51, 50 50))'),
+ multipolygon2)
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN
+---- RESULTS
+false,false,false,false
+====
+---- QUERY
+-- constant RHS false case: table geometries do not contain distant point
+SELECT
+ st_Contains(polygon1,
+ st_GeomFromText('POINT(50 50)')),
+ st_Contains(multipolygon1,
+ st_GeomFromText('POINT(50 50)'))
+FROM functional.geom_relations WHERE id = 'contains';
+---- TYPES
+BOOLEAN,BOOLEAN
+---- RESULTS
+false,false
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/geospatial-wkb-serialization.test b/testdata/workloads/functional-query/queries/QueryTest/geospatial-wkb-serialization.test
new file mode 100644
index 000000000..a5a41ad93
--- /dev/null
+++ b/testdata/workloads/functional-query/queries/QueryTest/geospatial-wkb-serialization.test
@@ -0,0 +1,177 @@
+# Some tests are based on ClickHouse's WKB geospatial test suite:
+# https://github.com/ClickHouse/ClickHouse/blob/c017cc7907535287f232c2de4754470d453ea219/tests/queries/0_stateless/03458_wkb_function.sql
+====
+---- QUERY
+# WKB round-trip: Point
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_Point(1.5, 2.5))));
+---- RESULTS
+'POINT (1.5 2.5)'
+====
+---- QUERY
+# WKB round-trip: Point with high-precision coordinates
+# Java uses 16 significant digits, DoubleToBuffer uses 17 for this value.
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_Point(1.123456789012345, -2.987654321098765))))
+ in ('POINT (1.123456789012345 -2.9876543210987649)',
+ 'POINT (1.123456789012345 -2.987654321098765)');
+---- RESULTS
+true
+====
+---- QUERY
+# WKB round-trip: LineString
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_LineString(0,0, 1,1, 2,0))));
+---- RESULTS
+'LINESTRING (0 0, 1 1, 2 0)'
+====
+---- QUERY
+# WKB round-trip: Polygon (simple triangle)
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_Polygon(0,0, 10,0, 10,10, 0,0))));
+---- RESULTS
+'POLYGON ((0 0, 10 0, 10 10, 0 0))'
+====
+---- QUERY
+# WKB round-trip: Polygon with hole
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('polygon ((0 0, 0 10, 10 10, 10 0, 0 0), (2 2, 2 4, 4 4, 4 2, 2 2))'))));
+---- RESULTS
+'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 4, 4 4, 4 2, 2 2))'
+====
+---- QUERY
+# WKB round-trip: MultiPoint
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('multipoint ((0 0), (1 1), (2 2))'))));
+---- RESULTS
+'MULTIPOINT ((0 0), (1 1), (2 2))'
+====
+---- QUERY
+# WKB round-trip: MultiLineString
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('multilinestring ((0 0, 1 1), (2 2, 3 3, 4 4))'))));
+---- RESULTS
+'MULTILINESTRING ((0 0, 1 1), (2 2, 3 3, 4 4))'
+====
+---- QUERY
+# WKB round-trip: MultiPolygon
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('multipolygon (((0 0, 1 0, 1 1, 0 0)), ((2 2, 3 2, 3 3, 2 2)))'))));
+---- RESULTS
+'MULTIPOLYGON (((0 0, 1 0, 1 1, 0 0)), ((2 2, 3 2, 3 3, 2 2)))'
+====
+---- QUERY
+# WKB from literal hex: valid Point (0, 0) — LE, type=1, x=0.0, y=0.0
+select ST_AsText(ST_GeomFromWKB(cast(unhex('010100000000000000000000000000000000000000') as binary)));
+---- RESULTS
+'POINT (0 0)'
+====
+---- QUERY
+# WKB from literal hex: Point (1.0, 2.0) — LE, type=1
+select ST_AsText(ST_GeomFromWKB(cast(unhex('0101000000000000000000F03F0000000000000040') as binary)));
+---- RESULTS
+'POINT (1 2)'
+====
+---- QUERY
+# WKB from literal hex: LineString with 2 points (0,0) (1,1)
+select ST_AsText(ST_GeomFromWKB(cast(unhex('01020000000200000000000000000000000000000000000000000000000000F03F000000000000F03F') as binary)));
+---- RESULTS
+'LINESTRING (0 0, 1 1)'
+====
+---- QUERY
+# WKB from literal hex: Polygon triangle (0,0)(1,0)(0,1)(0,0)
+select ST_AsText(ST_GeomFromWKB(cast(unhex('0103000000010000000400000000000000000000000000000000000000000000000000F03F00000000000000000000000000000000000000000000F03F00000000000000000000000000000000') as binary)));
+---- RESULTS
+'POLYGON ((0 0, 1 0, 0 1, 0 0))'
+====
+---- QUERY
+# WKB from literal hex: big-endian Point (1.0, 2.0)
+select ST_AsText(ST_GeomFromWKB(cast(unhex('00000000013FF00000000000004000000000000000') as binary)));
+---- RESULTS
+'POINT (1 2)'
+====
+---- QUERY
+# WKB from literal hex: big-endian LineString (0,0)(1,1)
+select ST_AsText(ST_GeomFromWKB(cast(unhex('000000000200000002000000000000000000000000000000003FF00000000000003FF0000000000000') as binary)));
+---- RESULTS
+'LINESTRING (0 0, 1 1)'
+====
+---- QUERY
+# WKB error: empty input
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: too short (only 4 bytes, need at least 5 for header)
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('01010000') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: unknown geometry type (99)
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('016300000000000000000000000000000000000000') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: Point too short (header + only x, missing y)
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('01010000000000000000000000') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: LineString truncated (claims 2 points but data has only 1)
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('0102000000020000000000000000000000000000000000F03F') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: Polygon truncated (claims 1 ring with 4 points, only 2 present)
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('0103000000010000000400000000000000000000000000000000000000000000000000F03F000000000000F03F') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: MultiPolygon with truncated sub-polygon
+select ST_GeometryType(ST_GeomFromWKB(cast(unhex('0106000000010000000103000000010000000400000000000000000000000000000000000000') as binary)));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB error: NULL input
+select ST_GeometryType(ST_GeomFromWKB(NULL));
+---- RESULTS
+'NULL'
+====
+---- QUERY
+# WKB/WKT round-trip: negative coordinates
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_Point(-179.5, -89.5))));
+---- RESULTS
+'POINT (-179.5 -89.5)'
+====
+---- QUERY
+# WKB/WKT round-trip: zero-area polygon (degenerate, OGC-invalid)
+# ESRI rejects this as invalid (NULL), boost accepts it. Both are valid behaviors.
+select coalesce(ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('polygon ((0 0, 1 0, 1 0, 0 0))')))), 'NULL')
+ in ('POLYGON ((0 0, 1 0, 1 0, 0 0))', 'NULL');
+---- RESULTS
+true
+====
+---- QUERY
+# WKB round-trip: large coordinate values (verify via ST_X/ST_Y to avoid formatting differences)
+select ST_X(ST_GeomFromWKB(ST_AsBinary(ST_Point(1e15, -1e15)))),
+ ST_Y(ST_GeomFromWKB(ST_AsBinary(ST_Point(1e15, -1e15))));
+---- TYPES
+double, double
+---- RESULTS
+1000000000000000,-1000000000000000
+====
+---- QUERY
+# WKB round-trip: very small coordinate values
+select ST_X(ST_GeomFromWKB(ST_AsBinary(ST_Point(1e-10, 2e-10)))),
+ ST_Y(ST_GeomFromWKB(ST_AsBinary(ST_Point(1e-10, 2e-10))));
+---- TYPES
+double, double
+---- RESULTS
+1e-10,2e-10
+====
+---- QUERY
+# ST_AsText round-trip: MultiPolygon with holes
+select ST_AsText(ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText(
+ 'multipolygon (((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 4, 4 4, 4 2, 2 2)), ((20 20, 30 20, 30 30, 20 20)))'))));
+---- RESULTS
+'MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 4, 4 4, 4 2, 2 2)), ((20 20, 30 20, 30 30, 20 20)))'
+====
diff --git a/tests/custom_cluster/test_geospatial_library.py b/tests/custom_cluster/test_geospatial_library.py
index 6ab0350ac..563bb5c6d 100644
--- a/tests/custom_cluster/test_geospatial_library.py
+++ b/tests/custom_cluster/test_geospatial_library.py
@@ -25,19 +25,18 @@ ST_X_SIGNATURE_BUILTIN = "DOUBLE\tst_x(BINARY)\tBUILTIN\ttrue"
SHOW_FUNCTIONS = "show functions in _impala_builtins"
+@SkipIfApacheHive.feature_not_supported
class TestGeospatialLibrary(CustomClusterTestSuite):
"""Tests the geospatial_library backend flag"""
@CustomClusterTestSuite.with_args(start_args='--geospatial_library=NONE')
- @SkipIfApacheHive.feature_not_supported
- @pytest.mark.execute_serially
def test_disabled(self):
result = self.execute_query(SHOW_FUNCTIONS)
assert ST_POINT_SIGNATURE not in result.data
assert ST_X_SIGNATURE_BUILTIN not in result.data
- @SkipIfApacheHive.feature_not_supported
@pytest.mark.execute_serially
+ @CustomClusterTestSuite.with_args(start_args='--geospatial_library=HIVE_ESRI')
def test_enabled(self):
result = self.execute_query(SHOW_FUNCTIONS)
assert ST_POINT_SIGNATURE in result.data
@@ -47,12 +46,23 @@ class TestGeospatialLibrary(CustomClusterTestSuite):
# ESRI test files are intentionally excluded.
@CustomClusterTestSuite.with_args(
start_args='--geospatial_library=WKB_EXPERIMENTAL')
- @SkipIfApacheHive.feature_not_supported
def test_wkb_experimental_esri(self, vector):
self.run_test_case('QueryTest/geospatial-esri', vector)
@CustomClusterTestSuite.with_args(
start_args='--geospatial_library=WKB_EXPERIMENTAL')
- @SkipIfApacheHive.feature_not_supported
def test_wkb_experimental_extra(self, vector):
self.run_test_case('QueryTest/geospatial-esri-extra', vector)
+
+ @CustomClusterTestSuite.with_args(
+ start_args='--geospatial_library=WKB_EXPERIMENTAL')
+ def test_wkb_experimental_serialization(self, vector):
+ # WKB is the inter-function serialization format in this mode, so this exercises
+ # the WKB round-trip / malformed-input handling of the WKB serialization path.
+ self.run_test_case('QueryTest/geospatial-wkb-serialization', vector)
+
+ @CustomClusterTestSuite.with_args(
+ start_args='--geospatial_library=WKB_EXPERIMENTAL')
+ @pytest.mark.execute_serially
+ def test_relations_table(self, vector):
+ self.run_test_case('QueryTest/geospatial-relations-table', vector)
diff --git a/tests/query_test/test_geospatial_functions.py b/tests/query_test/test_geospatial_functions.py
index 3dee728f4..e2ba9b10d 100644
--- a/tests/query_test/test_geospatial_functions.py
+++ b/tests/query_test/test_geospatial_functions.py
@@ -53,6 +53,13 @@ class TestGeospatialFuctions(ImpalaTestSuite):
# 3D/4D geometry tests (ST_Z, ST_M, ST_Is3D, etc.)
self.run_test_case('QueryTest/geospatial-esri-high-dimension', vector)
+ def test_wkb_serialization(self, vector):
+ # WKB serialization round-trip and error cases
+ self.run_test_case('QueryTest/geospatial-wkb-serialization', vector)
+
+ def test_relations_table(self, vector):
+ self.run_test_case('QueryTest/geospatial-relations-table', vector)
+
def test_esri_geospatial_planner(self, vector):
# These tests are not among planner tests because with default flags
# geospatial builtin functions are not loaded.