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)