Finding myself with the SAP HANA Academy

Finding myself with the SAP HANA Academy

My sense of direction is infamous.  My first graduate job  after leaving university was on Emergency Response for the railways.  My nine months intensive training was followed by a quick spin around the patch which covered three city centres and all that was between them.  For national security reasons, none of the hundreds of locations I was shown were on any map.  To make things more interesting, I had just passed my driving test on Thursday 26 September 1996 at 19:00.  This was after eight failed attempts.  I had to start work on Friday 27 September at 23:59. Oh, and my wife was 8 months pregnant with our first daughter.  I went into battle issued with a phone we were told was robust enough to be used in self defence, and a pager for when the phone’s reception failed, as it invariably did.  Believe it or not this was cutting edge stuff at the time. It would have made a huge difference to my first working days as a father, husband and property owning citizen if I had been issued with a mobile device that recorded location information, like the ninety percent of mobile devices which are location aware today.

Today I am going to review the SAP HANA Spatial Reference training materials provided by the SAP HANA Academy.  There are two videos in the this HANA spatial Reference mini-series. I have also included another video from the SAP HANA Academy that I found extremely useful in helping me work with large files. 

Geo Spatial in HANA Importing Data from a CSV File

The first tutorial covers how to import spatial data into HANA from a CSV file on a work directory on the HANA system.

The tutorial covers importing a simple data set from a character separated file. Note the format of the second column which you will be adding this point type spatial data to a new table. 

Using an FTP tool a CSV file has been added to the directory on our HANA system along with a CTL file.  This CTL file will be used to control the import as it identifies the structure of the CSV file. 

If you look at the CTL file, the syntax states that you will be importing data from your CSV file which is located in the same work directory on HANA as the CTL file itself. The data will be imported into a table also named TEST which you will create later in a schema named Geo. The CTL also states that the data is tab-delimited for the columns, line delimited for the records and any text columns are enclosed in double quotes.  This is not really applicable to our dataset as you are not importing any text columns.  If the import does fail there will be an error log created that you can use to identify any issues.

You will need another piece of syntax to execute the import.  This script will create the columnar table consisting of two columns and afterwards will execute the import of data.  Note that you need defined the spatial type of the second column as point.  You did not declare a system reference ID in a column definition.  This means that it’s going to default to 0 which is a two-dimensional Cartesian system.  Also note that the Import From references the control file which you have placed in our file structure

on our HANA system.  If you now press the Execute button or press the F8 key the new table will be created and the data from the CSV file referenced in the CTL file will be added to the new table.

After refreshing your Geo schema you can go to your tables.  If you open our data preview you’ll see your data, all which is stored in binary format.  At this point you may want to see the data in a more recognizable format. 

To do this right-click on the TEST, click Generate, SELECT Statement as shown.

The syntax you just appended to the coordinates column will return the data as a spatial type and in a well-known text format.  As you see now you’ve got the data in your original point format.

Large Files

If you are using a large file, please use this video on Importing Data using CTL Method to help you.  Using this video you could import hundreds of tables in just a few seconds.  You may also wish to refer to help provided by Jamie Wiseman on SCN.  A couple of things to remember.  Firstly, the HANA Studio, File, Import method will not work for importing geo-spatial data.  Secondly, remember not to include a header row in the csv as the field names are created in the script.

Geo Spatial in HANA Creating Spatial Type Data from an Existing Table

In the second tutorial you learn how to change existing coordinate data into spatial type data in HANA.  You take an existing table in HANA Studio then create a new table which combines individual coordinate columns into a single spatial tape point field.

If you’ve got a table that you’ve imported into HANA that includes individual fields for X&Y coordinates or latitude and longitude then you can create a new table based on that table.  You can then insert your data plus new spatial type data into the new table. You will be using this simple TEMP_MYDATA table as an example.

In order to start the process that the new table creation, right click TEMP_MYDATA then go to Open Definition.

Press the Export SQL button then you will have the majority of your CREATE TABLE syntax written out.  Now you need to change the name of the table to the desired name of the new table.

You want an additional column in the new table and this field should be defined as a special type point.  In the column XY point we could at this time define a system reference ID.  However, this will default on a system reference of  0 which is a two-dimensional cartesian coordinate system.  You can run the CREATE TABLE script so that we have the new table and the next step is to insert the data from our existing table into the new table.  Also adding point data into the new XY point column at the same time.  You can paste in this syntax.

The line dealing with the insert into is going to select the three columns from your existing table and also create a new spatial tight point using your individual X&Y column data and then inserts the state into your new table. If you run the insert line

along with the SELECT statement you can see the data in the new table.

As well as viewing the XY point data in a well-known text format you have now created a new table with a special type point column using data from your existing table.

Where do we go from here?

When I think back to when I first started working, paying a mortgage, driving a car and bringing up children it’s overwhelming to think that eighty percent of data contains geographical references.  Back then the Internet was in its infancy but you could see there being a demand for a huge expansion in bandwidth.  For people like me intuitive mapping interfaces make a huge difference to both our work and social lives.  Personally I think it’s fantastic  that we live in an age where data can be visualised in its geographical context during real time. I mean, who likes traffic jams?  However, there is one last challenge and that is finding a way to bring the data together.  SAP HANA’s in-memory platform breaks not just technological barriers but social ones as well.  Spatial processing with SAP HANA allows you to process and analyze massive amounts of spatial data in real time to reveal new patterns and trends which can hugely influence business decisions.  That bike trip Bob helped me plan over the Karakoram Highway is now a possibility.  All I have to do now, is get fit.