core/src/test/resources/sql/spatial.iq

# spatial.iq - Geo-spatial functions
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
!use geo
!set outputformat csv

# Check that the data set is OK.
select count(*) as c from GEO."countries";
C
245
!ok

select count(*) as c from GEO."states";
C
11
!ok

select count(*) as c from GEO."parks";
C
3
!ok

#### Geometry conversion functions (2D)

# ST_AsBinary(geom) Geometry to WKB
SELECT ST_AsBinary('LINESTRING (1 2, 3 4)');
EXPR$0
0000000002000000023ff0000000000000400000000000000040080000000000004010000000000000
!ok

# ST_AsEWKT(geom) Geometry to EWKT
SELECT ST_AsEWKT('LINESTRING (1 2, 3 4)');
EXPR$0
srid:0;LINESTRING (1 2, 3 4)
!ok

# ST_AsGeoJSON(geom) Geometry to GeoJSON
SELECT ST_AsGeoJSON('LINESTRING (1 2, 3 4)');
EXPR$0
{"type":"LineString","coordinates":[[1,2],[3,4]],"crs":{"type":"name","properties":{"name":"EPSG:0"}}}
!ok

# ST_AsGML(geom) Geometry to GML
SELECT ST_AsGML('LINESTRING (1 2, 3 4)');
EXPR$0
<gml:LineString><gml:coordinates>1.0,2.0 3.0,4.0 </gml:coordinates></gml:LineString>
!ok

# ST_AsText(geom) Alias for `ST_AsWKT`
SELECT ST_AsText('POINT(-71.064544 42.28787)');
EXPR$0
POINT (-71.064544 42.28787)
!ok

# ST_AsWKT(geom) Converts *geom* → Well-Known Text
SELECT ST_AsWKT('POINT(-71.064544 42.28787)');
EXPR$0
POINT (-71.064544 42.28787)
!ok

# ST_AsEWKB(geom) Geometry to WKB
SELECT ST_AsWKB('LINESTRING (1 2, 3 4)');
EXPR$0
0000000002000000023ff0000000000000400000000000000040080000000000004010000000000000
!ok

# PostGIS can implicitly assign from CHAR to GEOMETRY; we can't
!if (false) {
# ST_AsWKT(geom) Geometry to Well Known Text
SELECT ST_AsText('01030000000100000005000000000000000000
000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000');
!ok
!}

SELECT ST_AsWKT(CAST(NULL AS GEOMETRY));
EXPR$0
null
!ok

# ST_Force2D(geom) 3D Geometry to 2D Geometry

SELECT ST_AsText(ST_Force2D('POINT(-10 10)'));
EXPR$0
POINT (-10 10)
!ok

SELECT ST_AsText(ST_Force2D('POINT(-10 10 6)'));
EXPR$0
POINT (-10 10)
!ok

SELECT ST_AsText(ST_Force2D('LINESTRING(-10 10 2, 10 10 3)'));
EXPR$0
LINESTRING (-10 10, 10 10)
!ok

# ST_GeomFromEWKT(ewkt) EWKT to Geometry
SELECT ST_AsEWKT(ST_GeomFromEWKT('srid:4326;LINESTRING (1 2, 3 4)'));
EXPR$0
srid:4326;LINESTRING (1 2, 3 4)
!ok

# ST_GeomFromGeoJSON(geoJSON) GeoJSON to Geometry
SELECT ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[1,2],[3,4]],"crs":{"type":"name","properties":{"name":"EPSG:0"}}}');
EXPR$0
LINESTRING (1 2, 3 4)
!ok

# ST_GeomFromGML(gml) GML to Geometry
SELECT ST_GeomFromGML('<gml:LineString><gml:coordinates>1.0,2.0 3.0,4.0</gml:coordinates></gml:LineString>');
EXPR$0
LINESTRING (1 2, 3 4)
!ok

# ST_GeomFromGML(gml, srid) GML to Geometry
SELECT ST_AsEWKT(ST_GeomFromGML('<gml:LineString><gml:coordinates>1.0,2.0 3.0,4.0</gml:coordinates></gml:LineString>', 4326));
EXPR$0
srid:4326;LINESTRING (1 2, 3 4)
!ok

# ST_GeomFromText(wkt [, srid ]) Returns a specified geometry value from Well-Known Text representation
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');
EXPR$0
LINESTRING (-71.160281 42.258729, -71.160837 42.259113, -71.161144 42.25932)
!ok

SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269);
EXPR$0
LINESTRING (-71.160281 42.258729, -71.160837 42.259113, -71.161144 42.25932)
!ok

SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');
EXPR$0
MULTILINESTRING ((-71.160281 42.258729, -71.160837 42.259113, -71.161144 42.25932))
!ok

SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');
EXPR$0
POINT (-71.064544 42.28787)
!ok

SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
EXPR$0
POLYGON ((-71.1776585052917 42.3902909739571, -71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754, -71.1775826583081 42.3903033653531, -71.1776585052917 42.3902909739571))
!ok

SELECT ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
-71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
-71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
-71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
-71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
-71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
-71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
-71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
-71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
-71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
-71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
-71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
-71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
-71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
-71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
-71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
-71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
-71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
-71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
-71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
-71.1031880899493 42.3152774590236)),
((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
-71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 42.315113108546)))',4326);
EXPR$0
MULTIPOLYGON (((-71.1031880899493 42.3152774590236, -71.1031627617667 42.3152960829043, -71.102923838298 42.3149156848307, -71.1023097974109 42.3151969047397, -71.1019285062273 42.3147384934248, -71.102505233663 42.3144722937587, -71.10277487471 42.3141658254797, -71.103113945163 42.3142739188902, -71.10324876416 42.31402489987, -71.1033002961013 42.3140393340215, -71.1033488797549 42.3139495090772, -71.103396240451 42.3138632439557, -71.1041521907712 42.3141153348029, -71.1041411411543 42.3141545014533, -71.1041287795912 42.3142114839058, -71.1041188134329 42.3142693656241, -71.1041112482575 42.3143272556118, -71.1041072845732 42.3143851580048, -71.1041057218871 42.3144430686681, -71.1041065602059 42.3145009876017, -71.1041097995362 42.3145589148055, -71.1041166403905 42.3146168544148, -71.1041258822717 42.3146748022936, -71.1041375307579 42.3147318674446, -71.1041492906949 42.3147711126569, -71.1041598612795 42.314808571739, -71.1042515013869 42.3151287620809, -71.1041173835118 42.3150739481917, -71.1040809891419 42.3151344119048, -71.1040438678912 42.3151191367447, -71.1040194562988 42.3151832057859, -71.1038734225584 42.3151140942995, -71.1038446938243 42.3151006300338, -71.1038315271889 42.315094347535, -71.1037393329282 42.315054824985, -71.1035447555574 42.3152608696313, -71.1033436658644 42.3151648370544, -71.1032580383161 42.3152269126061, -71.103223066939 42.3152517403219, -71.1031880899493 42.3152774590236)), ((-71.1043632495873 42.315113108546, -71.1043583974082 42.3151211109857, -71.1043443253471 42.3150676015829, -71.1043850704575 42.3150793250568, -71.1043632495873 42.315113108546)))
!ok

# Disabled: Should not return null
!if (false) {
SELECT ST_GeomFromText('GEOMETRYCOLLECTION(
  POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,-7 4.2))
  POINT(5 5)
  POINT(-2 3)
  LINESTRING(5 5, 10 10)');
EXPR$0
!ok
!}

# PostGIS does CIRCULARSTRING; we don't currently
!if (false) {
SELECT ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)');
!ok
!}

# In PostGIS prior to 2.0, ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed
# but ST_GeomFromText('GEOMETRYCOLLECTION EMPTY') is not preferred.
SELECT ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
EXPR$0
GEOMETRYCOLLECTION EMPTY
!ok

# ST_GeomFromWKB(wkb) WKB to Geometry
SELECT ST_GeomFromWKB(ST_AsWKB('LINESTRING (1 2, 3 4)'));
EXPR$0
LINESTRING (1 2, 3 4)
!ok

# ST_GeomFromWKB(wkb, srid) WKB to Geometry
SELECT ST_AsEWKT(ST_GeomFromWKB(ST_AsWKB('LINESTRING (1 2, 3 4)'), 4326));
EXPR$0
srid:4326;LINESTRING (1 2, 3 4)
!ok

# ST_GoogleMapLink(geom [, layerType [, zoom ]]) Geometry to Google map link
# Not implemented

# ST_LineFromText(wkt [, srid ]) Well Known Text to LINESTRING
SELECT ST_LineFromText('LINESTRING(1 2, 3 4)') AS aline,
  ST_LineFromText('POINT(1 2)') AS null_return;
ALINE, NULL_RETURN
LINESTRING (1 2, 3 4), null
!ok

# ST_LineFromWKB(wkb [, srid ]) Well Known Binary to LINESTRING
# Not implemented

# ST_MLineFromText(wkt [, srid ]) Well Known Text to MULTILINESTRING
SELECT ST_MLineFromText('MULTILINESTRING((1 2, 3 4), (4 5, 6 7))');
EXPR$0
MULTILINESTRING ((1 2, 3 4), (4 5, 6 7))
!ok

# ST_MPointFromText(wkt [, srid ]) Well Known Text to MULTIPOINT
SELECT ST_MPointFromText('MULTIPOINT(1 2, 3 4)');
EXPR$0
MULTIPOINT ((1 2), (3 4))
!ok

SELECT ST_AsEWKT(ST_MPointFromText('MULTIPOINT(-70.9590 42.1180, -70.9611 42.1223)', 4326));
EXPR$0
srid:4326;MULTIPOINT ((-70.959 42.118), (-70.9611 42.1223))
!ok

# ST_MPolyFromText(wkt [, srid ]) Well Known Text to MULTIPOLYGON
SELECT ST_AsWKT(ST_MPolyFromText('MULTIPOLYGON Z(((0 0 1,20 0 1,20 20 1,0 20 1,0 0 1),(5 5 3,5 7 3,7 7 3,7 5 3,5 5 3)))'));
EXPR$0
MULTIPOLYGON Z(((0 0 1, 20 0 1, 20 20 1, 0 20 1, 0 0 1), (5 5 3, 5 7 3, 7 7 3, 7 5 3, 5 5 3)))
!ok

SELECT ST_MPolyFromText('MULTIPOLYGON(((-70.916 42.1002,-70.9468 42.0946,-70.9765 42.0872,-70.9754 42.0875,-70.9749 42.0879,-70.9752 42.0881,-70.9754 42.0891,-70.9758 42.0894,-70.9759 42.0897,-70.9759 42.0899,-70.9754 42.0902,-70.9756 42.0906,-70.9753 42.0907,-70.9753 42.0917,-70.9757 42.0924,-70.9755 42.0928,-70.9755 42.0942,-70.9751 42.0948,-70.9755 42.0953,-70.9751 42.0958,-70.9751 42.0962,-70.9759 42.0983,-70.9767 42.0987,-70.9768 42.0991,-70.9771 42.0997,-70.9771 42.1003,-70.9768 42.1005,-70.977 42.1011,-70.9766 42.1019,-70.9768 42.1026,-70.9769 42.1033,-70.9775 42.1042,-70.9773 42.1043,-70.9776 42.1043,-70.9778 42.1048,-70.9773 42.1058,-70.9774 42.1061,-70.9779 42.1065,-70.9782 42.1078,-70.9788 42.1085,-70.9798 42.1087,-70.9806 42.109,-70.9807 42.1093,-70.9806 42.1099,-70.9809 42.1109,-70.9808 42.1112,-70.9798 42.1116,-70.9792 42.1127,-70.979 42.1129,-70.9787 42.1134,-70.979 42.1139,-70.9791 42.1141,-70.9987 42.1116,-71.0022 42.1273,
    -70.9408 42.1513,-70.9315 42.1165,-70.916 42.1002)))',4326);
EXPR$0
MULTIPOLYGON (((-70.916 42.1002, -70.9468 42.0946, -70.9765 42.0872, -70.9754 42.0875, -70.9749 42.0879, -70.9752 42.0881, -70.9754 42.0891, -70.9758 42.0894, -70.9759 42.0897, -70.9759 42.0899, -70.9754 42.0902, -70.9756 42.0906, -70.9753 42.0907, -70.9753 42.0917, -70.9757 42.0924, -70.9755 42.0928, -70.9755 42.0942, -70.9751 42.0948, -70.9755 42.0953, -70.9751 42.0958, -70.9751 42.0962, -70.9759 42.0983, -70.9767 42.0987, -70.9768 42.0991, -70.9771 42.0997, -70.9771 42.1003, -70.9768 42.1005, -70.977 42.1011, -70.9766 42.1019, -70.9768 42.1026, -70.9769 42.1033, -70.9775 42.1042, -70.9773 42.1043, -70.9776 42.1043, -70.9778 42.1048, -70.9773 42.1058, -70.9774 42.1061, -70.9779 42.1065, -70.9782 42.1078, -70.9788 42.1085, -70.9798 42.1087, -70.9806 42.109, -70.9807 42.1093, -70.9806 42.1099, -70.9809 42.1109, -70.9808 42.1112, -70.9798 42.1116, -70.9792 42.1127, -70.979 42.1129, -70.9787 42.1134, -70.979 42.1139, -70.9791 42.1141, -70.9987 42.1116, -71.0022 42.1273, -70.9408 42.1513, -70.9315 42.1165, -70.916 42.1002)))
!ok

# ST_OSMMapLink(geom [, marker ]) Geometry to OSM map link
# Not implemented

# ST_PointFromText(wkt [, srid ]) Well Known Text to POINT
SELECT ST_PointFromText('POINT(-71.064544 42.28787)');
EXPR$0
POINT (-71.064544 42.28787)
!ok

SELECT ST_AsEWKT(ST_PointFromText('POINT(-71.064544 42.28787)', 4326));
EXPR$0
srid:4326;POINT (-71.064544 42.28787)
!ok

# ST_PointFromWKB(wkb [, srid ]) Well Known Binary to POINT
# Not implemented

# ST_PolyFromText(wkt [, srid ]) Well Known Text to POLYGON
SELECT ST_AsWKT(ST_PolyFromText('POLYGON Z((0 0 1,20 0 1,20 20 1,0 20 1,0 0 1))'));
EXPR$0
POLYGON Z((0 0 1, 20 0 1, 20 20 1, 0 20 1, 0 0 1))
!ok

SELECT ST_PolyFromText(CAST(NULL AS VARCHAR));
EXPR$0
null
!ok

SELECT ST_PolyFromText('POLYGON((0 0, 0 1, 1 1, 0 0))');
EXPR$0
POLYGON ((0 0, 0 1, 1 1, 0 0))
!ok

# ST_PolyFromWKB(wkb [, srid ]) Well Known Binary to POLYGON
# Not implemented

# ST_ToMultiLine(geom) Converts the coordinates of *geom* (which may be a geometry-collection) into a multi-line-string

SELECT ST_ToMultiLine('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1))');
EXPR$0
MULTILINESTRING ((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1))
!ok

SELECT ST_ToMultiLine('GEOMETRYCOLLECTION(LINESTRING(1 4 3, 10 7 9, 12 9 22), POLYGON((1 1 -1, 3 1 0, 3 2 1, 1 2 2, 1 1 -1)))');
EXPR$0
MULTILINESTRING ((1 4, 10 7, 12 9), (1 1, 3 1, 3 2, 1 2, 1 1))
!ok

# ST_ToMultiPoint(geom)) Converts the coordinates of *geom* (which may be a geometry-collection) into a multi-point

SELECT ST_ToMultiPoint('POINT(5 5)');
EXPR$0
MULTIPOINT ((5 5))
!ok

SELECT ST_ToMultiPoint('MULTIPOINT(5 5, 1 2, 3 4, 99 3)');
EXPR$0
MULTIPOINT ((5 5), (1 2), (3 4), (99 3))
!ok

SELECT ST_ToMultiPoint('LINESTRING(5 5, 1 2, 3 4, 1 5)');
EXPR$0
MULTIPOINT ((5 5), (1 2), (3 4), (1 5))
!ok

# ST_ToMultiSegments(geom) Converts *geom* (which may be a geometry-collection) into a set of distinct segments stored in a multi-line-string

SELECT ST_ToMultiSegments('LINESTRING(5 4, 1 1, 3 4, 4 5)');
EXPR$0
MULTILINESTRING ((5 4, 1 1), (1 1, 3 4), (3 4, 4 5))
!ok

SELECT ST_ToMultiSegments('MULTILINESTRING((1 4 3, 15 7 9, 16 17 22), (0 0 0, 1 0 0, 1 2 0, 0 2 1))');
EXPR$0
MULTILINESTRING ((1 4, 15 7), (15 7, 16 17), (0 0, 1 0), (1 0, 1 2), (1 2, 0 2))
!ok

SELECT ST_ToMultiSegments('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1))');
EXPR$0
MULTILINESTRING ((0 0, 10 0), (10 0, 10 6), (10 6, 0 6), (0 6, 0 0), (1 1, 2 1), (2 1, 2 5), (2 5, 1 5), (1 5, 1 1))
!ok

#### Geometry conversion functions (3D)

# ST_Force3D(geom) 2D Geometry to 3D Geometry

SELECT ST_AsText(ST_Force3D('POINT(-10 10 6)'));
EXPR$0
POINT Z(-10 10 6)
!ok

SELECT ST_AsText(ST_Force3D('POINT(-10 10)'));
EXPR$0
POINT Z(-10 10 0)
!ok

SELECT ST_AsText(ST_Force3D('LINESTRING(-10 10, 10 10)'));
EXPR$0
LINESTRING Z(-10 10 0, 10 10 0)
!ok

#### Geometry creation functions (2D)

# ST_BoundingCircle(geom) Returns the minimum bounding circle of *geom*

SELECT roundGeom(ST_asText(ST_BoundingCircle('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))')), 2);
EXPR$0
POLYGON ((4.63 2.5, 4.59 2.09, 4.46 1.69, 4.27 1.33, 4 1.01, 3.68 0.74, 3.32 0.55, 2.92 0.42, 2.5 0.38, 2.09 0.42, 1.69 0.55, 1.33 0.74, 1.01 1, 0.74 1.33, 0.55 1.69, 0.42 2.09, 0.38 2.50, 0.42 2.92, 0.55 3.32, 0.74 3.68, 1.00 4, 1.33 4.27, 1.69 4.46, 2.09 4.59, 2.50 4.63, 2.92 4.59, 3.32 4.46, 3.68 4.27, 4.00 4, 4.27 3.68, 4.46 3.32, 4.59 2.92, 4.63 2.5))
!ok

SELECT roundGeom(ST_asText(ST_BoundingCircle('MULTIPOINT((1 1), (4 2))')), 2);
EXPR$0
POLYGON ((4.09 1.5, 4.06 1.20, 3.97 0.90, 3.82 0.63, 3.62 0.39, 3.38 0.19, 3.11 0.04, 2.81 -0.06, 2.5 -0.09, 2.20 -0.06, 1.90 0.04, 1.63 0.19, 1.39 0.39, 1.19 0.63, 1.04 0.90, 0.95 1.20, 0.92 1.50, 0.95 1.81, 1.04 2.11, 1.19 2.38, 1.39 2.62, 1.63 2.82, 1.90 2.97, 2.20 3.06, 2.50 3.09, 2.81 3.06, 3.11 2.97, 3.38 2.82, 3.62 2.62, 3.82 2.38, 3.97 2.11, 4.06 1.81, 4.09 1.5))
!ok

SELECT roundGeom(ST_asText(ST_BoundingCircle('LINESTRING(1 1, 4 5, 3 2)')), 2);
EXPR$0
POLYGON ((5 3, 4.96 2.52, 4.81 2.05, 4.58 1.62, 4.27 1.24, 3.89 0.93, 3.46 0.70, 2.99 0.55, 2.5 0.5, 2.02 0.55, 1.55 0.70, 1.12 0.93, 0.74 1.24, 0.43 1.62, 0.20 2.05, 0.05 2.52, 0 3.00, 0.05 3.49, 0.20 3.96, 0.43 4.39, 0.74 4.77, 1.12 5.08, 1.55 5.31, 2.02 5.46, 2.50 5.5, 2.99 5.46, 3.46 5.31, 3.89 5.08, 4.27 4.77, 4.58 4.39, 4.81 3.96, 4.96 3.49, 5 3))
!ok

# ST_Expand(geom, distance) Expands *geom*'s envelope

SELECT ST_Expand('POINT(1 1)', 1);
EXPR$0
POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))
!ok

# ST_Expand(geom, deltaX, deltaY) Expands *geom*'s envelope

SELECT ST_Expand('POINT(4 4)', 5, 2);
EXPR$0
POLYGON ((-1 2, -1 6, 9 6, 9 2, -1 2))
!ok

SELECT ST_Expand('LINESTRING(3 2, 7 5, 2 7)', 2, 1);
EXPR$0
POLYGON ((0 1, 0 8, 9 8, 9 1, 0 1))
!ok

# ST_MakeEllipse(point, width, height) Constructs an ellipse

SELECT roundGeom(ST_AsText(ST_MakeEllipse('POINT(5 5)', 2, 5)), 2);
EXPR$0
POLYGON ((6 5, 6.00 5.16, 6.00 5.32, 5.99 5.47, 5.97 5.63, 5.96 5.78, 5.93 5.93, 5.91 6.07, 5.88 6.21, 5.85 6.34, 5.81 6.47, 5.78 6.60, 5.73 6.72, 5.69 6.83, 5.64 6.93, 5.59 7.03, 5.54 7.12, 5.49 7.20, 5.43 7.27, 5.37 7.33, 5.31 7.38, 5.25 7.43, 5.19 7.46, 5.13 7.49, 5.07 7.50, 5 7.5, 4.94 7.50, 4.88 7.49, 4.82 7.46, 4.76 7.43, 4.70 7.38, 4.64 7.33, 4.58 7.27, 4.52 7.20, 4.47 7.12, 4.42 7.03, 4.37 6.93, 4.32 6.83, 4.28 6.72, 4.23 6.60, 4.20 6.47, 4.16 6.34, 4.13 6.21, 4.10 6.07, 4.08 5.93, 4.05 5.78, 4.04 5.63, 4.02 5.47, 4.01 5.32, 4.01 5.16, 4 5.00, 4.01 4.85, 4.01 4.69, 4.02 4.54, 4.04 4.38, 4.05 4.23, 4.08 4.08, 4.10 3.94, 4.13 3.80, 4.16 3.67, 4.20 3.54, 4.23 3.41, 4.28 3.29, 4.32 3.18, 4.37 3.08, 4.42 2.98, 4.47 2.89, 4.52 2.81, 4.58 2.74, 4.64 2.68, 4.70 2.63, 4.76 2.58, 4.82 2.55, 4.88 2.52, 4.94 2.51, 5 2.5, 5.07 2.51, 5.13 2.52, 5.19 2.55, 5.25 2.58, 5.31 2.63, 5.37 2.68, 5.43 2.74, 5.49 2.81, 5.54 2.89, 5.59 2.98, 5.64 3.08, 5.69 3.18, 5.73 3.29, 5.78 3.41, 5.81 3.54, 5.85 3.67, 5.88 3.80, 5.91 3.94, 5.93 4.08, 5.96 4.23, 5.97 4.38, 5.99 4.54, 6.00 4.69, 6.00 4.85, 6 5))
!ok

# ST_MakeEnvelope(xMin, yMin, xMax, yMax  [, srid ]) Creates a rectangular Polygon

SELECT ST_AsText(ST_MakeEnvelope(10.0, 10.0, 11.0, 11.0, 4326));
EXPR$0
POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
!ok

SELECT ST_AsText(ST_MakeEnvelope(12.0, -1.0, 6.0, 4.0, 4326));

EXPR$0
POLYGON ((12 -1, 12 4, 6 4, 6 -1, 12 -1))
!ok

# ST_MakeGrid(geom, deltaX, deltaY) Calculates a regular grid of polygons based on *geom*
SELECT * FROM TABLE(ST_MakeGrid(ST_Point(13.0,22.0), 10.0, 5.0));
THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW
POLYGON ((10 20, 10 25, 20 25, 20 20, 10 20)), 0, 1, 1, 1, 4
!ok

# ST_MakeGridPoints(geom, deltaX, deltaY) Calculates a regular grid of points based on *geom*
SELECT * FROM TABLE(ST_MakeGridPoints(ST_Point(13.0,22.0), 10.0, 5.0));
THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW
POINT (15 22.5), 0, 1, 1, 1, 4
!ok

# Call ST_MakeGridPoints for each geometry in a set
select "name", "latitude", "longitude", p.*
from GEO."countries" AS c,
  lateral table(
    ST_MakeGridPoints(ST_MakePoint("longitude", "latitude"), 10.0, 10.0)) as p
ORDER BY "latitude" DESC LIMIT 3;
name, latitude, longitude, THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW
Svalbard and Jan Mayen, 77.553604, 23.670272, POINT (25 75), 0, 1, 1, 2, 7
Greenland, 71.706936, -42.604303, POINT (-45 75), 0, 1, 1, -5, 7
Iceland, 64.963051, -19.020835, POINT (-15 65), 0, 1, 1, -2, 6
!ok

# ST_MakeLine(point1 [, point ]*) Creates a line-string from the given points (or multi-points)

SELECT ST_MakeLine(ST_Point(1.0,1.0), ST_Point(-1.0,-1.0));
EXPR$0
LINESTRING (1 1, -1 -1)
!ok

SELECT ST_MakeLine(ST_Point(1.0,1.0), ST_Point(-1.0,-1.0), ST_Point(-3.0,0.0));
EXPR$0
LINESTRING (1 1, -1 -1, -3 0)
!ok

# ST_MakePoint(x, y [, z ]) Constructs a point from two or three coordinates

# Return point with unknown SRID
SELECT ST_MakePoint(-71.1043443253471, 42.3150676015829);
EXPR$0
POINT (-71.1043443253471 42.3150676015829)
!ok

# Return point marked as WGS 84 long lat
SELECT ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326);
EXPR$0
POINT (-71.1043443253471 42.3150676015829)
!ok

# Return a 3D point (e.g. has altitude)
SELECT ST_AsWKT(ST_MakePoint(1.0, 2.0, 1.5));
EXPR$0
POINT Z(1 2 1.5)
!ok

# Get x of point
SELECT ST_X(ST_MakePoint(1.0, 2.0,1.5));
EXPR$0
1.0
!ok

# Get y of point
SELECT ST_Y(ST_MakePoint(1.0, 2.0,1.5));
EXPR$0
2.0
!ok

# Get z of point
SELECT ST_Z(ST_MakePoint(1.0, 2.0,1.5));
EXPR$0
1.5
!ok

select "name", ST_MakePoint("longitude", "latitude") AS p
from GEO."countries" AS c
ORDER BY "latitude" DESC LIMIT 3;
name, P
U.S.Minor Outlying Islands, null
Svalbard and Jan Mayen, POINT (23.670272 77.553604)
Greenland, POINT (-42.604303 71.706936)
!ok

# ST_MakePolygon(lineString [, hole ]*) Creates a polygon from *lineString* with the given holes (which are required to be closed line-strings)

SELECT ST_MakePolygon('LINESTRING(100 250, 100 350, 200 350, 200 250, 100 250)');
EXPR$0
POLYGON ((100 250, 100 350, 200 350, 200 250, 100 250))
!ok

SELECT ST_MakePolygon('LINESTRING(0 5, 4 5, 4 0, 0 0, 0 5)', 'LINESTRING(1 1, 1 2, 2 2, 2 1, 1 1)');
EXPR$0
POLYGON ((0 5, 4 5, 4 0, 0 0, 0 5), (1 1, 1 2, 2 2, 2 1, 1 1))
!ok

SELECT ST_MakePolygon('POINT(1 1)');
Only supports LINESTRINGs.
!error

# ST_MinimumDiameter(geom) Returns the minimum diameter of *geom*

SELECT ST_MinimumDiameter('POINT(395 278)');
EXPR$0
LINESTRING (395 278, 395 278)
!ok

SELECT ST_MinimumDiameter('LINESTRING(0 0, 1 1, 3 9, 7 1)');
EXPR$0
LINESTRING (1 3, 7 1)
!ok

# ST_MinimumRectangle(geom) Returns the minimum rectangle enclosing *geom*

SELECT ST_MinimumRectangle('MULTIPOINT((8 3), (4 6))');
EXPR$0
LINESTRING (4 6, 8 3)
!ok

SELECT ST_MinimumRectangle('POLYGON((1 2, 3 0, 5 2, 3 2, 2 3, 1 2))');
EXPR$0
POLYGON ((1.4 3.1999999999999993, 0.6 0.8, 4.2 -0.3999999999999996, 4.999999999999999 1.9999999999999993, 1.4 3.1999999999999993))
!ok

# ST_OctagonalEnvelope(geom) Returns the octogonal envelope of *geom*

SELECT ST_OctagonalEnvelope('POLYGON((2 1, 1 2, 2 2, 2 4, 3 5, 3 3, 5 5, 7 2, 5 2, 6 1, 2 1))');
EXPR$0
POLYGON ((1 2, 1 3, 3 5, 5 5, 7 3, 7 2, 6 1, 2 1, 1 2))
!ok

# ST_RingBuffer(geom, bufferSize, bufferCount [, endCapStyle [, doDifference]]) Returns a multi-polygon of buffers centered at *geom* and of increasing buffer size
# Not implemented

### Geometry creation functions (3D)

# ST_Extrude(geom, height [, flag]) Extrudes a geometry
# Not implemented

# ST_GeometryShadow(geom, point, height) Computes the shadow footprint of *geom*
# Not implemented

# ST_GeometryShadow(geom, azimuth, altitude, height [, unify ]) Computes the shadow footprint of *geom*
# Not implemented

#### Geometry properties (2D)

# ST_Boundary(geom [, srid ]) Returns the boundary of *geom*
SELECT ST_AsText(ST_Boundary('LINESTRING(1 1,0 0, -1 1)'));
EXPR$0
MULTIPOINT ((1 1), (-1 1))
!ok

SELECT ST_AsText(ST_Boundary('POLYGON((1 1,0 0, -1 1, 1 1))'));
EXPR$0
LINEARRING (1 1, 0 0, -1 1, 1 1)
!ok

# Using a 3d polygon
SELECT ST_AsText(ST_Boundary('POLYGON Z((1 1 1,0 0 1, -1 1 1, 1 1 1))'));
EXPR$0
LINEARRING Z(1 1 1, 0 0 1, -1 1 1, 1 1 1)
!ok

# Using a 3d multilinestring
SELECT ST_AsText(ST_Boundary('MULTILINESTRING Z((1 1 1,0 0 0.5, -1 1 1),(1 1 0.5,0 0 0.5, -1 1 0.5, 1 1 0.5) )'));
EXPR$0
MULTIPOINT Z((-1 1 1), (1 1 1))
!ok

# ST_Centroid(geom) Returns the centroid of *geom* (which may be a geometry-collection)

SELECT ST_Centroid('MULTIPOINT((4 4), (1 1), (1 0), (0 3))');
EXPR$0
POINT (1.5 2)
!ok

SELECT ST_Centroid('LINESTRING(2 1, 1 3, 5 2)');
EXPR$0
POINT (2.472556942838389 2.3241856476127962)
!ok

SELECT ST_Centroid('MULTILINESTRING((1 5, 6 5), (5 1, 5 4))');
EXPR$0
POINT (4.0625 4.0625)
!ok

SELECT ST_Centroid('POLYGON((1 5, 1 2, 6 2, 3 3, 3 4, 5 6, 1 5))');
EXPR$0
POINT (2.5964912280701755 3.666666666666667)
!ok

SELECT ST_Centroid('MULTIPOLYGON(((0 2, 3 2, 3 6, 0 6, 0 2)), ((5 0, 7 0, 7 1, 5 1, 5 0)))');
EXPR$0
POINT (2.142857142857143 3.5)
!ok

SELECT ST_Centroid('GEOMETRYCOLLECTION(
                      POLYGON((1 2, 4 2, 4 6, 1 6, 1 2)),
                      LINESTRING(2 6, 6 2),
                      MULTIPOINT((4 4), (1 1), (1 0), (0 3)))');
EXPR$0
POINT (2.5 4)
!ok


# ST_CompactnessRatio(polygon) Returns the square root of *polygon*'s area divided by the area of the circle with circumference equal to its perimeter
# Not implemented

# ST_CoordDim(geom) Returns the dimension of the coordinates of *geom*

SELECT ST_CoordDim('Point(1 2 3)');
EXPR$0
3
!ok

SELECT ST_CoordDim('Point(1 2)');
EXPR$0
2
!ok

SELECT ST_CoordDim('Point Empty');
EXPR$0
2
!ok

# ST_Dimension(geom) Returns the dimension of *geom*

SELECT ST_Dimension('MULTIPOINT((4 4), (1 1), (1 0), (0 3))');
EXPR$0
0
!ok

SELECT ST_Dimension('LINESTRING(2 1, 1 3, 5 2)');
EXPR$0
1
!ok

SELECT ST_Dimension('MULTIPOLYGON(((0 2, 3 2, 3 6, 0 6, 0 2)), ((5 0, 7 0, 7 1, 5 1, 5 0)))');
EXPR$0
2
!ok

# ST_Distance(geom1, geom2) Returns the distance between *geom1* and *geom2*

SELECT ST_Distance(
    ST_GeomFromText('POINT(10 10)'),
    ST_GeomFromText('POINT(40 50)'));
EXPR$0
50.0
!ok

SELECT ST_Distance(
    ST_GeomFromText('POINT(10 10)', 4326),
    ST_GeomFromText('POINT(40 50)', 4326));
EXPR$0
50.0
!ok

# Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
SELECT round(ST_Distance(
    ST_GeomFromText('POINT(-72.1235 42.3521)', 4326),
    ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326)), 10);
EXPR$0
0.0015056773
!ok

# Geometry example - units in meters (SRID: 26986 Massachusetts state plane meters) (most accurate for Massachusetts)
SELECT round(ST_Distance(
    ST_Transform(ST_GeomFromText('POINT(-72.1235 42.3521)',4326),26986),
    ST_Transform(ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326),26986)), 10);
EXPR$0
123.7979378784
!ok

# Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (least accurate)
SELECT round(ST_Distance(
    ST_Transform(ST_GeomFromText('POINT(-72.1235 42.3521)', 4326), 2163),
    ST_Transform(ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326), 2163)), 10);

EXPR$0
126.6642560574
!ok

# Disabled: PostgreSQL does geography, Calcite does not
!if (false) {
# same as geometry example but note units in meters - use sphere for slightly faster less accurate
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT
    ST_GeogFromText('SRID=4326;POINT(-72.1235 42.3521)') As gg1,
    ST_GeogFromText('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)') As gg2) As foo;

  spheroid_dist   |   sphere_dist
------------------+------------------
 123.802076746848 | 123.475736916397
!ok
!}

# ST_EndPoint(geom) Returns the last coordinate of *geom*

SELECT ST_EndPoint('MULTILINESTRING((1 1, 1 6, 2 2, -1 2))');
EXPR$0
POINT (-1 2)
!ok

# ST_Envelope(geom [, srid ]) Returns the envelope of *geom* (which may be a geometry-collection) as a geometry

SELECT ST_AsText(ST_Envelope('POINT(1 3)'));
EXPR$0
POINT (1 3)
!ok

SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'));
EXPR$0
POLYGON ((0 0, 0 3, 1 3, 1 0, 0 0))
!ok

SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))'));
EXPR$0
POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))
!ok

SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'));
EXPR$0
POLYGON ((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))
!ok

# ST_Explode(query [, fieldName]) Explodes the geometry-collections in the *fieldName* column of a query into multiple geometries
# Not implemented

# ST_Extent(geom) Returns the minimum bounding box of *geom* (which may be a geometry-collection)

SELECT ST_Extent('MULTIPOINT((5 6), (1 2), (3 4), (10 3))');
EXPR$0
POLYGON ((1 2, 1 6, 10 6, 10 2, 1 2))
!ok

SELECT ST_Extent('POINT(5 6)');
EXPR$0
POINT (5 6)
!ok

SELECT ST_Extent('GEOMETRYCOLLECTION(
  POLYGON((0 0, 3 -1, 1.5 2, 0 0)),
  POLYGON((2 0, 3 3, 4 2, 2 0)),
  POINT(5 6),
  LINESTRING(1 1, 1 6))');
EXPR$0
POLYGON ((0 -1, 0 6, 5 6, 5 -1, 0 -1))
!ok

# ST_ExteriorRing(polygon) Returns the exterior ring of *polygon* as a linear-ring

SELECT ST_ExteriorRing('POLYGON((0 -1, 0 2, 3 2, 3 -1, 0 -1))');
EXPR$0
LINEARRING (0 -1, 0 2, 3 2, 3 -1, 0 -1)
!ok

SELECT ST_ExteriorRing('POINT(1 2)');
EXPR$0
null
!ok

# ST_GeometryN(geomCollection, n) Returns the *n*th geometry of *geomCollection*

SELECT ST_ExteriorRing('POINT(1 2)');
EXPR$0
null
!ok

# ST_GeometryType(geom) Returns the type of *geom*

SELECT ST_GeometryN('MULTIPOLYGON(((0 0, 3 -1, 1.5 2, 0 0)), ((1 2, 4 2, 4 6, 1 6, 1 2)))', 0);
EXPR$0
POLYGON ((0 0, 3 -1, 1.5 2, 0 0))
!ok

# ST_GeometryTypeCode(geom) Returns the type code of *geom*

SELECT id, ST_GeometryType(g), ST_GeometryTypeCode(g) FROM (VALUES
 ('ls', ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)')),
 ('p', ST_Point(0.0, 0.0)),
 ('np', ST_Point(0.0, CAST(NULL AS DECIMAL))),
 ('mp', ST_GeomFromText('MULTIPOLYGON(((1 1, 2 2, 5 3, 1 1)), ((0 0, 2 2, 5 3, 0 0)))'))
) AS t(id, g);
ID, EXPR$1, EXPR$2
ls, LINESTRING, 2
mp, MULTIPOLYGON, 6
np, null, null
p , POINT, 1
!ok

# ST_InteriorRingN(polygon, n) Returns the *n*th interior ring of *polygon*

SELECT ST_InteriorRing('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1), (8 5, 8 4, 9 4, 9 5, 8 5))', 0);
EXPR$0
LINEARRING (1 1, 2 1, 2 5, 1 5, 1 1)
!ok

SELECT ST_InteriorRing('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1), (8 5, 8 4, 9 4, 9 5, 8 5))', 1);
EXPR$0
LINEARRING (8 5, 8 4, 9 4, 9 5, 8 5)
!ok

# ST_IsClosed(geom) Returns whether *geom* is a closed line-string or multi-line-string

SELECT ST_IsClosed('LINESTRING(2 1, 1 3, 5 2)');
EXPR$0
false
!ok

SELECT ST_IsClosed('LINESTRING(2 1, 1 3, 5 2, 2 1)');
EXPR$0
true
!ok

# ST_IsEmpty(geom) Returns whether *geom* is empty

SELECT ST_IsEmpty('MULTIPOINT((4 4), (1 1), (1 0), (0 3))');
EXPR$0
false
!ok

SELECT ST_IsEmpty('GEOMETRYCOLLECTION(
  MULTIPOINT((4 4), (1 1), (1 0), (0 3)),
  LINESTRING(2 6, 6 2),
  POLYGON((1 2, 4 2, 4 6, 1 6, 1 2)))');
EXPR$0
false
!ok

SELECT ST_IsEmpty('POLYGON EMPTY');
EXPR$0
true
!ok

# ST_IsRectangle(geom) Returns whether *geom* is a rectangle

SELECT ST_IsRectangle('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))');
EXPR$0
true
!ok

SELECT ST_IsRectangle('POLYGON((0 0, 10 0, 10 7, 0 5, 0 0))');
EXPR$0
false
!ok

# ST_IsRing(geom) Returns whether *geom* is a closed and simple line-string or multi-line-string

SELECT ST_IsRing('LINESTRING(2 1, 1 3, 6 6, 2 1)');
EXPR$0
true
!ok

SELECT ST_IsRing('LINESTRING(2 1, 1 3, 6 6)');
EXPR$0
false
!ok

SELECT ST_IsRing('LINESTRING(2 1, 1 3, 6 6, 5 7, 5 2, 2 1)');
EXPR$0
false
!ok

SELECT ST_IsRing('LINESTRING(2 1, 1 3, 6 6, 5 7, 5 2)');
EXPR$0
false
!ok

# ST_IsSimple(geom) Returns whether *geom* is simple

SELECT ST_IsSimple('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1), (8 5, 8 4, 9 4, 9 5, 8 5))');
EXPR$0
true
!ok

SELECT ST_IsSimple('MULTILINESTRING((0 2, 3 2, 3 6, 0 6, 0 2), (5 0, 7 0, 7 1, 5 1, 5 0))');
EXPR$0
true
!ok

SELECT ST_IsSimple(
 'GEOMETRYCOLLECTION(
   MULTIPOINT((4 4), (1 1), (1 0), (0 3)),
   LINESTRING(2 6, 6 2),
   POLYGON((1 2, 4 2, 4 6, 1 6, 1 2)))');
EXPR$0
true
!ok

SELECT ST_IsSimple('LINESTRING(2 1, 1 3, 6 6, 5 7, 5 6)');
EXPR$0
true
!ok

SELECT ST_IsSimple('LINESTRING(2 1, 1 3, 6 6, 5 7, 5 2)');
EXPR$0
false
!ok

# ST_IsValid(geom) Returns whether *geom* is valid

SELECT ST_IsValid('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))');
EXPR$0
true
!ok

SELECT ST_IsValid('POLYGON((0 0, 10 0, 10 5, 6 -2, 0 0))');
EXPR$0
false
!ok

# ST_IsValidDetail(geom [, selfTouchValid ]) Returns a valid detail as an array of objects
# Not implemented

# ST_IsValidReason(geom [, selfTouchValid ]) Returns text stating whether *geom* is valid, and if not valid, a reason why
# Not implemented

# ST_NPoints(geom) Returns the number of points in *geom*
# Not implemented

# ST_NumGeometries(geom) Returns the number of geometries in *geom* (1 if it is not a geometry-collection)

SELECT ST_NumGeometries('LINESTRING(2 1, 1 3, 5 2)');
EXPR$0
1
!ok

SELECT ST_NumGeometries('MULTILINESTRING(
  (0 2, 3 2, 3 6, 0 6, 0 1),
  (5 0, 7 0, 7 1, 5 1, 5 0))');
EXPR$0
2
!ok

SELECT ST_NumGeometries('POLYGON(
  (0 0, 10 0, 10 6, 0 6, 0 0),
  (1 1, 2 1, 2 5, 1 5, 1 1),
  (8 5, 8 4, 9 4, 9 5, 8 5))');
EXPR$0
1
!ok

SELECT ST_NumGeometries('MULTIPOLYGON(
  ((0 0, 10 0, 10 6, 0 6, 0 0)),
  ((1 1, 2 1, 2 5, 1 5, 1 1)),
  ((8 5, 8 4, 9 4, 9 5, 8 5)))');
EXPR$0
3
!ok

SELECT ST_NumGeometries('GEOMETRYCOLLECTION(
  MULTIPOINT((4 4), (1 1), (1 0), (0 3)),
  LINESTRING(2 6, 6 2),
  POLYGON((1 2, 4 2, 4 6, 1 6, 1 2)))');
EXPR$0
3
!ok

SELECT ST_NumGeometries('MULTIPOINT(
  (0 2), (3 2), (3 6), (0 6),
  (0 1), (5 0), (7 0))');
EXPR$0
7
!ok

# ST_NumInteriorRings(geom) Returns the number of interior rings of *geom*

SELECT ST_NumInteriorRings('POLYGON(
  (0 0, 10 0, 10 6, 0 6, 0 0),
  (1 1, 2 1, 2 5, 1 5, 1 1),
  (8 5, 8 4, 9 4, 9 5, 8 5))');
EXPR$0
2
!ok

SELECT ST_NumInteriorRings('MULTIPOLYGON(
  ((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1)),
  ((1 1, 2 1, 2 5, 1 5, 1 1)),
  ((8 5, 8 4, 9 4, 9 5, 8 5)))');
EXPR$0
1
!ok

SELECT ST_NumInteriorRings('GEOMETRYCOLLECTION(
  MULTIPOINT((4 4), (1 1), (1 0), (0 3)),
  LINESTRING(2 6, 6 2),
  POLYGON((1 2, 4 2, 4 6, 1 6, 1 2)))');
EXPR$0
0
!ok

SELECT ST_NumInteriorRings(
  'GEOMETRYCOLLECTION(
    MULTIPOINT((4 4), (1 1), (1 0), (0 3)),
    LINESTRING(2 6, 6 2),
    POLYGON((1 2, 4 2, 4 6, 1 6, 1 2), (2 4, 3 4, 3 5, 2 5, 2 4)))');
EXPR$0
1
!ok


# ST_NumPoints(geom) Returns the number of points in *geom*

SELECT ST_NumPoints('POINT(2 2)');
EXPR$0
1
!ok

SELECT ST_NumPoints('MULTIPOINT(2 2, 4 4)');
EXPR$0
2
!ok

SELECT ST_NumPoints('MULTIPOINT(2 2, 4 4, 4 4)');
EXPR$0
3
!ok

SELECT ST_NumPoints('MULTILINESTRING((2 2, 4 4), (3 1, 6 3))');
EXPR$0
4
!ok

SELECT ST_NumPoints('POLYGON((0 0, 10 0, 10 6, 0 6, 0 0), (1 1, 2 1, 2 5, 1 5, 1 1), (8 5, 8 4, 9 4, 9 5, 8 5))');
EXPR$0
15
!ok

# ST_PointN(geom, n) Returns the *n*th point of a *geom*

SELECT ST_PointN('LINESTRING(1 1, 1 6, 2 2, -1 2))', 2);
EXPR$0
POINT (2 2)
!ok

SELECT ST_PointN('MULTILINESTRING((1 1, 1 6, 2 2, -1 2))', 3);
EXPR$0
POINT (-1 2)
!ok

SELECT ST_PointN('MULTIPOINT(1 1, 1 6, 2 2, -1 2)', -1);
EXPR$0
POINT (-1 2)
!ok

SELECT ST_PointN('MULTILINESTRING((1 1, 1 6, 2 2, -1 2), (0 1, 2 4))', 4);
EXPR$0
POINT (0 1)
!ok

# ST_PointOnSurface(geom) Returns an interior or boundary point of *geom*

SELECT ST_PointOnSurface('POINT(1 5)');
EXPR$0
POINT (1 5)
!ok

SELECT ST_PointOnSurface('MULTIPOINT((4 4), (1 1), (1 0), (0 3)))');
EXPR$0
POINT (1 1)
!ok

SELECT ST_PointOnSurface('LINESTRING(-1 5, 0 10)');
EXPR$0
POINT (0 10)
!ok

SELECT ST_PointOnSurface('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))');
EXPR$0
POINT (2.5 2.5)
!ok

# ST_SRID(geom) Returns SRID value of *geom* or 0 if it does not have one

SELECT ST_SRID(ST_GeomFromText('POINT(15 25)', 2154));
EXPR$0
2154
!ok

SELECT ST_SRID(ST_GeomFromText('LINESTRING(2 1, 1 3, 5 2, 2 1)', 4326));
EXPR$0
4326
!ok

# ST_StartPoint(geom) Returns the first coordinate of *geom*

SELECT ST_StartPoint('MULTILINESTRING((1 1, 1 6, 2 2, -1 2))');
EXPR$0
POINT (1 1)
!ok

# ST_X(geom) Returns the x-value of the first coordinate of *geom*

SELECT ST_X('POINT Z(1 2 3)');
EXPR$0
1.0
!ok

SELECT ST_X('POINT (1 2)');
EXPR$0
1.0
!ok

# ST_XMax(geom) Returns the maximum x-value of *geom*
SELECT ST_XMax('LINESTRING(1 3 4, 5 6 7)');
EXPR$0
5.0
!ok

# ST_XMin(geom) Returns the minimum x-value of *geom*
SELECT ST_XMin('LINESTRING(1 3 4, 5 6 7)');
EXPR$0
1.0
!ok

# ST_Y(geom) Returns the y-value of the first coordinate of *geom*

SELECT ST_Y('POINT Z(1 2 3)');
EXPR$0
2.0
!ok

SELECT ST_Y('POINT (1 2)');
EXPR$0
2.0
!ok

# ST_YMax(geom) Returns the maximum y-value of *geom*
SELECT ST_YMax('LINESTRING(1 3 4, 5 6 7)');
EXPR$0
6.0
!ok

# ST_YMin(geom) Returns the minimum y-value of *geom*
SELECT ST_YMin('LINESTRING(1 3 4, 5 6 7)');
EXPR$0
3.0
!ok

#### Geometry properties (3D)

# ST_Is3D(s) Returns whether *geom* has at least one z-coordinate

SELECT ST_Is3D('POINT Z(1 2 0)');
EXPR$0
true
!ok

SELECT ST_Is3D('POINT (1 2)');
EXPR$0
false
!ok

# ST_Z(geom) Returns the z-value of the first coordinate of *geom*

SELECT ST_Z('POINT Z(1 2 3)');
EXPR$0
3.0
!ok

SELECT ST_Z('POINT (1 2)');
EXPR$0
NaN
!ok

# ST_ZMax(geom) Returns the maximum z-value of *geom*

SELECT ST_ZMax('LINESTRING(1 2 3, 4 5 6)');
EXPR$0
6.0
!ok

SELECT ST_ZMax('LINESTRING(1 2, 4 5)');
EXPR$0
NaN
!ok

# ST_ZMin(geom) Returns the minimum z-value of *geom*

SELECT ST_ZMin('LINESTRING(1 2 3, 4 5 6)');
EXPR$0
3.0
!ok

SELECT ST_ZMin('LINESTRING(1 2, 4 5)');
EXPR$0
NaN
!ok

### Geometry predicates

# ST_Contains(geom1, geom2) Returns whether *geom1* contains *geom2*

SELECT ST_Contains(ST_Point(0.0, 0.0), ST_Point(1.0, 2.0));
EXPR$0
false
!ok

SELECT ST_Contains(ST_Point(0.0, 0.0), ST_Point(0.0, 0.0));
EXPR$0
true
!ok

# ST_ContainsProperly(geom1, geom2) Returns whether *geom1* contains *geom2*

-- Example demonstrating difference between contains and contains properly
SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa, ST_ContainsProperly(geomA, geomA) AS acontainspropa,
   ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
FROM (VALUES ( ST_Buffer(ST_Point(1.0,1.0), 5/*,1*/) ),
             ( ST_MakeLine(ST_Point(1.0,1.0), ST_Point(-1.0,-1.0) ) ),
             ( ST_Point(1.0,1.0))) As foo(geomA);

GEOMTYPE, ACONTAINSA, ACONTAINSPROPA, ACONTAINSBA, ACONTAINSPROPBA
LINESTRING, true, true, false, false
POINT, true, true, false, false
POLYGON, true, true, false, false
!ok

# ST_Covers(geom1, geom2) Returns whether no point in *geom2* is outside *geom1*

SELECT ST_Covers(ST_Buffer('POINT(0 0)', 1), 'POINT(0 0)');
EXPR$0
true
!ok

SELECT ST_Covers(ST_Buffer('POINT(0 0)', 1), 'POINT(1 1)');
EXPR$0
false
!ok

# ST_Crosses(geom1, geom2) Returns whether *geom1* crosses *geom2*

SELECT ST_Crosses('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
true
!ok

# ST_DWithin(geom1, geom2, distance) Returns whether *geom1* and *geom* are within *distance* of one another

# Countries within 10 degrees of London
select "name" from GEO."countries" AS c
where ST_Distance(ST_MakePoint(-0.12, 51.5), ST_MakePoint("longitude", "latitude")) < 10;
name
Andorra
Belgium
France
Guernsey
Ireland
Isle of Man
Jersey
Luxembourg
Netherlands
Switzerland
United Kingdom
!ok

# Countries within 10 degrees of London, formulated a different way
select "name" from GEO."countries" AS c
where ST_DWithin(ST_MakePoint(-0.12, 51.5), ST_MakePoint("longitude", "latitude"), 10);
name
Andorra
Belgium
France
Guernsey
Ireland
Isle of Man
Jersey
Luxembourg
Netherlands
Switzerland
United Kingdom
!ok

# ST_Disjoint(geom1, geom2) Returns whether *geom1* and *geom2* are disjoint

SELECT ST_Disjoint('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
false
!ok


# ST_EnvelopesIntersect(geom1, geom2) Returns whether the envelope of *geom1* intersects the envelope of *geom2*

SELECT ST_EnvelopesIntersect('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
true
!ok

# ST_Equals(geom1, geom2) Returns whether *geom1* equals *geom2*

SELECT ST_Equals('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
false
!ok

# ST_Intersects(geom1, geom2) Returns whether *geom1* intersects *geom2*

SELECT ST_Intersects('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
true
!ok

# ST_OrderingEquals(geom1, geom2) Returns whether *geom1* equals *geom2* and their coordinates and component Geometries are listed in the same order
# Not implemented

# ST_Overlaps(geom1, geom2) Returns whether *geom1* overlaps *geom2*

SELECT ST_Overlaps('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
false
!ok

# ST_Relate(geom1, geom2) Returns the DE-9IM intersection matrix of *geom1* and *geom2*

SELECT ST_Relate('LINESTRING(1 2, 3 4)', 'LINESTRING(5 6, 7 3)');
EXPR$0
FF1FF0102
!ok

SELECT ST_Relate('POLYGON((1 1, 4 1, 4 5, 1 5, 1 1))', 'POLYGON((3 2, 6 2, 6 6, 3 6, 3 2))');
EXPR$0
212101212
!ok

# ST_Relate(geom1, geom2, iMatrix) Returns whether *geom1* and *geom2* are related by the given intersection matrix *iMatrix*

SELECT ST_Relate('POLYGON((1 1, 4 1, 4 5, 1 5, 1 1))', 'POLYGON((3 2, 6 2, 6 6, 3 6, 3 2))', '212101212');
EXPR$0
true
!ok

SELECT ST_Relate('POLYGON((1 1, 4 1, 4 5, 1 5, 1 1))', 'POLYGON((3 2, 6 2, 6 6, 3 6, 3 2))', '112101212');
EXPR$0
false
!ok

SELECT ST_Relate('POINT(1 2)', ST_Buffer('POINT(1 2)', 2), '0F*FFF212');
EXPR$0
true
!ok

# ST_Touches(geom1, geom2) Returns whether *geom1* touches *geom2*

SELECT ST_Touches('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
false
!ok

# ST_Within(geom1, geom2) Returns whether *geom1* is within *geom2*

SELECT ST_Within('LINESTRING(1 3, 5 3)', 'LINESTRING(1 1, 5 2, 2 5)');
EXPR$0
false
!ok

#### Geometry operators (2D)

# ST_Buffer(geom, bufferSize [, quadSegs | style ]) Computes a buffer around *geom*

SELECT roundGeom(ST_AsWKT(ST_Buffer('POINT(100 90)', 50)), 10);
EXPR$0
POLYGON ((150 90, 149.0392640202 80.2454838992, 146.1939766256 70.8658283818, 141.5734806152 62.2214883491, 135.3553390594 54.6446609407, 127.7785116510 48.4265193849, 119.1341716183 43.8060233745, 109.7545161009 40.9607359799, 100 40, 90.2454838992 40.9607359799, 80.8658283818 43.8060233745, 72.2214883491 48.4265193849, 64.6446609407 54.6446609407, 58.4265193849 62.2214883491, 53.8060233745 70.8658283818, 50.9607359799 80.2454838992, 50 90, 50.9607359799 99.7545161009, 53.8060233745 109.1341716183, 58.4265193849 117.7785116510, 64.6446609407 125.3553390594, 72.2214883491 131.5734806152, 80.8658283818 136.1939766256, 90.2454838992 139.0392640202, 100.0000000000 140, 109.7545161009 139.0392640202, 119.1341716183 136.1939766256, 127.7785116510 131.5734806152, 135.3553390594 125.3553390594, 141.5734806152 117.7785116510, 146.1939766256 109.1341716183, 149.0392640202 99.7545161009, 150 90))
!ok

SELECT roundGeom(ST_AsWKT(ST_Buffer('LINESTRING(10 10,30 10)', 5)), 10);
EXPR$0
POLYGON ((30 15, 30.9754516101 14.9039264021, 31.9134171619 14.6193976626, 32.7778511651 14.1573480616, 33.5355339060 13.5355339060, 34.1573480616 12.7778511651, 34.6193976626 11.9134171619, 34.9039264021 10.9754516101, 35 10, 34.9039264021 9.0245483900, 34.6193976626 8.0865828382, 34.1573480616 7.2221488350, 33.5355339060 6.4644660941, 32.7778511651 5.8426519385, 31.9134171619 5.3806023375, 30.9754516101 5.0960735980, 30 5, 10 5, 9.0245483900 5.0960735980, 8.0865828382 5.3806023375, 7.2221488350 5.8426519385, 6.4644660941 6.4644660941, 5.8426519385 7.2221488350, 5.3806023375 8.0865828382, 5.0960735980 9.0245483900, 5 10, 5.0960735980 10.9754516101, 5.3806023375 11.9134171619, 5.8426519385 12.7778511651, 6.4644660941 13.5355339060, 7.2221488350 14.1573480616, 8.0865828382 14.6193976626, 9.0245483900 14.9039264021, 10 15, 30 15))
!ok

SELECT roundGeom(ST_AsWKT(ST_Buffer(
  'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
    -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))',
 50)), 10);
EXPR$0
POLYGON ((-63.1158577185 -6.9555028496, -73.3944111820 -7.5605449307, -83.5789612644 -6.0473771149, -93.2376230056 -2.4801666374, -101.9608122109 2.9898157492, -109.3786142359 10.1306105467, -115.1764705475 18.6394059614, -119.1085178604 28.1553789096, -119.1085413820 28.1554581099, -120.9273691116 37.3935076607, -120.9820790302 46.8087453442, -119.2707311309 56.0673079853, -115.8540096006 64.8408880867, -110.8530709662 72.8183755726, -104.4452478994 79.7168897137, -96.8577610211 85.2918100436, -88.3596616869 89.3454505701, -79.2522914583 91.7340696965, -79.2522157327 91.7340820883, -68.9820023260 92.3421525366, -58.8047481859 90.8353243254, -49.1513461748 87.2773947728, -40.4305098774 81.8190023936, -33.0114691531 74.6912490435, -27.2083373815 66.1959153402, -23.2668122747 56.6926836308, -23.2667886318 56.6926044300, -21.4340346574 47.4479146866, -21.3686346320 38.0235315014, -23.0729121735 28.7542967086, -26.4863154763 19.9695398266, -31.4875686756 11.9813772080, -37.8989806948 5.0736227608, -45.4927584873 -0.5082957645, -53.9991003661 -4.5660566526, -63.1157818715 -6.9554904582, -63.1158577185 -6.9555028496))
!ok

# Negative buffer size makes the polgyon smaller
SELECT ST_Buffer('POLYGON((10 10,10 20,20 20,20 10, 10 10))', -1);
EXPR$0
POLYGON ((11 11, 11 19, 19 19, 19 11, 11 11))
!ok

!if (fixed.calcite2539) {
# ST_BUFFER(geom, bufferSize, style) variant - not implemented
SELECT ST_Buffer('POINT(100 90)', 50, 'quad_segs=8');
at org.apache.calcite.runtime.Geometries.todo
!error GeoFunctions

# ST_BUFFER(geom, bufferSize, quadSegs) variant - not implemented
# When implemented, remove comment from ST_Contains test case
SELECT ST_Buffer('POINT(100 90)', 50, 2);
at org.apache.calcite.runtime.Geometries.todo
!error GeoFunctions
!}

# ST_ConvexHull(geom) Computes the smallest convex polygon that contains all the points in the Geometry

SELECT ST_ConvexHull('GEOMETRYCOLLECTION(
                        POINT(1 2),
                        LINESTRING(1 4, 4 7),
                        POLYGON((3 1, 7 1, 7 6, 3 1)))');
EXPR$0
POLYGON ((3 1, 1 2, 1 4, 4 7, 7 6, 7 1, 3 1))
!ok

# ST_Difference(geom1, geom2) Computes the difference between two geometries

SELECT ST_Difference('POLYGON((1 1, 7 1, 7 6, 1 6, 1 1))', 'POLYGON((3 2, 8 2, 8 8, 3 8, 3 2))');
EXPR$0
POLYGON ((7 2, 7 1, 1 1, 1 6, 3 6, 3 2, 7 2))
!ok

SELECT ST_Difference('POLYGON((3 2, 8 2, 8 8, 3 8, 3 2))', 'POLYGON((1 1, 7 1, 7 6, 1 6, 1 1))');
EXPR$0
POLYGON ((3 6, 3 8, 8 8, 8 2, 7 2, 7 6, 3 6))
!ok

# ST_Intersection(geom1, geom2) Computes the intersection of two geometries

SELECT ST_Intersection('POLYGON((1 1, 7 1, 7 6, 1 6, 1 1))', 'POLYGON((3 2, 8 2, 8 8, 3 8, 3 2))');
EXPR$0
POLYGON ((3 6, 7 6, 7 2, 3 2, 3 6))
!ok

SELECT ST_Intersection('POLYGON((1 1, 4 1, 4 6, 1 6, 1 1))', 'POLYGON((4 2, 8 2, 8 8, 4 8, 4 2))');
EXPR$0
LINESTRING (4 2, 4 6)
!ok

SELECT ST_Intersection('POLYGON((1 1, 4 1, 4 6, 1 6, 1 1))', 'POLYGON((4 6, 8 6, 8 8, 4 8, 4 6))');
EXPR$0
POINT (4 6)
!ok

SELECT ST_Intersection('LINESTRING(2 2, 6 6)', 'LINESTRING(2 8, 8 2)');
EXPR$0
POINT (5 5)
!ok

SELECT ST_Intersection('POLYGON((1 1, 7 1, 7 6, 1 6, 1 1))', 'POINT(3 5)');
EXPR$0
POINT (3 5)
!ok


# ST_SymDifference(geom1, geom2) Computes the symmetric difference between two geometries

SELECT ST_SymDifference('POLYGON((1 1, 7 1, 7 6, 1 6, 1 1))', 'POLYGON((3 2, 8 2, 8 8, 3 8, 3 2))');
EXPR$0
MULTIPOLYGON (((7 2, 7 1, 1 1, 1 6, 3 6, 3 2, 7 2)), ((7 2, 7 6, 3 6, 3 8, 8 8, 8 2, 7 2)))
!ok

# ST_Union(geom1, geom2) Computes the union of two or more geometries

# NOTE: PostGIS altered the order: it returned MULTIPOINT(-2 3,1 2)
SELECT ST_AsText(ST_Union('POINT(1 2)', 'POINT(-2 3)'));
EXPR$0
MULTIPOINT ((-2 3), (1 2))
!ok

# NOTE: PostGIS returned a point not a multipoint: POINT(1 2). ESRI bug?
SELECT ST_AsText(ST_Union('POINT(1 2)', 'POINT(1 2)'));
EXPR$0
POINT (1 2)
!ok

# ST_Union(geomCollection) Computes the union of two or more geometries

# Disabled: ST_GeomFromText cannot handle GEOMETRYCOLLECTION
!if (false) {
SELECT ST_AsText(st_union('GEOMETRYCOLLECTION(
  POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,-7 4.2))
  POINT(5 5)
  POINT(-2 3)
  LINESTRING(5 5, 10 10)'));
EXPR$0
null
!ok
!}

# ST_UNION(ARRAY[GEOMETRY]) is a PostGIS extension
# We don't support it
!if (false) {

SELECT ST_Union(ARRAY(SELECT the_geom FROM sometable));
!ok

SELECT ST_AsText(ST_Union(ARRAY[
    ST_GeomFromText('LINESTRING(1 2, 3 4)'),
    ST_GeomFromText('LINESTRING(3 4, 4 5)')])) As wktunion;

--wktunion---
MULTILINESTRING((3 4,4 5),(1 2,3 4))
!ok
!}

#### Affine transformation functions (3D and 2D)

# ST_Rotate(geom, angle [, origin | x, y]) Rotates a *geom* counter-clockwise by *angle* (in radians) about *origin* (or the point (*x*, *y*))

SELECT ST_Rotate('LINESTRING(1 3, 1 1, 2 1)', pi());
EXPR$0
LINESTRING (-1.0000000000000004 -3, -1.0000000000000002 -0.9999999999999999, -2 -0.9999999999999998)
!ok

SELECT ST_Rotate('LINESTRING(1 3, 1 1, 2 1)', pi() / 3);
EXPR$0
LINESTRING (-2.098076211353316 2.3660254037844393, -0.3660254037844385 1.3660254037844388, 0.1339745962155616 2.232050807568877)
!ok

SELECT ST_Rotate('LINESTRING(1 3, 1 1, 2 1)', -pi() / 2, ST_PointFromText('POINT(2 1)'));
EXPR$0
LINESTRING (4 2, 1.9999999999999998 2, 2 1)
!ok

SELECT ST_Rotate('LINESTRING(1 3, 1 1, 2 1)', pi() / 2, 1.0, 1.0);
EXPR$0
LINESTRING (-1 1.0000000000000002, 1 0.9999999999999999, 1 2)
!ok

# ST_Scale(geom, xFactor, yFactor) Scales *geom* by multiplying the ordinates by the indicated scale factors

SELECT ST_Scale('LINESTRING(1 2, 4 5)', 0.5, 0.75);
EXPR$0
LINESTRING (0.5 1.5, 2 3.75)
!ok

# ST_Translate(geom, x, y, [, z]) Translates *geom*

SELECT ST_Translate('POINT(1 2)', 10, 20);
EXPR$0
POINT (11 22)
!ok

SELECT ST_Translate('LINESTRING(0 0, 1 0)', 1, 2);
EXPR$0
LINESTRING (1 2, 2 2)
!ok

SELECT ST_Translate('LINESTRING(-71.01 42.37, -71.11 42.38)', 1, 0.5);
EXPR$0
LINESTRING (-70.01 42.87, -70.11 42.88)
!ok

SELECT ST_Translate('MULTIPOINT((0 1), (2 2), (1 3))', 1, 0);
EXPR$0
MULTIPOINT ((1 1), (3 2), (2 3))
!ok

SELECT ST_Translate('GEOMETRYCOLLECTION(
  POLYGON((0 0, 3 5, 6  6, 0 7, 0 0)),
  MULTIPOINT((0 1), (2 2), (1 3)))', -1, 1);
EXPR$0
GEOMETRYCOLLECTION (POLYGON ((-1 1, 2 6, 5 7, -1 8, -1 1)), MULTIPOINT ((-1 2), (1 3), (0 4)))
!ok


#### Geometry editing functions (2D)

# ST_AddPoint(geom, point [, tolerance ]) Adds *point* to *geom* with a given *tolerance* (default 0)
# Not implemented

# ST_CollectionExtract(geom, dimension) Filters *geom*, returning a multi-geometry of those members with a given *dimension* (1 = point, 2 = line-string, 3 = polygon)
# Not implemented

# ST_Densify(geom, tolerance) Inserts extra vertices every *tolerance* along the line segments of *geom*
# Not implemented

# ST_FlipCoordinates(geom) Flips the X and Y coordinates of *geom*
# Not implemented

# ST_Holes(geom) Returns the holes in *geom* (which may be a geometry-collection)
# Not implemented

# ST_Normalize(geom) Converts *geom* to normal form
# Not implemented

# ST_RemoveDuplicatedCoordinates(geom) Removes duplicated coordinates from *geom*
# Not implemented

# ST_RemoveHoles(geom) Removes a *geom*'s holes
# Not implemented

# ST_RemovePoints(geom, poly) Removes all coordinates of *geom* located within *poly*; null if all coordinates are removed
# Not implemented

# ST_RemoveRepeatedPoints(geom, tolerance) Removes from *geom* all repeated points (or points within *tolerance* of another point)
# Not implemented

# ST_Reverse(geom) Reverses the vertex order of *geom*
# Not implemented

#### Geometry editing functions (3D)

# ST_AddZ(geom, zToAdd) Adds *zToAdd* to the z-coordinate of *geom*
# Not implemented

# ST_Interpolate3DLine(geom) Returns *geom* with a interpolation of z values, or null if it is not a line-string or multi-line-string
# Not implemented

# ST_MultiplyZ(geom, zFactor) Returns *geom* with its z-values multiplied by *zFactor*
# Not implemented

# ST_Reverse3DLine(geom [, sortOrder ]) Potentially reverses *geom* according to the z-values of its first and last coordinates
# Not implemented

# ST_UpdateZ(geom, newZ [, updateCondition ]) Updates the z-values of *geom*
# Not implemented

# ST_ZUpdateLineExtremities(geom, startZ, endZ [, interpolate ]) Updates the start and end z-values of *geom*
# Not implemented

#### Geometry measurement functions (2D)

# ST_Area(geom) Returns the area of *geom* (which may be a geometry collection)
# Not implemented

# ST_ClosestCoordinate(geom, point) Returns the coordinate(s) of *geom* closest to *point*
# Not implemented

# ST_ClosestPoint(geom1, geom2) Returns the point of *geom1* closest to *geom2*
# Not implemented

# ST_FurthestCoordinate(geom, point) Returns the coordinate(s) of *geom* that are furthest from *point*
# Not implemented

# ST_Length(lineString) Returns the length of *lineString*
# Not implemented

# ST_LocateAlong(geom, segmentLengthFraction, offsetDistance) Returns a multi-point containing points along the line segments of *geom* at *segmentLengthFraction* and *offsetDistance*
# Not implemented

# ST_LongestLine(geom1, geom2) Returns the 2-dimensional longest line-string between the points of *geom1* and *geom2*
# Not implemented

# ST_MaxDistance(geom1, geom2) Computes the maximum distance between *geom1* and *geom2*
# Not implemented

# ST_Perimeter(polygon) Returns the length of the perimeter of *polygon* (which may be a multi-polygon)
# Not implemented

# ST_ProjectPoint(point, lineString) Projects *point* onto a *lineString* (which may be a multi-line-string)
# Not implemented

#### Geometry measurement functions (3D)

# ST_3DArea(geom) Return a polygon's 3D area
# Not implemented

# ST_3DLength(geom) Returns the 3D length of a line-string
# Not implemented

# ST_3DPerimeter(geom) Returns the 3D perimeter of a polygon or multi-polygon
# Not implemented

# ST_SunPosition(point [, timestamp ]) Computes the sun position at *point* and *timestamp* (now by default)
# Not implemented

#### Geometry processing functions (2D)

# ST_LineIntersector(geom1, geom2) Splits *geom1* (a line-string) with *geom2*
# Not implemented

# ST_LineMerge(geom) Merges a collection of linear components to form a line-string of maximal length

SELECT ST_LineMerge('LINESTRING (1 1, 1 4)');
EXPR$0
MULTILINESTRING ((1 1, 1 4))
!ok

SELECT ST_LineMerge('MULTILINESTRING ((1 1, 1 4), (1 4, 5 4), (5 4, 5 1), (3 3, 3 4))');
EXPR$0
MULTILINESTRING ((1 1, 1 4, 5 4, 5 1), (3 3, 3 4))
!ok

# ST_MakeValid(geom [, preserveGeomDim [, preserveDuplicateCoord [, preserveCoordDim]]]) Makes *geom* valid

SELECT ST_MakeValid('LINESTRING(0 0, 0 0)');
EXPR$0
LINESTRING EMPTY
!ok

# ST_Polygonize(geom) Creates a multi-polygon from edges of *geom*

SELECT ST_Polygonize('LINESTRING(1 2, 2 4, 4 4, 5 2, 1 2)');
EXPR$0
POLYGON ((1 2, 2 4, 4 4, 5 2, 1 2))
!ok

# ST_PrecisionReducer(geom, n) Reduces *geom*'s precision to *n* decimal places
SELECT ST_PrecisionReducer('MULTIPOINT((190.1239999997 300), (10 11.1233))', 3);
EXPR$0
MULTIPOINT ((190.124 300), (10 11.123))
!ok

# ST_RingSideBuffer(geom, bufferSize, bufferCount [, endCapStyle [, doDifference]]) Computes a ring buffer on one side
# Not implemented

# ST_SideBuffer(geom, bufferSize [, bufferStyle ]) Compute a single buffer on one side
# Not implemented

# ST_Simplify(geom, distance) Simplifies *geom* using the Douglas-Peuker algorithm with a *distance* tolerance

SELECT ST_Simplify('POLYGON((2 1, 1 2, 2 2, 2 3, 3 3, 3 2, 4 2, 4 1, 3 0, 2 0, 2 1))', 0.5);
EXPR$0
POLYGON ((2 1, 1 2, 3 3, 4 1, 3 0, 2 0, 2 1))
!ok

SELECT ST_Simplify('POLYGON((2 1, 1 2, 2 2, 2 3, 3 3, 3 2, 4 2, 4 1, 3 0, 2 0, 2 1))', 1);
EXPR$0
POLYGON ((2 1, 1 2, 3 3, 4 1, 2 1))
!ok

SELECT ST_Simplify('POLYGON((2 1, 1 2, 2 2, 2 3, 3 3, 3 2, 4 2, 4 1, 3 0, 2 0, 2 1))', 2);
EXPR$0
POLYGON EMPTY
!ok

SELECT ST_Simplify('MULTIPOINT((190 300), (10 11))', 4);
EXPR$0
MULTIPOINT ((190 300), (10 11))
!ok

SELECT ST_Simplify('LINESTRING(250 250, 280 290, 300 230, 340 300, 360 260, 440 310, 470 360, 604 286)', 40);
EXPR$0
LINESTRING (250 250, 280 290, 300 230, 470 360, 604 286)
!ok

# ST_SimplifyPreserveTopology(geom, distance) Simplifies *geom*, preserving its topology

SELECT ST_SimplifyPreserveTopology('POLYGON((8 25, 28 22, 28 20, 15 11, 33 3, 56 30, 46 33,  46 34, 47 44, 35 36, 45 33, 43 19, 29 21, 29 22, 35 26, 24 39, 8 25))', 10);
EXPR$0
POLYGON ((8 25, 28 22, 15 11, 33 3, 56 30, 47 44, 35 36, 43 19, 24 39, 8 25))
!ok

SELECT ST_SimplifyPreserveTopology('POLYGON((8 25, 28 22, 28 20, 15 11, 33 3, 56 30, 46 33,  46 34, 47 44, 35 36, 45 33, 43 19, 29 21, 29 22, 35 26, 24 39, 8 25))', 20);
EXPR$0
POLYGON ((8 25, 33 3, 56 30, 47 44, 43 19, 8 25))
!ok

SELECT ST_SimplifyPreserveTopology('POLYGON((8 25, 28 22, 28 20, 15 11, 33 3, 56 30, 46 33,  46 34, 47 44, 35 36, 45 33, 43 19, 29 21, 29 22, 35 26, 24 39, 8 25))', 30);
EXPR$0
POLYGON ((8 25, 33 3, 56 30, 47 44, 8 25))
!ok

# ST_Snap(geom1, geom2, tolerance) Snaps *geom1* and *geom2* together

SELECT ST_Snap('LINESTRING(1 2, 2 4, 4 4, 5 2)', 'LINESTRING(5 2, 2 1, 1 2)', 1);
EXPR$0
LINESTRING (1 2, 2 4, 4 4, 5 2)
!ok

SELECT ST_Snap('LINESTRING(1 2, 2 4, 4 4, 5 2)', 'LINESTRING(5 2, 2 1, 1 2)', 2);
EXPR$0
LINESTRING (1 2, 2 1, 2 4, 4 4, 5 2)
!ok

SELECT ST_Snap('LINESTRING(1 2, 2 4, 4 4, 5 2)', 'LINESTRING(5 2, 2 1, 1 2)', 3);
EXPR$0
LINESTRING (1 2, 1 2, 2 1, 5 2, 5 2)
!ok

SELECT ST_Snap('POLYGON((1 1, 1 7, 7 7, 7 1, 1 1))', 'POLYGON((3 3, 1 2, 0 2, 0 1, -2 1, -1 7, 3 6, 4 8, 7 8, 6 6, 9 6, 8 1, 8 1, 3 3))', 2);
EXPR$0
POLYGON ((0 1, 1 2, 0 2, -1 7, 1 7, 3 6, 6 6, 8 1, 0 1))
!ok

SELECT ST_Snap('POLYGON((3 3, 1 2, 0 2, 0 1, -2 1, -1 7, 3 6, 4 8, 7 8, 6 6, 9 6, 8 1, 8 1, 3 3))', 'POLYGON((1 1, 1 7, 7 7, 7 1, 1 1))', 2);
EXPR$0
POLYGON ((3 3, 1 1, 1 1, 1 1, -2 1, -1 7, 1 7, 3 6, 4 8, 7 7, 7 7, 9 6, 7 1, 7 1, 3 3))
!ok

# ST_Split(geom1, geom2 [, tolerance]) Splits *geom1* by *geom2* using *tolerance* (default 1E-6) to determine where the point splits the line
# Not implemented

#### Geometry projection functions

# ST_SetSRID(geom, srid) Returns a copy of *geom* with a new SRID
SELECT ST_AsEWKT(ST_SetSRID(ST_MakePoint(-123.365556, 48.428611),4326)) As wgs84long_lat;
WGS84LONG_LAT
srid:4326;POINT (-123.365556 48.428611)
!ok

# Mark a point as WGS 84 long lat and then transform to web mercator (Spherical Mercator)
SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_MakePoint(-123.365556, 48.428611),4326),3785)) As sphere_merc;
SPHERE_MERC
srid:9804;POINT (-13732990.875349075 6178458.964254234)
!ok

# ST_Transform(geom, srid) Transforms *geom* from one coordinate reference system (CRS) to the CRS specified by *srid*
SELECT roundGeom(ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
  743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)), 10) As wgs_geom;
WGS_GEOM
POLYGON ((-71.1776848523 42.3902896513, -71.1776843767 42.3903829479, -71.1775844306 42.3903826678, -71.1775825928 42.3902893648, -71.1776848523 42.3902896513))
!ok

#### Trigonometry functions

# ST_Azimuth(point1, point2) Return the azimuth of the segment from *point1* to *point2*
# Not implemented

#### Topography functions

# ST_TriangleAspect(geom) Returns the aspect of a triangle
# Not implemented

# ST_TriangleContouring(query \[, z1, z2, z3 ]\[, varArgs]*) Splits triangles into smaller triangles according to classes
# Not implemented

# ST_TriangleDirection(geom) Computes the direction of steepest ascent of a triangle and returns it as a line-string
# Not implemented

# ST_TriangleSlope(geom) Computes the slope of a triangle as a percentage
# Not implemented

# ST_Voronoi(geom [, outDimension [, envelopePolygon ]]) Creates a Voronoi diagram
# Not implemented

#### Triangulation functions

# ST_ConstrainedDelaunay(geom [, flag [, quality ]]) Computes a constrained Delaunay triangulation based on *geom*
# Not implemented

# ST_Delaunay(geom [, flag [, quality ]]) Computes a Delaunay triangulation based on points
# Not implemented

# ST_Tessellate(polygon) Tessellates *polygon* (may be multi-polygon) with adaptive triangles
# Not implemented

#### Geometry aggregate functions

# ST_Accum(geom) Accumulates *geom* into a geometry-collection (or multi-point, multi-line-string or multi-polygon if possible)
# Not implemented

# ST_Collect(geom) Alias for `ST_Accum`
# Not implemented

# ST_Union(geom) Computes the union of geometries
# Not implemented

# Disabled - ST_Union agg function is not implemented
!if (false) {
SELECT ST_AsText(st_union(the_geom))
FROM (VALUES
  ST_GeomFromText('POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,-7 4.2))'),
  ST_GeomFromText('POINT(5 5)'),
  ST_GeomFromText('POINT(-2 3)'),
  ST_GeomFromText('LINESTRING(5 5, 10 10)')) as foo(the_geom);

st_asewkt
---------
GEOMETRYCOLLECTION(POINT(-2 3 1),LINESTRING(5 5 5,10 10 10),POLYGON((-7 4.2 5,-7.1 4.2 5,-7.1 4.3 5,-7 4.2 5)))
!ok
!}

# 3d example - sort of supports 3d (and with mixed dimensions!)
# WRONG: Currently returns 4 rows, should return 1 row when ST_Union is aggregate function
SELECT ST_AsText(st_union(the_geom))
FROM (
  SELECT ST_GeomFromText('POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,-7 4.2))') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('POINT Z(5 5 5)') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('POINT Z(-2 3 1)') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('LINESTRING Z(5 5 5, 10 10 10)') as the_geom ) as foo;

EXPR$0
LINESTRING Z(5 5 5, 10 10 10)
POINT Z(-2 3 1)
POINT Z(5 5 5)
POLYGON ((-7 4.2, -7.1 4.2, -7.1 4.3, -7 4.2))
!ok

# 3d example not mixing dimensions
# WRONG: Currently returns 4 rows, should return 1 row when ST_Union is aggregate function
SELECT ST_AsText(st_union(the_geom))
FROM (
  SELECT ST_GeomFromText('POLYGON Z((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2,-7 4.2 2))') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('POINT Z(5 5 5)') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('POINT Z(-2 3 1)') as the_geom
  UNION ALL
  SELECT ST_GeomFromText('LINESTRING Z(5 5 5, 10 10 10)') as the_geom ) as foo;

EXPR$0
LINESTRING Z(5 5 5, 10 10 10)
POINT Z(-2 3 1)
POINT Z(5 5 5)
POLYGON Z((-7 4.2 2, -7.1 4.2 3, -7.1 4.3 2, -7 4.2 2))
!ok

# Polygon-to-polygon joins
select *
from GEO."states" as s
order by "name";
name, geom
AZ, POLYGON ((-114 37, -109.05 37, -109.05 31.33, -111.07 31.33, -114.75 32.5, -114.75 35.1, -114 37))
CA, POLYGON ((-124.25 42, -120 42, -120 39, -114.75 35.1, -114.75 32.5, -117.15 32.5, -118.3 33.75, -120.5 34.5, -122.4 37.2, -124.25 42))
CO, POLYGON ((-109.05 41, -102 41, -102 37, -109.05 37, -109.05 41))
ID, POLYGON ((-117 49, -116.05 49, -116.05 48, -114.4 46.6, -112.9 44.45, -111.05 44.45, -111.05 42, -117.03 42, -117.03 44.2, -116.5 45.5, -117 46, -117 49))
MT, POLYGON ((-116.05 49, -104.05 49, -104.05 45, -111.05 45, -111.05 44.45, -112.9 44.45, -114.4 46.6, -116.05 48, -116.05 49))
NM, POLYGON ((-109.05 37, -103 37, -103 32, -106.65 32, -106.5 31.8, -108.2 31.8, -108.2 31.33, -109.05 31.33, -109.05 37))
NV, POLYGON ((-120 42, -114 42, -114 37, -114.75 35.1, -120 39, -120 42))
OR, POLYGON ((-123.9 46.2, -122.7 45.7, -119 46, -117 46, -116.5 45.5, -117.03 44.2, -117.03 42, -124.25 42, -124.6 42.8, -123.9 46.2))
UT, POLYGON ((-114 42, -111.05 42, -111.05 41, -109.05 41, -109.05 37, -114 37, -114 42))
WA, POLYGON ((-124.8 48.4, -123.2 48.2, -123.2 49, -117 49, -117 46, -119 46, -122.7 45.7, -123.9 46.2, -124.8 48.4))
WY, POLYGON ((-111.05 45, -104.05 45, -104.05 41, -111.05 41, -111.05 45))
!ok

select *
from GEO."parks" as p
order by "name";
name, geom
Death Valley NP, POLYGON ((-118.2 37.3, -117 37, -116.3 35.7, -117 35.7, -117.2 36.2, -117.8 36.4, -118.2 37.3))
Yellowstone NP, POLYGON ((-111.2 45.1, -109.3 45.1, -109.3 44.1, -109 43.8, -110 43, -111.2 43.4, -111.2 45.1))
Yosemite NP, POLYGON ((-120.2 38, -119.3 38.2, -119 37.7, -119.9 37.6, -120.2 38))
!ok

# Parks that may intersect states
select s."name", p."name"
from GEO."states" as s
cross apply table(ST_MakeGrid(s."geom", 5.0, 5.0)) as sg,
    GEO."parks" as p
cross apply table(ST_MakeGrid(p."geom", 5.0, 5.0)) as pg
where (sg.abs_col, sg.abs_row) = (pg.abs_col, pg.abs_row)
order by 2, 1;
name, name
CA, Death Valley NP
NV, Death Valley NP
ID, Yellowstone NP
MT, Yellowstone NP
NV, Yellowstone NP
UT, Yellowstone NP
WY, Yellowstone NP
CA, Yosemite NP
!ok

# Parks that intersect states
select s."name", p."name"
from GEO."states" as s
cross apply table(ST_MakeGrid(s."geom", 5.0, 5.0)) as sg,
    GEO."parks" as p
cross apply table(ST_MakeGrid(p."geom", 5.0, 5.0)) as pg
where (sg.abs_col, sg.abs_row) = (pg.abs_col, pg.abs_row)
and ST_Intersects(s."geom", p."geom")
order by 2, 1;
name, name
CA, Death Valley NP
NV, Death Valley NP
ID, Yellowstone NP
MT, Yellowstone NP
WY, Yellowstone NP
CA, Yosemite NP
!ok

# Space-filling curves.
select x, y, hilbert(ST_Point(x, y))
from (
  values (0.0, 0.0),
     (0, 1),
     (1, 0),
     (0, -1),
     (10, 10),
     (20, 20)) as t(x, y);
X, Y, EXPR$2
0.0, -1.0, 10921
0.0, 0.0, 10922
0.0, 1.0, 32767
1.0, 0.0, 54613
10.0, 10.0, 32973
20.0, 20.0, 33204
!ok

values hilbert(ST_Point(20.0, 20.0));
EXPR$0
33204
!ok


# End spatial.iq