Skip to Content

Access methods

test
0 %
Access methods
Details
// Explore More Tutorials

Access methods

Spatial access methods help you to retrieve properties of geometries

You will learn

You will learn about a number of access methods and how to apply them to different geometries.

You must have table "TESTSGEO"."SPATIALSHAPES" from previous tutorials already created and loaded in your system to be able to run examples from this tutorial.


Step 1: Access methods

If you have done previous tutorials, then you must have seen some of the spatial access methods already.

E.g. ST_Dimension() returned the dimension of a geometry object and could be applied to a geometry of any type - be it a point or a polygon. ST_X() returns the X coordinate of an ST_POINT and works with points only.

Step 2: Access methods for all geometries

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';
access methods

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.

Step 3: Access methods for line strings

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';
Line strings
Step 4: Access methods for collections

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";
Number of geometries

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();
Geometry 1

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.

Optional

  • You can find all available methods in SAP HANA Spatial Reference. Make sure you review documentation for the version of SAP HANA you run.

Next Steps

Back to top