X

Best Practices from Oracle Development's A‑Team

Adding location-based search to URM (or UCM) using Google Maps

Mark Nelson
Architect

Introduction

Recently I was working with an organisation that manages a large collection of physical assets and wanted to add a location-based search capability to Oracle Universal Records Management. In order to carry out a ‘proof of concept’ we built some simple integration with Google Maps.

This same simple add-on could equally be done with Oracle Universal Content Management.  The only difference would be in the way the metadata are defined and accessed in the query.  If you are using UCM, not URM, the tables will be different.  If you don’t know how to work it out, add a question to the post.

Main Article

This post was built using Oracle Universal Records Management 11.1.1.2 running on Windows Server 2008 DataCenter Edition with Oracle Database 10g Express Edition.

The integration is built as a Java ServerPages (JSP) add-on.  URM has a built-in JSP server, but we have to enable it.

Connect to the URM web interface at http://yourserver:16300 and click on the Login link.

slide01

Log on as an administration, such as weblogic.

slide0273d9

Open the Administration menu and select the link for Admin Server.  Note that your menus may look different based on user preferences, e.g. they may be drop-down menus across the top of the screen, or a different colour scheme.

slide03

A new window will open for the Admin Server.  Select the General Configuration link as highlighted in the image below.

slide04

Tick the checkbox to Enable Java Server Page (JSP) support, as shown, and provide a name for the JSP Enabled Groups.  I called mine “JSP.”  Note that this needs to match the group that we will create a little later on, and also becomes part of the URL to access the JSP pages.

Save these changes, and then restart your URM server instance.

These changes tell the URM server that when we access a JSP file in the group called JSP, it should run that page on its internal JSP server and return us the output, rather than just sending us the text in the file itself.  This is a very quick and easy way to add some new functionality to URM (or UCM for that matter.)

slide05

Once the server is restarted, return to the web interface, and open the Admin Applets in the Administration menu.  Then open the User Admin applet from that page.

Note that the admin applets will require a Java enabled browser as they are Java applets that will load and run on your local machine.

slide0673d9

When the User Admin applet opens, select Permissions by Group… from the Security menu.

slide07

This will open a list of groups.  Click on the Add Group… button.  In the popup dialog box, enter JSP as the name for your new group.  Note that this MUST match the name you specified earlier in the Admin Server General Configuration page.  Then click on OK.

slide0873d9

Click on the Edit Permissions… button and set the permissions for admin to RWDA as shown below.

slide09

Close the User Admin applet, saving your changes.

This completes the configuration to allow us to run JSP pages.  Now we want to define some metadata to store our location information.  In this post, we will use two fields to hold the latitude and longitude where the physical item was found.

To create our metadata, we go to the Records menu and select Configure then Metadata then Metadata Sets as shown in the image below.

slide12

On the Auxiliary metadata set information page, we provide a name and other details and the add the fields we want to define.  Complete this page using the information below:

Name:          gaMetadata Display Name:  Location Metadata Table Name:    gametadata Column Prefix: ga

Add fields with the following names and types:

SurveyNumber                 Text SurveyName                   Text SourceLocationLatitude       Text SourceLocationLongitude      Text SampleType                   Text SampleOwner                  Text EmbargoDate                  Date CollectionDate               Date

We will use these fields later to construct the search and display information about the search results on the map.

slide1373d9

Now we are ready to add our actual JSP search page.  As this activity was conducted as a ‘proof of concept’ this JSP does not have all the error handling that you would want in a real production system, and it does not use connection pooling, etc.  So please be aware that you should improve the code quality if you plan to use this!

Here is the source code for the JSP.  You can put this into a file, I called mine mark.jsp, that we will upload shortly.  I wont give a line by line description of this code (unless somebody asks for it) but at a high level it is a simple HTML form that posts back to itself.  If the form has data in it, the page will use JDBC to connect to the database and run a query to find items within a specified distance (uncomment this if you want it) of a given location.  It then loops through the result set and creates markers on a Google Map to display the search results, along with some hover text and a popup box.  The marker also allows you to click on the search result to go to it’s details page in the web interface.  Note that you will need to set the database URL, userid, password, and your server address correctly.  These are highlighted in bold in the code below.

Note also that the code uses the SDO_GEOM package to do the spatial calculations – in this case the distance between two points in kilometres.  These are written to work on a standard projection with the normal everyday latitude and longitude coordinate system that most people are familiar with.  The Oracle database supports many other coordinate systems, measurement scales and spatial routines.

# Copyright 2012 Oracle Corporation. # All Rights Reserved. # # Provided on an 'as is' basis, without warranties or conditions of any kind, # either express or implied, including, without limitation, any warranties or # conditions of title, non-infringement, merchantability, or fitness for a # particular purpose. You are solely responsible for determining the # appropriateness of using and assume any risks. You may not redistribute. <%@page language="java" import="java.sql.*"%> <%   System.out.println("+++ Entering mark.jsp");   java.sql.Connection conn;   java.sql.Statement stmt;   java.sql.ResultSet rs;   if (request.getParameter("xLat") != null) {     try {       // query the database       Class.forName("oracle.jdbc.driver.OracleDriver");       conn = DriverManager.getConnection(         "jdbc:oracle:thin:@localhost:1521:ecm11g", "dev_urmserver", "welcome1");       String query = "select g.did, sdo_geom.sdo_distance( SDO_GEOMETRY( 2001, "         + "8307, SDO_POINT_TYPE(" + request.getParameter("xLong") + ", " + request.getParameter("xLat")         + ", NULL), NULL, NULL), SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(gacol_4, gacol_3, "         + "NULL), NULL, NULL), 0.001, 'UNIT=KM') distance_in_km, gacol_4, gacol_3, gacol_1, gacol_2, "         + "gacol_5, gacol_6, gacol_7, gacol_8, x.ddocname from gametadata g, extitems x ";         //+ "where g.did = x.did and distance_in_km < " + request.getParameter("xDist");       stmt = conn.createStatement();       rs = stmt.executeQuery(query); %> <head> <title>test</title> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/reset-fonts-grids/reset-fonts-grids.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/container/assets/container.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-core.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-skin.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/skins/sam/treeview-skin.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/menu/assets/menu-core.css" /> <link href="/urm/resources/layouts/Trays/Oracle/skin.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script> <script type="text/javascript"> function initialize() {   var latlng = new google.maps.LatLng(<%   if (request.getParameter("xLat") != null) { %><%= request.getParameter("xLat") %><%   } else { %>-35.282019<%   } %>,<%   if (request.getParameter("xLong") != null) { %><%= request.getParameter("xLong") %><%   } else { %>149.128748<%   } %>);   var myOptions = {     zoom: 12,     center: latlng,     mapTypeId: google.maps.MapTypeId.ROADMAP   };   var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions); <%     int i = 1;     while (rs.next()) { %>     var latlng<%= i %> = new google.maps.LatLng(<%= rs.getString(4) %>,<%= rs.getString(3) %>);     var marker<%= i %> = new google.maps.Marker({       position: latlng<%= i %>,       map: map,       title: "<%= rs.getString(11) %> from Survey <%= rs.getString(5) %>, <%= rs.getString(6) %>. A <%= rs.getString(7) %> submitted by <%= rs.getString(8) %>"     });     var contentString<%= i %> = '<div id="content">' +       '</div>' +       '<h1 id="firstHeading"><%= rs.getString(11) %></h1>' +       '<div id="bodyContent">' +       '<p><%= rs.getString(11) %> from Survey <%= rs.getString(5) %>, <%= rs.getString(6) %>.<br>A <%= rs.getString(7) %> submitted by <%= rs.getString(8) %>. </p>' +       '<p><a href="http://yourserver:16300/urm/idcplg?IdcService=INFO_EXTERNAL_ITEM_FORM&dSource=Physical&dID=<%= rs.getString(1) %>">View details</a></p></div></div>';     var infowindow<%= i %> = new google.maps.InfoWindow({       content: contentString<%= i %>     });     google.maps.event.addListener(marker<%= i %>, 'click', function() {       infowindow<%= i %>.open(map,marker<%= i %>);     }); <%     i++;   } %> } </script> </head> <body onload="initialize()" onunload="GUnload()"> <div > <h1>Search by Location</h1> <form method="POST" action="#"> Latitude: <input type="text" name="xLat" value="<% if (request.getParameter("xLat") != null) { %><%= request.getParameter("xLat") %><% } else { %>-35.282019<% } %>"><br/> Longitude: <input type="text" name="xLong" value="<% if (request.getParameter("xLong") != null) { %><%= request.getParameter("xLong") %><% } else { %>149.128748<% } %>"<br/> Distance: <input type="text" name="xDist" value="<% if (request.getParameter("xDist") != null) { %><%= request.getParameter("xDist") %><% } %>"<br/> <input type="submit" value="Search"> </form> <p/> </div> <div id="map_canvas" style="width: 800px; height: 450px"></div> </body> <%       rs.close();       stmt.close();       conn.close();     } catch (Exception e) { %><%= e.toString() %><%     }   } else { %> <head> <title>test</title> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/reset-fonts-grids/reset-fonts-grids.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/container/assets/container.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-core.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/treeview-skin.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/treeview/assets/skins/sam/treeview-skin.css" /> <link rel="stylesheet" type="text/css" href="/urm/resources/yui/menu/assets/menu-core.css" /> <link href="/urm/resources/layouts/Trays/Oracle/skin.css" rel="stylesheet" type="text/css" /> <body onload="initialize()" onunload="GUnload()"> <div > <h1>Search by Location</h1> <form method="POST" action="#"> Latitude: <input type="text" name="xLat" value="-35.282019"><br/> Longitude: <input type="text" name="xLong" value="149.128748"<br/> Distance: <input type="text" name="xDist" value="50"><br/> <input type="submit" value="Search"> </form> <p/> </div> <div id="map_canvas" style="width: 800px; height: 450px"></div> </body> <% } %>

Once you have the code saved in a file, you are ready to check that file into the URM server.

From the New Check-In menu, select your URM instance and then Standard Check In.  A form similar to the one below will open.  Set the fields as shown below:

Type:            Document - Any generic document Title:           mark Author:          weblogic Security Group:  JSP Primary File:    (click on Choose File and point to your mark.jsp) Content ID:      MARK Revision:        1

When you have completed the fields, click on the Check In button.  Note that the Security Group MUST match the one we defined earlier, which in turn matched the one we set in the Admin Server General Configuration page.  You can use a different title or content ID if you wish, however you will need to adjust your URL (coming soon) to match the values you choose.

slide10

Now we will set up a quick link to our page so it is easy to access it from the URM web interface.  In the My Content Server menu, click on the My URLs link.  This will open a page where you can define links, as shown below.  Add a new link.  I gave mine the title Search by Location and the URL shown below.  Note that you will need to change the URL (where highlighted in bold) if you have used a different filename or security group.  You will also need to put your server name into the URL.

http://yourserver:16300/urm/groups/jsp/documents/document/mark.jsp

slide11c87a

Press Update once you are done.  You will notice that a Search by Location link appears under the My URLs folder in your menu now.

Finally, we will need some actual physical assets in the system so that we have some search results to look at!  The image below shows two sample records I used.

slide1473d9

Check some physical assets into your system, if you have not already.  Make sure you add the Location Metadata Auxiliary Metadata Set to your new record using the drop down box at the top right.  You might want to do this before you start filling out the other fields…

slide1573d9

Provide some values as shown above.  Note that this item has its SurveyName, SourceLocationLatitude, SourceLocationLongitude and SampleType set.  Note the values, you will see them later in the search results.

Now we are all set to run our add-in.  Click on the Search by Location link under My URLs in the My Content Server menu.  You should see your JSP page appear in the main content pane on the right, as shown below.  Enter some search terms and press the Search button.  Here I searched for -33.8122, 151.1755, the location of a large tunnel near Oracle’s office in Sydney, Australia.

slide1673d9

The search results will be displayed on the map, as shown in the digram below.  Note that you can hover over the markers to see details.  You can also click on the marker to make the balloon appear (as has been done below).  The View details link in the balloon will take you to the subject item’s page in the URM web interface.  Note that you can also pan and zoon the map, and select the mode (Map, Satellite, Hybrid).

slide1773d9

That concludes the example!  Happy searching by location.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha