Wednesday, September 21, 2011

Finding sales offices within a certain distance of a customer

In this post lets do a simple demo that shows all the customers (from the MVDEMO's customers table) on the map. Now, when you click on a customer marker, I want to display all the cities that are within 50 miles radius of the customer. I mentioned sales office in the title to make it sound more interesting, but our small MVDEMO sample data set does not have such a table, so we will use the readily available cities table instead.

Basically, we want to run a query like following when you click on any given customer marker:

select city, state_abrv, location from cities
where sdo_within_distance(location,
sdo_geometry(2001, 8307, sdo_point_type(:1, :2, null), null, null),
:3)='TRUE';



And display the query result as a new theme on the map. Note the three binding parameters in the above query. The first two should be the longitude and latitude of the customer location. The 3rd parameter is a Oracle Spatial WITHIN_DISTANCE operator parameter, typically in the form of 'distance=50 unit=mile'. So a properly bound query looks like this (you can execute it in the mvdemo schema).

select city, state_abrv, location from cities
where sdo_within_distance(location,
sdo_geometry(2001, 8307, sdo_point_type(-122, 37.5, null), null, null),
'distance=50 unit=mile')='TRUE';

We could use a dynamic-query based theme to display the above query result. But, ever mindful of safety and efficiency, we will instead create a pre-defined theme in the mvdemo schema. The trick is to add a query condition that contains these binding variables and disable the default (window-based) spatial filter for the new theme. Below is a screenshot of such a theme (cities_near_customer) and its query condition:



Now that we have the theme defined, we can work on the actual demo itself. The demo page source is found below. Just copy and paste it into an html file, save it in your web server where MapViewer is deployed, and you are all set (assuming the usual mvdemo datasource exists in the MapViewer instance).



<html>
<head>
<META http-equiv="Content-Type" content="text/html" charset=UTF-8">
<TITLE>Map Cache Server/Map Client</TITLE>
<link rel="stylesheet" type="text/css" href="../t.css" />
<script language="Javascript" src="/mapviewer/fsmc/jslib/oraclemaps.js"></script>

<script language=javascript>
var mapview;
var nearestCitiesTheme;

function showMap()
{
var baseURL = "http://"+document.location.host+"/mapviewer";
var mapCenterLon = -122.45;
var mapCenterLat = 37.7706;
var mapZoom = 3;
var mpoint = MVSdoGeometry.createPoint(mapCenterLon,mapCenterLat,8307);
mapview = new MVMapView(document.getElementById("map"), baseURL);
mapview.addMapTileLayer(new MVMapTileLayer("mvdemo.demo_map"));
mapview.setCenter(mpoint);
mapview.setZoomLevel(mapZoom);

var customersTheme = new MVThemeBasedFOI('themebasedfoi1','mvdemo.customers') ;

customersTheme.setBringToTopOnMouseOver(true);

customersTheme.attachEventListener(MVEvent.MOUSE_CLICK, foiClick);
mapview.addThemeBasedFOI(customersTheme);

mapview.display();
}

function foiClick(pointOfClick,foi)
{
alert("Locating all cities within 50 mile radius.");

if(nearestCitiesTheme)
{
mapview.removeThemeBasedFOI(nearestCitiesTheme);
}

nearestCitiesTheme = new MVThemeBasedFOI('cities-theme', 'mvdemo.cities_near_customer');

//set the binding variable for this theme.
var x = foi.x; // :1
var y = foi.y; // :2
var distanceParam = "distance=50 unit=mile"; // :3

nearestCitiesTheme.setQueryParameters(x,y, distanceParam);

nearestCitiesTheme.setBringToTopOnMouseOver(true);
mapview.addThemeBasedFOI(nearestCitiesTheme);
}
</script>
</head>

<body onload="javascript:showMap()">
<h3>Oracle Maps example - finding cities closest to a customer </h3>
<div id="map" style="left:0px; top:10px;width:100%; height:60%"></div>

</body>

</html>





The main things I want to point out here are:

1. It uses a mouse-click listener on the customers theme, so that we can do the magic when user clicks on a customer marker.

2. in the mouse click function (foiClick), we create a new theme-based FOI layer for the cities_near_customers theme. Then we set the 3 binding variables this theme's query condition is expecting, using setQueryParameters().

3. In the demo I want to find out all the cities that are within 50 mile distance of a customer; you can easily use a different distance by changing the value of the distanceParam variable above.

That's all for this post.

-LJ