Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Spatial Data

A geometry value is a point, a curve, a surface, a solid or a collection of them, with an SRID naming its spatial reference system. Its type is ST_GEOMETRY or one of its 26 subtypes, the type family of SQL/MM Spatial. Its functions read and write the well-known text (WKT) and binary (WKB) forms, observe a value's coordinates and bounds, relate two values by their intersection matrix, combine them, buffer and measure them, and measure the planar distance between them. An index of the method rtree (extension) on a GEOMETRY column finds the rows a relationship filter or join matches, and the rows nearest a point, without reading the table.

The types and their functions are built in, so every database has them and nothing is registered before its files are opened. For an introduction with complete examples, read the Spatial Guide.

The examples on this page are run in order, by the user ANN, on a table of places on a field, in a local grid whose unit is the metre:

CREATE SCHEMA AUTHORIZATION ANN;
CREATE TABLE PLACES (ID INTEGER PRIMARY KEY, NAME VARCHAR(20), SHAPE GEOMETRY(0));
INSERT INTO PLACES VALUES
    (1, 'Well', ST_POINT(2, 2)),
    (2, 'Road', ST_GEOMFROMTEXT('LINESTRING (-5 -5, 15 -5)')),
    (3, 'Field', ST_GEOMFROMTEXT('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 6 4, 6 6, 4 6, 4 4))')),
    (4, 'Pond', ST_POINT(5, 5)),
    (5, 'Gate', CAST('POINT (10 5)' AS GEOMETRY(0))),
    (6, 'Lost', ST_GEOMFROMTEXT('POINT EMPTY')),
    (7, 'Unmapped', NULL);
COMMIT;
OK
OK
OK
OK

The geometry types

ST_GEOMETRY holds a value of any type of the family and any SRID, a signed 32-bit integer. GEOMETRY is another name of it. Each type takes an SRID as its parameter, as ST_POINT(4326), and then holds only values of that SRID, empty ones included. The parameter is an integer from 0 to 2147483647. The SRID is a label stored as given. It is not looked up, 0 does not mean WGS 84, and no coordinates are transformed or axes exchanged.

The types form a hierarchy. A value of a type is a value of each type above it, and the types in italics hold no value of their own:

Type Subtypes
ST_GEOMETRY ST_POINT, ST_CURVE, ST_SURFACE, ST_SOLID, ST_GEOMCOLLECTION
ST_CURVE ST_LINESTRING, ST_CIRCULARSTRING, ST_GEODESICSTRING, ST_ELLIPTICALCURVE, ST_NURBSCURVE, ST_CLOTHOID, ST_SPIRALCURVE, ST_COMPOUNDCURVE
ST_CIRCULARSTRING ST_CIRCLE
ST_SURFACE ST_CURVEPOLYGON, ST_POLYHDRLSURFACE, ST_COMPOUNDSURFACE
ST_CURVEPOLYGON ST_POLYGON
ST_POLYGON ST_TRIANGLE
ST_POLYHDRLSURFACE ST_TIN
ST_SOLID ST_BREPSOLID
ST_GEOMCOLLECTION ST_MULTIPOINT, ST_MULTICURVE, ST_MULTISURFACE
ST_MULTICURVE ST_MULTILINESTRING
ST_MULTISURFACE ST_MULTIPOLYGON

A column, variable or parameter of a type takes a value of that type or of a type below it. A value of a type above it is stored only through TREAT(value AS type), which raises SQLSTATE 0D000 of a value of another type. value IS OF (type, ...) tests a value's type, and IS OF (ONLY type) that type alone. CAST(value AS type) converts within the hierarchy as SQL/MM Spatial's Table 4 says: a point becomes a multipoint of one point, a collection of one element becomes that element, an empty value becomes the empty value of the target, and anything else raises SQLSTATE 2FF08. NEW type(...) is refused of a type in italics.

ST_GEOMETRYTYPE names a value's most specific type in the spelling of SQL/MM Spatial, case-sensitive, as ST_Point. The XY values of seven types, ST_POINT, ST_LINESTRING, ST_POLYGON, ST_MULTIPOINT, ST_MULTILINESTRING, ST_MULTIPOLYGON and ST_GEOMCOLLECTION, are related to each other. A value of every instantiable type, with Z or M coordinates too, is read and written as text, WKB and GML, and the curves and surfaces have the methods of Curves and Surfaces.

CREATE TABLE MARKS (ID INTEGER, SPOT ST_POINT(0), ROUTE ST_CURVE(0));
INSERT INTO MARKS VALUES (1, ST_POINT(1, 1), TREAT(ST_GEOMFROMTEXT('LINESTRING (0 0, 3 4)') AS ST_CURVE));
INSERT INTO MARKS (ID, SPOT) VALUES (2, ST_GEOMFROMTEXT('POINT (2 2)'));
INSERT INTO MARKS (ID, SPOT) VALUES (2, TREAT(ST_GEOMFROMTEXT('POINT (2 2)') AS ST_POINT));
SELECT ID, ST_GEOMETRYTYPE(ROUTE) AS ROUTE_TYPE, ROUTE IS OF (ST_LINESTRING) AS IS_LINE FROM MARKS ORDER BY ID;
VALUES ST_GEOMETRYTYPE(CAST(ST_POINT(1, 2) AS ST_MULTIPOINT));
VALUES CAST(ST_POINT(1, 2) AS ST_LINESTRING);
ROLLBACK;
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): cannot assign a ST_GEOMETRY value to column SPOT (ST_POINT(0)) [ISO/IEC 9075:1992 §13.8 SR6; ISO/IEC 9075:1992 §9.2 SR2] (at character 40)
OK
           ID | ROUTE_TYPE                       | IS_LINE
--------------+----------------------------------+---------
            1 | ST_LineString                    | TRUE
            2 | NULL                             | NULL
(2 rows)

----------------------------------
 ST_MultiPoint
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF08): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §4.2.1.5 Table 4] a value of ST_Point is not cast to ST_LineString
OK
SELECT ID, ST_ASTEXT(SHAPE) AS WKT, ST_GEOMETRYTYPE(SHAPE) AS KIND, ST_ISEMPTY(SHAPE) AS IS_EMPTY FROM PLACES ORDER BY ID;
           ID | WKT                                                          | KIND                             |     IS_EMPTY
--------------+--------------------------------------------------------------+----------------------------------+--------------
            1 | POINT (2 2)                                                  | ST_Point                         |            0
            2 | LINESTRING (-5 -5, 15 -5)                                    | ST_LineString                    |            0
            3 | POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 6 4, 6 6, 4 6, 4 4)) | ST_Polygon                       |            0
            4 | POINT (5 5)                                                  | ST_Point                         |            0
            5 | POINT (10 5)                                                 | ST_Point                         |            0
            6 | POINT EMPTY                                                  | ST_Point                         |            1
            7 | NULL                                                         | NULL                             |         NULL
(7 rows)

An empty value, as POINT EMPTY, is a known geometry with no coordinates, and keeps its kind and SRID. NULL is no value at all.

A value of another SRID is refused by a GEOMETRY(srid) column, as is a parameter out of range:

INSERT INTO PLACES VALUES (8, 'Elsewhere', ST_POINT(1, 1, 4326));
CREATE TABLE MAPS (ID INTEGER, AREA GEOMETRY(2147483648));
ROLLBACK;
turborel: SQLCODE -301 (SQLSTATE 22000): invalid geometry: SRID 4326 does not match the column's required SRID 0
turborel: SQLCODE -103 (SQLSTATE 2A000): GEOMETRY accepts at most one parameter: an SRID from 0 to 2147483647 (at character 36)
OK

CAST(text AS GEOMETRY) reads WKT as ST_GEOMFROMTEXT does, of any type and coordinate dimension, with SRID 0, and CAST(text AS GEOMETRY(srid)) with that SRID. A cast of a geometry to GEOMETRY(srid) checks its SRID and changes nothing. A character string is not stored in a GEOMETRY column by assignment, so WKT is read with a constructor or CAST.

The ordering of the types is EQUALS ONLY BY RELATIVE, with ST_ORDERINGEQUALS. = and <> compare two values by it: they are equal if they have one SRID and are written alike, or are both empty. This is not the spatial equality of ST_Equals, and a line reversed is a different value. Byte order and the sign of a zero do not count. ORDER BY, GROUP BY, DISTINCT, < and the other orderings are refused of a geometry value, and so are a PRIMARY KEY, UNIQUE or FOREIGN KEY of a geometry column, as its values have no key of their own.

VALUES CASE WHEN ST_GEOMFROMTEXT('LINESTRING (0 0, 1 1)') = ST_GEOMFROMTEXT('LINESTRING (1 1, 0 0)')
    THEN 'equal' ELSE 'different' END;
SELECT DISTINCT SHAPE FROM PLACES;

-----------
 different
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): DISTINCT compares values of ST_GEOMETRY, which has an ordering of EQUALS ONLY (ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1.65, ST_ORDERINGEQUALS) [ISO/IEC 9075-2:2023 §9.12 SR3]

Routines

Routine Result
ST_POINT(x, y [, srid]) (extension) A point of two finite numbers, SRID 0 by default
ST_MAKEENVELOPE(min_x, min_y, max_x, max_y [, srid]) (extension) A rectangular polygon of finite bounds, each minimum less than its maximum, SRID 0 by default
ST_GEOMFROMTEXT(text [, srid]) The geometry of a WKT text, a character string or CLOB, SRID 0 by default
ST_GEOMFROMWKB(binary [, srid]) The geometry of a WKB string, a binary string or BLOB, of any type and coordinate dimension, SRID 0 by default
ST_ASTEXT(geometry) Its WKT, without its SRID, a CHARACTER LARGE OBJECT
ST_ASBINARY(geometry) Its little-endian WKB, without its SRID, a BINARY LARGE OBJECT
ST_SRID(geometry) Its SRID
ST_SETSRID(geometry, srid) (extension) The same coordinates with another SRID
ST_X(point), ST_Y(point) Its X or Y, DOUBLE PRECISION; NULL of POINT EMPTY
ST_MINX(geometry), ST_MAXX(geometry) (extension) Its least or greatest X, DOUBLE PRECISION; NULL of an empty value
ST_MINY(geometry), ST_MAXY(geometry) (extension) Its least or greatest Y, DOUBLE PRECISION; NULL of an empty value
ST_ISEMPTY(geometry) INTEGER 1 if it has no coordinates, else 0
ST_GEOMETRYTYPE(geometry) Its most specific type's name, as ST_Point
ST_ORDERINGEQUALS(a, b) INTEGER 0 if a and b are equal by the ordering of the types, else 1
ST_INTERSECTS(a, b) INTEGER 1 if a and b share a point, else 0; NULL if either is empty
ST_DISJOINT(a, b) INTEGER 1 if a and b share no point, else 0; NULL if either is empty
ST_EQUALS(a, b), ST_TOUCHES, ST_CROSSES, ST_WITHIN, ST_CONTAINS, ST_OVERLAPS INTEGER 1 or 0 by the intersection matrix, see Relationships
ST_RELATE(a, b, matrix) INTEGER 1 if their intersection matrix matches the nine characters of matrix, else 0
ST_INTERSECTION(a, b), ST_UNION, ST_DIFFERENCE, ST_SYMDIFFERENCE The point set the operation leaves, see Set operations
ST_BUFFER(geometry, distance [, unit]) Every point within the distance, a surface
ST_CONVEXHULL(geometry) The least convex value holding it
ST_BOUNDARY(geometry) Its boundary
ST_ISVALID(geometry), ST_ISSIMPLE(geometry) INTEGER 1 if it is well formed, or has no anomalous point, else 0
ST_ISRING(curve) INTEGER 1 if it is simple and closed, else 0
ST_LENGTH(curves [, unit]) The length of a curve or multicurve, DOUBLE PRECISION
ST_AREA(surfaces [, unit]), ST_PERIMETER(surfaces [, unit]) The area or boundary length of a surface or multisurface, DOUBLE PRECISION
ST_CENTROID(surfaces), ST_POINTONSURFACE(surfaces) Its centroid, or a point inside it
ST_BDPOLYFROMTEXT(text [, srid]), ST_BDPOLYFROMWKB(binary [, srid]) The polygon a multilinestring's rings bound
ST_BDMPOLYFROMTEXT(text [, srid]), ST_BDMPOLYFROMWKB(binary [, srid]) The multipolygon a multilinestring's rings bound
ST_DISTANCE(a, b [, unit]) The least planar distance between them, DOUBLE PRECISION; NULL if either is empty
ST_TRANSFORM(geometry, srid) The value moved to the system of the SRID, see Transform
ST_NUMGEOMETRIES(collection) The count of its elements, INTEGER
ST_GEOMETRYN(collection, n) Its nth element, from 1; NULL and the warning 01F01 if there is none
ST_GEOMETRIES(collection) Its elements, an ARRAY of the collection's element type

The routines are scalar functions, called where SQL.FUNCTION_CALLS is ON, its default. Each gives NULL if an argument is NULL.

ST_X and ST_Y take a point only. ST_SETSRID relabels a value and is right only where its coordinates are already of that reference system. ST_MAKEENVELOPE makes a polygon of four numbers, and is not SQL/MM's ST_Envelope, which derives a value's envelope. Reversed bounds, or a zero-width rectangle, are refused, and ST_POINT makes a single point:

SELECT ID, ST_X(SHAPE) AS X, ST_Y(SHAPE) AS Y FROM PLACES WHERE ID IN (1, 5, 6, 7) ORDER BY ID;
SELECT ST_MINX(SHAPE) AS MIN_X, ST_MINY(SHAPE) AS MIN_Y, ST_MAXX(SHAPE) AS MAX_X, ST_MAXY(SHAPE) AS MAX_Y
    FROM PLACES WHERE ID = 3;
SELECT ST_X(SHAPE) FROM PLACES WHERE ID = 2;
VALUES ST_ASTEXT(ST_MAKEENVELOPE(0, 0, 0, 5));
           ID |                        X |                        Y
--------------+--------------------------+--------------------------
            1 |                      2E0 |                      2E0
            5 |                      1E1 |                      5E0
            6 |                     NULL |                     NULL
            7 |                     NULL |                     NULL
(4 rows)
                    MIN_X |                    MIN_Y |                    MAX_X |                    MAX_Y
--------------------------+--------------------------+--------------------------+--------------------------
                      0E0 |                      0E0 |                      1E1 |                      1E1
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22000): invalid geometry: expected a POINT geometry
turborel: SQLCODE -301 (SQLSTATE 22000): invalid geometry: a rectangle needs a minimum strictly below its maximum on both axes

Methods

Each routine of a value is also a method of its type, invoked as value.ST_X(), and a type has the methods of its supertypes. A method is found by the declared type of the value. ST_X of a column declared ST_GEOMETRY is refused until TREAT makes it an ST_POINT. A method that a subtype overrides runs as the value's most specific type chooses: ST_GEOMETRIES of an ST_MULTIPOINT returns an ST_POINT ARRAY, whose elements have ST_X. (value AS type).method() runs the method of a supertype instead, here ST_GEOMCOLLECTION's, whose ARRAY is of ST_GEOMETRY, so its elements have no ST_X. value.SPECIFICTYPE names the value's most specific type:

CREATE TABLE GROUPS (ID INTEGER, PTS ST_MULTIPOINT);
INSERT INTO GROUPS VALUES (1, TREAT(ST_GEOMFROMTEXT('MULTIPOINT ((1 2), (3 4))') AS ST_MULTIPOINT));
SELECT PTS.ST_NUMGEOMETRIES() AS N, PTS.ST_GEOMETRIES()[2].ST_X() AS X, PTS.SPECIFICTYPE AS T FROM GROUPS;
SELECT (PTS AS ST_GEOMCOLLECTION).ST_GEOMETRIES()[1].ST_X() FROM GROUPS;
SELECT (PTS AS ST_GEOMCOLLECTION).ST_GEOMETRIES()[1].ST_GEOMETRYTYPE() AS T FROM GROUPS;
ROLLBACK;
OK
OK
            N |                        X | T
--------------+--------------------------+--------------------------------------------------------------
            2 |                      3E0 | "TURBOREL"."INFORMATION_SCHEMA"."ST_MULTIPOINT"
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): ST_Geometry and its supertypes have no method ST_X of 0 arguments [ISO/IEC 9075-2:2023 §9.18 SR10b, §9.19 SR3] (at character 52)
 T
----------------------------------
 ST_Point
(1 row)
OK

Constraints

The routines are deterministic, so a CHECK, an assertion, a domain's constraint or a generated column may call them. SQL.FUNCTION_CALLS is checked as the constraint is written, by CREATE or ALTER. A constraint written while the setting was ON keeps its calls when the setting is OFF, in later statements, after the database is opened again or recovered, and on a replica that replays it. A new constraint written while the setting is OFF is refused (SQLCODE -102):

CREATE TABLE SITES (ID INTEGER, P ST_POINT CHECK (ST_X(P) >= 0));
COMMIT;
SET SQL.FUNCTION_CALLS = 'OFF';
INSERT INTO SITES VALUES (1, NEW ST_POINT(2, 3));
INSERT INTO SITES VALUES (2, NEW ST_POINT(-1, 3));
ALTER TABLE SITES ADD CONSTRAINT NORTH CHECK (ST_Y(P) >= 0);
SET SQL.FUNCTION_CALLS = 'ON';
ALTER TABLE SITES ADD CONSTRAINT NORTH CHECK (ST_Y(P) >= 0);
SET SQL.FUNCTION_CALLS = 'OFF';
INSERT INTO SITES VALUES (3, NEW ST_POINT(1, -3));
SELECT ID FROM SITES;
ROLLBACK;
SET SQL.FUNCTION_CALLS = 'ON';
OK
OK
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.SITES_CHECK1 of ANN.SITES is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
turborel: SQLCODE -102 (SQLSTATE 2A000): CHECK ANN.NORTH: no function of the schema is of this name, and a call of a function registered is an [EXTENSION] taken while SQL.FUNCTION_CALLS is ON [ISO/IEC 9075-2:2023 §10.4] (at character 22)
OK
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.NORTH of ANN.SITES is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
           ID
--------------
            1
(1 row)
OK
OK

Cast methods

Each instantiable type has a cast method of ST_GEOMETRY, also called as a function of the value. A value of the type, or of a subtype, is returned as it is. ST_TOLINESTRING makes a line string of a curve and ST_TOPOLYGON a polygon of a curve polygon. ST_TOCOMPOUND, ST_TOCOMPSURFACE and ST_TOGEOMCOLL put a curve, a surface or any point, curve or surface in a compound or collection of one element. The multi types are made of a collection's elements, or of one value of their element type, and raise 2FF08 of an element they do not hold. A collection of one element, or a compound curve of one, gives that element cast by the method. Any other value raises 2FF16, unless it is empty, which gives the empty value of the type in the value's SRID.

Method Result type
ST_TOPOINT() ST_Point
ST_TOLINESTRING(), ST_TOCIRCULAR(), ST_TOCIRCLE(), ST_TOGEODESIC() ST_LineString, ST_CircularString, ST_Circle, ST_GeodesicString
ST_TOELLIPTICAL(), ST_TONURBS(), ST_TOCLOTHOID(), ST_TOSPIRAL() ST_EllipticalCurve, ST_NURBSCurve, ST_Clothoid, ST_SpiralCurve
ST_TOCOMPOUND() ST_CompoundCurve
ST_TOCURVEPOLY(), ST_TOPOLYGON(), ST_TOTRIANGLE() ST_CurvePolygon, ST_Polygon, ST_Triangle
ST_TOPOLYHDRLSURF(), ST_TOTIN(), ST_TOCOMPSURFACE() ST_PolyhdrlSurface, ST_TIN, ST_CompoundSurface
ST_TOBREPSOLID() ST_BRepSolid
ST_TOGEOMCOLL() ST_GeomCollection
ST_TOMULTIPOINT(), ST_TOMULTICURVE(), ST_TOMULTILINE() ST_MultiPoint, ST_MultiCurve, ST_MultiLineString
ST_TOMULTISURFACE(), ST_TOMULTIPOLYGON() ST_MultiSurface, ST_MultiPolygon

ST_TONURBSCURVE and ST_TOSPIRALCURVE are the same methods as ST_TONURBS and ST_TOSPIRAL. CAST follows the table of casts instead, so CAST of a line string to ST_POINT raises 2FF08 where ST_TOPOINT raises 2FF16:

VALUES ST_ASTEXT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION (POINT (1 2))').ST_TOPOINT());
VALUES ST_ASTEXT(ST_TOCOMPOUND(ST_GEOMFROMTEXT('LINESTRING (0 0, 1 1)')));
VALUES ST_ASTEXT(ST_TOMULTIPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION (POINT (1 2), POINT (3 4))')));
VALUES ST_ASTEXT(ST_TOPOINT(ST_GEOMFROMTEXT('LINESTRING EMPTY')));
VALUES ST_TOPOINT(ST_GEOMFROMTEXT('LINESTRING (0 0, 1 1)'));

--------------------------------------------------------------
 POINT (1 2)
(1 row)

--------------------------------------------------------------
 COMPOUNDCURVE ((0 0, 1 1))
(1 row)

--------------------------------------------------------------
 MULTIPOINT ((1 2), (3 4))
(1 row)

--------------------------------------------------------------
 POINT EMPTY
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF16): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1.55] not an empty set

Constructors and mutators

NEW ST_X(...) builds a value of the type ST_X. Every type takes its text, binary or GML form with an SRID, 0 if not given. ST_POINT takes x and y with z and m if given; a null x and y make the empty point. A curve takes its points, a circular string also a center, a radius and two angles, a circle a center, a radius and a normal. An elliptical curve, a NURBS curve, a clothoid and a spiral take their parameters, a start and an end measure, the unit their lengths are in and an SRID. A surface takes its rings, a polyhedral surface its patches and a collection its elements.

Each attribute a type observes, such as ST_X, ST_POINTS or ST_UAXISLENGTH, is also a mutator of one more argument. It returns the value with that attribute replaced, checked as the type checks it: a circle's three points are not on one line, a polygon's rings are closed, a polyhedral surface's patches share their edges. A length may be given with a unit, converted to the unit of the value's system. The start and end measures of a curve are both set or both removed.

VALUES ST_ASTEXT(NEW ST_POINT(1E0, 2E0, 3E0));
VALUES ST_ASTEXT(ST_POINT(1, 2).ST_X(5E0).ST_M(7E0));
VALUES ST_ASTEXT(NEW ST_LINESTRING(ARRAY[ST_POINT(0, 0), ST_POINT(2, 0)]).ST_POINTS(ARRAY[ST_POINT(0, 0), ST_POINT(3, 3)]));
VALUES ST_ELLIPTICFROMTXT('ELLIPTICALCURVE (REFERENCELOCATION AFFINEPLACEMENT (LOCATION (0 0), REFERENCEDIRECTIONS (VECTOR (1 0), VECTOR (0 1))), UAXISLENGTH 2, VAXISLENGTH 1, STARTANGLE RADIANS (0), ENDANGLE RADIANS (1))').ST_UAXISLENGTH(3E0, 'KILOMETRE').ST_UAXISLENGTH();
VALUES ST_CIRCLEFROMTXT('CIRCLE (1 0, 0 1, -1 0)').ST_POINTS(ARRAY[ST_POINT(0, 0), ST_POINT(1, 1), ST_POINT(2, 2)]);

--------------------------------------------------------------
 POINT Z (1 2 3)
(1 row)

--------------------------------------------------------------
 POINT M (5 2 7)
(1 row)

--------------------------------------------------------------
 LINESTRING (0 0, 3 3)
(1 row)

--------------------------
                      3E3
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF78): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §7.4.3 Description 4d] points are collinear

Text and binary forms

WKT is read in any case, with decimal and exponent coordinates. A MULTIPOINT's members are in parentheses, MULTIPOINT ((1 2), (3 4)). WKB is read in either byte order, mixed orders within a collection too, and written little-endian. POINT EMPTY is written in WKB as a pair of NaN coordinates, the convention of GEOS, and no other coordinate may be infinite or NaN.

Neither form carries the SRID, so a value is exported with ST_SRID beside it and read back with that SRID given to the constructor:

SELECT ST_ASBINARY(SHAPE) AS WKB, ST_SRID(SHAPE) AS SRID FROM PLACES WHERE ID = 1;
SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(SHAPE), ST_SRID(SHAPE))) AS ROUND_TRIP FROM PLACES WHERE ID = 2;
VALUES ST_ASTEXT(CAST('multipoint ((1 2), (3.5e1 4))' AS GEOMETRY));
VALUES ST_SRID(ST_SETSRID(ST_POINT(1, 2), 4326));
 WKB                                                          |         SRID
--------------------------------------------------------------+--------------
 010100000000000000000000400000000000000040                   |            0
(1 row)
 ROUND_TRIP
--------------------------------------------------------------
 LINESTRING (-5 -5, 15 -5)
(1 row)

--------------------------------------------------------------
 MULTIPOINT ((1 2), (35 4))
(1 row)

--------------
         4326
(1 row)

A reader refuses input cut short or followed by more, a line of one point, a polygon ring of fewer than four coordinates or not closed, and a WKB collection holding a member of the wrong kind. Text and WKB carry Z and M coordinates and every one of the 23 instantiable types. The SRID and dimension flags of EWKB are not supported. A reader checks a value's structure, not its topology, so a ring may cross itself and a hole lie outside its shell:

VALUES ST_ASTEXT(ST_GEOMFROMTEXT('LINESTRING (1 1)'));
VALUES ST_ASTEXT(ST_GEOMFROMTEXT('POINT Z (1 2 3)'));
VALUES ST_ASTEXT(ST_GEOMFROMTEXT('POLYGON ((0 0, 4 4, 4 0, 0 4, 0 0))'));
turborel: SQLCODE -301 (SQLSTATE 2FF02): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §7.2] a curve of points that is not empty has at least two

--------------------------------------------------------------
 POINT Z (1 2 3)
(1 row)

--------------------------------------------------------------
 POLYGON ((0 0, 4 4, 4 0, 0 4, 0 0))
(1 row)

The functions take and give text and binary strings of at most 32,767 characters or octets. Larger values are read by the library's codecs, within the limits of Appendix D.

Each instantiable type has its own constructors of each form, which return the value as that type and refuse a value of another with the form's SQLSTATE, 2FF22 for text, 2FF23 for WKB and 2FF24 for GML. ST_WKTTOSQL and ST_WKBTOSQL read any type of SRID 0:

Type Text WKB GML
ST_Point ST_POINTFROMTEXT ST_POINTFROMWKB ST_POINTFROMGML
ST_LineString ST_LINEFROMTEXT ST_LINEFROMWKB ST_LINEFROMGML
ST_CircularString ST_CIRCULARFROMTXT ST_CIRCULARFROMWKB ST_CIRCULARFROMGML
ST_Circle ST_CIRCLEFROMTXT ST_CIRCLEFROMWKB ST_CIRCLEFROMGML
ST_GeodesicString ST_GEODESICFROMTXT ST_GEODESICFROMWKB ST_GEODESICFROMGML
ST_EllipticalCurve ST_ELLIPTICFROMTXT ST_ELLIPTICFROMWKB ST_ELLIPTICFROMGML
ST_NURBSCurve ST_NURBSFROMTXT ST_NURBSFROMWKB ST_NURBSFROMGML
ST_Clothoid ST_CLOTHOIDFROMTXT ST_CLOTHOIDFROMWKB ST_CLOTHOIDFROMGML
ST_SpiralCurve ST_SPIRALFROMTXT ST_SPIRALFROMWKB ST_SPIRALFROMGML
ST_CompoundCurve ST_COMPOUNDFROMTXT ST_COMPOUNDFROMWKB ST_COMPOUNDFROMGML
ST_CurvePolygon ST_CPOLYFROMTEXT ST_CPOLYFROMWKB ST_CPOLYFROMGML
ST_Polygon ST_POLYFROMTEXT ST_POLYFROMWKB ST_POLYFROMGML
ST_Triangle ST_TRIFROMTEXT ST_TRIFROMWKB ST_TRIFROMGML
ST_PolyhdrlSurface ST_PHSFROMTEXT ST_PHSFROMWKB ST_PHSFROMGML
ST_TIN ST_TINFROMTEXT ST_TINFROMWKB ST_TINFROMGML
ST_CompoundSurface ST_COMPSURFFROMTXT ST_COMPSURFFROMWKB ST_COMPSURFFROMGML
ST_BRepSolid ST_BREPFROMTEXT ST_BREPFROMWKB ST_BREPFROMGML
ST_GeomCollection ST_GEOMCOLLFROMTXT ST_GEOMCOLLFROMWKB ST_GEOMCOLLFROMGML
ST_MultiPoint ST_MPOINTFROMTEXT ST_MPOINTFROMWKB ST_MPOINTFROMGML
ST_MultiCurve ST_MCURVEFROMTEXT ST_MCURVEFROMWKB ST_MCURVEFROMGML
ST_MultiLineString ST_MLINEFROMTEXT ST_MLINEFROMWKB ST_MLINEFROMGML
ST_MultiSurface ST_MSURFACEFROMTXT ST_MSURFACEFROMWKB ST_MSURFACEFROMGML
ST_MultiPolygon ST_MPOLYFROMTEXT ST_MPOLYFROMWKB ST_MPOLYFROMGML
VALUES ST_ASTEXT(ST_CIRCULARFROMTXT('CIRCULARSTRING Z (0 0 1, 1 1 2, 2 0 3)', 4326));
VALUES ST_GEOMETRYTYPE(ST_WKBTOSQL(ST_ASBINARY(ST_TRIFROMTEXT('TRIANGLE (0 0, 1 0, 0 1)'))));
VALUES ST_POINTFROMTEXT('LINESTRING (0 0, 1 1)');

--------------------------------------------------------------
 CIRCULARSTRING Z (0 0 1, 1 1 2, 2 0 3)
(1 row)

----------------------------------
 ST_Triangle
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF22): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1.62] ST_POINTFROMTEXT reads a ST_LineString, no ST_Point

GML

ST_ASGML writes a value's GML 3.2, and ST_GMLTOSQL, ST_GEOMFROMGML and each type's FromGML read it. A value's SRID is written as the srsName of its reference system, the organization and its identifier there, or else the system's definition. A reader finds the SRID of an srsName among the systems of the Spatial Information Schema. ST_GEOMFROMGML and the typed readers take an SRID too, which replaces it. A GML representation is at most ST_MaxGeometryAsGML characters, 16777216, and a longer one, written or read, raises 22001. GML has no M coordinate, so a value's measures are left out:

VALUES ST_ASGML(ST_POINTFROMTEXT('POINT (1 2)', 4326));
VALUES ST_SRID(ST_GMLTOSQL('<gml:Point srsName="EPSG:4326"><gml:pos>1 2</gml:pos></gml:Point>'));
VALUES ST_ASTEXT(ST_LINEFROMGML(ST_ASGML(ST_LINEFROMTEXT('LINESTRING M (0 0 5, 3 4 6)'))));

--------------------------------------------------------------
 <gml:Point xmlns:gml="http://www.opengis.net/gml/3.2" srsName="EPSG:4326"><gml:pos>1 2</gml:pos></gml:Point>
(1 row)

--------------
         4326
(1 row)

--------------------------------------------------------------
 LINESTRING (0 0, 3 4)
(1 row)

Curves

Every curve has a length, end points and a linear form. Its arcs, splines, clothoids and spirals are measured exactly or by quadrature, as CONFORMANCE.md describes. A method of a position, as ST_POINTN, gives NULL and the warning 01F01 where there is none.

Method Of Result
ST_LENGTH() ST_Curve Its length, DOUBLE PRECISION
ST_STARTPOINT(), ST_ENDPOINT() ST_Curve Its first or last point
ST_ISCLOSED(), ST_ISRING() ST_Curve INTEGER 1 if its ends meet, and if it is also simple
ST_CURVETOLINE() ST_Curve Its linear form, an ST_LineString
ST_POINTATDISTANCE(d [, unit]) ST_Curve The point d along it; 2FF79 beyond its end
ST_DISTANCETOPOINT(p [, unit]) ST_Curve How far along it p lies; 2FF80 off it
ST_PERPPOINTS(p) ST_Curve The perpendicular projections of p on it, a point, a curve or a collection
ST_POINTS(), ST_NUMPOINTS(), ST_POINTN(n) ST_LineString, ST_CircularString, ST_GeodesicString, ST_Circle Its points, their count, the nth
ST_NUMSEGMENTS(), ST_SEGMENTN(n), ST_MIDPOINTREP() ST_CircularString Its arcs' count, the nth arc, its points with each arc's middle
ST_CENTER(), ST_RADIUS([unit]), ST_BULGE() ST_CircularString of one arc, ST_Circle Its center, radius or signed bulge; 2FF76 of more arcs
ST_STARTANGLE(), ST_ENDANGLE(), ST_BULGENORMAL() ST_CircularString of one arc The angles of its ends about its center, ST_Angle values, and the normal of its bulge, an ST_Vector; 2FF76 of more arcs
ST_NORMAL() ST_Circle The normal of its plane, an ST_Vector
ST_STARTM(), ST_ENDM() ST_EllipticalCurve, ST_NURBSCurve, ST_Clothoid, ST_SpiralCurve Its first or last measure
ST_UAXISLENGTH([unit]), ST_VAXISLENGTH([unit]) ST_EllipticalCurve Its axis lengths
ST_STARTANGLE(), ST_ENDANGLE() ST_EllipticalCurve Its start and end angles, ST_Angle values
ST_REFLOCATION() ST_EllipticalCurve, ST_Clothoid, ST_SpiralCurve Its placement, an ST_AffinePlacement whose reference directions are its axis vectors
ST_DEGREE() ST_NURBSCurve Its degree
ST_CONTROLPOINTS(), ST_KNOTS() ST_NURBSCurve Its control points, an ARRAY of ST_NURBSPoint, and its knots, an ARRAY of ST_Knot
ST_SCALEFACTOR(), ST_STARTDISTANCE([unit]), ST_ENDDISTANCE([unit]) ST_Clothoid Its parameters
ST_STARTCURVATURE(), ST_ENDCURVATURE(), ST_SPIRALTYPE() ST_SpiralCurve Its parameters
ST_CURVES(), ST_NUMCURVES(), ST_CURVEN(n) ST_CompoundCurve Its parts, their count, the nth
CREATE TABLE ROADS (ID INTEGER, PATH ST_CURVE);
INSERT INTO ROADS VALUES (1, ST_COMPOUNDFROMTXT('COMPOUNDCURVE (LINESTRING (0 0, 1 0), CIRCULARSTRING (1 0, 2 1, 3 0))'));
SELECT CAST(PATH.ST_LENGTH() AS DECIMAL(9, 4)) AS LEN, PATH.ST_ISCLOSED() AS CLOSED,
    ST_ASTEXT(PATH.ST_ENDPOINT()) AS LAST_POINT FROM ROADS;
SELECT ST_ASTEXT(TREAT(PATH AS ST_COMPOUNDCURVE).ST_CURVEN(2)) AS ARC FROM ROADS;
SELECT TREAT(PATH AS ST_COMPOUNDCURVE).ST_CURVEN(3) AS NONE FROM ROADS;
ROLLBACK;
OK
OK
         LEN |       CLOSED | LAST_POINT
-------------+--------------+--------------------------------------------------------------
      4.1416 |            0 | POINT (3 0)
(1 row)
 ARC
--------------------------------------------------------------
 CIRCULARSTRING (1 0, 2 1, 3 0)
(1 row)
 NONE
--------------------------------------------------------------
 NULL
(1 row, with a warning: SQLSTATE 01F01)
OK

A unit form takes the name of a linear unit of ST_UNITS_OF_MEASURE last, as the measures of Validity and measures do. Its length, or the distance it is given, is in that unit, converted from the unit of the value's SRID, the metre for SRID 0:

VALUES ST_LINEFROMTEXT('LINESTRING (0 0, 1000 0)').ST_DISTANCETOPOINT(ST_POINT(250, 0), 'KILOMETRE');
VALUES ST_ASTEXT(ST_LINEFROMTEXT('LINESTRING (0 0, 1000 0)').ST_POINTATDISTANCE(5E-1, 'KILOMETRE'));
VALUES ST_CIRCLEFROMTXT('CIRCLE (0 0, 1000 1000, 2000 0)').ST_RADIUS('KILOMETRE');

--------------------------
                   2.5E-1
(1 row)

--------------------------------------------------------------
 POINT (500 0)
(1 row)

--------------------------
                      1E0
(1 row)

The curves' angles, vectors, placements, control points and knots are values of the types of Systems, angles, directions and vectors, whose methods observe them:

VALUES CAST(ST_CIRCULARFROMTXT('CIRCULARSTRING (1 0, 0 1, -1 0)').ST_ENDANGLE().ST_DEGREES() AS DECIMAL(6, 2));
VALUES ST_ASTEXT(ST_CLOTHOIDFROMTXT('CLOTHOID (REFERENCELOCATION AFFINEPLACEMENT (LOCATION (1 2), REFERENCEDIRECTIONS (VECTOR (0 1), VECTOR (-1 0))), SCALEFACTOR 10, STARTDISTANCE 0, ENDDISTANCE 5)').ST_REFLOCATION().ST_LOCATION());
VALUES ST_NURBSFROMTXT('NURBSCURVE (DEGREE 2, CONTROLPOINTS (NURBSPOINT (WEIGHTEDPOINT (0 0), WEIGHT 1), NURBSPOINT (WEIGHTEDPOINT (2 2), WEIGHT 2), NURBSPOINT (WEIGHTEDPOINT (2 0), WEIGHT 1)), KNOTS (KNOT (VALUE 0, MULTIPLICITY 3), KNOT (VALUE 1, MULTIPLICITY 3)))').ST_CONTROLPOINTS()[2].ST_WEIGHT();

----------
   180.00
(1 row)

--------------------------------------------------------------
 POINT (1 2)
(1 row)

--------------------------
                      2E0
(1 row)

Surfaces

Every surface and multisurface has an area, a perimeter, a centroid and a point on it, measured in the coordinate plane. The area and perimeter of a surface with arcs or other curves are those of its curves, and the perimeter of patches is their boundary's length. A polygon has its rings, a polyhedral surface and a TIN their patches, and a compound surface its parts.

Method Of Result
ST_AREA(), ST_PERIMETER() ST_Surface, ST_MultiSurface DOUBLE PRECISION
ST_CENTROID(), ST_POINTONSURFACE() ST_Surface, ST_MultiSurface An ST_Point
ST_EXTERIORRING(), ST_INTERIORRINGS(), ST_NUMINTERIORRING(), ST_INTERIORRINGN(n) ST_CurvePolygon, ST_Polygon, ST_Triangle Its rings, as curves, or line strings of a polygon
ST_CURVEPOLYTOPOLY() ST_CurvePolygon Its linear form, an ST_Polygon
ST_PATCHES(), ST_NUMPATCHES(), ST_PATCHN(n) ST_PolyhdrlSurface, ST_TIN Its patches, polygons or triangles
ST_SURFACES(), ST_NUMSURFACES(), ST_SURFACEN(n) ST_CompoundSurface Its parts
ST_SURFACES(array) ST_CompoundSurface The value with the surfaces of an ARRAY

ST_COMPOUNDSURFACE makes a compound surface of its text, GML or WKB, of a surface, or of an ARRAY of surfaces, with an SRID or 0, and with no argument the empty one. Each surface of the ARRAY must meet the one before it in a curve, else 2FF85, and ST_SURFACES(array) checks its surfaces so too. They are of one SRID, 2FF10 else, and of one coordinate dimension, 2FF25 else.

VALUES CAST(ST_CPOLYFROMTEXT('CURVEPOLYGON (CIRCULARSTRING (0 0, 2 2, 4 0, 2 -2, 0 0))').ST_AREA() AS DECIMAL(9, 4));
VALUES ST_POLYFROMTEXT('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))').ST_PERIMETER();
VALUES ST_ASTEXT(ST_POLYFROMTEXT('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))').ST_INTERIORRINGN(1));
VALUES ST_ASTEXT(ST_POLYFROMTEXT('POLYGON ((0 0, 4 0, 4 2, 0 2, 0 0))').ST_CENTROID());
VALUES ST_COMPOUNDSURFACE(ARRAY[ST_POLYFROMTEXT('POLYGON ((0 0, 1 0, 1 1, 0 0))'),
    ST_POLYFROMTEXT('POLYGON ((0 0, 1 1, 0 1, 0 0))')]).ST_NUMSURFACES();
VALUES ST_COMPOUNDSURFACE(ARRAY[ST_POLYFROMTEXT('POLYGON ((0 0, 1 0, 1 1, 0 0))'),
    ST_POLYFROMTEXT('POLYGON ((5 5, 6 5, 6 6, 5 5))')]);

-------------
     12.5664
(1 row)

--------------------------
                      2E1
(1 row)

--------------------------------------------------------------
 LINESTRING (1 1, 1 2, 2 2, 2 1, 1 1)
(1 row)

--------------------------------------------------------------
 POINT (2 1)
(1 row)

--------------
            2
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF85): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §8.7.3 Description 4b iii] non-contiguous surfaces

TINs

A TIN is built from its elements, of the type ST_TINELEMENT, as their constrained Delaunay triangulation. Random points and spots are its vertices, breaklines and boundaries its edges, and voids and holes are cut out of it. A maximum side length above 0 removes the triangles of longer outer sides. ST_TIN takes the triangles too, and keeps them as they are.

Routine Result
ST_TINELEMENT(type, [id,] tag, geometry) An element, its type one of random points, group spot, boundary, breakline, soft break, control contour, break void, drape void, void, hole and stop line; 2FF74 of a geometry the type does not take
ST_TINELEMENT() The empty element, whose parts are NULL until a mutator gives them
ST_ELEMENTTYPE(e), ST_ELEMENTID(e), ST_ELEMENTTAG(e), ST_ELEMENTGEOMETRY(e) Its parts, and of a second argument the element with that part replaced
e.ST_ISEMPTY() INTEGER 1 if the element is empty, 0 else
ST_TIN(elements, max_side [, srid]) The triangulation of an ARRAY of elements
ST_TIN(triangles, elements, max_side [, srid]) The TIN of an ARRAY of triangles and one of elements
tin.ST_TINELEMENTS(), tin.ST_MAXSIDELENGTH() Its elements, an ARRAY, and its maximum side length
tin.ST_TINELEMENTS(elements, triangulate) The TIN with an ARRAY of elements in place of its own, its triangles those of their triangulation if triangulate is 1; 2FF10 of an element of another SRID
VALUES ST_TIN(ARRAY[ST_TINELEMENT('random points', 'spots',
    ST_MPOINTFROMTEXT('MULTIPOINT Z ((0 0 0), (4 0 1), (4 4 2), (0 4 3), (2 2 5))'))], 0E0).ST_NUMPATCHES();
VALUES ST_ELEMENTTAG(ST_TIN(ARRAY[ST_TINELEMENT('random points', 'spots',
    ST_MPOINTFROMTEXT('MULTIPOINT Z ((0 0 0), (4 0 1), (4 4 2))'))], 0E0).ST_TINELEMENTS()[1]);
VALUES ST_TINFROMTEXT('TIN Z (PATCHES ((0 0 0, 1 0 0, 0 1 0)))').ST_TINELEMENTS(ARRAY[ST_TINELEMENT('random points', 'spots',
    ST_MPOINTFROMTEXT('MULTIPOINT Z ((0 0 0), (4 0 1), (4 4 2), (0 4 3), (2 2 5))'))], 1).ST_NUMPATCHES();
VALUES ST_TINELEMENT('breakline', 'edge', ST_LINEFROMTEXT('LINESTRING (0 0, 1 1)'));
VALUES ST_TINELEMENT().ST_ISEMPTY(), ST_TINELEMENT().ST_ELEMENTTAG('t').ST_ISEMPTY();

--------------
            4
(1 row)

--------------------------------------------------------------
 spots
(1 row)

--------------
            4
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF74): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §17.1.2 Description 2a] a breakline element is of an ST_LineString with z

--------------
            1
            0
(2 rows)

Measures

A value with M coordinates locates its measures. ST_LOCATEBETWEEN(a, b) gives the parts whose M lies from a to b, interpolated along each curve, without Z. ST_LOCATEALONG(m) is ST_LOCATEBETWEEN(m, m). The parts are a multipoint, a multicurve or a collection of both, and the empty point where there are none. A surface's measures are those of its rings:

VALUES ST_ASTEXT(ST_LINEFROMTEXT('LINESTRING M (0 0 0, 10 0 10, 10 10 20)').ST_LOCATEBETWEEN(5E0, 15E0));
VALUES ST_ASTEXT(ST_LINEFROMTEXT('LINESTRING M (0 0 0, 10 0 10, 10 10 20)').ST_LOCATEALONG(12E0));
VALUES ST_LINEFROMTEXT('LINESTRING M (0 0 0, 10 0 10)').ST_LOCATEBETWEEN(2E0, 1E0);

--------------------------------------------------------------
 MULTICURVE M ((5 0 5, 10 0 10, 10 5 15))
(1 row)

--------------------------------------------------------------
 MULTIPOINT M ((10 2 12))
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF02): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1.15] the end measure is less than the start measure

Relationships

ST_INTERSECTS and ST_DISJOINT test the closed geometries, boundaries included and polygon holes excluded. Of the field, the well and the gate on its edge intersect it, and the pond in its hole and the road below it do not. Of an empty value both give NULL:

SELECT P.NAME, ST_INTERSECTS(P.SHAPE, F.SHAPE) AS MEETS, ST_DISJOINT(P.SHAPE, F.SHAPE) AS APART
    FROM PLACES P, PLACES F WHERE F.ID = 3 ORDER BY P.ID;
 NAME                 |        MEETS |        APART
----------------------+--------------+--------------
 Well                 |            1 |            0
 Road                 |            0 |            1
 Field                |            1 |            0
 Pond                 |            0 |            1
 Gate                 |            1 |            0
 Lost                 |         NULL |         NULL
 Unmapped             |         NULL |         NULL
(7 rows)

Both values must have the same SRID, even when empty. A query writes ST_INTERSECTS(a, b) = 1 in a WHERE or ON condition. The tests decide by the exact orientation of the binary64 coordinates as stored, without a tolerance, in the plane, even where the SRID names a geographic reference system. A curve takes part as its linear form, the line string that approximates it:

VALUES ST_INTERSECTS(ST_POINT(5, 5), ST_POINT(5, 5, 4326));
turborel: SQLCODE -301 (SQLSTATE 22000): invalid geometry: spatial operands must have the same SRID

The other relationships come from the intersection matrix of the two values, which records the dimension of each meeting of one value's interior, boundary and exterior with the other's. ST_RELATE compares it with a pattern of nine characters, each T (they meet), F (they do not), 0, 1 or 2 (they meet in points, curves or a surface) or * (anything). ST_EQUALS, ST_TOUCHES, ST_CROSSES, ST_WITHIN, ST_CONTAINS and ST_OVERLAPS are its named patterns. Each gives NULL of an empty value, but two empty values are equal. ST_TOUCHES gives NULL of two point sets, and ST_CROSSES and ST_OVERLAPS of values of dimensions they do not compare. Every one is also a method, as F.SHAPE.ST_CONTAINS(P.SHAPE):

SELECT P.NAME, ST_WITHIN(P.SHAPE, F.SHAPE) AS INSIDE, ST_TOUCHES(P.SHAPE, F.SHAPE) AS EDGE,
       ST_RELATE(P.SHAPE, F.SHAPE, 'FT*******') AS ON_EDGE
    FROM PLACES P, PLACES F WHERE F.ID = 3 AND P.ID IN (1, 4, 5) ORDER BY P.ID;
VALUES ST_RELATE(ST_POINT(1, 1), ST_POINT(1, 1), 'TTX******');
 NAME                 |       INSIDE |         EDGE |      ON_EDGE
----------------------+--------------+--------------+--------------
 Well                 |            1 |            0 |            0
 Pond                 |            0 |            0 |            0
 Gate                 |            0 |            1 |            1
(3 rows)
turborel: SQLCODE -301 (SQLSTATE 2FF04): [ISO/IEC 13249-3:2016 §5.1.45 Description 2c] invalid intersection matrix

Set operations

ST_INTERSECTION, ST_UNION, ST_DIFFERENCE and ST_SYMDIFFERENCE give the point set each operation leaves, closed, of the first value's SRID. A result is the most specific type that holds it, within the types the standard allows for the two operands' types. An empty result is an empty point, and a union with a collection is a collection:

SELECT ST_ASTEXT(ST_INTERSECTION(R.SHAPE, ST_MAKEENVELOPE(0, -6, 3, 0))) AS PIECE,
       ST_AREA(ST_DIFFERENCE(F.SHAPE, ST_MAKEENVELOPE(5, 0, 10, 10))) AS WEST_HALF
    FROM PLACES R, PLACES F WHERE R.ID = 2 AND F.ID = 3;
VALUES ST_ASTEXT(ST_UNION(ST_POINT(2, 2), ST_POINT(5, 5)));
VALUES ST_ASTEXT(ST_INTERSECTION(ST_POINT(2, 2), ST_POINT(5, 5)));
 PIECE                                                        |                WEST_HALF
--------------------------------------------------------------+--------------------------
 LINESTRING (0 -5, 3 -5)                                      |                    4.8E1
(1 row)

--------------------------------------------------------------
 MULTIPOINT ((2 2), (5 5))
(1 row)

--------------------------------------------------------------
 POINT EMPTY
(1 row)

Buffer, hull and boundary

ST_BUFFER gives every point within a distance of a value, a surface whose arcs are polygons inscribed in their circles, a step of π/16 apart. A negative distance leaves no point. ST_CONVEXHULL gives the least convex value holding the value, and ST_BOUNDARY its boundary. Each gives NULL of an empty value. A unit names a row of ST_UNITS_OF_MEASURE, and the distance is converted from it to the linear unit of the SRID's system, the metre for SRID 0. The system is one supplied or one the catalog defines, as the statement sees it:

SELECT ST_CONTAINS(ST_BUFFER(W.SHAPE, 5), P.SHAPE) AS NEAR_WELL,
       ST_ASTEXT(ST_CONVEXHULL(ST_UNION(W.SHAPE, P.SHAPE))) AS HULL
    FROM PLACES W, PLACES P WHERE W.ID = 1 AND P.ID = 4;
VALUES ST_ASTEXT(ST_BOUNDARY(ST_GEOMFROMTEXT('LINESTRING (0 0, 3 0, 3 4)')));
VALUES ST_AREA(ST_BUFFER(ST_POINT(0, 0), 0.001, 'KILOMETRE')) > 3.1;
    NEAR_WELL | HULL
--------------+--------------------------------------------------------------
            1 | LINESTRING (2 2, 5 5)
(1 row)

--------------------------------------------------------------
 MULTIPOINT ((0 0), (3 4))
(1 row)

-------
 TRUE
(1 row)

Validity and measures

ST_ISVALID tells whether a value is well formed, a polygon's rings closed, simple and nested without crossing. ST_ISSIMPLE tells whether it has no anomalous point, as a line crossing itself, and ST_ISRING whether a curve is simple and closed. ST_LENGTH measures a curve or multicurve, and ST_AREA, ST_PERIMETER, ST_CENTROID and ST_POINTONSURFACE a surface or multisurface, each NULL of an empty value. A unit converts the measure, and a geographic SRID, which has no linear unit, refuses one:

SELECT ST_ISVALID(SHAPE) AS VALID, ST_AREA(SHAPE) AS AREA, ST_PERIMETER(SHAPE, 'KILOMETRE') AS PERIMETER_KM,
       ST_ASTEXT(ST_CENTROID(SHAPE)) AS CENTROID
    FROM PLACES WHERE ID = 3;
SELECT ST_LENGTH(SHAPE) AS ROAD, ST_ISSIMPLE(SHAPE) AS SIMPLE FROM PLACES WHERE ID = 2;
VALUES ST_ISVALID(ST_GEOMFROMTEXT('POLYGON ((0 0, 2 2, 2 0, 0 2, 0 0))'));
VALUES ST_AREA(ST_POINT(1, 1));
        VALID |                     AREA |             PERIMETER_KM | CENTROID
--------------+--------------------------+--------------------------+--------------------------------------------------------------
            1 |                    9.6E1 |                   4.8E-2 | POINT (5 5)
(1 row)
                     ROAD |       SIMPLE
--------------------------+--------------
                      2E1 |            1
(1 row)

--------------
            0
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF02): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1] ST_AREA takes no ST_Point

Polygons from rings

ST_BDPOLYFROMTEXT and ST_BDPOLYFROMWKB take a multilinestring's text or binary and give the polygon its rings bound, the outer one the shell and those inside it holes. ST_BDMPOLYFROMTEXT and ST_BDMPOLYFROMWKB give the multipolygon of rings that bound several. A line that is not a closed simple ring is an invalid argument:

VALUES ST_ASTEXT(ST_BDPOLYFROMTEXT('MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))'));
VALUES ST_AREA(ST_BDMPOLYFROMTEXT('MULTILINESTRING ((0 0, 1 0, 1 1, 0 0), (5 5, 6 5, 6 6, 5 5))'));
VALUES ST_BDPOLYFROMTEXT('MULTILINESTRING ((0 0, 1 0, 1 1))');

--------------------------------------------------------------
 POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (2 1, 1 1, 1 2, 2 2, 2 1))
(1 row)

--------------------------
                      1E0
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF02): [ISO/IEC 13249-3:2016 §8.3.9 Description 4c] invalid argument

Distance

ST_DISTANCE gives the least distance between the points of two values, in their coordinates' unit, 0 if they intersect. Of longitude and latitude it gives degrees, not metres along the Earth. Its result is approximate, and a positive distance too small for DOUBLE PRECISION may round to 0, so ST_INTERSECTS is the test of contact. Both values must have the same SRID. A unit gives the distance in that unit, as for ST_BUFFER:

SELECT P.NAME, ST_DISTANCE(P.SHAPE, F.SHAPE) AS FROM_FIELD
    FROM PLACES P, PLACES F WHERE F.ID = 3 ORDER BY P.ID;
VALUES ST_DISTANCE(ST_POINT(-1E308, 0), ST_POINT(1E308, 0));
VALUES ST_DISTANCE(ST_POINT(0, 0), ST_POINT(3000, 4000), 'KILOMETRE');
 NAME                 |               FROM_FIELD
----------------------+--------------------------
 Well                 |                      0E0
 Road                 |                      5E0
 Field                |                      0E0
 Pond                 |                      1E0
 Gate                 |                      0E0
 Lost                 |                     NULL
 Unmapped             |                     NULL
(7 rows)
turborel: SQLCODE -301 (SQLSTATE 22003): geometry distance exceeds the finite binary64 range

--------------------------
                      5E0
(1 row)

Transform

ST_TRANSFORM gives a value moved to the system of another SRID, a system supplied or one the catalog defines. Each point is moved, with its z, and the points that define a curve. A value of the SRID already is given as it is, and an empty one takes the SRID. The unspecified plane, SRID 0, has no system, so a value of it is not moved, nor one of a system the catalog does not know:

VALUES ST_ASTEXT(ST_TRANSFORM(ST_POINT(0, 0, 4326), 3857));
VALUES ST_X(ST_TRANSFORM(ST_POINT(1, 0, 4326), 3857)) BETWEEN 111319 AND 111320;
VALUES ST_TRANSFORM(ST_POINT(5, 5), 4326);

--------------------------------------------------------------
 POINT (0 0)
(1 row)

-------
 TRUE
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF15): [ISO/IEC 13249-3:2016 §5.1.6 Description 3c] the unspecified plane, SRID 0, has no transform to or from another system

Systems, angles, directions and vectors

SQL/MM Spatial has types beside the geometry types, and each is a type of columns, variables and CASTs here. ST_SPATIALREFSYS is a spatial reference system, made of its SRID or of its well-known text. ST_ANGLE is an angle and ST_DIRECTION a direction, a bearing from north, each made of radians, of degrees or gradians, of points or of lines. ST_VECTOR is a displacement of two or three coordinates and an SRID. ST_TINELEMENT, ST_AFFINEPLACEMENT, ST_NURBSPOINT and ST_KNOT are the parts that TINs, placements and NURBS curves are built of. A type's constructor is a function of its name, or NEW, and its observers and mutators are methods. A mutator returns a changed copy:

VALUES NEW ST_ANGLE('D', 90e0).ST_RADIANS();
VALUES NEW ST_DIRECTION('N', NEW ST_ANGLE('D', 30e0), 'E').ST_DEGREESNAZIMUTH(1);
VALUES ST_DIRECTION(ST_POINT(0, 0), ST_POINT(1, 1)).ST_DMSBEARING(0);
VALUES ST_SPATIALREFSYS(4326).ST_ASWKTSRS();
VALUES ST_VECTOR(1e0, 2e0, 3e0, 4326).ST_X(5e0).ST_ASTEXT();
VALUES NEW ST_KNOT(0.5e0, 2).ST_MULTIPLICITY();

--------------------------
     1.5707963267948966E0
(1 row)

--------------------------------------------------------------
 N 30.0
(1 row)

--------------------------------------------------------------
 N 45 0 0 E
(1 row)

--------------------------------------------------------------
 GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]
(1 row)

--------------------------------------------------------------
 VECTOR Z (5 2 3)
(1 row)

--------------
            2
(1 row)

Exact numbers such as 1.0 choose an INTEGER parameter before a DOUBLE PRECISION one, as SQL chooses among routines of one name, so ST_VECTOR(1.0, 2.0, 3.0) is the 2D vector of SRID 3. Write the coordinates as 1e0 or CAST them.

Angles and directions are ordered by their radians, so <, ORDER BY, GROUP BY, MAX and an index work on them, and ST_ORDERINGCOMPARE gives -1, 0 or 1. Systems and vectors are compared for equality alone, by = and ST_ORDERINGEQUALS, and nothing sorts them. The four types of parts are not compared at all. Each value CASTs to and from its well-known text, an angle and a direction to and from a number of radians, and a vector to and from its well-known binary:

CREATE TABLE HEADINGS (ID INTEGER, D ST_DIRECTION);
INSERT INTO HEADINGS VALUES (1, ST_DIRECTION(2e0)), (2, ST_DIRECTION(0.5e0)), (3, CAST('DIRECTION(N 1)' AS ST_DIRECTION));
SELECT ID, CAST(D AS DOUBLE PRECISION) AS RADIANS FROM HEADINGS ORDER BY D;
VALUES CAST(ST_VECTOR(1e0, 2e0) AS VARBINARY(29));
VALUES ST_VECTOR(1e0, 2e0) < ST_VECTOR(3e0, 4e0);
OK
OK
           ID |                  RADIANS
--------------+--------------------------
            2 |                     5E-1
            3 |                      1E0
            1 |                      2E0
(3 rows)

------------------------------------------------------------
 0165000000000000000000F03F0000000000000040
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): the comparison compares values of ST_VECTOR, which has an ordering of EQUALS ONLY (ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §17.2.20, ST_ORDERINGEQUALS) [ISO/IEC 9075-2:2023 §9.14 SR3] (at character 7)

The support routines of §18 work on arrays. ST_MAXDIMENSION, ST_CHECKSRID and ST_GETCOORDDIM look over an array of geometries or vectors, the procedures ST_CHECKNULLS and ST_CHECKCONSECDUPS check one, and ST_TOPOINTARY and its peers check that every element is of one type. ST_DEFINESRS reads its definition before it stores it, and refuses one that is no spatial reference system:

VALUES ST_MAXDIMENSION(ARRAY[ST_POINT(1, 2), ST_GEOMFROMTEXT('LINESTRING (0 0, 1 1)')]);
VALUES ST_CHECKSRID(ARRAY[ST_VECTOR(1e0, 2e0, 4326), ST_VECTOR(3e0, 4e0, 4326)]);
VALUES ST_TOPOINTARY(ARRAY[ST_GEOMFROMTEXT('LINESTRING (0 0, 1 1)')]);
CALL ST_DEFINESRS('GRID', 900001, 'APP', 1, 'GRID[1]', 'a grid');

---------
       1
(1 row)

--------------
         4326
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF08): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §18.1.8 Description 2b i] element is not an ST_Point type
turborel: SQLCODE -301 (SQLSTATE 2FF22): [ISO/IEC 13249-3:2016 §14.1.9] GRID is not PROJCS, GEOGCS or GEOCCS

Routines in space

The routines named ST_3D take z into account. ST_3DLENGTH, ST_3DAREA, ST_3DPERIMETER and ST_3DDISTANCE measure in space, in the linear unit of the value's spatial reference system, or in a unit of ST_UNITS_OF_MEASURE named as a last argument. ST_3DINTERSECTS, ST_3DDISJOINT and ST_3DEQUALS relate two values in space, and ST_3DINTERSECTION, ST_3DUNION, ST_3DDIFFERENCE and ST_3DSYMDIFFERENCE combine them, solids too. Both values must have z.

A solid has ST_3DVOLUME, ST_3DSURFACEAREA, ST_3DCENTROID and ST_3DPOINTONSOLID. A B-rep solid has its exterior and interior shells, which ST_ISSHELL tests: a shell is a closed surface whose patches meet only along their edges. ST_3DISCLOSED, also spelled ST_IS3DCLOSED as the standard spells it too, tests a curve or surface for closure. ST_BREPSOLID makes a B-rep solid of shells, or of its text, GML or WKB with an SRID or 0, and with no argument the empty solid. A triangle has ST_3DSLOPE, its rise over its run. A TIN has ST_MAXSIDELENGTH, which can retriangulate it, and ST_CLIP, which keeps what lies within a polygon. TABLE(ST_TINTABLE(tin)) lists a TIN's points, triangles and elements as rows, and tin.ST_TINTABLE(table, columns..., length) builds a TIN from such rows.

The values here are written as well-known binary. The first line rises 12 over a run of 5:

VALUES ST_3DLENGTH(ST_GEOMFROMWKB(X'01EA03000002000000000000000000000000000000000000000000000000000000000000000000084000000000000010400000000000002840'));
VALUES ST_3DLENGTH(ST_GEOMFROMWKB(X'01EA03000002000000000000000000000000000000000000000000000000000000000000000000084000000000000010400000000000002840'), 'FOOT');
VALUES ST_3DDISTANCE(ST_GEOMFROMWKB(X'01EA03000002000000000000000000000000000000000000000000000000000000000000000000084000000000000010400000000000002840'), ST_GEOMFROMWKB(X'01EA03000002000000000000000000000000000000000000000000000000001440000000000000104000000000000000000000000000001440'));
VALUES ST_3DSLOPE(ST_GEOMFROMWKB(X'01F9030000000000000000000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F0000000000000000000000000000F03F0000000000000000'));
VALUES ST_3DUNION(ST_GEOMFROMWKB(X'01EA03000002000000000000000000000000000000000000000000000000000000000000000000084000000000000010400000000000002840'), ST_POINT(1, 2));

--------------------------
                    1.3E1
(1 row)

--------------------------
       4.26509186351706E1
(1 row)

--------------------------
     1.5811388300841898E0
(1 row)

--------------------------
                      1E0
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2FF72): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §5.1.31 Description 3b] both geometries must be 3D

The rtree index method

CREATE INDEX <index name> ON <table name> ( <column name> ) USING RTREE

An rtree index (extension) of one GEOMETRY(srid) column holds each value's envelope, its bounding rectangle, and omits NULLs. A column of plain GEOMETRY, which may mix SRIDs, cannot have one. The index is kept up to date as rows change, and it is made outside a transaction, so the one the examples began is ended first:

COMMIT;
CREATE INDEX PLACES_SHAPE ON PLACES (SHAPE) USING RTREE;
CREATE TABLE SKETCHES (ID INTEGER PRIMARY KEY, SHAPE GEOMETRY);
COMMIT;
CREATE INDEX SKETCHES_SHAPE ON SKETCHES (SHAPE) USING RTREE;
OK
OK
OK
OK
turborel: SQLCODE -900 (SQLSTATE 0A000): the rtree index method needs GEOMETRY(srid) for a geometry key

CREATE INDEX over rows already in the table builds the index whole. It sorts their envelopes by X, cuts them into slabs, sorts each slab by Y and cuts it into one run for each node under the root, then tiles each run the same way, down to leaves nine tenths full (Sort-Tile-Recursive packing, from the root down). So the nodes under any node tile its region and barely overlap. The sorts spill to temporary files past MEMORY.WORK_BYTES, so a large build stays within it. Rows that other transactions write while the build runs go into the index as usual and are moved into the packed tree before it takes the index's place. Recovery rebuilds an index the same way. A delete or update that leaves a node of the index empty, or under two fifths full, takes it out or merges it with a neighbour, and a node above left sparse by that goes the same way. The nodes above it shrink to what is left under them, so searches of an emptied region stop reading them, and when the top node keeps a single node under it, that one takes its place and the index grows shorter. A freed page is reused once no query that began before can still read it, so a query running meanwhile neither misses a row nor sees one twice:

CREATE TABLE GRID (ID INTEGER PRIMARY KEY, SPOT GEOMETRY(0));
INSERT INTO GRID
    WITH RECURSIVE N (I, X, Y) AS (
        VALUES (CAST(0 AS INTEGER), CAST(0 AS INTEGER), CAST(0 AS INTEGER))
        UNION ALL SELECT I + 1, CASE WHEN Y = 99 THEN X + 1 ELSE X END, CASE WHEN Y = 99 THEN 0 ELSE Y + 1 END
        FROM N WHERE I < 9999)
    SELECT I, ST_POINT(X, Y) FROM N;
COMMIT;
CREATE INDEX GRID_SPOT ON GRID (SPOT) USING RTREE;
SELECT COUNT(*) AS INSIDE FROM GRID WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(10, 10, 19, 19)) = 1;
DELETE FROM GRID WHERE ID < 9000;
COMMIT;
SELECT COUNT(*) AS INSIDE FROM GRID WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(90, 10, 99, 19)) = 1;
OK
OK
OK
OK
               INSIDE
----------------------
                  100
(1 row)
OK
OK
               INSIDE
----------------------
                  100
(1 row)

The planner uses the index where it costs less than reading the table, which these few rows may not, in these forms.

  • A filter ST_INTERSECTS(column, value) = 1, or with the column second, where the value is a constant or a parameter. ST_CONTAINS, ST_WITHIN, ST_TOUCHES, ST_CROSSES, ST_OVERLAPS and ST_EQUALS do the same, as each holds only of values that meet. An empty value equals every empty row, so ST_EQUALS of one reads them all. So does ST_RELATE(column, value, pattern) = 1 of a constant pattern asking an interior or boundary of one to meet one of the other's, as 'T*F**F***' does.
  • A filter ST_DISTANCE(column, value) <= limit, or <, or the limit first, where the limit is a constant or a parameter. With a unit, as ST_DISTANCE(column, value, 'KILOMETRE'), the unit is converted to the column's as the statement is planned. The index reads the envelopes within the limit of the value's.
  • A join on one of these, inner or LEFT, RIGHT or FULL outer, where the join's geometry columns are of one SRID, or the other column has none fixed. Each row of one side probes the other's index, and for a broad inner join an adaptive scan may read it once instead. When both columns have an index, the join may descend the two together, as Joins of two indexes shows. A row whose geometry is NULL or empty, or that no row matches, keeps its place with nulls, and a FULL join then reads the table for the rows no probe matched.
  • ORDER BY ST_DISTANCE(column, value) ASC NULLS LAST with FETCH FIRST, ONLY or WITH TIES, OFFSET too, more sort keys after it and a filter, where the value is a constant or a parameter, which reads the nearest rows first until enough pass the filter. DESC NULLS LAST reads the farthest first. A unit, as ST_DISTANCE(column, value, 'KILOMETRE'), does the same where it converts, unless it is so small that a distance in it could overflow, as FOOT of a metre column. A subquery ordered by the distance to its outer row's geometry reads the index for each row. FETCH FIRST PERCENT, which needs the count of every row, and NULLS FIRST read the table.
SELECT NAME FROM PLACES WHERE ST_INTERSECTS(SHAPE, ST_MAKEENVELOPE(0, 0, 3, 3)) = 1 ORDER BY ID;
SELECT NAME, ST_DISTANCE(SHAPE, ST_POINT(12, 5)) AS DISTANCE FROM PLACES
    ORDER BY ST_DISTANCE(SHAPE, ST_POINT(12, 5)) ASC NULLS LAST, ID FETCH FIRST 2 ROWS ONLY;
SELECT NAME FROM PLACES WHERE ST_DISTANCE(SHAPE, ST_POINT(12, 5), 'KILOMETRE') < 0.003 ORDER BY ID;
SELECT NAME FROM PLACES ORDER BY ST_DISTANCE(SHAPE, ST_POINT(12, 5), 'KILOMETRE') ASC NULLS LAST, ID DESC
    FETCH FIRST 1 ROW ONLY;
SELECT NAME FROM PLACES ORDER BY ST_DISTANCE(SHAPE, ST_POINT(12, 5)) DESC NULLS LAST, ID
    FETCH FIRST 2 ROWS ONLY;
SELECT P.NAME, F.NAME AS IN_FIELD FROM PLACES P LEFT JOIN PLACES F
    ON ST_INTERSECTS(P.SHAPE, F.SHAPE) = 1 AND F.ID = 3 ORDER BY P.ID;
 NAME
----------------------
 Well
 Field
(2 rows)
 NAME                 |                 DISTANCE
----------------------+--------------------------
 Field                |                      2E0
 Gate                 |                      2E0
(2 rows)
 NAME
----------------------
 Field
 Gate
(2 rows)
 NAME
----------------------
 Gate
(1 row)
 NAME
----------------------
 Well
 Road
(2 rows)
 NAME                 | IN_FIELD
----------------------+----------------------
 Well                 | Field
 Road                 | NULL
 Field                | Field
 Pond                 | NULL
 Gate                 | Field
 Lost                 | NULL
 Unmapped             | NULL
(7 rows)

The Pond lies in the Field's hole, and the Gate on its edge.

An envelope finds candidates, and each is checked against the visible row's exact geometry and the whole condition, so the rows are those a scan gives, with the same errors. A query value of another SRID makes the index read every row, so its error is still raised, even of a value no envelope is near, and so does a tree whose distances may overflow. A join or filter whose other conjunct may raise an error keeps the index from skipping rows. ST_DISJOINT, ST_RELATE of a pattern that disjoint values may match, distance comparisons > or >=, and nearest queries descending, NULLS FIRST, WITH TIES or PERCENT read the table. CREATE INDEX gives the index's syntax, and its numeric forms of X and Y columns.

Under SERIALIZABLE, a read through the index counts as reading the rows whose envelopes meet the window it searched, not the whole table, and each probe of a join reads its own window. A distance's window is its value's envelope widened by the limit, and a FULL join also reads the whole table for the rows no probe matched. Another transaction that commits first a row added, removed or moved whose envelope, before or after, meets that window refuses this one's commit, SQLSTATE 40001. A row elsewhere does not. A NULL is in no window, and an empty geometry is where the index keeps it, at the origin. A search of a NULL or empty value reads no row, and one the index answers by reading every entry, ST_EQUALS of an empty value or a value of another SRID, reads the whole table. Past TRANSACTIONS.TRACKED_ROWS windows in a table, a transaction's windows merge into one rectangle holding them all, which may refuse a commit that need not be, never let through one that must not be (Transactions).

-- in one session, SERIALIZABLE, through PLACES_SHAPE:
SELECT NAME FROM PLACES WHERE ST_INTERSECTS(SHAPE, ST_MAKEENVELOPE(0, 0, 3, 3)) = 1;
INSERT INTO PLACES VALUES (8, 'Shed', ST_POINT(40, 40));
-- in another, a point in that window:
INSERT INTO PLACES VALUES (9, 'Stile', ST_POINT(1, 1));
COMMIT;
-- in the first:
COMMIT;
turborel: SQLCODE -911 (SQLSTATE 40001): the engine of ANN.PLACES refused to commit, so the transaction was rolled back: the transaction read rows another transaction changed meanwhile, or changed rows another read: it is not serializable; run it again [X3.135-1986 §8.2]

How the index is priced

Whether the planner reads the index or the table depends on three things the table's statistics keep of a geometry column, once the database's maintenance has gathered them (Background work). A grid of the column's envelopes tells how many rows a window meets, so a broad window is estimated without reading the index. A window the grid puts at no more than 1000 rows is counted by the index itself, exactly. The coordinates a value holds, on average, tell what decoding each row read costs, which a scan pays of every row and the index only of the rows it finds, so polygons of many vertices are read through the index over broader windows than points are. And how closely rows near each other in space lie near each other in the table tells what fetching the rows the index finds costs. Rows inserted in spatial order are fetched cheaply, and the same rows scattered cost a fetch each. Before the statistics are gathered, the index estimates the rows itself and nothing else is priced.

EXPLAIN shows these after the filter's condition: the rows estimated and by what, the mean vertices, the clustering from 0 to 1, and the cost of the cheapest probe of the index against the scan's. Here the same 20,000 points are inserted in order and scattered, and the same window of three tenths of them is read through the index of the first and by a scan of the second. The filter's own estimate is the same figure. The two counts are the first queries to plan with the tables, so the maintenance then gathers their statistics in the background. A program embedding the database gathers them at once, before it goes on, with Database::analyze:

CREATE TABLE LAID (ID INTEGER PRIMARY KEY, SPOT GEOMETRY(0));
CREATE TABLE STREWN (ID INTEGER PRIMARY KEY, SPOT GEOMETRY(0));
INSERT INTO LAID WITH RECURSIVE N (I) AS (VALUES (CAST(0 AS INTEGER)) UNION ALL SELECT I + 1 FROM N WHERE I < 19999)
    SELECT I, ST_POINT(I - FLOOR(I / 200) * 200, FLOOR(I / 200)) FROM N;
INSERT INTO STREWN WITH RECURSIVE N (I) AS (VALUES (CAST(0 AS INTEGER)) UNION ALL SELECT I + 1 FROM N WHERE I < 19999),
    M (I, K) AS (SELECT I, I * 7919 - FLOOR(I * 7919 / 20000) * 20000 FROM N)
    SELECT I, ST_POINT(K - FLOOR(K / 200) * 200, FLOOR(K / 200)) FROM M;
COMMIT;
CREATE INDEX LAID_SPOT ON LAID (SPOT) USING RTREE;
CREATE INDEX STREWN_SPOT ON STREWN (SPOT) USING RTREE;
SELECT COUNT(*) FROM LAID WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(0, 0, 59.5, 100)) = 1;
SELECT COUNT(*) FROM STREWN WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(0, 0, 59.5, 100)) = 1;
OK
OK
OK
OK
OK
OK
OK

----------------------
                 6000
(1 row)

----------------------
                 6000
(1 row)

Once the statistics are gathered, EXPLAIN of the same window shows them. The output below is that of a run, shown rather than run here, since when the background pass ends is not fixed:

EXPLAIN SELECT ID FROM LAID WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(0, 0, 59.5, 100)) = 1;
EXPLAIN SELECT ID FROM STREWN WHERE ST_INTERSECTS(SPOT, ST_MAKEENVELOPE(0, 0, 59.5, 100)) = 1;

 PLAN
--------------------------------------------------------------
 -- bound:
 Project LAID.ID
   Filter ST_INTERSECTS(LAID.SPOT, ASSIGN(ST_MAKEENVELOPE(ASSIGN(0), ASSIGN(0), ASSIGN(59.5), ASSIGN(100)))) = 1
     Scan ANN.LAID
 -- rules: constant_folding
 -- optimized:
 Project LAID.ID
   Filter ST_INTERSECTS(LAID.SPOT, ST_GEOMFROMTEXT('POLYGON ((0 0, 0 100, 59.5 100, 59.5 0, 0 0))', 0)) = 1
     Scan ANN.LAID
 -- physical:
 Project LAID.ID
   Filter ST_INTERSECTS(LAID.SPOT, ST_GEOMFROMTEXT('POLYGON ((0 0, 0 100, 59.5 100, 59.5 0, 0 0))', 0)) = 1  [spatial: est. rows=6070 by statistics, vertices=1, clustering=0.98, probe cost=14421.0, scan cost=24600.0]  (est. rows=6070)
     IndexScan ANN.LAID using LAID_SPOT [rtree]: ST_INTERSECTS(LAID.SPOT, ST_GEOMFROMTEXT('POLYGON ((0 0, 0 100, 59.5 100, 59.5 0, 0 0))', 0)) = 1  (est. rows=6070, cost=14421.0)
(13 rows)
 PLAN
--------------------------------------------------------------
 -- bound:
 Project STREWN.ID
   Filter ST_INTERSECTS(STREWN.SPOT, ASSIGN(ST_MAKEENVELOPE(ASSIGN(0), ASSIGN(0), ASSIGN(59.5), ASSIGN(100)))) = 1
     Scan ANN.STREWN
 -- rules: constant_folding
 -- optimized:
 Project STREWN.ID
   Filter ST_INTERSECTS(STREWN.SPOT, ST_GEOMFROMTEXT('POLYGON ((0 0, 0 100, 59.5 100, 59.5 0, 0 0))', 0)) = 1
     Scan ANN.STREWN
 -- physical:
 Project STREWN.ID
   Filter ST_INTERSECTS(STREWN.SPOT, ST_GEOMFROMTEXT('POLYGON ((0 0, 0 100, 59.5 100, 59.5 0, 0 0))', 0)) = 1  [spatial: est. rows=6026 by statistics, vertices=1, clustering=0.00, probe cost=53291.7, scan cost=24600.0]  (est. rows=6026)
     SeqScan ANN.STREWN  (rows=20000, cost=24600.0)
(13 rows)

The two tables hold the same points, and the window the same 6,000 of them, 6,070 and 6,026 as the grid estimates. The points of LAID were inserted row by row, so the rows the index finds lie together, and a probe costs less than the scan. Those of STREWN were inserted scattered, so each costs a fetch of its own, and the scan is cheaper. The statistics were gathered as the counts first planned with each table.

Joins of two indexes

A join on ST_INTERSECTS, or one of the relationships that hold only of values that meet, whose two geometry columns have one SRID and each an rtree index, may descend both trees together instead of probing one for each row of the other. Pairs of tree nodes whose envelopes are apart are skipped whole, and each pair of entries whose envelopes meet is checked against the visible rows, the other side's filter and the whole condition. The planner takes it when no order is asked of the join, it costs less than the probes, and both tables' statistics are gathered and put each row of one side at no more than a quarter of a row of the other, which, as the estimate runs low, stands for about one. A broader join probes, as a pair costs the traversal more than a probe's descent saves. EXPLAIN calls it SynchronizedSpatialJoin, naming both tables and indexes, and the line below it is the scan the probes would read instead. LEFT, RIGHT and FULL joins are taken too, the rows no pair kept then read with nulls.

CREATE TABLE STOPS (ID INTEGER PRIMARY KEY, SPOT GEOMETRY(0));
CREATE TABLE BLOCKS (ID INTEGER PRIMARY KEY, AREA GEOMETRY(0));
INSERT INTO STOPS WITH RECURSIVE N (I) AS (VALUES (CAST(0 AS INTEGER)) UNION ALL SELECT I + 1 FROM N WHERE I < 39)
    SELECT A.I * 40 + B.I, ST_POINT(B.I, A.I) FROM N A, N B WHERE A.I < 25;
INSERT INTO BLOCKS WITH RECURSIVE N (I) AS (VALUES (CAST(0 AS INTEGER)) UNION ALL SELECT I + 1 FROM N WHERE I < 19)
    SELECT A.I * 20 + B.I, ST_MAKEENVELOPE(B.I * 2 + 0.75, A.I * 2 + 0.75, B.I * 2 + 1.25, A.I * 2 + 1.25) FROM N A, N B WHERE A.I < 10;
COMMIT;
CREATE INDEX STOPS_SPOT ON STOPS (SPOT) USING RTREE;
CREATE INDEX BLOCKS_AREA ON BLOCKS (AREA) USING RTREE;
SELECT COUNT(*) FROM STOPS;
SELECT COUNT(*) FROM BLOCKS;
OK
OK
OK
OK
OK
OK
OK

----------------------
                 1000
(1 row)

----------------------
                  200
(1 row)

Once both tables' statistics are gathered, EXPLAIN of the join shows the traversal. The output below is that of a run, shown rather than run here, since when the background pass ends is not fixed:

EXPLAIN SELECT B.ID, S.ID FROM BLOCKS B JOIN STOPS S ON ST_INTERSECTS(S.SPOT, B.AREA) = 1;

 PLAN
--------------------------------------------------------------
 -- bound:
 Project B.ID, S.ID
   Filter ST_INTERSECTS(S.SPOT, B.AREA) = 1
     Product
       Scan ANN.BLOCKS
       Scan ANN.STOPS
 -- rules: none
 -- optimized:
 Project B.ID, S.ID
   Filter ST_INTERSECTS(S.SPOT, B.AREA) = 1
     Product
       Scan ANN.BLOCKS
       Scan ANN.STOPS
 -- physical:
 Project B.ID, S.ID
   SynchronizedSpatialJoin ANN.BLOCKS using BLOCKS_AREA with ANN.STOPS using STOPS_SPOT [rtree]: ST_INTERSECTS(S.SPOT, B.AREA) = 1  (est. rows each=22, cost=237017.4)
     SeqScan ANN.STOPS  (rows=1000, cost=1000.0)
(17 rows)

The join's rows are the same whichever way it is read:

SELECT COUNT(*) AS PAIRS, COUNT(S.ID) AS PAIRED FROM BLOCKS B LEFT JOIN STOPS S
    ON ST_INTERSECTS(S.SPOT, B.AREA) = 1 AND S.ID < 400;
DROP TABLE STOPS CASCADE;
DROP TABLE BLOCKS CASCADE;
COMMIT;
                PAIRS |               PAIRED
----------------------+----------------------
                  200 |                  100
(1 row, with a warning: SQLSTATE 01003)
OK
OK
OK

The counts ask the database's maintenance for the tables' statistics, and the EXPLAIN is run once it has gathered them. Before, the join has no estimate of its pairs and probes, here the product, as probing every stop for each block would cost more. Each block holds one stop, and the hundred blocks of the stops past 400 keep a null, which COUNT(S.ID) eliminates.

A broad inner join that buffers one side to scan the other's table holds those rows within MEMORY.WORK_BYTES. Past it, they are written to the spill area, and if the scan still costs less, both sides are split by a grid of envelopes and joined a cell at a time, a pair produced only in one cell. The rows are those of the join in memory, with the same errors, in no particular order.

Under SERIALIZABLE, a join of two indexes counts as reading the whole of the side whose tree it pairs from and, of the other, the rows whose envelopes meet the hull of the first side's envelopes.

The Spatial Information Schema

The schema ST_INFORMTN_SCHEMA holds SQL/MM Spatial's views, and SELECT on each is everyone's.

View Rows
ST_GEOMETRY_COLUMNS Each column of a geometry type the user may see, with the name and identifier of the system its type names, as ST_POINT(4326) names WGS 84. Both are NULL of a type that names no system, or one not supplied
ST_SPATIAL_REFERENCE_SYSTEMS The spatial reference systems supplied, each with its name, identifier, organization and well-known text
ST_UNITS_OF_MEASURE The units of length and angle, each with its factor to the metre or the radian
ST_SIZINGS Each implementation-defined limit, 0 if there is none and NULL if what it limits is not supported
GEOMETRY_COLUMNS, SPATIAL_REF_SYS, ST_UNITS The first three under short names

The systems supplied are WGS 84 (4326), its geocentric form (4978), NAD83 (4269), ETRS89 (4258), WGS 84 / Pseudo-Mercator (3857) and the 120 WGS 84 UTM zones (32601 to 32660 and 32701 to 32760), by the EPSG identifiers. The tables the views read are in ST_DEFINITION_SCHEMA, which no statement reads directly.

A system of its own is defined by CALL ST_DEFINESRS(name, id, organization, organization_id, definition, description) and dropped by CALL ST_DROPSRS(name) (extension). A column is registered with a system by CALL ST_REGISTERGEOMETRYCOLUMN(schema, table, column, name) and unregistered by CALL ST_UNREGISTERGEOMETRYCOLUMN(schema, table, column) (extension). Each is a change to the catalog, made in the transaction and undone by ROLLBACK. Each keeps the constraints of the tables: a name and an identifier unique, an organization and its identifier both given or both NULL, a column registered with a system that is there and of its SRID if its type names one, a system no column is registered with dropped. A column's owner registers it. A registration goes with its column or table. A column registered with a system holds only values of its SRID. A row written with another is refused, as is the registration of a column that holds one. ST_TRANSFORM and the unit forms of the measures and ST_BUFFER read a system defined as soon as the statement's catalog holds it.

CREATE TABLE TRACKS (ID INTEGER, PATH ST_LINESTRING(4326), PLAN ST_GEOMETRY);
SELECT TABLE_NAME, COLUMN_NAME, SRS_NAME, SRS_ID FROM ST_INFORMTN_SCHEMA.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME = 'TRACKS' ORDER BY COLUMN_NAME;
SELECT SRID, AUTH_NAME, AUTH_ID FROM ST_INFORMTN_SCHEMA.SPATIAL_REF_SYS WHERE SRS_NAME = 'WGS 84 / UTM zone 33N';
SELECT UNIT_NAME, CONVERSION_FACTOR FROM ST_INFORMTN_SCHEMA.ST_UNITS_OF_MEASURE WHERE UNIT_TYPE = 'LINEAR' ORDER BY CONVERSION_FACTOR;
ROLLBACK;
OK
 TABLE_NAME                                                   | COLUMN_NAME                                                  | SRS_NAME                                                     |       SRS_ID
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------
 TRACKS                                                       | PATH                                                         | WGS 84                                                       |         4326
 TRACKS                                                       | PLAN                                                         | NULL                                                         |         NULL
(2 rows)
         SRID | AUTH_NAME                                                    |      AUTH_ID
--------------+--------------------------------------------------------------+--------------
        32633 | EPSG                                                         |        32633
(1 row)
 UNIT_NAME                                                    |        CONVERSION_FACTOR
--------------------------------------------------------------+--------------------------
 FOOT                                                         |                 3.048E-1
 US SURVEY FOOT                                               |     3.048006096012192E-1
 METRE                                                        |                      1E0
 KILOMETRE                                                    |                      1E3
 STATUTE MILE                                                 |               1.609344E3
 NAUTICAL MILE                                                |                  1.852E3
(6 rows)
OK

A column registered with a system holds its values, measured in its unit, and the system is not dropped:

CREATE TABLE PLOTS (ID INTEGER, AREA ST_POLYGON);
CALL ST_DEFINESRS('FIELD GRID', 900001, 'ANN', 1, 'PROJCS["field",GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["central_meridian",15],UNIT["metre",1]]', 'the field');
CALL ST_REGISTERGEOMETRYCOLUMN('ANN', 'PLOTS', 'AREA', 'FIELD GRID');
SELECT COLUMN_NAME, SRS_NAME, SRS_ID FROM ST_INFORMTN_SCHEMA.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME = 'PLOTS';
INSERT INTO PLOTS VALUES (1, TREAT(ST_GEOMFROMTEXT('POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0))', 900001) AS ST_POLYGON));
INSERT INTO PLOTS VALUES (2, TREAT(ST_GEOMFROMTEXT('POLYGON ((0 0, 1 0, 1 1, 0 0))') AS ST_POLYGON));
SELECT ID, ST_AREA(AREA, 'KILOMETRE') AS SQUARE_KM FROM PLOTS;
CALL ST_DROPSRS('FIELD GRID');
ROLLBACK;
OK
OK
OK
 COLUMN_NAME                                                  | SRS_NAME                                                     |       SRS_ID
--------------------------------------------------------------+--------------------------------------------------------------+--------------
 AREA                                                         | FIELD GRID                                                   |       900001
(1 row)
OK
turborel: SQLCODE -201 (SQLSTATE 23000): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §4.11] column AREA of ANN.PLOTS holds values of FIELD GRID, SRID 900001, not of SRID 0
           ID |                SQUARE_KM
--------------+--------------------------
            1 |                     1E-2
(1 row)
turborel: SQLCODE -201 (SQLSTATE 23000): [ISO/IEC 13249-3:2016 (DIS, SC32 N2593) §20] ST_GEOMETRY_COLUMNS: SRS_SUPPORTED: column AREA of ANN.PLOTS is registered with FIELD GRID
OK

Simple feature access

The SQL option of Simple feature access, OGC 06-104r4 (ISO 19125-2), is built in its "SQL with Geometry Types" environment, over the types and routines of SQL/MM Spatial, so a tool written for either finds what it reads. Its tables are views of the schema SF_INFORMTN_SCHEMA, and SELECT on each is everyone's.

View Rows
GEOMETRY_COLUMNS Each column of a geometry type the user may see, with its table as F_TABLE_* and G_TABLE_*, its type's code as GEOMETRY_TYPE (0 GEOMETRY, 1 POINT, 2 LINESTRING, 3 POLYGON and so on, 1000 more in three dimensions), COORD_DIMENSION 3 in a geocentric system and 2 otherwise, and its SRID. STORAGE_TYPE and MAX_PPR are NULL
SPATIAL_REF_SYS Each system supplied or defined, as SRID, AUTH_NAME, AUTH_SRID and SRTEXT

A column's SRID is the system it is registered with, else the one its type names, else 0, the unspecified system, which SPATIAL_REF_SYS has no row of. Written without a schema, GEOMETRY_COLUMNS and SPATIAL_REF_SYS name these views, unless the default schema has a table of that name. SQL/MM's own views of those names, with other columns, stay in ST_INFORMTN_SCHEMA.

The type names POINT, CURVE, LINESTRING, SURFACE, POLYGON, POLYHEDSURFACE, GEOMCOLLECTION, MULTIPOINT, MULTICURVE, MULTILINESTRING, MULTISURFACE and MULTIPOLYGON name the ST_ types, as GEOMETRY names ST_GEOMETRY. A structured type a schema defines of one of these names is the type it names there. Every routine is called by its name without ST_ too: Area(SHORE) is SHORE.ST_Area(), and PolyFromText is ST_PolyFromText. A routine of a value whose declared type does not declare it, a collection held in a GEOMETRY column say, is the routine of the one subtype that does.

CREATE TABLE LAKES (FID INTEGER PRIMARY KEY, NAME VARCHAR(64), SHORE POLYGON);
CALL ST_DEFINESRS('UTM_ZONE_14N', 101, 'POSC', 32214, 'PROJCS["UTM_ZONE_14N",GEOGCS["World Geodetic System 72",DATUM["WGS_72",SPHEROID["NWL_10D",6378135,298.26]],PRIMEM["Greenwich",0],UNIT["Meter",1.0]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",500000.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-99.0],PARAMETER["Scale_Factor",0.9996],PARAMETER["Latitude_of_origin",0.0],UNIT["Meter",1.0]]', 'Blue Lake');
CALL ST_REGISTERGEOMETRYCOLUMN('ANN', 'LAKES', 'SHORE', 'UTM_ZONE_14N');
INSERT INTO LAKES VALUES (101, 'Blue Lake', PolyFromText('POLYGON ((52 18, 66 23, 73 9, 48 6, 52 18), (59 18, 67 18, 67 13, 59 13, 59 18))', 101));
SELECT F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE, COORD_DIMENSION, SRID FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME IN ('LAKES', 'PLACES') ORDER BY F_TABLE_NAME;
SELECT SRID, AUTH_NAME, AUTH_SRID FROM SPATIAL_REF_SYS WHERE SRID = 101;
SELECT GeometryType(SHORE) AS KIND, Area(SHORE) AS WATER, NumInteriorRing(SHORE) AS ISLANDS, AsText(ExteriorRing(SHORE)) AS OUTLINE FROM LAKES;
ROLLBACK;
OK
OK
OK
OK
 F_TABLE_NAME                                                 | F_GEOMETRY_COLUMN                                            | GEOMETRY_TYPE | COORD_DIMENSION |         SRID
--------------------------------------------------------------+--------------------------------------------------------------+---------------+-----------------+--------------
 LAKES                                                        | SHORE                                                        |             3 |               2 |          101
 PLACES                                                       | SHAPE                                                        |             0 |               2 |            0
(2 rows)
         SRID | AUTH_NAME                                                    |    AUTH_SRID
--------------+--------------------------------------------------------------+--------------
          101 | POSC                                                         |        32214
(1 row)
 KIND                             |                    WATER |      ISLANDS | OUTLINE
----------------------------------+--------------------------+--------------+--------------------------------------------------------------
 POLYGON                          |                  2.195E2 |            1 | LINESTRING (52 18, 66 23, 73 9, 48 6, 52 18)
(1 row)
OK

GeometryType, AsText and AsBinary mean what Simple feature access means by them, so they differ from SQL/MM's ST_GeometryType, ST_AsText and ST_AsBinary, which keep their meaning. GeometryType gives the type's name without ST_, POLYHEDSURFACE for a polyhedral surface. AsText and AsBinary write the forms of OGC 06-103r4, which differ from SQL/MM's only for a triangle, a polyhedral surface and a TIN. A triangle is the text of a polygon of one ring, and a surface's patches are listed without PATCHES. A TIN's elements and maximum side length have no such form and are left out. The readers take both forms.

UNION and OVERLAPS are reserved words, and INTERSECTION is a set function of one argument, yet Union(a, b), Overlaps(a, b) and Intersection(a, b) are the routines' calls. A PolyhedSurface is made by NEW from its text, its binary form or an array of surfaces, each with an SRID or not, and its NumSurfaces, SURFACE(n) and ST_Geometries are those of its patches.

SELECT AsText(G) AS PART_1, ST_AsText(G) AS SQLMM FROM (VALUES (GeomFromText('TRIANGLE ((0 0, 1 0, 0 1, 0 0))'))) AS T (G);
SELECT GeometryType(G) AS SFA_NAME, ST_GeometryType(G) AS SQLMM_NAME FROM (VALUES (GeomFromText('MULTIPOINT ((1 1), (2 2))'))) AS T (G);
SELECT AsText(Union(P, Q)) AS BOTH_POINTS, Overlaps(P, Q) AS OVERLAP FROM (VALUES (GeomFromText('POINT (1 1)'), GeomFromText('POINT (2 2)'))) AS T (P, Q);
SELECT H.NumSurfaces() AS PATCHES, AsText(H.SURFACE(2)) AS PATCH_2 FROM (VALUES (NEW PolyhedSurface('POLYHEDRALSURFACE Z (((0 0 0, 0 1 0, 1 1 0, 0 0 0)), ((0 0 0, 0 1 0, 0 0 1, 0 0 0)))'))) AS T (H);
 PART_1                                                       | SQLMM
--------------------------------------------------------------+--------------------------------------------------------------
 TRIANGLE ((0 0, 1 0, 0 1, 0 0))                              | TRIANGLE (0 0, 1 0, 0 1)
(1 row)
 SFA_NAME                         | SQLMM_NAME
----------------------------------+----------------------------------
 MULTIPOINT                       | ST_MultiPoint
(1 row)
 BOTH_POINTS                                                  |      OVERLAP
--------------------------------------------------------------+--------------
 MULTIPOINT ((1 1), (2 2))                                    |            0
(1 row)
      PATCHES | PATCH_2
--------------+--------------------------------------------------------------
            2 | POLYGON Z ((0 0 0, 0 1 0, 0 0 1, 0 0 0))
(1 row)

Annotation text

Text placed on a map is a value of ANNOTATION_TEXT, the text types of Simple feature access. An ANNOTATION_TEXT_ELEMENT is a string, the point it is placed at, a leader line from it, and its text attributes, written as XML. Each is read by a method of its name, Value(), Location(), LeaderLine() and TextAttributes(), and changed by the same method of one argument. The constructor takes all four, and NEW ANNOTATION_TEXT_ELEMENT() makes one whose attributes are all NULL. An ANNOTATION_TEXT_ELEMENT_ARRAY holds elements. ElementN(n) reads one, and ElementN(element, n) replaces it or adds one after the last. An ANNOTATION_TEXT is made of an array. ELEMENT_ARRAY() gives the array back, ENVELOPE() gives the envelope of the elements' locations and leader lines, and CONCAT(other) appends another text's elements.

Strings, attributes and arrays hold at most 4096 characters or elements. A text has no well-known text or binary form, and is shown as the constructors that make it. ANNOTATION_TEXT_METADATA, a view of SF_INFORMTN_SCHEMA, lists each column of type ANNOTATION_TEXT the user may see. Its default base scale, expression and attributes are NULL.

CREATE TABLE LABELS (ID INTEGER PRIMARY KEY, LABEL ANNOTATION_TEXT);
INSERT INTO LABELS VALUES (1, ANNOTATION_TEXT(ANNOTATION_TEXT_ELEMENT_ARRAY(ARRAY[ANNOTATION_TEXT_ELEMENT('Pond', ST_POINT(5, 5), GeomFromText('LINESTRING (5 5, 7 8)'), '<font size="9"/>')])));
SELECT AsText(LABEL.ENVELOPE()) AS BOX, LABEL.ELEMENT_ARRAY().ElementN(1).Value() AS WORDS FROM LABELS;
UPDATE LABELS SET LABEL = LABEL.CONCAT(ANNOTATION_TEXT(ANNOTATION_TEXT_ELEMENT_ARRAY(ARRAY[(NEW ANNOTATION_TEXT_ELEMENT()).Value('Well').Location(ST_POINT(2, 2))]))) WHERE ID = 1;
SELECT AsText(LABEL.ENVELOPE()) AS BOX, LABEL.ELEMENT_ARRAY().ElementN(2).Value() AS WORDS, LABEL.ELEMENT_ARRAY().ElementN(2).LeaderLine() AS LEADER FROM LABELS;
SELECT F_TABLE_NAME, F_TEXT_COLUMN, A_TEXT_DEFAULT_MAP_BASE_SCALE FROM ANNOTATION_TEXT_METADATA;
ROLLBACK;
OK
OK
 BOX                                                          | WORDS
--------------------------------------------------------------+--------------------------------------------------------------
 POLYGON ((5 5, 7 5, 7 8, 5 8, 5 5))                          | Pond
(1 row)
OK
 BOX                                                          | WORDS                                                        | LEADER
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 POLYGON ((2 2, 7 2, 7 8, 2 8, 2 2))                          | Well                                                         | NULL
(1 row)
 F_TABLE_NAME                                                 | F_TEXT_COLUMN                                                | A_TEXT_DEFAULT_MAP_BASE_SCALE
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 LABELS                                                       | LABEL                                                        | NULL
(1 row)
OK

Errors

SQLSTATE SQLCODE When
01F01 warning A method of a position is given one outside the value
0A000 -900 More than the limits of Appendix D, or an rtree index of a column of plain GEOMETRY
0D000 -301 TREAT is given a value of another type
22000 -301 WKT or WKB is malformed, a coordinate is not finite, ST_MAKEENVELOPE's bounds are reversed or of no area, ST_X or ST_Y is given another kind than a point, or two values' SRIDs differ
22000 -301 A value's SRID is not its column's or CAST's
22003 -301 A distance is greater than DOUBLE PRECISION holds
2202F -301 ST_VECTOROFFSETEXP is given more than three vectors
23000 -201 ST_DEFINESRS, ST_DROPSRS or ST_REGISTERGEOMETRYCOLUMN would break a constraint of the Spatial Information Schema's tables, or a column registered with a system would hold a value of another SRID
2A000 -103 A type's parameter is not one integer from 0 to 2147483647, a function's arguments are not of its types, a value of a supertype is assigned without TREAT, NEW names a type that holds no value of its own, geometry values are ordered, grouped or made distinct, a key names a geometry column, or a method is not of the value's declared type
2FF02 -301 A ring of ST_BDPOLYFROMTEXT's is not a closed simple ring, a measure is given a value of a type it does not measure, or a 3D routine's argument is out of its range: a TIN's maximum side length not above 0, a vertical triangle's slope, a measure range reversed, an interior shell outside the exterior one, a TIN table or column that does not exist, or a method given an argument it does not take
2FF03 -301 A TIN's maximum side length, an array of shells or one of its elements, an array ST_CHECKNULLS checks, or a mutator's argument is null
2FF04 -301 ST_RELATE's matrix is not nine of T, F, 0, 1, 2 and *
2FF05 -301 ST_CHECKCONSECDUPS finds two equal values in a row, or ST_POINTS is given them or an arc of equal ends
2FF07 -301 ST_INTERIORRINGS is given rings of an empty surface
2FF06 -301 ST_CHECKNULLS, or ST_TOPOINTARY and its peers, find an empty element
2FF08 -301 CAST is given a value Table 4 does not convert, a value is assigned to a type it is not of, ST_TOPOINTARY and its peers find an element of another type, or a polygon's interior rings are given a curve that is not a line string
2FF09 -301 ST_CHECKNULLS, or ST_TOPOINTARY and its peers, find a null element
2FF10 -301 ST_CHECKSRID's elements, a compound surface's surfaces, or a mutator's value and SELF are of several SRIDs
2FF11 -301 ST_CURVES is given curves that do not meet end to start
2FF12 -301 A polygon's exterior ring is given a curve that is not a line string
2FF14 -301 A unit is not a linear unit of ST_UNITS_OF_MEASURE, or the SRID's system has no linear unit
2FF15 -301 ST_TRANSFORM cannot move a value to the system of the SRID
2FF16 -301 ST_Z of a vector or a point is given a null value of one that is not empty, NEW ST_POINT a z of a null x and y, or a cast method a value it does not cast that is not empty
2FF18 -301 ST_X or ST_Y of an empty point is given a value
2FF22 -301 A text is malformed, a typed constructor reads a value of another type, ST_BDPOLYFROMTEXT's text is of no multilinestring, or ST_DEFINESRS's definition is no spatial reference system
2FF23 -301 WKB of a type is malformed, a typed constructor reads a value of another type, or ST_BDPOLYFROMWKB's is of no multilinestring
2FF24 -301 GML is malformed, or a typed constructor reads a value of another type
2FF25 -301 A vector's text has a dimension not its tag's, or ST_GETCOORDDIM's elements, a compound surface's surfaces, or a mutator's points or rings, are of several dimensions
2FF59 -301 A unit form is given a value of an SRID no system has
2FF66, 2FF67, 2FF75 -301 A triangle is given interior rings, a TIN a patch that is not a triangle, or a triangle a ring of other than four points
2FF68, 2FF69, 2FF70 -301 ST_TOTRIANGLEARY, ST_TOPOLYHDRLARY (and ST_TOCOMPSURFARY, ST_TOBREPSOLIDARY) or ST_TOTINARY finds an element of another type
2FF72 -301 A 3D routine of two values is given one without z
2FF74 -301 A TIN element's geometry is not of the type its element type takes
2FF76, 2FF79, 2FF80 -301 ST_CENTER of more than one arc, ST_POINTATDISTANCE beyond a curve's end, ST_DISTANCETOPOINT of a point off it
2FF77, 2FF78 -301 A circle is given other than three points, or three on one line
2FF84 -301 A constructor or mutator of measures is given one without the other
2FF85 -301 A surface of a compound surface does not meet the one before it in a curve
2FF86, 2FF96 -301 ST_REFLOCATION is given a placement of other than two directions, or of a dimension not SELF's
TB916 -916 A stored value or index key is damaged, or of an unknown version

Standard

The types, their hierarchy, their ordering, their WKB forms and the Spatial Information Schema are those of ISO/IEC 13249-3:2016, SQL/MM Spatial, §4.2, §5.1.65, §5.1.68, §19 and §20. The text forms are those of OGC 06-103r4, Simple Feature Access Part 1, §§7–8. The functions named here are called as functions, or as methods, as ST_POINT(1, 2).ST_X(). Each routine of §5 to §10 is built, and turborel claims conformance to neither yet. The rtree index method is an extension. The choices made are listed in CONFORMANCE.md. Simple feature access is that of OGC 06-104r4, the SQL option, in its "SQL with Geometry Types" environment, claimed with its readings in CONFORMANCE.md.

See also

CREATE INDEX, Spatial Guide, Appendix D: Limits