Access methods

Some other methods that can be applied to all types of geometries are presented below.

ST_XMin(), ST_XMax(), ST_YMin(), ST_YMax all retrieve corresponding minimum or maximum values of geometry’s coordinates X and Y. There are similar methods available for coordinates Z and M.

In case of a point both minimum and maximum values will be the same and equal to the same value as point-only methods retrieving coordinate value, like ST_X().

select
"SHAPE".ST_asWKT(),
"SHAPE".ST_X(),
"SHAPE".ST_XMin(),
"SHAPE".ST_XMax()
from "TESTSGEO"."SPATIALSHAPES"
where "SHAPE".ST_GeometryType()='ST_Point';

After running the SQL statement above you should notice is that these methods return null when applied to an empty geometry.

The other access method you used here was ST_GeometryType() to check and filter the type of geometry.

You used ST_GeometryType() in the previous step as a predicate to select only records for points. Otherwise trying to apply ST_X() to a string or polygon would return run-time error from SQL.

There are some access methods that can be used only with line strings. For example:
- ST_StartPoint() and ST_EndPoint() to retrieve an ST_Point value of the starting and the ending points,
- ST_NumPoints() to get the total number of points that can be used then to retrieve Nth point of using ST_PointN(). Numbering starts from 1 and ST_PointN() returns null in Nth point does not exist.

select
"SHAPE".ST_asWKT(),
"SHAPE".ST_StartPoint().st_asWKT(),
"SHAPE".ST_EndPoint().st_asWKT(),
"SHAPE".ST_NumPoints(),
"SHAPE".ST_PointN(2).st_asWKT()
from "TESTSGEO"."SPATIALSHAPES"
where "SHAPE".ST_GeometryType()='ST_LineString';

Similarly to points in strings you can get a number of individual geometries in collections (types ST_MultiPoint, ST_MultiLineString, ST_MultiPolygon) using ST_NumGeometries(). And then access each of them using ST_GeometryN() method.

select ST_UnionAggr("SHAPE").ST_GeometryType(), ST_UnionAggr("SHAPE").ST_NumGeometries()
from "TESTSGEO"."SPATIALSHAPES";

The query above aggregates all different geometries from the SPATIALSHAPES table. The result is ST_GeometryCollection with 5 different geometries in it. Some of overlapping polygons got aggregated into new single polygons, so the total number of geometries in the collection is smaller than a number of single geometries in the table.

select "SHAPE".ST_GeometryType(), ST_UnionAggr("SHAPE").ST_GeometryType(),
ST_UnionAggr("SHAPE").ST_NumGeometries(), ST_UnionAggr("SHAPE").ST_GeometryN(1).ST_asWKT()
from "TESTSGEO"."SPATIALSHAPES"
group by "SHAPE".ST_GeometryType();

This query now produced 3 different collections. Each of them contains only the same geometry types thanks to GROUP BY statement.

You will learn more about different kinds of spatial aggregations in a separate tutorial.