[SAP HANA Academy] Geo Spatial in SAP HANA: Creating Spatial Type Data from an Existing Table

[Former Member]

In another installment of the SAP HANA Academy’s tutorial video series on Geo Spatial data in SAP HANA jamie.wiseman details how to change existing coordinate data into spatial type data in SAP HANA. This process is demonstrated using an existing data table and combining individual coordinate columns into a single spatial type point field in SAP HANA Studio.

Importing a Table (0:23 – 1:15)

First import a table into SAP HANA Studio that includes individual fields for X and Y coordinates or latitude and longitude. In this example Jamie will use a table named “TEMP_MYDATA” located in the table folder of the GEO schema.

Create a new table by right clicking on TEMP_MYDATA in the systems window in the table folder and selecting open definition. Clicking the export SQL button at the top right of the screen will write out a majority of the syntax that is needed to create the new table. 

Changing the SQL Statement and Creating the Table (1:15 – 2:00)

Change the name of the table to a desired name in the first line of the SQL syntax. Next add an addition spatial type point column by entering XY_POINT ST_POINT, under the “Y_COORD” line. It is possible define a System Reference ID at this point but leaving it blank will by default define the System Reference ID as zero and thus make it a two-dimensional Cartesian coordinate system.

Now click on the green execute button to run this create table script.

Inserting Data into the Table and Viewing the Data (2:00 – 3:09)

Insert the syntax below beneath the PRIMARY KEY line in the SQL window.

The INSERT INTO statement selects the three columns from the existing table, creates a new spatial type point using the individual X and Y column data and then inserts the data into the newly created table.

Highlight the INSERT INTO and SELECT SQL lines and click execute to see the spatial type point data displayed in the new table in a well-known text format.

Check out Jamie’s video on creating Spatial Type data using existing data.

SAP HANA Academy - over 500 free tutorial technical videos on using SAP HANA.

-Tom Flanagan

SAP HANA Academy

Follow @saphanaacademy