session id 2521 1 presented by: tom jamate, university of massachusetts, amherst april 8, 2013...

Session ID 2521 1

Presented by: Tom Jamate, University of Massachusetts, Amherst

April 8, 2013

Session ID 2521

Going Beyond the Address Push-Pin

Extending Advance Web Lookups with Google Maps

2Session ID 2521

Introduction

• In this presentation we will provide an overview and demonstrate the key technical details of creating a Google map display from Advance Web Lookup results, hopefully enough to get your feet wet to try some of these techniques on your own.

• This is one example of the increasing capability of the browser environment. Javascript, DOM, CSS and HTML5 have become a powerful platform that can consume and present a variety of web services.

• This enhancement is experimental and under revision. This is one approach, any number of variations are possible.

3Session ID 2521

Problem• Alumni and development operations need to quickly find key demographic clusters. • Text-only reports do not easily lend themselves to this task.• By integrating geographic visualizations to Advance Web lookups, users can easily create any

number of specialized demographic views to help find Alumni ‘hotspots’, to assist with prospect trip planning or event planning.

4Session ID 2521

Enhancement benefit

One example: Advance Web Lookup to find all School of Engineering alumni who graduated before 2002, gave at least $1000 and live in Florida.

5Session ID 2521

Agenda

1. Review of the Google map heatmap visualization library. The API is one example of a rich web service that can be connected to any browser based application such as Advance Web.

2. Create an Advance Web form a container for the map to be rendered in, with a Javascript helper file. Review two methods of getting map data inside the form’s HTML-Javascript control.

3. A database stored procedure (Oracle, et, al) reads the lookup result table (temp_web_report_keys) and outputs the data as a string formatted as a JS data array. City or town longitude and latitude are pulled from the zip_city table.

4. Variations of a theme: the Circle ‘ratio’ map.

5. Review / Resources.

6Session ID 2521

Programming knowledge needed for this enhancement.

1. Client-side browser: Javascript, HTML

2. Oracle PL/SQL, the ability to create stored procedures.

3. ASP VB.Net (Visual Studio, or text editor).

4. Advance Config Utility web form designer.

7Session ID 2521

Overview

User’s web browser

• Google maps work within Advance Web through the Client-side Javascript layer. Here are the major players:

AW Web report form

Javascript API Call

Database (Oracle, et al)Store Procedure to query address, temp_web_report_key, zip_city (latitude, longitude).

JS dataObject

Google MapsAPI Server

RenderedMap

IIS Web ServerAdvance Web

Session ID 2521 8

Google map Heatmap visualization library.

9Session ID 2521

What is a Heatmap?

• A Heatmap is a data visualization technique that renders the intensity and distribution of data points on a map. A color gradient is used to reflect the variation of point values, the higher the value, the ‘hotter’ the color used for the data point on the map.

• Heatmaps serve as a good general reference to quickly reveal clusters or 'hotspots' for a given dataset:

Clusters of top donorsin Texas:

10Session ID 2521

What a Heatmap is not, or why cartographers hate Heatmaps.

• Google Heatmaps are not a substitute for sophisticated demographic analysis tools. Tools such as esri Arcmap can provide precise classifications and visual representations of demographic data. (See Choropleths, Isopleths and Jenks breaks)

• The ability to use the Google map api to create Heatmaps and Circle-ratio maps is a step towards it becoming a serious GIS platform. A trend that will probably continue.

11Session ID 2521

The Google maps API

• Google maps are created in the browser’s Javascript environment and are rendered inside a <DIV> tag with a unique ID.

• To display a map on an Advance Web form, the DIV tag needs to exist in an HTML control on the form.

• The Google map API is called from within a <script> tag on a web page.

• Weighted map points are created with a data object array within the map <script> tag.

12Session ID 2521

Calling the Google Heatmap API, client-side javascript.

<script type=“text/javascript”

src="https://maps.googleapis.com/maps/

api/js?v=3.exp&sensor=false&libraries=visualization">

</script>

<div id="map_canvas" style="height: 600px;

width: 800px;"></div>

var geoData =

[

{location: new google.maps.LatLng(37.782551, -122.445368),weight:6},

{location: new google.maps.LatLng(26.8486, -80.0588),weight:6},

{location: new google.maps.LatLng(37.783273, -122.440324),weight:3}

];

The map data object lives within a JS script tag. We’ll need to generate and place this data object on the page:

13Session ID 2521

Creating the map, client-side javascript

var mapOptions = {

zoom: 4,

center: new google.maps.LatLng(39.50, -98.35),

mapTypeId: google.maps.MapTypeId.ROADMAP

};

map = new

google.maps.Map(document.getElementById(‘map_canvas’), mapOptions); // Create the map object.

pointArray = new google.maps.MVCArray(geoData);

heatmap = new google.maps.visualization.HeatmapLayer({

data: pointArray

}); // Create the heat map layer.

heatmap.setMap(map);

14Session ID 2521

Google map Heatmap example

If you want to quickly get your feet wet, a great way to learn is to try out the demo code of a Google Heatmap example from on your workstation.

A working Heatmap example can be found here:

https://google-developers.appspot.com/maps/documentation/javascript/examples/layer-heatmap

Session ID 2521 15

The Advance web-form map container.

16Session ID 2521

Two methods for pushing map-point data into web form.

1. Use the traditional Advance Web Data Command – Data Transaction referenced by a web form. Limited to varchar2 output size.

2. Use a small custom Vb.Net aspx handler program to insert the data from a <script> tag reference on the web page. A little more work but allows for unlimited output from a looping refcursor, not vendor supported.

( This is similar to current web development methods of delivering a web service-like JSON object into a client-side javascript environment for presentation.)

17Session ID 2521

Method 1, Data access - stored procedure

We have all the components to create a weighted map point:

Population (entity) counts from lookup temp_web_report_keys

Where they live: addresses

Zip code level Longitude and latitude: zip_city

The count of homes per city is used for the map’s weighted map points.

18Session ID 2521

Method 1, Data access – create the stored procedure

Data access for the context-report web form references a stored procedure that queries the lookup results and outputs the JS object array.

The count of homes per city is used for the map’s weighted map points:

CURSOR g_cnt IS

select '{location: new google.maps.LatLng('||

z.latitude||', '||z.longitude||'),'||

'weight:'||count(unique e.id_number)||'},' m_dat,

count(unique e.id_number) cnt, z.city, z.state,

substr(h.zipcode,1,5) a_zip, z.latitude, z.longitude

from temp_web_report_keys k, address h, zip_city z, entity e

where

k.session_id = in_session_id

and k.id_number = e.id_number

and k.id_number = h.id_number

and substr(h.zipcode,1,5) = substr(z.start_zip,1,5)

and h.addr_status_code = 'A'

and h.addr_type_code in ( 'H') -- home

group by z.city, z.state, substr(h.zipcode,1,5), z.latitude, z.longitude

order by cnt desc

19Session ID 2521

Method 1, stored procedure to AW form to output string.

The stored procedure loops through the SQL cursor to build the JS object array string that’s passed to the web form and into the Javascript environment:

…

FOR gr in g_cnt

Loop

JSMTXT := JSMTXT ||' '||gr.m_dat;

End loop;

open RC1 for

select rtrim(JSMTXT,',') "JSMTXT“ from dual;

20Session ID 2521

Method 1, data access command/transaction

Data access for the web form is provided from a typical AW command/data transaction via the Config utility:

21Session ID 2521

Advance web-form container

Create an Advance Web report form for a context sensitive report. An HTML control sets up the JS environment for the map:

22Session ID 2521

Javascript helper file, holds the code for the map definition.

var map, pointarray, heatmap;

function makeHeatmap(divID) {

var mapOptions = {

zoom: 4,

center: new google.maps.LatLng(39.50, -98.35),

mapTypeId: google.maps.MapTypeId.ROADMAP

};

map = new google.maps.Map(document.getElementById(divID),

mapOptions);

pointArray = new google.maps.MVCArray(taxiData);

heatmap = new google.maps.visualization.HeatmapLayer({

data: pointArray

});

heatmap.setMap(map);

}

23Session ID 2521

Method 2. Use a custom Vb.net handler to populate the map.

Use a small custom Vb.Net aspx handler program to insert the data from a <script> tag reference. A little more work but allows for unlimited output from a looping refcursor, not vendor supported.

This is similar to current web development methods of delivering a web service-like JSON object into a client-side javascript environment for presentation.

24Session ID 2521

Method 2, refcursor output, database side.

open RC1 for

select

'{location: new google.maps.LatLng('||

z.latitude||', '||z.longitude||'),'||

'weight:'||count(unique e.id_number)||'},' m_dat,

count(unique e.id_number) cnt,

z.city, z.state,

substr(h.zipcode,1,5) a_zip, z.latitude, z.longitude

from temp_web_report_keys k, address h ,

zip_city z, entity e

where

k.session_id = in_session_id

and k.id_number = e.id_number

and k.id_number = h.id_number

and substr(h.zipcode,1,5) = substr(z.start_zip,1,5)

and h.addr_status_code = 'A'

and h.addr_type_code in ( 'H') -- home

group by z.city, z.state, substr(h.zipcode,1,5), z.latitude, z.longitude

order by cnt desc;

First create the stored procedure using a refcursor output parameter:

25Session ID 2521

Method 2, create the custom VB.Net aspx program on web folder.

Protected Function GetAdvPInfo() As AdvanceWebPageInformation

Dim _AdvPInfo = New AdvanceWebPageInformation

With _AdvPInfo

.InitCollections(Me.Controls, Me.Request.Form, Me.Request.QueryString, Me.Session, Me.Validators)

.User = DirectCast(Session("AdvUser"), IAdvanceUser)

.Request = Me.Request

.Response = Me.Response

.PageType = Page.GetType()

.InstanceId = _instanceId

End With

Return _AdvPInfo

End Function

If not ( IsDBNull(Me.GetAdvPInfo().User.Database ) ) Then

dbi2 = New DBInterface( Me.GetAdvPInfo().User.Database)

dbconn2 = dbi2.GetConnection()

End If

Using the AdvanceWebPageInformation object you can get access to the Advance Web user login context. You can use it to test for authentication as well as the db connection handle. Customizations at the VB.Net level are not vendor supported:

26Session ID 2521

Method 2, aspx file, execute the stored procedure, loop through refcursor.

propCMD = New OracleCommand(“ADV_SCHEMA._geo_hm_hmap_js_wkeys_rc", dbconn2)

propCMD.Parameters.Add("in_session_id", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = Session.SessionID

…

Response.Write( NL + " var geoData = [" )

Dim mr as string = ""

Do While propDatarowIndex < propDsS.Tables(0).Rows.Count

If not ( IsDBNull(propDsS.Tables(0).Rows(propDatarowIndex)("M_DAT")) ) Then

mr = propDsS.Tables(0).Rows(propDatarowIndex)("M_DAT")

‘Remove trailing ‘,’

if propDatarowIndex = (propDsS.Tables(0).Rows.Count - 1) then

mr = mr.substring( 0, mr.length-1)

end if

Response.Write( NL + mr )

End If

propDatarowIndex += 1

Loop 'Do While propDatarowIndex < propDsS.Tables(0).Rows.Count)

Response.Write( NL + "];" )

27Session ID 2521

Method 2, reference the custom aspx file from script tag.

The script tag call the custom VB.Net program, which generates data for the map points:

Now the only practical limit is browser-side memory and network bandwidth.

28Session ID 2521

Web form to report application.

The web form is then referenced by an AW application for a context sensitive report and made available to users:

Session ID 2521 29

Variations of a theme: the Circle-ratio map

30Session ID 2521

Circle-ratio map.

• The Circle-ratio is another type of mapping visualization:

31Session ID 2521

Circle ratio data array, client-side javascript

• The Circle ratio takes a slightly different JS data array, a center object with a population parameter:

// Create an object containing LatLng, population.

var myLatLng = new google.maps.LatLng( 42.22, -71.75 ); var myOptions = { zoom: 8, center: myLatLng,

mapTypeId: google.maps.MapTypeId.TERRAIN }; var cir_factor = 6; var citymap = {};

citymap['MA Pittsfield'] = { center: new google.maps.LatLng(42.4665, -73.2893), population: 1470000 /cir_factor };

citymap['MA Amherst'] = { center: new google.maps.LatLng(42.3729, -72.4509), population: 1000000 /cir_factor };

citymap['MA Westfield'] = { center: new google.maps.LatLng(42.1627, -72.7713), population: 890000 /cir_factor };

32Session ID 2521

Variations of a theme: the Circle ratio map.

• Loop through the citymap array to render the circle :

for (var city in citymap) {

// Construct the circle for each value in citymap. // We scale population by 20.

var populationOptions = { strokeColor: "#FF0000", strokeOpacity: 0.7, strokeWeight: 1, fillColor: "#FF0000", fillOpacity: 0.35, map: map, center: citymap[city].center, radius: citymap[city].population / 20 }; cityCircle = new google.maps.Circle(populationOptions);

}

33Session ID 2521

The Google Maps API license

– Google has a restriction on the number of free maps rendered per application (currently 25,000 map loads per day, but that can change).

– Many institutions have site license agreements with Google, yours may.

– Non-profits aren’t affected by the Maps API limits and can apply for a free Maps API for Business license through the Google Earth Outreach grants program.

(see: www.google.com/earth/outreach/grants/software/ )

– Business enterprise licenses are also available.

(see: www.google.com/enterprise/earthmaps/maps-apis.html )

34Session ID 2521

Summary

• What is a Heatmap?

• Details of the Google map api

• Connected map to an Advance Web form

• Map point data from AW command

• Map point data from an external custom Vb.Net

• Circle ratio maps.

35Session ID 2521

Questions? Answers?

?

session id 2521 1 presented by: tom jamate, university of massachusetts, amherst april 8, 2013...

Documents

session id

advance web form

advance web lookups

map data

advance web lookup results

rich web service

variety of web services

google map display