Using SQL Anywhere's SVG capabilities for choropleth maps

Robert_Russell · ‎02-07-2014

Using SQL Anywhere's SVG capabilities for choropleth maps

What's an old ABAP developer supposed to do?

Using SQL Anywhere's SVG capabilities for choropleth maps

Background

I have been interested in geographic information systems (GIS) software (or simply maps : ) ever since I lived in a place where the streets have no names. I was lost as well as lost in translation. The following video, a TED talk from Derek Sivers, covers the “weird or just different” nature of not understanding where I was and features the country that gave me a hobby of checking out the free GIS world.

So the following is based on my interests in maps and a bit of déjà vu for me as the above video did appear in a previous blog but that blog was badly broken by the move from SDN to SCN(jive) :sad: . However I really like the video and it sums up some of the feelings I had at the time.

What follows is based as my “Hello World” example for using GIS software and that is based on mapping BITLY links. I will also cover using choropleth maps with England and Wales crime data, which I failed to map using another SAP product. The failures to map this data lead me to try and achieve the results I wanted with choropleth maps on SQL Anywhere by using its inbuilt SVG capabilities.

What are choropleth maps

The title of my blog mentions choropleth maps and if you are curious as to what a choropleth map is, then I like the explanation found here. In this video, I use choropleth maps as a featured “star” here. However I still struggle to pronounce the actual word “choropleth” . Here is an example that I created using standard SQL Anywhere web services, procedures and functions. All the code for this bitly based example is at the bottom of my blog. Now the interactive nature of the SVG is unfortunately blocked by the SCN Jive platform so only the static screen shot is available. :sad:

What are BITLY links

Bitly.com offers an URL (webpage link) shortening service and also makes statistics on the performance of the links available. @SCNblogs twitter account uses this service to tweet about the blogs appearing on the SCN Jive platform. (@SCNblogs also uses other URL shortening services such as tinyurl.com).

Starting point

While I was failing to make a map in another product, I was convinced I could achieve what I wanted by just using SQL Anywhere. As my example of mapping bitly clicks was based on importing the Natural Earth shapefile and that BITLY use the ISO codes for countries found in this shapefile. Then it’s a case matching both and working out how to colour the countries for a range of values. Using SQL Anywhere’s inbuilt support for shapefiles and web services I was sure I could achieve this objective.

As SCN is worldwide I thought it would be a good idea to map out an example @SCNblogs bitly link and get an impression of the world wide nature of SCN on a map.(Bitly now offer maps on their stats page and the location is based on IP addresses. So the results are not perfect as some web proxies could transport a user to another country. However all the bitly links a mapped via the same method, so a fair comparison for the results of each link.).

A plan comes together

SVG code

I had a basic SVG map of clicks up and running in SQL Anywhere but I thought it needed some more details and more control. Searching the web I came across what I wanted on Peter Collingridge’s site here.

http://www.petercollingridge.co.uk/interactive-svg-components/pan-and-zoom-control

The ability to pan and zoom control all in SVG and I was impressed with the detail and the way the code was implemented. So I approached Peter via his website contact details and asked if I could use his code. He allowed me to use it and I thank him for that.

My own “Sybase blog from the archives” retrieval.

I had successfully adapted my SQL Anywhere standalone SVG code with Peter’s code and now needed a way to get this automatically via an SQL Anywhere procedure. Searching for SVG and SQL Anywhere I found Breck Carter’s SQL Anywhere blog that listed some SVG blog examples. I have found Breck's blog a valuable source of information to use and manage my SQL Anywhere installations. The link for the SVG blogs.

http://sqlanywhere.blogspot.co.uk/2012/03/tales-from-doc-face.html

However the link for the SVG blogs was via http://iablog.sybase.com/ but that is no longer available, so I went to the “Way Back Machine” and found a copy.

http://web.archive.org/web/20130401052034/http://iablog.sybase.com/hinsperg/2012/01/svg-support-in-s...

So now I had the way I would implement the SVG choropleth map, by adapting Jason Hinsperger’s code in the above link. Thanks to Jason not only for the code but taking time to answer my questions in the forums.

I had already created the bitly api in SQL Anywhere previously so knew I could avoid JSON and use OPENXML to query the BITLY api in XML format. Also by starting a web server process with SQL Anywhere I could do all the work with SQL Anywhere. I have been impressed by the capabilities of SQL Anywhere and it enables me to achieve so much more than I could without it.

So now I had to decide which bitly link to feature.

Choosing which BITLY link to feature

For my Data Geek 2 entry I used SQL Anywhere to analyse the @SCNblogs bitly links that came from the twitter domain. So I decided to adapt this code and use the bitly countries api, which would allow me to count the clicks per country. I analysed all the @SCNblogs tweets from January to find the tweet which had attracted clicks from the most countries. It looked like this in SAPUI5.

I put the results in a spreadsheet and sorted by the count of clicks per country. Shown here in Google docs and can share the data if you would like to check it out.

I did find it interesting, again, that 2 out of the 4 tweets shown above (and maybe more in the other 810 tweets) no longer appear at the tweeted SCN link. I did find that a lot of content has been moved or removed while collecting the data for my Data Geek entry as well.

However the top blog with the clicks from the most countries was Katan Patel’s blog with a total of 24 countries. His blog link and title was.

What's an old ABAP developer supposed to do?

Here is a static screen shot of Katan’s blog on the bitly stats page.

So in honour of Katan’s blog I have to name my map….

A map of old ABAP developers

I am being a bit cheeky with the title as I have jumped to the conclusion that Katan had touched on a subject close to old ABAP developer’s hearts. I was imagining old ABAP developers clicking on the link from all over the world when the tweet appeared on the SCNblogs timeline.

Also I did think to change the title of my blog to….

“A map of old ABAP developers” :razz:

But I resisted this temptation.

So which countries clicked most on the link?

When I moved around the map I found that the most clicks came from Australia.

Also I zoomed in to focus on second placed Germany,

Suits Vs Geeks

While extracting the data from @SCNblogs for January, I noticed that both the ABAP and Business Trends communities had both posted 43 blogs each. I knew I could loop through these bitly links for each community separately and therefore produce a map for each. I categorised the Business Trends blogs as suits and the ABAP blogs as geeks. The intention was to see where in the world the suit’s (business trends) blogs were popular against those of the geek’s (ABAP) blogs :smile: . This would be based on the @SCNblogs followers not the SCN site as the links are relevant to @SCNblogs tweets.

Link count stats for the communities January's tweets from @SCNblogs.

abap\ Count	43
business-trends\ Count	43

1) A map for the Business Trends blogs - Most SCN suits in the United States

2) A map for the ABAP blogs - More Geeks in the SCN world and top click count to Germany

I decided to focus more on Europe as there appeared to be more SCN geeks in that part of the world.

3) Business Trends Blogs - BITLY clicks in Europe, European Suits.

4) ABAP Blogs BITLY clicks in Europe - more @SCNblogs geek followers than suits in Europe for January :smile:

Using SQL Anywhere with my SAP HANA Cloud Portal Site (update 20/6/2014)

I decided to use my SQL Anywhere web services and procedures as a base for many pages of my SAP HANA Cloud Portal Moive Producer Entry at the following link.

SAP HANA Cloud Portal Movie Producer: It’s an SCN World

One item that SQL Anywhere allowed me to achieve was expanding Twitter t.co links to the original URL. Expanding the url allowed me to get to the bit.ly links that @SCNblogs and @SCNLibrary use, so I could subsequently use the the bitly api. I did think originally that it was a step too far in programming terms for myself. However I found that SQL Anywhere had a procedure option REDIR and setting (COUNT = 0) that would allow me to expand the t.co link with a simple select statement. Yet another reason why I am so impressed with SQL Anywhere. Using this method I was then able to cut and paste the t.co links into my SAP HANA Cloud portal page and my SQL Anywhere services could help me map the links as per the details in the blog link above.

Creating the following procedure,

CREATE PROCEDURE "DBA"."xtco"( in link long varchar )

result(

attribute long varchar,

value long varchar )

url '!link' type

--url 'http://t.co/Azu4wKEa9C'

'HTTP:GET' set

'REDIR (COUNT = 0)'

then allows me to use a select statement to expand the t.co link.

select value from xtco( 'http://t.co/Azu4wKEa9C' ) where attribute = 'location'

Mapping crime data

First I will divert slightly to show how I got the crime data to use with a choropleth map.

Previously I had uploaded over 13 million crimes to the SAP HANA Cloud Platform (SAPHCP). The details can be found in my SCN blog here.

http://scn.sap.com/community/lumira/blog/2013/12/02/trying-out-lumira-with-the-hana-cloud-platform-f...

Now I did have the data in CSV files but I was just curious to see if I could get the data back down from the cloud by connecting SQL Anywhere to the SAPHCP.

I will thank Stoyen Manchev again here as I am enjoying trying to use the SAPHCP in various ways as a result of following his blog.

http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/10/17/8-easy-steps-to-develop...

Connecting SQL Anywhere to the SAP HANA Cloud Platform

First I opened the NEO tunnel

Then I setup an ODBC HANA connection on Windows called HDBODBC as shown.

With the details for the connection taken from the tunnel opened in the first step.

Then from SQL Anywhere I created a remote server with an EXTERNAL Login. The external login is important, and uses the logon details displayed when the SAPHCP tunnel is opened.

CREATE SERVER "HDBODBC" CLASS 'HANAODBC' USING 'HDBODBC';

CREATE EXTERNLOGIN "DBA" TO "HDBODBC1" REMOTE LOGIN 'DEV_1ZKJCC2R73UYDMCIK0COYPNHF' IDENTIFIED BY '***';

The external login would have to be deleted and recreated every time the tunnel is opened as the password gets reset each time.

Next I created a proxy table by picking the CRIME table which I had adapted from my previous blog. By adapted I mean I had to convert the table name to upper case and convert data types from NVARCHAR to VARCHAR to allow SQL Anywhere to read the data from the ODBC connection.

I selected just the columns I wanted

As I was testing I thought I would copy the data to a new local table. So I did a straight forward select into a new table and left it running thinking it would take a long time to complete. I was surprised when I came back the process had finished.

So now I had my data back from the clouds. It was now time to adapt the choropleth procedures to use the local table. This time for the map, I had created my shapefile from the KML files available on the Police data website http://data.police.uk/data/kmls/ using open source software.

As I am still investigating how to use the data so I will just show a couple of examples of the choropleth maps.

First up is one showing all crime is in London :smile:

Not surprising if you take into account population. I like this map which is of the UK distorted by population

https://twitter.com/Amazing_Maps/status/414561616735502336

Using exactly the same data but this time I filtered out Metropolitan Police force for London with SQL on the table, to produce the following map.

The SQL Anywhere SVG Choropleth code.

I do not use SQL Anywhere in my day job. However as I can achieve more with SQL Anywhere than without it and I am enjoying using it, then I thought I would share the code. Also Peter (as stated earlier) was fine with me using his SVG code. Also I have run the process on a few SQL Anywhere instances now and know it at least works and would appreciate any comments on my blog/code.

Import Natural Earth data

I used the public domain shapefiles from http://www.naturalearthdata.com/downloads/

The exact download I used was

http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/cultural/ne_50m_admin_0_...

Then I followed the help pages to import a shapefile. I used an SRID of “4326” and imported into a table called countries

http://dcx.sybase.com/1200/en/dbspatial/tutorials-spatial.html

Setup Bitly API

I followed the bitly api to get the basic OAUTH token that would be used in the SQL Anywhere function.

http://dev.bitly.com/authentication.html

e.g.

curl -u "user:password" -X POST "https://api-ssl.bitly.com/oauth/access_token"

Used Openssl to get the BITLY certificate to be used by the SQL Anywhere function.

openssl s_client -connect api-ssl.bitly.com:443 -showcerts >/var/tmp/httpsBITLYcert2

SQL Anywhere code

The following code was tested by a cut and paste into Interactive SQL and worked with the following changes.

Lines to adapt.

In the bitlycountries_f_xml function,

certificate 'file=c:\\\\Users\\\\robert.russell\\\\httpsBITLYcert2'

The above line needs to point to the bitly API certificate file created with the openssl command.

In the controlSCNbitly procedure ,

from openxml( bitlycountries_f_xml( '{BITLY OAUTH TOKEN GOES HERE', 'xml' ,long_link ), 'response/data/countries',1, )

The above line needs the BITLY OAUTH token created with the curl command above.

The code below would create the following.

1) 1) CREATE FUNCTION "DBA"."bitlycountries_f_xml"

2) 2) CREATE FUNCTION "DBA"."retCol_f"

3) 3) CREATE PROCEDURE "DBA"."controlSCNbitly"

4) 4) CREATE SERVICE "SCNblogsBITLY"

Once created if the SQL Anywhere is started with a web server e.g. –xs HTTP option. Then,

http://{SQL_ANYWHERE_HOST}:{PORT}/SCNblogsBITLY/http://bit.ly/1ifPTz8

would display the choropleth map for Katan’s blog. Changing the bit.ly at the end of the url would map the clicks for that link.

CREATE FUNCTION "DBA"."bitlycountries_f_xml"( in "access_token" long varchar,in "format" long varchar,in "link" long varchar )

returns long varchar

url 'https://api-ssl.bitly.com/v3/link/countries'

header 'Content-type: application/xml'

certificate 'file=c:\\\\Users\\\\robert.russell\\\\httpsBITLYcert2'

type 'HTTP:GET'

;

CREATE FUNCTION "DBA"."retCol_f"( "val" dec, "max" dec ) RETURNS LONG VARCHAR

BEGIN

DECLARE op dec;

DECLARE col_r LONG VARCHAR;

--select (( val / max ) * 100) as op;

set op = (( val / max ) * 100);

--CASE cast(op as int)

set col_r = CASE

WHEN op = 0 THEN 'lightgrey'

WHEN op BETWEEN 0.00001 AND 25 THEN '#FC4E2A'

WHEN op BETWEEN 25.0000001 AND 50 THEN '#E31A1C'

WHEN op BETWEEN 50.0000001 AND 75 THEN '#BD0026'

WHEN op >= 75.0000001 THEN '#800026'

ELSE 'white'

END;

return (col_r);

END

;

CREATE PROCEDURE "DBA"."controlSCNbitly"(inout long_link long varchar) result ( html_string LONG VARCHAR )

BEGIN

--set up

declare local temporary table c_clicks (

country as long VARCHAR,

clicks as int,

col as long VARCHAR,

id as int,

name as long varchar

);

call dbo.sa_set_http_header( 'Content-Type', 'image/svg+xml');

CREATE OR REPLACE VARIABLE @blink long varchar;

CREATE OR REPLACE VARIABLE @JS TEXT;

CREATE OR REPLACE VARIABLE @Max_c TEXT;

CREATE OR REPLACE VARIABLE @head TEXT;

CREATE OR REPLACE VARIABLE @style TEXT;

CREATE OR REPLACE VARIABLE @scr TEXT;

CREATE OR REPLACE VARIABLE @paths TEXT;

CREATE OR REPLACE VARIABLE @ENDING TEXT;

CREATE OR REPLACE VARIABLE @svg TEXT;

CREATE OR REPLACE VARIABLE @i int;

CREATE OR REPLACE VARIABLE @M_C int;

CREATE OR REPLACE VARIABLE @leg1 TEXT;

CREATE OR REPLACE VARIABLE @leg2 TEXT;

CREATE OR REPLACE VARIABLE @leg3 TEXT;

CREATE OR REPLACE VARIABLE @leg4 TEXT;

CREATE OR REPLACE VARIABLE @leg5 TEXT;

CREATE OR REPLACE VARIABLE @format2 TEXT;

--populate the temp table with countris. option to use where clause to narrow selection e.g. where region_un = 'Africa';

--set the default colour to lightgrey

insert into c_clicks

select iso_a2, 0, 'lightgrey', record_number, name FROM countries ;

--use openxml to update temp table with clicks from countries matching ISO codes

message long_link;

update c_clicks

set c_clicks.country = ox.country,

c_clicks.clicks = ox.clicks,

c_clicks.col = 'red'

from openxml( bitlycountries_f_xml( '{BITLY OAUTH TOKEN GOES HERE', 'xml' ,long_link ), 'response/data/countries',1, )

with(

country long varchar 'country',

clicks int 'clicks'

) ox

where c_clicks.country = ox.country;

--Need to get MAX value for clicks to choropleth map

select MAX(clicks) into @Max_c FROM c_clicks;

IF @Max_c < 4 then

set @Max_c = 4;

END if;

set @leg1 = '0';

set @leg2 = STRING('<= ',(@max_c/100)*25); --25

set @leg3 = STRING('<= ', cast((@max_c/100)*50 as numeric(35,2)) ) ;--50

set @leg4 = STRING('<= ', cast((@max_c/100)*75 as numeric(35,2)) ) ; --75

set @leg5 = STRING('<= ', cast((@max_c/100)*100 as numeric(35,2)) ); --1000

--cast(6.1234567890 as numeric(10,2))

--call function to set the colour depending on the clicks

update c_clicks

SET col = retCol_f(clicks, @Max_c)

WHERE clicks > 0;

--set the output header as svg

set @head = '<?xml version="1.0" standalone="no"?>

<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN"

"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">

set @style = '<style>

.country:hover{

fill: #22aa44;

}

.compass{

fill: #fff;

stroke: #000;

stroke-width: 1.5;

}

.button{

fill: #225EA8;

stroke: #0C2C84;

stroke-miterlimit: 6;

stroke-linecap: round;

}

.button:hover{

stroke-width: 2;

}

.plus-minus{

fill: #fff;

pointer-events: none;

}

.wrapper .text {

position:relative;

bottom:30px;

left:0px;

visibility:hidden;

}

.wrapper:hover .text {

visibility:visible;

}

.colour0 {fill: lightgrey;}

.colour1 {fill: #FC4E2A;}

.colour2 {fill: #E31A1C;}

.colour3 {fill: #BD0026;}

.colour4 {fill: #800026;}

</style>' ;

set @scr = '<script type="text/ecmascript">

<![CDATA[

var transMatrix = [1.6106127360000013, 0, 0, 2.147483648000001, 303.0237952,

173.1564544];

function onhover(text) {

running = true;

varName = document.getElementById("varName");

varName.firstChild.nodeValue = "Country: " + text;

target = window.event.target;

};

function init(evt)

{

if ( window.svgDocument == null )

{

svgDoc = evt.target.ownerDocument;

}

mapMatrix = svgDoc.getElementById("map-matrix");

width = evt.target.getAttributeNS(null, "width");

height = evt.target.getAttributeNS(null, "height");

}

function pan(dx, dy)

{

transMatrix[4] += dx;

transMatrix[5] += dy;

newMatrix = "matrix(" + transMatrix.join('' '') + ")";

mapMatrix.setAttributeNS(null, "transform", newMatrix);

}

function zoom(scale)

{

for (var i=0; i<transMatrix.length; i++)

{

transMatrix[i] *= scale;

}

transMatrix[4] += (1-scale)*width/2;

transMatrix[5] += (1-scale)*height/2;

newMatrix = "matrix(" + transMatrix.join('' '') + ")";

mapMatrix.setAttributeNS(null, "transform", newMatrix);

console.log(transMatrix);

}

]]>

</script>

<g id="map-matrix" transform="matrix(1.6106127360000013, 0, 0, 2.147483648000001, 303.0237952,

173.1564544)">

';

SET @i = 1;

select max(id) into @M_C from c_clicks;

WHILE @i <= @M_C LOOP

SET @paths = @paths ||

( '<path fill="' || (select c_clicks.col from c_clicks where c_clicks.id = @i) || '" stroke="black" stroke-width="0.01%" d="'||

(SELECT geometry.ST_AsSVG( 'PathDataOnly=Yes' ) FROM countries WHERE record_number = @i) || '" onmouseover="onhover(''' || (SELECT name FROM c_clicks WHERE id = @i) || ' No.clicks='|| (SELECT clicks FROM c_clicks WHERE id = @i) || ''')' ||'"/>');

SET @i = @i + 1;

END LOOP;

set @ENDING = STRING('</g>

<text style="font-weight: bold;" id="varName" x="175" y="390" >Country: </text>

</svg>');

set @format2 = '"/>';

SELECT @head || @style|| @scr || @paths || @format2 ||

@ending from dummy;

END

;

CREATE SERVICE "SCNblogsBITLY" TYPE 'RAW' AUTHORIZATION OFF USER "DBA" URL ON METHODS 'HEAD,GET' AS call "controlSCNbitly"(:url);

;

Choropleth maps of @SCNblogs links and crime data using SQL Anywhere

Using SQL Anywhere's SVG capabilities for choropleth maps

Background

What are choropleth maps

What are BITLY links

Starting point

A plan comes together

SVG code

My own “Sybase blog from the archives” retrieval.

Choosing which BITLY link to feature

What's an old ABAP developer supposed to do?

A map of old ABAP developers

Suits Vs Geeks

Using SQL Anywhere with my SAP HANA Cloud Portal Site (update 20/6/2014)

Mapping crime data

Connecting SQL Anywhere to the SAP HANA Cloud Platform

The SQL Anywhere SVG Choropleth code.

Import Natural Earth data

Setup Bitly API

SQL Anywhere code

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z