Spatial columns

For the purpose of this tutorial, create a schema TESTSGEO or use any other schema in your instance, where you have privileges for creating tables.

CREATE SCHEMA TESTSGEO;
SET SCHEMA TESTSGEO;

Create and load data into the SpatialShapes table. This example is taken from the SAP HANA Spatial Reference, so that you can run exercises from the official help as well.

CREATE COLUMN TABLE SpatialShapes
(
  ShapeID integer,
  shape ST_GEOMETRY
);

Insert following sample data.

-- a set of points
INSERT INTO SpatialShapes VALUES(1,  NEW ST_POINT('POINT(2.5 3.0)'));
INSERT INTO SpatialShapes VALUES(2,  NEW ST_POINT('POINT(3.0 4.5)'));
INSERT INTO SpatialShapes VALUES(3,  NEW ST_POINT('POINT(3.0 6.0)'));
INSERT INTO SpatialShapes VALUES(4,  NEW ST_POINT('POINT(4.0 6.0)'));
INSERT INTO SpatialShapes VALUES(5,  NEW ST_POINT());

-- a set of linestrings
INSERT INTO SpatialShapes VALUES(6,  NEW ST_LINESTRING('LINESTRING(3.0 3.0, 5.0 4.0, 6.0 3.0)'));
INSERT INTO SpatialShapes VALUES(7,  NEW ST_LINESTRING('LINESTRING(4.0 4.0, 6.0 5.0, 7.0 4.0)'));
INSERT INTO SpatialShapes VALUES(8,  NEW ST_LINESTRING('LINESTRING(7.0 5.0, 9.0 7.0)'));
INSERT INTO SpatialShapes VALUES(9,  NEW ST_LINESTRING('LINESTRING(7.0 3.0, 8.0 5.0)'));
INSERT INTO SpatialShapes VALUES(10,  NEW ST_LINESTRING());

-- a set of polygons
INSERT INTO SpatialShapes VALUES(11, NEW ST_POLYGON('POLYGON((6.0 7.0, 10.0 3.0, 10.0 10.0, 6.0 7.0))'));
INSERT INTO SpatialShapes VALUES(12, NEW ST_POLYGON('POLYGON((4.0 5.0, 5.0 3.0, 6.0 5.0, 4.0 5.0))'));
INSERT INTO SpatialShapes VALUES(13, NEW ST_POLYGON('POLYGON((1.0 1.0, 1.0 6.0, 6.0 6.0, 6.0 1.0, 1.0 1.0))'));
INSERT INTO SpatialShapes VALUES(14, NEW ST_POLYGON('POLYGON((1.0 3.0, 1.0 4.0, 5.0 4.0, 5.0 3.0, 1.0 3.0))'));
INSERT INTO SpatialShapes VALUES(15, NEW ST_POLYGON());

Now, check the shapes you loaded, including types of geometries and which geometry values represent empty sets.

SELECT SHAPEID, SHAPE.ST_asWKT(), SHAPE.ST_GeometryType(), SHAPE.ST_isEmpty()
FROM "TESTSGEO"."SPATIALSHAPES";

Geospatial data validation is a new feature of SAP HANA 2.0 SPS 2, and therefore this step is valid only if you run this or later version of the software.

Consider following example.

SELECT NEW ST_LINESTRING('LINESTRING(1.0 2.0, 1.0 2.0)').st_IsValid() from dummy;

The result of the query is 0, meaning the shape is not valid. Indeed the beginning and end points of a single line string cannot be the same.

Now insert this string into the table.

INSERT INTO SpatialShapes VALUES(100,  NEW ST_LINESTRING('LINESTRING(1.0 2.0, 1.0 2.0)'));

The insert will be successful and the new row with the invalid shape is added to the table. This is because by default there is no additional validation done on spatial columns as can be seen in the view ST_GEOMETRY_COLUMNS.

SELECT TABLE_NAME, COLUMN_NAME, VALIDATION
FROM ST_GEOMETRY_COLUMNS
WHERE SCHEMA_NAME='TESTSGEO' and TABLE_NAME='SPATIALSHAPES';

Alter the definition of the table’s column to do validation checks now. Check how values are changes in the view ST_GEOMETRY_COLUMNS.

ALTER TABLE "TESTSGEO"."SPATIALSHAPES" ALTER ("SHAPE" ST_GEOMETRY(0) VALIDATION FULL);

Try to insert invalid shape into the column once again.

INSERT INTO SpatialShapes VALUES(101,  NEW ST_LINESTRING('LINESTRING(1.0 2.0, 1.0 2.0)'));

This time you should receive an error message, like following: spatial error: The geometry is considered invalid by function ST_IsValid, but only valid geometries are allowed for column...

Altering column for turn validation does not do revalidation of already inserted data and applies only to new and modified data.

Remove these test records from the table as they won’t be needed in further exercises.

DELETE FROM "TESTSGEO"."SPATIALSHAPES" WHERE "SHAPEID" > 15;

When you need to combine multiple shapes into one you can use different set operations and aggregation methods.

Aggregation methods are executed on spatial columns of tables in SAP HANA.

ST_UnionAggr() returns the spatial union of all of the geometries in a column.

SELECT ST_UnionAggr(SHAPE).ST_asWKT() as UnionAggr
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE SHAPE.ST_isEmpty()=0 and SHAPE.ST_GeometryType() = 'ST_LineString';

Presented graphically, it looks like the diagram below:

Also note that spatial predicates were used in the query above to select only geometries of type LineString type and only those that are not empty.

Check your knowledge

1. Query the union aggregation of polygons only from the table.
2. Query the union aggregation of boundaries of polygons from the table.

Two other important aggregate methods are
- ST_EnvelopeAggr() which returns the bounding rectangle for all of the geometries in a group,
- ST_ConvexHullAggr() which returns the convex hull for all of the geometries in a group, known as “rubber band” method.

Execute this query to best illustrate both types of aggregations. It uses the set operation method ST_Union() to return the geometry value that represents the point set union of two geometries.

SELECT
ST_ConvexHullAggr(SHAPE).ST_Boundary().ST_Union(ST_UnionAggr(SHAPE)).ST_asWKT() as ConvexHullAggr,
ST_EnvelopeAggr(SHAPE).ST_Boundary().ST_Union(ST_UnionAggr(SHAPE)).ST_asWKT() as EnvelopeAggr
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE SHAPE.ST_isEmpty()=0 and SHAPE.ST_GeometryType() = 'ST_LineString';

Please note the use of the ST_Boundary() method to convert a polygon (which is a result of the aggregation) into just a curve surrounding the shape, so that the combined geometries are all visible.

Seeing is believing, so here are the graphical outputs (with slightly modified SVG to draw the shapes of the aggregates in red)

The result of ST_ConvexHullAggr():

And the result of ST_EnvelopeAggr():