[SAP HANA Academy] Geo Spatial in SAP HANA: Importing Data from a CSV File

[Former Member]

Discover how to import Geo Spatial data into SAP HANA using a CSV file from a SAP HANA work directory with a quick tutorial video from the SAP HANA Academy’s jamie.wiseman.

Information on CSV and CTL files (0:16 – 2:20)

For this instructional video a simple data set from a character separated file will be imported. The second column of this data set will be added to a newly created table as point type spatial data. A CSV file and a CTL file were added to a directory on a SAP HANA system using a FTP tool. The CTL file identifies the structure of the CSV file and is used to control the import.

The syntax of the CTL file states that data will be imported from the CSV file that is located on the same SAP HANA work directory as the CTL file. That data will be imported into a table named TEST created in a schema named GEO. The CTL states that the data is tab delimited for the columns and line delimited for the records.

In SAP HANA Studio enter the syntax below into a SQL window in the GEO schema. This script creates a columnar table with two columns. Note that the spatial type of the second column is defined as point. No System Reference ID is declared in the column definition so it will default to zero making it a two-dimensional Cartesian system.

Loading the Geo Spatial Data (2:20 – 2:55)

After clicking the execute button a new table is created with data from the CSV file that is referenced in the CTL file. Then after refreshing the Geo schema the data can be viewed in binary format after clicking data preview on the TEST table.

Seeing the Data in the Original Point Format (2:55 -3:45)

In order to see the data in a more recognizable format, right-click on the TEST table and choose generate followed by select statement. Now by appending .ST_AsWKT() to “COORDINATES_POINT” in the SQL window and then clicking execute the data will be displayed in the well-known text format of spatial type data.

Watch Jamie’s tutorial video to learn how to import Geo Spatial data from a CSV file into SAP HANA.

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

-Tom Flanagan

SAP HANA Academy

Follow @saphanaacademy