Going Beyond the Address Push-Pin Extending Advance Web

Going Beyond the Address Push-Pin Extending Advance Web Lookups with Google Maps Presented by: Tom Jamate, University of Massachusetts, Amherst April 8, 2013 Session ID 2521 1

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 HTML 5 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. Session ID 2521 2

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. Session ID 2521 3

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. Session ID 2521 4

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. Session ID 2521 5

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. Session ID 2521 6

Overview • Google maps work within Advance Web through the Client-side Javascript layer. Here are the major players: Database (Oracle, et al) IIS Web Server Advance Web User’s web browser AW Web report form Store Procedure to query address, temp_web_report_key, zip_city (latitude, longitude). JS data Object Javascript API Call Rendered Map Session ID 2521 7 Google Maps API Server

Google map Heatmap visualization library. Session ID 2521 8

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 donors in Texas: Session ID 2521 9

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. Session ID 2521 10

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. Session ID 2521 11

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: 600 px; width: 800 px; "></div> The map data object lives within a JS script tag. We’ll need to generate and place this data object on the page: var geo. Data [ {location: ]; Session ID 2521 = new google. maps. Lat. Lng(37. 782551, -122. 445368), weight: 6}, new google. maps. Lat. Lng(26. 8486, -80. 0588), weight: 6}, new google. maps. Lat. Lng(37. 783273, -122. 440324), weight: 3} 12

Creating the map, client-side javascript var map. Options = { zoom: 4, center: new google. maps. Lat. Lng(39. 50, -98. 35), map. Type. Id: google. maps. Map. Type. Id. ROADMAP }; map = new google. maps. Map(document. get. Element. By. Id(‘map_canvas’), map. Options); // Create the map object. point. Array = new google. maps. MVCArray(geo. Data); heatmap = new google. maps. visualization. Heatmap. Layer({ data: point. Array }); // Create the heat map layer. heatmap. set. Map(map); Session ID 2521 13

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 14

The Advance web-form map container. Session ID 2521 15

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 varchar 2 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. ) Session ID 2521 16

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. Session ID 2521 17

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. Lat. Lng('|| 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 Session ID 2521 18

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 RC 1 for select rtrim(JSMTXT, ', ') "JSMTXT“ from dual; Session ID 2521 19

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: Session ID 2521 20

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: Session ID 2521 21

Javascript helper file, holds the code for the map definition. var map, pointarray, heatmap; function make. Heatmap(div. ID) { var map. Options = { zoom: 4, center: new google. maps. Lat. Lng(39. 50, -98. 35), map. Type. Id: google. maps. Map. Type. Id. ROADMAP }; map = new google. maps. Map(document. get. Element. By. Id(div. ID), map. Options); point. Array = new google. maps. MVCArray(taxi. Data); heatmap = new google. maps. visualization. Heatmap. Layer({ data: point. Array }); heatmap. set. Map(map); } Session ID 2521 22

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 clientside javascript environment for presentation. Session ID 2521 23

Method 2, refcursor output, database side. First create the stored procedure using a refcursor output parameter: open RC 1 for select '{location: new google. maps. Lat. Lng('|| 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; Session ID 2521 24

Method 2, create the custom VB. Net aspx program on web folder. Using the Advance. Web. Page. Information 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: Protected Function Get. Adv. PInfo() As Advance. Web. Page. Information Dim _Adv. PInfo = New Advance. Web. Page. Information With _Adv. PInfo. Init. Collections(Me. Controls , Me. Request. Form, Me. Request. Query. String, Me. Session, Me. Validators). User = Direct. Cast(Session("Adv. User "), IAdvance. User). Request = Me. Request. Response = Me. Response. Page. Type = Page. Get. Type(). Instance. Id = _instance. Id End With Return _Adv. PInfo End Function If not ( Is. DBNull(Me. Get. Adv. PInfo(). User. Database ) ) Then dbi 2 = New DBInterface( Me. Get. Adv. PInfo(). User. Database ) dbconn 2 = dbi 2. Get. Connection() End If Session ID 2521 25

Method 2, aspx file, execute the stored procedure, loop through refcursor. prop. CMD = New Oracle. Command(“ADV_SCHEMA. _geo_hm_hmap_js_wkeys_rc", dbconn 2) prop. CMD. Parameters. Add("in_session_id", Oracle. Data. Access. Client. Oracle. Db. Type. Varchar 2). Value = Session. ID … Response. Write( NL + " var geo. Data = [" ) Dim mr as string = "" Do While prop. Datarow. Index < prop. Ds. S. Tables(0). Rows. Count If not ( Is. DBNull(prop. Ds. S. Tables(0). Rows(prop. Datarow. Index)("M_DAT")) ) Then mr = prop. Ds. S. Tables(0). Rows(prop. Datarow. Index)("M_DAT") ‘Remove trailing ‘, ’ if prop. Datarow. Index = (prop. Ds. S. Tables(0). Rows. Count - 1) then mr = mr. substring( 0, mr. length-1) end if Response. Write( NL + mr ) End If prop. Datarow. Index += 1 Loop 'Do While prop. Datarow. Index < prop. Ds. S. Tables(0). Rows. Count) Response. Write( NL + "]; " ) Session ID 2521 26

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. Session ID 2521 27

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 28

Variations of a theme: the Circle-ratio map Session ID 2521 29

Circle-ratio map. • The Circle-ratio is another type of mapping visualization: Session ID 2521 30

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 Lat. Lng, population. var my. Lat. Lng = new google. maps. Lat. Lng( 42. 22, -71. 75 ); var my. Options = { zoom: 8, center: my. Lat. Lng, map. Type. Id: google. maps. Map. Type. Id. TERRAIN }; var cir_factor = 6; var citymap = {}; citymap['MA Pittsfield'] = { center: new google. maps. Lat. Lng(42. 4665, 73. 2893), population: 1470000 /cir_factor }; citymap['MA Amherst'] = { center: new google. maps. Lat. Lng(42. 3729, -72. 4509), population: 1000000 /cir_factor }; citymap['MA Westfield'] = { center: new google. maps. Lat. Lng(42. 1627, 72. 7713), population: 890000 /cir_factor }; Session ID 2521 31

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 population. Options = { stroke. Color: "#FF 0000", stroke. Opacity: 0. 7, stroke. Weight: 1, fill. Color: "#FF 0000", fill. Opacity: 0. 35, map: map, center: citymap[city]. center, radius: citymap[city]. population / 20 }; city. Circle = new google. maps. Circle(population. Options); } Session ID 2521 32

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 ) Session ID 2521 33

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. Session ID 2521 34

Questions? Answers? ? Session ID 2521 35