ASCII art map of the World: using SAP HANA geospatial processing

H   H EEEEE L     L      oOo  !!
H   H E     L     L     O   O !! 
HHHHH EEEEE L     L     O   O !! 
H   H E     L     L     O   O    
H   H EEEEE LLLLL LLLLL  OOO  !!

Last year mankind celebrated 50th anniversary of the ASCII art: presenting graphics using only 95 printable characters defined by the ASCII Standard from 1963. [Source: Wikipedia]. The ASCII art left a mark in the history of SAP UI too:

In addition to tutorials previously announced, recently I published last two tutorials in a series introducing SAP HANA geospatial processing:

• 3rd and 4th coordinate dimensions: http://www.sap.com/developer/tutorials/hana-spatial-intro5-z-m-coordinates.html
• Spatial Reference Systems (SRS): http://www.sap.com/developer/tutorials/hana-spatial-intro6-srs.html

When I was thinking about an example to showcase the use of SRS, the idea of producing ASCII map from Earth shapefiles came to my mind.

For this showcase I used a free SAP HANA 1 SP12, express edition. It has geospatial processing enabled already as a part of the installation. You can try it out too, so follow along with me! And if you are not familiar with geospatial processing yet, just do introduction tutorials first. Geospatial is fun!

To start with my example I needed a shapefile with all Earth’s lands, which I found at a public domain project Natural Earth. So, I downloaded the file ne_110m_land.zip from http://www.naturalearthdata.com/downloads/110m-physical-vectors/110m-land/, copied it into /usr/sap/HXE/HDB00/work/ on my HANA Express host and unzipped. [Make sure files are accessible at the OS level for hxeadm HANA user.]

I checked the .prj file to make sure it is not using some exotic Geographic coordinate system. Luckily, no. WGS 1984 is the one supported by SAP HANA out of the box with two Spatial Reference Systems: 4326 for the round Earth and 1000004326 for the planar projection.

All I needed was to load the content of this shapefile into a table with correct SRS id using the following SQL statement. Obviously, the user running this and other SQL statements has to have developers rights in SAP HANA.

IMPORT NATURAL_EARTH.NE_110M_LAND AS SHAPEFILE FROM '/usr/sap/HXE/HDB00/work/ne_110m_land' WITH SRID 1000004326;

SAP HANA created a schema NATURAL_EARTH, the table NE_110M_LAND in it, and then loaded the content of the shape files. 127 records have been created, each with a single polygon with requested SRS ID 1000004326.

Each row stores a single polygon (a shape) representing a landmass – a continent or an island. But for my exercise I needed a single multipolygon containing all of the Earth land in one geometry. Let’s create a new table NE_EARTH with the spatial ST_UnionAggr() aggregation of data from the shapefile.

CREATE COLUMN TABLE "NATURAL_EARTH"."NE_EARTH" ("SHAPE" ST_GEOMETRY(1000004326) ) ;
INSERT INTO "NATURAL_EARTH"."NE_EARTH" SELECT st_unionaggr(SHAPE) FROM "NATURAL_EARTH"."NE_110M_LAND";
SELECT "SHAPE".st_asSVG() FROM "NATURAL_EARTH"."NE_EARTH";

Visualization of the SVG from the last SQL statement gives me exactly what I was looking for – the simplest cylindrical projection of the Earth with constant intervals of meridians and parallels.

Now I need a table DRAW_EARTH to store my calculated ASCII map:

CREATE COLUMN TABLE "NATURAL_EARTH"."DRAW_EARTH"( "LATIT" DEC, "SSTRING" NVARCHAR(1000) );

and the procedure ASCII_MAP_FLAT to go throw a multipoligon with the Earth’s land and check if particular point with latitude and longitude coordinates is covered by the land (True=1 in SAP HANA geospatial method ST_CoveredBy()), or not (False=0). The procedure takes resolution as an input parameter, which defines how many ASCII characters will be printed in one line.

create procedure "NATURAL_EARTH"."ASCII_MAP_FLAT" (IN resolution INT)
 LANGUAGE SQLSCRIPT AS
 llon, llat INTEGER;
 ssurf, ppoint STRING;
 BEGIN
    llat := 90;
    WHILE llat>=-90 DO
        llon := -180;
        ssurf := '';
        WHILE llon<=180 DO
            select new st_point('POINT ('||:llon||' '||:llat||')', 1000004326).ST_CoveredBy(SHAPE) into ppoint FROM "NATURAL_EARTH"."NE_EARTH";
            ssurf := concat (ssurf, ppoint);
            
        llon := llon+360/resolution;
        END WHILE;
        INSERT INTO "NATURAL_EARTH"."DRAW_EARTH" VALUES (:llat, :ssurf);
        llat := llat-360/resolution;
    END WHILE;
 END;

Let me try it out for 180 characters per line. And then I need to replace 0 with " ", and 1 with "*" when presenting the output from DRAW_EARTH result table.

truncate table "NATURAL_EARTH"."DRAW_EARTH";
CALL "NATURAL_EARTH"."ASCII_MAP_FLAT"(RESOLUTION => 180);
SELECT replace(replace("SSTRING", '0', ' '), '1', '*') as "ASCIIMAP" FROM "NATURAL_EARTH"."DRAW_EARTH";

But here comes the problem – the output formatting of ASCII art in SQL Console…

For my ASCII art I need an output using monospaced font. And not just the monospaced font, but the “square” font, where characters have the same width and heights. The only place I found such a font on my Windows 10 laptop was a Command Prompt with 8x8 raster font. It looks promising.

Ok, so I need to use the Command Prompt application on Windows. I ssh into my HANA Express host using PuTTY’s plink utility. You obviously should use IP address of your HANA server and the path to .ppk file with your PuTTY’s private key file, if you are following my steps.

plink -ssh root@34.00.00.00 -P 22 -i "C:\.....\hxe_1_18.ppk"

Switch user to hxeadm HANA user and open HANA command line interface hdbsql connecting to your database with NATURAL_EARTH schema.

su - hxeadm
hdbsql -i <db-instance-nr> -d <db-name> -u <development-user> -p <user-password>

(I know, I know… Who on the earth is using SystemDB for development?! and SYSTEM as a developer user?? ;-))

Once in HANA command line interface, toggle escape output format OFF using \es, and toggle page by page output OFF using \pa commands in hdbsql.

Now let’s check the map!!

SELECT replace(replace("SSTRING", '0', ' '), '1', '*') as "ASCIIMAP" FROM "NATURAL_EARTH"."DRAW_EARTH";

Hurrah!!

Beautiful, isn’t it?!

And to have a look at this map from other perspectives, please follow to the part 2 of this blog.

-Vitaliy, aka @Sygyzmundovych