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

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:

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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sergio Guerrero
      Sergio Guerrero

      this is a great blog - I have recently been working with the geospatial capabilities.. this is a fun way to incorporate it.

      well done and thank you for sharing!

       

      Author's profile photo Morten Wittrock
      Morten Wittrock

      ASCII art 4 life <3 Cool blog, Vitaliy.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      \(◎o◎)/

      Neat! Thanks for sharing! 🙂

      Author's profile photo Alisdair Templeton
      Alisdair Templeton

      Hey Vitaliy,

      This was fun!

      Question on the data set used. When I ran the statement with the ST_UnionAggr("SHAPE") command I was getting the error:

      * 669: spatial error: exception 1600002: An internal error occurred

      I found that one of the rows of the imported data failed the ST_IsValid() check, so by adding WHERE "SHAPE".ST_isValid = 1 to the aggregate fixed the error. But now my map is 1 polygon short...

      Possibly the polygon wasn't closed?? I guess i'm wondering how you deal with this sort of issue - how can the geometry be fixed? Looking at the generated SVG I'm wondering which country is now missing 🙂

      Thanks again for a great blog.

      AT

       

       

       

       

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

      Hi  Alisdair. I assume you used already HANA 2.0 in your exercise? If so that's why I did not run into such issue, and you might as there was change in ST_UnionAggr method.

      I'll have a look when get free minute into that, but the workaround you used should make it working indeed.

      I talked to product manager just a few weeks ago, and they planned improvements in working with invalid shapes.

      Thanks for trying it out 🙂

      Author's profile photo Alisdair Templeton
      Alisdair Templeton

      Hi Vitaliy,

      Yes, you are correct, it was the HANA 2.0 HXE - will be very interested to know how we can deal with invalid shapes. Hoping to do some more work in this area in the future.

       

      Thanks again!

      AT

       

      Author's profile photo Serban Petrescu
      Serban Petrescu

      Hi Vitaly,

      Very nice blog!

      I was thinking that you can also use the SERIES_GENERATE function here instead of using some  temporary table. You could for example create a simple table table function to generate a result set containing the latitude, longitude and a flag indicating if there is land there:

      CREATE FUNCTION ascii_map (IN increment DECIMAL DEFAULT 1) 
      	RETURNS TABLE (lat DECIMAL, lng DECIMAL, land TINYINT)
      	LANGUAGE SQLSCRIPT AS
      BEGIN
      
      	RETURN SELECT glat.generated_period_start AS lat, 
      		glng.generated_period_start AS lng,
      		NEW ST_POINT('POINT (' || glng.generated_period_start || ' ' 
      			|| glat.generated_period_start || ')', 1000004326
      		).ST_CoveredBy(shape) AS land
      		FROM series_generate_decimal(:increment, -90, 90) AS glat
      		CROSS JOIN series_generate_decimal(:increment, -180, 180) AS glng
      		CROSS JOIN ne_earth;
      END;

      Then you could use the STRING_AGG aggregate function to collect the results into map rows:

      SELECT lat, STRING_AGG(CASE WHEN land = 0 THEN ' ' ELSE '*' END ORDER BY lng) AS map_row 
      	FROM ascii_map() GROUP BY lat ORDER BY lat DESC;

      Cheers,

      Serban