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
SELECT ST_LineFromWKB(ST_AsWKB('LINESTRING (4 3, 6 5, 8 12)'));
EXPR$0
LINESTRING (4 3, 6 5, 8 12)
!ok

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

SELECT ST_LineFromWKB(ST_AsWKB('POINT(2 3)'));
EXPR$0
null
!ok

# 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
SELECT ST_PointFromWKB(ST_AsWKB('POINT (1 1)'));
EXPR$0
POINT (1 1)
!ok

SELECT ST_PointFromWKB(ST_AsWKB('POINT (5 5)'));
EXPR$0
POINT (5 5)
!ok

SELECT ST_PointFromWKB(ST_AsWKB('LINESTRING (4 3, 6 5, 8 12)'));
EXPR$0
null
!ok

# 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
SELECT ST_PolyFromWKB(ST_AsWKB('POLYGON ((49 30, 50 28, 53 28, 53 32, 50 32, 49 30))'));
EXPR$0
POLYGON ((49 30, 50 28, 53 28, 53 32, 50 32, 49 30))
!ok

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

SELECT ST_PolyFromWKB(ST_AsWKB('POINT (1 1)'));
EXPR$0
null
!ok

# ST_ReducedPrecision(geom, gridSize) Reduces the precision of a geom to the provided gridSize
SELECT ST_ReducePrecision('POINT(1.412 19.323)', 0.1);
EXPR$0
POINT (1.4 19.3)
!ok

SELECT ST_ReducePrecision('POINT(1.412 19.323)', 1.0);
EXPR$0
POINT (1 19)
!ok

SELECT ST_ReducePrecision('POINT(1.412 19.323)', 10);
EXPR$0
POINT (0 20)
!ok

# 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 ST_ReducePrecision(ST_asText(ST_BoundingCircle('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))')), 0.01);
EXPR$0
POLYGON ((4.62 2.5, 4.58 2.09, 4.46 1.69, 4.26 1.32, 4 1, 3.68 0.74, 3.31 0.54, 2.91 0.42, 2.5 0.38, 2.09 0.42, 1.69 0.54, 1.32 0.74, 1 1, 0.74 1.32, 0.54 1.69, 0.42 2.09, 0.38 2.5, 0.42 2.91, 0.54 3.31, 0.74 3.68, 1 4, 1.32 4.26, 1.69 4.46, 2.09 4.58, 2.5 4.62, 2.91 4.58, 3.31 4.46, 3.68 4.26, 4 4, 4.26 3.68, 4.46 3.31, 4.58 2.91, 4.62 2.5))
!ok

SELECT ST_ReducePrecision(ST_asText(ST_BoundingCircle('MULTIPOINT((1 1), (4 2))')), 0.01);
EXPR$0
POLYGON ((4.08 1.5, 4.05 1.19, 3.96 0.89, 3.81 0.62, 3.62 0.38, 3.38 0.19, 3.11 0.04, 2.81 -0.05, 2.5 -0.08, 2.19 -0.05, 1.89 0.04, 1.62 0.19, 1.38 0.38, 1.19 0.62, 1.04 0.89, 0.95 1.19, 0.92 1.5, 0.95 1.81, 1.04 2.11, 1.19 2.38, 1.38 2.62, 1.62 2.81, 1.89 2.96, 2.19 3.05, 2.5 3.08, 2.81 3.05, 3.11 2.96, 3.38 2.81, 3.62 2.62, 3.81 2.38, 3.96 2.11, 4.05 1.81, 4.08 1.5))
!ok

SELECT ST_ReducePrecision(ST_asText(ST_BoundingCircle('LINESTRING(1 1, 4 5, 3 2)')), 0.01);
EXPR$0
POLYGON ((5 3, 4.95 2.51, 4.81 2.04, 4.58 1.61, 4.27 1.23, 3.89 0.92, 3.46 0.69, 2.99 0.55, 2.5 0.5, 2.01 0.55, 1.54 0.69, 1.11 0.92, 0.73 1.23, 0.42 1.61, 0.19 2.04, 0.05 2.51, 0 3, 0.05 3.49, 0.19 3.96, 0.42 4.39, 0.73 4.77, 1.11 5.08, 1.54 5.31, 2.01 5.45, 2.5 5.5, 2.99 5.45, 3.46 5.31, 3.89 5.08, 4.27 4.77, 4.58 4.39, 4.81 3.96, 4.95 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 ST_ReducePrecision(ST_AsText(ST_MakeEllipse('POINT(5 5)', 2, 5)), 0.01);
EXPR$0
POLYGON ((6 5, 6 5.16, 5.99 5.31, 5.98 5.47, 5.97 5.62, 5.95 5.77, 5.93 5.92, 5.9 6.06, 5.88 6.2, 5.84 6.34, 5.81 6.47, 5.77 6.59, 5.73 6.71, 5.68 6.82, 5.64 6.93, 5.59 7.02, 5.54 7.11, 5.48 7.19, 5.43 7.26, 5.37 7.32, 5.31 7.38, 5.25 7.42, 5.19 7.46, 5.13 7.48, 5.06 7.5, 5 7.5, 4.94 7.5, 4.87 7.48, 4.81 7.46, 4.75 7.42, 4.69 7.38, 4.63 7.32, 4.57 7.26, 4.52 7.19, 4.46 7.11, 4.41 7.02, 4.36 6.93, 4.32 6.82, 4.27 6.71, 4.23 6.59, 4.19 6.47, 4.16 6.34, 4.12 6.2, 4.1 6.06, 4.07 5.92, 4.05 5.77, 4.03 5.62, 4.02 5.47, 4.01 5.31, 4 5.16, 4 5, 4 4.84, 4.01 4.69, 4.02 4.53, 4.03 4.38, 4.05 4.23, 4.07 4.08, 4.1 3.94, 4.12 3.8, 4.16 3.66, 4.19 3.53, 4.23 3.41, 4.27 3.29, 4.32 3.18, 4.36 3.07, 4.41 2.98, 4.46 2.89, 4.52 2.81, 4.57 2.74, 4.63 2.68, 4.69 2.62, 4.75 2.58, 4.81 2.54, 4.87 2.52, 4.94 2.5, 5 2.5, 5.06 2.5, 5.13 2.52, 5.19 2.54, 5.25 2.58, 5.31 2.62, 5.37 2.68, 5.43 2.74, 5.48 2.81, 5.54 2.89, 5.59 2.98, 5.64 3.07, 5.68 3.18, 5.73 3.29, 5.77 3.41, 5.81 3.53, 5.84 3.66, 5.88 3.8, 5.9 3.94, 5.93 4.08, 5.95 4.23, 5.97 4.38, 5.98 4.53, 5.99 4.69, 6 4.84, 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

SELECT t.geom, t.index FROM table(ST_Explode(ST_GeomFromText('MULTIPOINT((1 1), (2 2), (3 3), (4 4))'))) as t(geom, index);
GEOM, INDEX
POINT (1 1), 0
POINT (2 2), 1
POINT (3 3), 2
POINT (4 4), 3
!ok

SELECT t1.id, t2.geom, t2.index
FROM (SELECT 1 as id, 'MULTIPOINT((1 1), (2 2))' as points UNION ALL SELECT 2 as id, 'MULTIPOINT((3 3), (4 4))' as points) as t1,
LATERAL table(ST_Explode(t1.points)) as t2(geom, index);
ID, GEOM, INDEX
1, POINT (1 1), 0
1, POINT (2 2), 1
2, POINT (3 3), 0
2, POINT (4 4), 1
!ok

# 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)))', 1);
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*
# Alias: ST_NumPoints

# 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_CoveredBy(geom1, geom2) Returns whether no point in *geom1* is outside *geom2*

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

SELECT ST_CoveredBy('POINT(1 1)', ST_Buffer('POINT(0 0)', 1));
EXPR$0
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
SELECT ST_OrderingEquals('LINESTRING(0 0 1, 0 0, 10 10 3)', 'LINESTRING(0 0 1, 0 0, 10 10 3)');
EXPR$0
true
!ok

SELECT ST_OrderingEquals('LINESTRING(0 0, 10 10)', 'LINESTRING(0 0, 5 5, 10 10)');
EXPR$0
false
!ok

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

# 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 ST_ReducePrecision(ST_AsWKT(ST_Buffer('POINT(100 90)', 50)), 0.01);
EXPR$0
POLYGON ((150 90, 149.04 80.25, 146.19 70.87, 141.57 62.22, 135.36 54.64, 127.78 48.43, 119.13 43.81, 109.75 40.96, 100 40, 90.25 40.96, 80.87 43.81, 72.22 48.43, 64.64 54.64, 58.43 62.22, 53.81 70.87, 50.96 80.25, 50 90, 50.96 99.75, 53.81 109.13, 58.43 117.78, 64.64 125.36, 72.22 131.57, 80.87 136.19, 90.25 139.04, 100 140, 109.75 139.04, 119.13 136.19, 127.78 131.57, 135.36 125.36, 141.57 117.78, 146.19 109.13, 149.04 99.75, 150 90))
!ok

SELECT ST_ReducePrecision(ST_AsWKT(ST_Buffer('LINESTRING(10 10,30 10)', 5)), 0.01);
EXPR$0
POLYGON ((30 15, 30.98 14.9, 31.91 14.62, 32.78 14.16, 33.54 13.54, 34.16 12.78, 34.62 11.91, 34.9 10.98, 35 10, 34.9 9.02, 34.62 8.09, 34.16 7.22, 33.54 6.46, 32.78 5.84, 31.91 5.38, 30.98 5.1, 30 5, 10 5, 9.02 5.1, 8.09 5.38, 7.22 5.84, 6.46 6.46, 5.84 7.22, 5.38 8.09, 5.1 9.02, 5 10, 5.1 10.98, 5.38 11.91, 5.84 12.78, 6.46 13.54, 7.22 14.16, 8.09 14.62, 9.02 14.9, 10 15, 30 15))
!ok

SELECT ST_ReducePrecision(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)), 0.01);
EXPR$0
POLYGON ((-63.12 -6.96, -73.39 -7.56, -83.58 -6.05, -93.24 -2.48, -101.96 2.99, -109.38 10.13, -115.18 18.64, -119.11 28.16, -119.11 28.16, -120.93 37.39, -120.98 46.81, -119.27 56.07, -115.85 64.84, -110.85 72.82, -104.45 79.72, -96.86 85.29, -88.36 89.35, -79.25 91.73, -79.25 91.73, -68.98 92.34, -58.8 90.84, -49.15 87.28, -40.43 81.82, -33.01 74.69, -27.21 66.2, -23.27 56.69, -23.27 56.69, -21.43 47.45, -21.37 38.02, -23.07 28.75, -26.49 19.97, -31.49 11.98, -37.9 5.07, -45.49 -0.51, -54 -4.57, -63.12 -6.96, -63.12 -6.96))
!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

SELECT ST_PrecisionReducer(ST_Buffer('POINT(100 90)', 50, 'quad_segs=8'), 3);
EXPR$0
POLYGON ((149.039 80.245, 146.194 70.866, 141.573 62.221, 135.355 54.645, 127.779 48.427, 119.134 43.806, 109.755 40.961, 100 40, 90.245 40.961, 80.866 43.806, 72.221 48.427, 64.645 54.645, 58.427 62.221, 53.806 70.866, 50.961 80.245, 50 90, 50.961 99.755, 53.806 109.134, 58.427 117.779, 64.645 125.355, 72.221 131.573, 80.866 136.194, 90.245 139.039, 100 140, 109.755 139.039, 119.134 136.194, 127.779 131.573, 135.355 125.355, 141.573 117.779, 146.194 109.134, 149.039 99.755, 150 90, 149.039 80.245))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('POINT(100 90)', 50, 'quad_segs=2'), 3);
EXPR$0
POLYGON ((135.355 54.645, 100 40, 64.645 54.645, 50 90, 64.645 125.355, 100 140, 135.355 125.355, 150 90, 135.355 54.645))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'endcap=round join=round'), 3);
EXPR$0
POLYGON ((144.444 158.315, 146.173 159.239, 148.049 159.808, 150 160, 151.951 159.808, 153.827 159.239, 155.556 158.315, 157.071 157.071, 158.315 155.556, 159.239 153.827, 159.808 151.951, 160 150, 160 50, 159.808 48.049, 159.239 46.173, 158.315 44.444, 157.071 42.929, 155.556 41.685, 153.827 40.761, 151.951 40.192, 150 40, 148.049 40.192, 146.173 40.761, 144.444 41.685, 142.929 42.929, 141.685 44.444, 140.761 46.173, 140.192 48.049, 140 50, 140 125.858, 57.071 42.929, 55.556 41.685, 53.827 40.761, 51.951 40.192, 50 40, 48.049 40.192, 46.173 40.761, 44.444 41.685, 42.929 42.929, 41.685 44.444, 40.761 46.173, 40.192 48.049, 40 50, 40.192 51.951, 40.761 53.827, 41.685 55.556, 42.929 57.071, 142.929 157.071, 144.444 158.315))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'endcap=square join=round'), 3);
EXPR$0
POLYGON ((144.444 158.315, 146.173 159.239, 148.049 159.808, 150 160, 151.951 159.808, 153.827 159.239, 155.556 158.315, 157.071 157.071, 158.315 155.556, 159.239 153.827, 159.808 151.951, 160 150, 160 50, 160 40, 140 40, 140 125.858, 57.071 42.929, 50 35.858, 35.858 50, 142.929 157.071, 144.444 158.315))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'endcap=flat join=round'), 3);
EXPR$0
POLYGON ((144.444 158.315, 146.173 159.239, 148.049 159.808, 150 160, 151.951 159.808, 153.827 159.239, 155.556 158.315, 157.071 157.071, 158.315 155.556, 159.239 153.827, 159.808 151.951, 160 150, 160 50, 140 50, 140 125.858, 57.071 42.929, 42.929 57.071, 142.929 157.071, 144.444 158.315))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'join=bevel'), 3);
EXPR$0
POLYGON ((160 150, 160 50, 159.808 48.049, 159.239 46.173, 158.315 44.444, 157.071 42.929, 155.556 41.685, 153.827 40.761, 151.951 40.192, 150 40, 148.049 40.192, 146.173 40.761, 144.444 41.685, 142.929 42.929, 141.685 44.444, 140.761 46.173, 140.192 48.049, 140 50, 140 125.858, 57.071 42.929, 55.556 41.685, 53.827 40.761, 51.951 40.192, 50 40, 48.049 40.192, 46.173 40.761, 44.444 41.685, 42.929 42.929, 41.685 44.444, 40.761 46.173, 40.192 48.049, 40 50, 40.192 51.951, 40.761 53.827, 41.685 55.556, 42.929 57.071, 142.929 157.071, 160 150))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'join=mitre mitre_limit=5.0'), 3);
EXPR$0
POLYGON ((160 50, 159.808 48.049, 159.239 46.173, 158.315 44.444, 157.071 42.929, 155.556 41.685, 153.827 40.761, 151.951 40.192, 150 40, 148.049 40.192, 146.173 40.761, 144.444 41.685, 142.929 42.929, 141.685 44.444, 140.761 46.173, 140.192 48.049, 140 50, 140 125.858, 57.071 42.929, 55.556 41.685, 53.827 40.761, 51.951 40.192, 50 40, 48.049 40.192, 46.173 40.761, 44.444 41.685, 42.929 42.929, 41.685 44.444, 40.761 46.173, 40.192 48.049, 40 50, 40.192 51.951, 40.761 53.827, 41.685 55.556, 42.929 57.071, 160 174.142, 160 50))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'join=mitre mitre_limit=1.0'), 3);
EXPR$0
POLYGON ((160 50, 159.808 48.049, 159.239 46.173, 158.315 44.444, 157.071 42.929, 155.556 41.685, 153.827 40.761, 151.951 40.192, 150 40, 148.049 40.192, 146.173 40.761, 144.444 41.685, 142.929 42.929, 141.685 44.444, 140.761 46.173, 140.192 48.049, 140 50, 140 125.858, 57.071 42.929, 55.556 41.685, 53.827 40.761, 51.951 40.192, 50 40, 48.049 40.192, 46.173 40.761, 44.444 41.685, 42.929 42.929, 41.685 44.444, 40.761 46.173, 40.192 48.049, 40 50, 40.192 51.951, 40.761 53.827, 41.685 55.556, 42.929 57.071, 160 174.142, 160 50))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'side=left'), 3);
EXPR$0
POLYGON ((150 150, 50 50, 42.929 57.071, 142.929 157.071, 144.444 158.315, 146.173 159.239, 148.049 159.808, 150 160, 151.951 159.808, 153.827 159.239, 155.556 158.315, 157.071 157.071, 158.315 155.556, 159.239 153.827, 159.808 151.951, 160 150, 160 50, 150 50, 150 150))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'side=right'), 3);
EXPR$0
POLYGON ((150 150, 150 50, 140 50, 140 125.858, 57.071 42.929, 50 50, 150 150))
!ok

SELECT ST_PrecisionReducer(ST_Buffer('LINESTRING(50 50,150 150,150 50)', 10, 'side=left join=mitre'), 3);
EXPR$0
POLYGON ((150 150, 50 50, 42.929 57.071, 160 174.142, 160 50, 150 50, 150 150))
!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_OffsetCurve(geom, distance, bufferStyle) Computes an offset line for *linestring*.
SELECT ST_ReducePrecision(ST_OffsetCurve('LINESTRING(0 0, 10 0, 10 10)', 2, ''), 0.01);
EXPR$0
LINESTRING (0 2, 8 2, 8 10)
!ok

SELECT ST_ReducePrecision(ST_OffsetCurve('LINESTRING(0 0, 10 0, 10 10)', -2, ''), 0.01);
EXPR$0
LINESTRING (0 -2, 10 -2, 10.39 -1.96, 10.77 -1.85, 11.11 -1.66, 11.41 -1.41, 11.66 -1.11, 11.85 -0.77, 11.96 -0.39, 12 0, 12 10)
!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_UnaryUnion(geom1, geom2) Computes the union of two or more geometries

SELECT ST_AsText(ST_UnaryUnion('POINT(1 2)', 'POINT(-2 3)'));
EXPR$0
MULTIPOINT ((-2 3), (1 2))
!ok

SELECT ST_AsText(ST_UnaryUnion('POINT(1 2)', 'POINT(1 2)'));
EXPR$0
POINT (1 2)
!ok

SELECT ST_AsText(ST_UnaryUnion('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
GEOMETRYCOLLECTION (POINT (-2 3), LINESTRING (5 5, 10 10), POLYGON ((-7 4.2, -7.1 4.2, -7.1 4.3, -7 4.2)))
!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

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

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

#### Geometry editing functions (2D)

# ST_AddPoint(linestring, point [, index ]) Adds *point* to *linestring* at a given *index* (or at the end if *index* is not specified)
SELECT ST_AddPoint('LINESTRING(0 0, 1 1)', 'POINT(2 2)');
EXPR$0
LINESTRING (0 0, 1 1, 2 2)
!ok

SELECT ST_AddPoint('LINESTRING(0 0, 1 1, 3 3)', 'POINT(2 2)', 2);
EXPR$0
LINESTRING (0 0, 1 1, 2 2, 3 3)
!ok

# 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) Densifies a *geom* by inserting extra vertices along the line segments.
SELECT ST_Densify('POINT(14 2)', 10);
EXPR$0
POINT (14 2)
!ok

SELECT ST_ReducePrecision(ST_Densify('LINESTRING(1 11, 8 1)', 2), 0.01);
EXPR$0
LINESTRING (1 11, 2 9.57, 3 8.14, 4 6.71, 5 5.29, 6 3.86, 7 2.43, 8 1)
!ok

SELECT ST_Densify('LINESTRING(1 11, 8 1)', 10);
EXPR$0
LINESTRING (1 11, 4.5 6, 8 1)
!ok

SELECT ST_Densify('POLYGON((2 0, 2 8, 4 8, 4 0, 2 0))', 4.5);
EXPR$0
POLYGON ((2 0, 2 4, 2 8, 4 8, 4 4, 4 0, 2 0))
!ok

# ST_FlipCoordinates(geom) Flips the X and Y coordinates of *geom*
SELECT ST_FlipCoordinates('POINT(1 2)');
EXPR$0
POINT (2 1)
!ok

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

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

# ST_Holes(geom) Returns the holes in *geom* (which may be a geometry-collection)
SELECT ST_Holes('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))');
EXPR$0
GEOMETRYCOLLECTION EMPTY
!ok

SELECT ST_Holes('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0), (1 1, 2 1, 2 4, 1 4, 1 1))');
EXPR$0
GEOMETRYCOLLECTION (LINEARRING (1 1, 2 1, 2 4, 1 4, 1 1))
!ok

SELECT ST_Holes('GEOMETRYCOLLECTION(POLYGON((0 0, 10 0, 10 5, 0 5, 0 0), (1 1, 2 1, 2 4, 1 4, 1 1)), POLYGON((11 6, 14 6, 14 9, 11 9, 11 6), (12 7, 14 7, 14 8, 12 8, 12 7)))');
EXPR$0
GEOMETRYCOLLECTION (LINEARRING (1 1, 2 1, 2 4, 1 4, 1 1), LINEARRING (12 7, 14 7, 14 8, 12 8, 12 7))
!ok

SELECT ST_Holes('LINESTRING(5 5, 1 2, 3 4, 9 3)');
EXPR$0
GEOMETRYCOLLECTION EMPTY
!ok

# ST_Normalize(geom) Converts *geom* to normal form
SELECT ST_Normalize('POLYGON((2 4, 1 3, 2 1, 6 1, 6 3, 4 4, 2 4))');
EXPR$0
POLYGON ((1 3, 2 4, 4 4, 6 3, 6 1, 2 1, 1 3))
!ok

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

SELECT ST_Normalize('LINESTRING(3 1, 6 1, 6 3, 3 3, 1 1)');
EXPR$0
LINESTRING (1 1, 3 3, 6 3, 6 1, 3 1)
!ok

# ST_RemoveRepeatedPoints(geom) Removes duplicated coordinates from *geom*
SELECT ST_RemoveRepeatedPoints( 'LINESTRING (0 0, 0 0, 1 1, 5 5, 1 1, 2 2)', 2);
EXPR$0
LINESTRING (0 0, 5 5, 2 2)
!ok

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

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

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

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

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

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

# ST_RemoveHoles(geom) Removes a *geom*'s holes
SELECT ST_RemoveHoles('POLYGON((1 5, 0 4, 0 1, 1 0, 4 0, 4 2, 5 4, 5 4, 1 5), (1 3, 1 4, 2 4, 2 3, 1 3), (2 2, 1 1, 2 1, 2 2))');
EXPR$0
POLYGON ((1 5, 0 4, 0 1, 1 0, 4 0, 4 2, 5 4, 5 4, 1 5))
!ok

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

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

# ST_RemovePoint(linestring, index) Remove *point* at given *index* in *linestring*
SELECT ST_RemovePoint('LINESTRING(0 0, 1 1, 2 2, 3 3)', 2);
EXPR$0
LINESTRING (0 0, 1 1, 3 3)
!ok

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

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

# 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*
SELECT ST_Reverse('LINESTRING(1 1, 2 2, 1 3, 3 3, 5 2, 5 1)');
EXPR$0
LINESTRING (5 1, 5 2, 3 3, 1 3, 2 2, 1 1)
!ok

SELECT ST_Reverse('MULTILINESTRING((10 260, 150 290, 186 406, 286 286), (120 120, 130 125, 142 129, 360 160, 357 170, 380 340), (1 1, 5 5))');
EXPR$0
MULTILINESTRING ((286 286, 186 406, 150 290, 10 260), (380 340, 357 170, 360 160, 142 129, 130 125, 120 120), (5 5, 1 1))
!ok

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

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

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

#### Geometry editing functions (3D)

# ST_AddZ(geom, zToAdd) Adds *zToAdd* to the z-coordinate of *geom*
SELECT ST_AsText(ST_AddZ('MULTIPOINT((190 300 1), (10 11 0))', 10));
EXPR$0
MULTIPOINT Z((190 300 11), (10 11 10))
!ok

SELECT ST_Z(ST_GeometryN(ST_AddZ('MULTIPOINT((190 300 1), (10 11))', 10), 1));
EXPR$0
NaN
!ok

SELECT ST_AsText(ST_AddZ('MULTIPOINT Z((190 300 10), (10 11 5))', -10));
EXPR$0
MULTIPOINT Z((190 300 0), (10 11 -5))
!ok

SELECT ST_AsText(ST_AddZ('POLYGON((1 1 5, 1 7 10, 7 7 -1, 7 1 -1, 1 1 5))', -10));
EXPR$0
POLYGON Z((1 1 -5, 1 7 0, 7 7 -11, 7 1 -11, 1 1 -5))
!ok

# 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)

SELECT ST_Area('POINT(0 12)');
EXPR$0
0.0
!ok

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

SELECT ST_Area('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
EXPR$0
100.0
!ok

SELECT ST_Area('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0), (5 4, 1 1, 3 4, 4 5, 5 4)))');
EXPR$0
96.0
!ok

SELECT ST_Area('GEOMETRYCOLLECTION(LINESTRING(5 4, 1 1, 3 4, 4 5), POINT(0 12), POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)), POLYGON((5 4, 1 1, 3 4, 4 5, 5 4)))');
EXPR$0
104.0
!ok

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

SELECT ST_ClosestCoordinate('POINT(0 0)', 'POLYGON((2 2, 10 0, 10 5, 0 5, 2 2))');
EXPR$0
POINT (2 2)
!ok

SELECT ST_ClosestCoordinate('POINT(4 2.5)', 'POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))');
EXPR$0
MULTIPOINT ((0 0), (0 5))
!ok

SELECT ST_ClosestCoordinate('POINT(4 2)', 'LINESTRING(10 0, 10 5, 0 5)');
EXPR$0
POINT (0 5)
!ok

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

SELECT  ST_ClosestPoint('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))', 'POINT(4 2)');
EXPR$0
POINT (4 2)
!ok

SELECT  ST_ClosestPoint('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))', 'POINT(5 7)');
EXPR$0
POINT (5 5)
!ok

SELECT ST_ClosestPoint('LINESTRING(1 1, 1 5))', 'LINESTRING(2 1, 2 5))') A,
       ST_ClosestPoint('LINESTRING(1 1, 1 5))', 'LINESTRING(2 5, 2 1))') B;
A, B
POINT (1 1), POINT (1 5)
!ok

SELECT ST_ClosestPoint('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))',
                       'POLYGON((13 2, 15 0, 13 4, 13 2))') A,
       ST_ClosestPoint('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))',
                       'POLYGON((13 4, 13 2, 15 0, 13 4))') B;
A, B
POINT (10 2), POINT (10 4)
!ok

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

SELECT ST_FurthestCoordinate('POINT(0 0)', 'POLYGON((2 2, 10 0, 10 5, 0 5, 2 2))');
EXPR$0
POINT (10 5)
!ok

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

SELECT ST_FurthestCoordinate('POINT(5 2.5)', 'POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))');
EXPR$0
MULTIPOINT ((0 0), (10 0), (10 5), (0 5))
!ok

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

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

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

SELECT ST_Length('POLYGON((1 2, 4 2, 4 6, 1 6, 1 2))');
EXPR$0
14.0
!ok

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

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

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

SELECT ST_LocateAlong('LINESTRING(1 1, 5 4)', 0.5, 2.0);
EXPR$0
MULTIPOINT ((1.8 4.1))
!ok

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

SELECT ST_LocateAlong('POLYGON((1 1, 4 1, 4 3, 1 3, 1 1))', 0.5, -1.0);
EXPR$0
MULTIPOINT ((2.5 0), (5 2), (2.5 4), (0 2))
!ok

SELECT ST_LocateAlong('GEOMETRYCOLLECTION(
    LINESTRING(1 4, 5 4, 5 2),
    POLYGON((1 1, 4 1, 4 3, 1 3, 1 1)))', 2.0, 1.0);
EXPR$0
MULTIPOINT ((9 5), (6 0), (7 2), (3 5), (-2 2), (2 -1))
!ok

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

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

SELECT ST_LongestLine('POLYGON ((0 1, 1 1, 1 0, 0 0, 0 1))', 'LINESTRING (1 3, 4 3)');
EXPR$0
LINESTRING (0 0, 4 3)
!ok

SELECT ST_LongestLine('POLYGON ((0 1, 1 1, 1 0, 0 0, 0 1))', 'POINT (3 2)');
EXPR$0
LINESTRING (0 0, 3 2)
!ok

SELECT ST_LongestLine('MULTIPOLYGON (((0 1, 1 1, 1 0, 0 0, 0 1)), ((2 3, 2 2, 3 3, 2 3)))', 'POINT (3 2)');
EXPR$0
LINESTRING (0 0, 3 2)
!ok

SELECT ST_LongestLine('POLYGON ((1 3, 0 0, 3 2, 1 3))', 'POLYGON ((1 3, 0 0, 3 2, 1 3))');
EXPR$0
LINESTRING (0 0, 3 2)
!ok

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

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

SELECT ST_MaxDistance('POLYGON ((0 1, 1 1, 1 0, 0 0, 0 1))', 'LINESTRING (1 3, 4 3)');
EXPR$0
5.0
!ok

SELECT ST_MaxDistance('POLYGON ((0 1, 1 1, 1 0, 0 0, 0 1))', 'POINT (3 2)');
EXPR$0
3.605551275463989
!ok

SELECT ST_MaxDistance('POLYGON ((1 3, 0 0, 3 2, 1 3))', 'POLYGON ((1 3, 0 0, 3 2, 1 3))');
EXPR$0
3.605551275463989
!ok

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

SELECT ST_Perimeter('POLYGON((1 2, 4 2, 4 6, 1 6, 1 2))');
EXPR$0
14.0
!ok

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

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

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

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

SELECT ST_PROJECTPOINT('POINT(0 4)', 'LINESTRING(0 0, 4 4)');
EXPR$0
POINT (2 2)
!ok

SELECT ST_PROJECTPOINT('POINT(2 4)', 'MULTILINESTRING ((0 0, 4 0), (0 2, 4 2))');
EXPR$0
POINT (2 2)
!ok

SELECT ST_PROJECTPOINT('POINT(0 4)', 'LINESTRING (0 0, 4 4)');
EXPR$0
POINT (2 2)
!ok

SELECT ST_PROJECTPOINT('POINT(0 4)', 'POLYGON ((2 4, 0 2, 3 0, 2 4))');
EXPR$0
null
!ok

#### 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(geom, blade) Splits *geom* by *blade*

SELECT ST_Split('LINESTRING(0 0, 10 0)', 'POINT(5 0)');
EXPR$0
MULTILINESTRING ((0 0, 5 0), (5 0, 10 0))
!ok

SELECT ST_Split('MULTILINESTRING ((5 0, 5 10), (0 5, 10 5))', 'POINT(5 5)');
EXPR$0
MULTILINESTRING ((5 0, 5 5), (5 5, 5 10), (0 5, 5 5), (5 5, 10 5))
!ok

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

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

SELECT ST_Split('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))', 'LINESTRING(2 0, 2 5)');
EXPR$0
MULTIPOLYGON (((0 0, 0 5, 2 5, 2 0, 0 0)), ((2 0, 2 5, 5 5, 5 0, 2 0)))
!ok

SELECT ST_Split('MULTIPOLYGON (((0 0, 0 5, 2 5, 2 0, 0 0)), ((2 0, 2 5, 5 5, 5 0, 2 0)))', 'LINESTRING(0 2, 5 2)');
EXPR$0
MULTIPOLYGON (((0 0, 0 2, 2 2, 2 0, 0 0)), ((0 2, 0 5, 2 5, 2 2, 0 2)), ((2 0, 2 2, 5 2, 5 0, 2 0)), ((2 2, 2 5, 5 5, 5 2, 2 2)))
!ok

#### 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 ST_ReducePrecision(ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
  743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)), 0.01) As wgs_geom;
WGS_GEOM
POLYGON ((-71.18 42.39, -71.18 42.39, -71.18 42.39, -71.18 42.39, -71.18 42.39))
!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*
SELECT ST_ConstrainedDelaunay('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))');
EXPR$0
MULTIPOLYGON (((0 0, 0 10, 10 10, 0 0)), ((10 10, 10 0, 0 0, 10 10)))
!ok

SELECT ST_ConstrainedDelaunay('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 1);
EXPR$0
MULTILINESTRING ((0 0, 0 10), (0 0, 10 0), (0 0, 10 10), (0 10, 10 10), (10 0, 10 10))
!ok

# ST_Delaunay(geom [, flag [, quality ]]) Computes a Delaunay triangulation based on points
SELECT ST_Delaunay('MULTIPOINT((1 1), (0 4), (3 2), (3 7), (4 5), (5 2), (7 1), (7 6), (8 4), (5 8), (1 8), (4 0))');
EXPR$0
MULTIPOLYGON (((1 8, 0 4, 3 7, 1 8)), ((1 8, 3 7, 5 8, 1 8)), ((5 8, 3 7, 4 5, 5 8)), ((5 8, 4 5, 7 6, 5 8)), ((7 6, 4 5, 8 4, 7 6)), ((8 4, 4 5, 5 2, 8 4)), ((8 4, 5 2, 7 1, 8 4)), ((4 0, 7 1, 5 2, 4 0)), ((4 0, 5 2, 3 2, 4 0)), ((4 0, 3 2, 1 1, 4 0)), ((1 1, 3 2, 0 4, 1 1)), ((0 4, 3 2, 4 5, 0 4)), ((0 4, 4 5, 3 7, 0 4)), ((4 5, 3 2, 5 2, 4 5)))
!ok

SELECT ST_Delaunay('MULTIPOINT((1 1), (0 4), (3 2), (3 7), (4 5), (5 2), (7 1), (7 6), (8 4), (5 8), (1 8), (4 0))', 1);
EXPR$0
MULTILINESTRING ((0 4, 1 1), (0 4, 1 8), (0 4, 3 2), (0 4, 3 7), (0 4, 4 5), (1 1, 3 2), (1 1, 4 0), (1 8, 3 7), (1 8, 5 8), (3 2, 4 0), (3 2, 4 5), (3 2, 5 2), (3 7, 4 5), (3 7, 5 8), (4 0, 5 2), (4 0, 7 1), (4 5, 5 2), (4 5, 5 8), (4 5, 7 6), (4 5, 8 4), (5 2, 7 1), (5 2, 8 4), (5 8, 7 6), (7 1, 8 4), (7 6, 8 4))
!ok

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

#### Geometry aggregate functions

# ST_Accum(geom) Accumulates *geom* into an array
SELECT ST_Accum(tmp.geom)
FROM (
  SELECT ST_GeomFromText('POINT(1 1)') as geom
  UNION ALL
  SELECT ST_GeomFromText('POINT(2 2)') as geom
) AS tmp;
EXPR$0
[POINT (1 1), POINT (2 2)]
!ok

# ST_Collect(geom) Collects *geom* into a GeometryCollection
SELECT ST_Collect(tmp.geom)
FROM (
  SELECT ST_GeomFromText('POINT(1 1)') as geom
  UNION ALL
  SELECT ST_GeomFromText('POINT(2 2)') as geom
) AS tmp;
EXPR$0
GEOMETRYCOLLECTION (POINT (1 1), POINT (2 2))
!ok

# ST_Union(geom) Computes the union of geometries
SELECT ST_Union(tmp.geom)
FROM (
  SELECT ST_GeomFromText('POINT(1 1)') as geom
  UNION ALL
  SELECT ST_GeomFromText('POINT(2 2)') as geom
) AS tmp;
EXPR$0
MULTIPOINT ((1 1), (2 2))
!ok

# ST_Union agg function is not implemented
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);
EXPR$0
GEOMETRYCOLLECTION (POINT (-2 3), LINESTRING (5 5, 10 10), POLYGON ((-7 4.2, -7.1 4.2, -7.1 4.3, -7 4.2)))
!ok

# 3d example - sort of supports 3d (and with mixed dimensions!)
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
GEOMETRYCOLLECTION (POINT (-2 3), LINESTRING (5 5, 10 10), POLYGON ((-7 4.2, -7.1 4.2, -7.1 4.3, -7 4.2)))
!ok

# 3d example not mixing dimensions
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
GEOMETRYCOLLECTION Z(POINT Z(-2 3 1), LINESTRING Z(5 5 5, 10 10 10), 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