All I remember from my geography lessons at school is colouring in. So I feel a mild sense of trepidation as I review this video by Tahir Hussain Babar, aka Bob, of the SAP HANA Academy. In previous blogs I have indicated the advantages that SPS09 brings. Now I was going to discuss shape and location. However, as soon as I saw the word UFO that was it. For the second time in two days I am going to be considering key words through the prism of science fiction.
My only experience with anything other-worldly happened three years ago in Nevada. My brother had arranged a trip to see the sites of the famous British sci-fi film, Paul, to help my youngest daughter relax before re-sitting her 11 plus. This consisted of an epic road trip from Las Vegas to the Californian coast and back. She had her 11th birthday party in the famous Alien Inn from the movie. We stopped at the air hangar near Rachel, Nevada. It was a hot day so my brother and daughter stayed in the mustang we had hired while I looked around. The atmosphere was eerie despite it being a hot, clear day. I went to the back of the hangar to see if we could tour the “museum”. I saw a pickup truck near an RV with the engine running so assumed there must be someone nearby. I could hear a clicking sound so followed it. This led me to the back of the hangar. There was a mixture of industrial and agricultural tools neatly stored there. I shouted as I could hear machinery. Then the hairs pricked up at the back of my neck. I looked up to see a CCTV camera following my every move. I looked down to see the back of a baby’s push chair. It was shaking vigorously as if an inconceivably strong child was kicking its legs. But unlike any child I have ever had there was no noise coming from the chair. I had a feeling I had to get out of there. Something told me that I should not look at what was in the push chair. Despite the oppressive heat and my general lack of conditioning I ran to the mustang and told my brother to drive off. Unusually he did this with little demur which was unusual to say the least. This is one of the few times he has indulged in anything, spontaneous or naughty, at my behest without objection.
In this video Bob is being a bit naughty by using the System User to create geospatial joins within calculation views using SAP HANA Studio. As always Bob lays out his stall by demonstrating the resources he is going to be using in this session. He has a schema called geodata which contains two tables. One of the tables is called STATES which contains a list of all the US states with a shape file stored in a column called SHAPE. A shape file is an actual map of a particular state. This format can spatially describe vector features such as points or lines on polygons representing, for example, water wells, rivers and lakes.
There is also another table called UFO sightings which contains one row with a point column called LOCATION. This point column is the exact reference of the latitude and longitude and is indeed derived from both of them combined.
Having outlined his resources Bob moves onto the task described in the beginning which is essentially how to join these two tables together when you create a calculation view. Bob does this as the System User but does emphasise that this is not usual practise. He starts by creating a package called SPS09 in the Content folder before creating a calculation view with the SPS09 package. Bob then states that the calculation view will output the state where the UFO sighting happened by making a direct join between for the place where it happened via the LOCATION and the SHAPE columns. He then introduces the concept of a different type of join based on distance. The calculation view could, for example, return all the states that are in a fifty mile radius of the UFO sighting. This will be outputted as a Graphical view with DIMENSION information. This data will not have any measures.
Bob then demonstrates the join described above as below.
Bob also edits the join to make it geospatial. Note that validating geospatial joins is not supported yet.
Bob then selects the columns he wants outputted for this particular view.
Whilst in Semantics, Bob reminds viewers that they must ensure that the data category is set to DIMENSION.
Once these steps have completed, he clicks Save and Activate.
Having demonstrated that the calculation view has produced the desired result, Bob demonstrates how to modify the calculation view to find the states nearby. He does this be editing the join properties to make the Distance Fixed. He also discusses how he could have chosen input parameter.
This change will make the calculation view find the five nearest states to the UFO sighting.
I suppose everyone knows someone who knows someone who has seen a UFO. My mother saw one as a child in 1950s, rural, Northern Spain. She saw her whole future life pass before her, including leaving Spain and having two sons. On the subject of children, I have found that they can kill a lot of interests. If you are lucky they help you resurrect them later. I feel privileged as my children have taken an interest in four of my interests. My youngest started to love biking at an early age, which used to make the end of a working day really special. Later as a teenager she really got into Battle Star Galactica Reimagined. My middle one started to like Spiderman and collect the comics. Deep sigh. My eldest has recently got into the original Star Trek series but, anti-socialite that she is, does not want me to watch it with her. Deeper sigh.
I have really enjoyed reviewing this video on geo spatial joins and found Bob’s standard approach of saying what he is going to do, doing it while re-emphasising key points and varying tasks enough to reinforce the key points of what is being discussed very comforting. It’s an approach many teachers could do with adopting, especially now when dealing with the new changes to the curriculum.