March 2008 Oracle Spatial User Conference
Oracle Spatial User Conference
March 13, 2008 Sheraton Seattle Hotel Seattle, Washington USA
March 2008 Oracle Spatial User Conference
Trevor Quinn
Principal Developer Systalex Corporation
March 2008 Oracle Spatial User Conference
Web Mapping with Virtual Earth and Oracle in EPA's Grant Tracking Systems
March 2008 Oracle Spatial User Conference
Project Background
• GRTS: EPA's Grant Reporting and Tracking System for EPA's Non-Point Source Reduction Grants
• Enterprise web application • Tracks grant activities, budgeting, performance, and results. Not a GIS application per se. • Mapping streamlines user interface, allows users to more easily find grants, summarize program data, and enter locational information about grants
• Microsoft Virtual Earth is now an agency approved geospatial technology
March 2008 Oracle Spatial User Conference
Technologies Used
• Microsoft Virtual Earth SDK 6 • JavaScript/AJAX: Prototype 1.6 and Scriptaculous • Oracle 10g Locator • Oracle Application Express 3.0 • Oracle MapViewer (WMS) • PL/SQL (feature processing) • Java 1.4 (WMS tile server)
March 2008 Oracle Spatial User Conference
Overview
• Concepts and Implementation
• Tiles: Deploying a custom tile server for Virtual Earth (or Google Maps)
• Reflecting WMS as Virtual Earth tiles • Caching tiles
• Features: Passing data between Virtual Earth and Oracle 10g
• Enabling AJAX calls in Oracle Application Express • Generating and parsing GeoJSON for Virtual Earth • Submitting features to the database as WKT
• Demonstration
March 2008 Oracle Spatial User Conference
Data Flow Overview
Image files WMS Virtual Earth Tile Cache Image files
JDBC
Oracle 10g APEX 3.0 Features (GeoJSON and WKT) Web browser
March 2008 Oracle Spatial User Conference
Tiles
Deploying a custom tile server
March 2008 Oracle Spatial User Conference
Tiles
• VE tiles are 256px-by-256px quadtree-indexed image files that together form a grid of a map in Google's Mercator-projected geographic coordinate system • Any map that matches or approximates this projection can be broken up into usable tiles (including WMS)
March 2008 Oracle Spatial User Conference
Tiles
Image reproduced from Microsoft MSDN website
March 2008 Oracle Spatial User Conference
Sample VE Tiles and Their Quad Codes
032 0
02123 00
02310103012 123
March 2008 Oracle Spatial User Conference
Reflecting WMS as Tile Images
• We need a process for converting a VE tile request into a WMS request and returning the result • This process must be callable through a URL, passed to Virtual Earth's JavaScript API • Once provided the URL, the VE API will overlay WMS tiles on the VE background at a matching scale (with user-specified transparency)
March 2008 Oracle Spatial User Conference
Overlaying WMS Tiles on Virtual Earth
Virtual Earth WMS Combined
March 2008 Oracle Spatial User Conference
Tile Serving Process (without Caching)
WMS request (HTTP Get) Tile request (HTTP Get)
JDBC queries
Oracle 10g
JDBC result sets
WMS
VE Tile Cache
Browser
PNG file response (HTTP)
Forwarded PNG file response (HTTP)
March 2008 Oracle Spatial User Conference
Reflecting WMS as Tile Images
AddTileLayer: function(mapName, opacity) { var tileSourceSpec = new VETileSourceSpecification (mapName, "http://aruba.systalex.com/WMSTileServer/? MAP_NAME=" + mapName + "&VE_QUAD_CODE=%4"); tileSourceSpec.NumServers = 1; tileSourceSpec.Opacity= opacity; this.map.AddTileLayer(tileSourceSpec, false); },
VE replaces with a quadtree tile code based on scale and extent of current map view (023, 121122, 13, etc.)
March 2008 Oracle Spatial User Conference
VE Tile Cache Servlet
VE Tile Cache Java servlet (adapted from Active Web Solutions C# source)
public Box QuadKeyToBox(String quadString, int x, int y, int zoomLevel) { char c = quadString.charAt(0); int tileSize = 2 << (18 - zoomLevel - 1); if (c == '0') { y = y - tileSize; } else if (c == '1') { y = y - tileSize; x = x + tileSize; } else if (c == '3') { x = x + tileSize; } if (quadString.length() > 1) { return QuadKeyToBox(quadString.substring(1), x, y, zoomLevel + 1); } else{ return new Box(x, y, tileSize, tileSize); } } // Then convert Box x and y values to WGS84 latitude and longitude
March 2008 Oracle Spatial User Conference
Tile Serving Process (with Caching)
(Tile in local cache) Tile request (HTTP Get)
Oracle 10g
WMS
VE Tile Cache
Browser
Forwarded PNG file response (HTTP)
March 2008 Oracle Spatial User Conference
Caching Tiles in the VE Tile Cache Servlet
Servlet HTTP response source code fragment:
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ... File f = new File(dir, quadKey + ".png"); if (!f.exists()) { // If the image is not already cached, make call to WMS BufferedImage bi = ImageIO.read(buildURL(ml, quadKey)); ImageIO.write(bi, "png", response.getOutputStream()); f.createNewFile(); // Write the image to the designated tile cache folder on the server ImageIO.write(bi, "png", f); } else { // Otherwise, just display the image from cache ImageIO.write(ImageIO.read(f), "png", response.getOutputStream()); } }
March 2008 Oracle Spatial User Conference
Features
Passing data between Virtual Earth and Oracle 10g
March 2008 Oracle Spatial User Conference
Adding Features to the WMS Overlay in Virtual Earth
March 2008 Oracle Spatial User Conference
Feature Serving Process
AJAX feature request JSON response (HTTP)
AJAX feature request (HTTP Get)
PL/SQL Process in Oracle 10g
HTTP Server
JSON response (HTTP)
Browser
March 2008 Oracle Spatial User Conference
Serving Features to Virtual Earth
An AJAX request to run an APEX PL/SQL process:
/** * Makes an AJAX request to the database for a feature corresponding to a * drainage area ID. * * @param {Object} drainageAreaId * @return {Object} drainageArea */ function LoadDrainageArea(drainageAreaId) { var ajaxRequest = new htmldb_Get(null, $('pFlowId').value,'APPLICATION_PROCESS=LoadDrainageArea',0); ajaxRequest.add('P1131_PRJDRAR_SEQ',drainageAreaId); drainageArea = ajaxRequest.get(); ajaxRequest = null; return drainageArea; }
March 2008 Oracle Spatial User Conference
Serving Features to Virtual Earth
The APEX PL/SQL process that queries for and returns features:
DECLARE CURSOR map_cur IS SELECT * FROM drain_area_map m WHERE m.id = :P1131_ID; htp.prn('{'); htp.prn(' "type": "FeatureCollection",'); htp.prn(' "features": ['); BEGIN FOR v_map IN map_cur LOOP htp.prn(' {'); htp.prn(' "type": "Feature",'); htp.prn(' "id": "' || v_map.prjdrarmap_seq || '",'); htp.prn(' "geometry": {'); htp.prn(' "type": "MultiPoint",'); htp.prn(' "coordinates": [ [' || v_map.geo_start.sdo_point.x || ', ' || v_map.geo_start.sdo_point.y || '],' || ' [' || v_map.geo_end.sdo_point.x || ', ' || v_map.geo_end.sdo_point.y || '] ]'); htp.prn(' "properties": {'); ... htp.prn(' }'); htp.prn(' }'); END LOOP; END;
March 2008 Oracle Spatial User Conference
Serving Features to Virtual Earth
The JSON representation of features:
{ "type": "FeatureCollection", "features": [ { "type": "Feature", "id": "112322", "geometry": { "type": "MultiPoint", "coordinates": [ [102.0, 0.0], [103.0, 1.0] ] }, "properties": { "Stream name": "Miller Creek", "Inserted by": "John Wilson" "Inserted date": "03-MAR-2007" } }, ... ]
}
March 2008 Oracle Spatial User Conference
Serving Features to Virtual Earth
Parsing JSON and displaying in Virtual Earth
function GeoJSONCollection2VEShape(geoJSONCollection) { for (var i = 0; i < geoJSONCollection.features.length; i++) { feature = geoJSONCollection.features[i]; switch (feature.geometry.type) { case "MultiPoint": for (var j = 0; j < feature.geometry.coordinates.length; j++) { coordinate = feature.geometry.coordinates[j]; latLong = new VELatLong(coordinate[1], coordinate[0]);
var shape = new VEShape(VEShapeType.Pushpin, latLong); shape.SetDescription(Object.toJSON(feature.properties));
veShapeArray.push(shape);
} break;
this.GetVEShapeArray = function() { return veShapeArray;
} } ... var converter = new GeoJSONCollection2VEShape(geoJSONFeatureCollection); var veShapeArray = converter.GetVEShapeArray(); VEShapeLayer.addShape(veShapeArray[0]);
March 2008 Oracle Spatial User Conference
Submitting Features to the Database from Virtual Earth
• A similar process is used for submitting features to the database from Virtual Earth (create AJAX call on client, create PL/SQL process in database) • When creating features for database submission, forming WKT is easier than JSON, because Oracle Spatial has:
SDO_UTIL.FROM_WKTGEOMETRY(
eometry IN CLOB g ) RETURN SDO_GEOMETRY;
D E M O N S T R A T I O N
Web Mapping with Virtual Earth and Oracle 10g in EPA's Grant Tracking Systems
March 2008 Oracle Spatial User Conference
Resources
• MSDN Building Your Own Tile Server: http:// msdn2.microsoft.com/en-us/library/ bb545006.aspx • Accessing WMS from Virtual Earth by Rob Blackwell of Active Web Solutions Ltd. http:// viavirtualearth.com/vve/Articles/ WMS.ashx • Video demonstration of the Virtual Earth in the EPA grant tracking application: http://www.systalex.com/capabilities/ web_dev.html