[SAP HANA Academy] SAP HANA SPS11 - HANA Spatial: Intersection Aggregate Method, ST_INTERSECTIONAGGR

[Former Member]

In a tutorial video the SAP HANA Academy's jamie.wiseman examines the Intersection Aggregate method, which is part of SAP HANA Spatial. This method is new to SAP HANA and available in SAP HANA SPS11. Watch Jamie's video below.

The Intersection Aggregate method returns the spatial intersection of all of the geometries in a group. The aggregate is logically computed by repeatedly applying the spatial type intersection method to combine two geometries at a time.

Jamie is using the .ST_AsText method so that the results are returned in a more readable format as opposed to the results coming back in a binary format. The syntax used in this video is available in the description of the video and also at the bottom of this post.

Lines 3-19 of the syntax will create a test schema and a test table. Next several geometries will be added to the shape column. The SRID will be 0 as Jamie will be looking at geometries in a Cartesian system.

If you run lines 4-25 you will return the data from your test table as shown below.

The table contains a pair of closed polygons, specifically a square and a triangle, and a line segment. The visual representation of these geometries are depicted below. The geometries were added in the following order: square, triangle, line.

Executing lines 28-29 will run the Intersection Aggregation on the shape column. The result is the single line segment that is shown below.

Note that this method will return a null if there is no common intersection between the complete set of geometries that are being compared. Also if the final set that the method looks at is a single geometry then that single geometry will be returned.

For more tutorials videos on What's New with SAP HANA SPS11 please check out this playlist.

SAP HANA Academy - Over 1,300 free tutorials videos on SAP HANA, SAP Analytics and the SAP HANA Cloud Platform.

Follow us on Twitter @saphanaacademy and connect with us on LinkedIn to keep apprised of our latest free tutorials.

Tutorial's Syntax:

-- create a test schema and add a test table

CREATE SCHEMA DEVTEST;

CREATE COLUMN TABLE DEVTEST.SPATIALTEST (

ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

SHAPE ST_GEOMETRY(0)

);

-- add several geometries to the test table

INSERT INTO DEVTEST.SPATIALTEST VALUES (

NEW ST_Polygon('Polygon ((0 0, 4 0, 4 4, 0 4, 0 0))')

);

INSERT INTO DEVTEST.SPATIALTEST VALUES (

NEW ST_Polygon('Polygon ((1 3, 3 1, 3 3, 1 3))')

);

INSERT INTO DEVTEST.SPATIALTEST VALUES (

NEW ST_LineString('LineString (0 0, 3 3)')

);

-- look at the data in the test table

SELECT

SHAPE.ST_AsWKT() AS SHAPE,

SHAPE.ST_GeometryType() AS SHAPE_TYPE

FROM DEVTEST.SPATIALTEST;

-- use ST_IntersectionAggr on the SHAPE column

SELECT  ST_IntersectionAggr ( SHAPE ).ST_AsText()

FROM DEVTEST.SPATIALTEST;