Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

GeoArt with SAP HANA and DBeaver

Yes, I know that today is Friday, but I cannot wait till the next week to share these #GeospatialTuesday posts! It is how thrilled I am after reading Mathias Kemeter‘s Good Things Come Together: DBeaver, SAP HANA Spatial & Beer.

A minute of weakness…

It was not just beer that excited me in his post, but… Yes, I can confirm that Munich is the German destination for a beer. I was there only once in my life — to meet with our team (here with Thomas Grassl , Maximilian Streifeneder , Eshanno Byam, Myles Fenlon , Marius Obert and Kevin Muessig) in summer 2018…

…and to enjoy Bavarian beers ?

…but now: back to the point

What really exhilarated me in what Matthias shared were the built-in preview for spatial data in DBeaver and the fact that now it supports SAP HANA results too!

You’ve seen in my previous blogs that I had to copy spatial results from HANA SQL and to paste them into external web visualizers, or even to write my own visualziation using ASCIIart.

Not anymore!

It took me just a few minutes to install DBeaver, to connect it to my SAP HANA Express instance, and to fire first queries immediately previewing the results in a form of spatial visualizations or on the map!

In my previous post I loaded spatial data set WORLD_BOARDERS, so let’s play with it to do some GeoArt.

WITH POINTS_GRID AS 
(SELECT NEW st_point(glng.generated_period_start, glat.generated_period_start) AS point
FROM
	series_generate_decimal(0.3, -88, 88 + 0.3) AS glat
CROSS JOIN series_generate_decimal(0.3, -180, 180 + 0.3) AS glng)
SELECT point FROM POINTS_GRID g, TM.WORLD_BORDERS b
WHERE
	b.ISO2 = 'UA'
AND point.st_within(shape)=1;

Now switching to Spatial preview in DBeaver.

Now let me switch Spatial Reference System from Simple to EPSG:4326 and … boom!! I got the result preview on the map!

I do not know if you can read how excited I am! And I am!! I AM!!!

Let’s use some new SPS04 geospatial features from 2.0

In the previous post I used already new hexagonal grid clustering. Let’s combine it with the query above, and pay tribute to Australia now.

--Honeycombs
WITH POINTS_GRID AS 
(SELECT NEW st_point(glng.generated_period_start, glat.generated_period_start) AS point
FROM
	series_generate_decimal(0.3, -88, 88 + 0.3) AS glat
CROSS JOIN series_generate_decimal(0.3, -180, 180 + 0.3) AS glng)
SELECT COUNT(*), ST_ClusterID(), ST_ClusterCell() as "Honeycomb"
	FROM (SELECT point FROM POINTS_GRID g, TM.WORLD_BORDERS b
WHERE
	b.ISO2 = 'AU'
AND point.st_within(shape)=1)
GROUP CLUSTER BY point 
USING HEXAGON X CELLS 30;

Now let’s combine it with the enhanced ST_Buffer method, which get the new number_of_interpolation_points parameter in 2.0 SPS04.

Let’s use number_of_interpolation_points parameter equal to 3 to get a map of Australia made of triangles.

--Buffers
WITH POINTS_GRID AS 
(SELECT NEW st_point(glng.generated_period_start, glat.generated_period_start) AS point
FROM
	series_generate_decimal(0.3, -88, 88 + 0.3) AS glat
CROSS JOIN series_generate_decimal(0.3, -180, 180 + 0.3) AS glng)
SELECT ST_ClusterCell().ST_Centroid().ST_Buffer(0.4, 3) as "Buffer"
	FROM (SELECT point FROM POINTS_GRID g, TM.WORLD_BORDERS b
WHERE
	b.ISO2 = 'AU'
AND point.st_within(shape)=1)
GROUP CLUSTER BY point 
USING HEXAGON X CELLS 30;

 


Haven’t tried DBeaver with SAP HANA yet? Give it a try!

Weekend with plenty of time is coming 😉

-Vitaliy (aka @Sygyzmundovych)

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      Thumbs up for the very clever way of generating a grid of points and then selecting those that are inside the shape. Very nice.

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Thanks Serban Petrescu who opened my eyes ? for this approach!

      Actually I wanted to pass extreme points (Xmin(), ... Ymax()) as boundaries for a grid of points in a single SQL (without using SQLScript), but failed to come up with this in the short time. So, using the full range - which works, but is inefficient 🙁