none
Office 365 public website

    Question

  • Office 365 public website requires a visitor initiated searchable embedded map.

    REQUIREMENTS

    Site visitor search entry options Town/City, County, Post Code, Select distance from 5KM to 100KM (drop down box). Two buttons, Search & Clear search form. The map links to an excel.csv file containing 5170 addresses to display searched label point locations on the map.   

    Is this possible using Bing maps? If so, how can I do this?

    Thank you.


    Christopher Bird


    • Edited by CABird Thursday, March 27, 2014 1:39 PM
    Thursday, March 27, 2014 1:00 PM

Answers

  • Yes. This can be done using Bing Maps. The first task is to geocode all your data that is in the CSV file if there isn't already latitude and longitude values in it. You might want to turn the file into a Bing Spatial Data Source and batch geocode it using the Bing Maps portal: http://msdn.microsoft.com/en-us/library/hh698204.aspx

    If the data doesn't contain any sensitive data then you could also host it in the Bing Spatial Data Services and have it exposed as a spatial REST service.

    Linking a flat file to a web page isn't a great idea as that means the user will have to download all the data when they may only need a few rows. Hosting this data in a database or as a list in Office 365 might make more sense. If you want to use a flat file you will then need to parse it into JSON. You might find it makes more sense to convert the CSV to JSON ahead of time to make for faster parsing. Take a look at this simple tool: http://csvtojson.com/

    Once you have each record in JSON it is just a matter of looping through each one and running your search logic against it. The take the results and create a pushpin based on the latitude and longitude values and then add it to the map. You can all this in a simple web page. You can then add the web page in an iframe in Office 365. Here is an article on how to do this with SharePoint 2013, the steps are very similar. http://rbrundritt.wordpress.com/2013/03/26/connecting-a-sharepoint-list-to-bing-maps/


    http://rbrundritt.wordpress.com

    Thursday, March 27, 2014 4:59 PM
  • Hi Christopher,

    There isn't any blog posts that are 100% in line with what your asking for, mainly because most people who want good performance when using thousands of locations usually don't use flat files. Also it's worth noting that the fact your are using Office 365 really makes no difference. At the end of the day you can create all of the functionality as a standalone webpage and then add it to your Office 365 site. Here is a good example of how that can be done: http://community.office365.com/en-us/forums/148/t/161594.aspx

    I strongly recommend using the Bing Spatial Data Services as it will provide you with the most flexibility and best performance. However, if you want to stick with a flat file here is how you can go about it. Note that this is going to require a lot more code than the if you used the Bing Spatial Data Services.

    1. Convert your CSV file into a JSON file. You can then load this in as a JavaScript file. You will need to store the location data as a variable so add something like the following inside the JSON file: var data = [YOUR_JSON_DATA];
    2. Once this is done create a simple web page and load in the JavaScript file and the Bing Maps control. I recommend starting off with the basic example here: http://msdn.microsoft.com/en-us/library/gg427624.aspx
    3. Next create a function that searches through your data object. This search algorithm will need to first geocode the users query and then take those coordinates and calculate the distance to each data point in your data set using the Haversine formula and storing those that are within your specified radius. You can use the Search module do the geocoding: http://msdn.microsoft.com/en-us/library/hh868060.aspx
    4. If you want to also display an infobox I recommend using this approach: http://rbrundritt.wordpress.com/2011/10/13/multiple-pushpins-and-infoboxes-in-bing-maps-v7/

    I've put together a simplified code sample below to get you started.

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>
       <head>
          <title></title>
          <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    
          <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0&mkt=en-GB"></script>
    	  <script type="text/javascript">
    	  //Hard coding data for sample
    	  var data = [
    		{
    			name: 'Point 1',
    			latitude : 51.5, 
    			longitude : 0
    		},{
    			name: 'Point 2',
    			latitude : 54,
    			longitude : -1
    		},{
    			name: 'Point 2',
    			latitude : 51.6,
    			longitude : 0.1
    		}
    	  ];
    
    	  var map, infobox, dataLayer, searchManager;
    	  
          function GetMap()
          {   
            map = new Microsoft.Maps.Map(document.getElementById("mapDiv"), 
    		{
    			credentials: "YOUR_BING_MAPS_KEY"
    		});
    				
    		dataLayer = new Microsoft.Maps.EntityCollection();
    		map.entities.push(dataLayer);
    
    		var infoboxLayer = new Microsoft.Maps.EntityCollection();
    		map.entities.push(infoboxLayer);
    
    		infobox = new Microsoft.Maps.Infobox(new Microsoft.Maps.Location(0, 0), { visible: false, offset: new Microsoft.Maps.Point(0, 20) });
    		infoboxLayer.push(infobox);
    				
    		Microsoft.Maps.loadModule('Microsoft.Maps.Search');
          }
    	  
    	  function Search(query, radius){		
    		dataLayer.clear();
    		infobox.setOptions({ visible: false });
    		
    		if(!searchManager){
    			searchManager = new Microsoft.Maps.Search.SearchManager(map);
    		}
    		
            searchManager.geocode({where:query, count:10, callback: function(r){
    			if(r && r.results && r.results.length > 0){
    				var center = r.results[0].location;
    			    var locs = []; //An array to store the locations of all results in view. Used to position map later.
    				
    				//Loop through data and find locations that are within specified distance
    				for(var i=0;i<data.length;i++){
    					var d = HaversineDistance(center.latitude, center.longitude, data[i].latitude, data[i].longitude);
    					
    					if(d <= radius){
    						//add location to map if it is within the specified radius
    						var loc = new Microsoft.Maps.Location(data[i].latitude, data[i].longitude);
    						locs.push(loc);
    						
    						var pin = new Microsoft.Maps.Pushpin(loc);
    						pin.Metadata = data[i];
    						Microsoft.Maps.Events.addHandler(pin, 'click', displayInfobox);
    						dataLayer.push(pin);
    					}
    				}
    				
    				//Zoom into the results
    				if(locs.length > 0){
    					var bounds = Microsoft.Maps.LocationRect.fromLocations(locs);
    					map.setView({
    						bounds: bounds,
    						padding : 100
    					});
    				}
    			}
    		}});
          }
    
    	  function HaversineDistance(lat1,lon1,lat2,lon2){
    			var R = 6371; // km 
    			var x1 = lat2-lat1;
    			var dLat = x1 * Math.PI / 180;  
    			var x2 = lon2-lon1;
    			var dLon = x2 * Math.PI / 180;  
    			var a = Math.sin(dLat/2) * Math.sin(dLat/2) + 
    							Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) * 
    							Math.sin(dLon/2) * Math.sin(dLon/2);  
    			var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
    			return R * c; 
    	  }
    	  
    	  function displayInfobox(e) {
    		  if (e.targetType == 'pushpin') {
    			  infobox.setLocation(e.target.getLocation());
    			  infobox.setOptions({ visible: true, title: e.target.Metadata.name});
    		  }
    	  } 
          </script>
       </head>
       <body onload="GetMap();">
          <div id='mapDiv' style="; width:800px; height:600px;"></div>
    	  <br/>
    	  <input type='button' value='Search' onclick='Search("London, UK", 20)'/>
       </body>
    </html>

    http://rbrundritt.wordpress.com

    Friday, March 28, 2014 3:27 PM
  • I do have a book on Bing Maps that I recently released as a free eBook. Chapter 6 talks about the Bing Spatial Data Services but doesn't go into too much detail on creating data sources but has lots of information on how to use them. You can download a copy of the book here: http://rbrundritt.wordpress.com/my-book/

    As for the uploading issue I recommend taking a look at this document: http://msdn.microsoft.com/en-us/library/hh463582.aspx


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 10:05 AM
  • Also looking at you CSV file screenshot it is not inline with the schema. It should look something like this:

    Bing Spatial Data Services, 1.0, Location
    EntityID(Edm.String,primaryKey),Name(Edm.String),AddressLine(Edm.String),Locality(Edm.String),PostalCode(Edm.String),AdminDistrict(Edm.String),CountryRegion(Edm.String),Latitude(Edm.Double),Longitude(Edm.Double)
    1,Microsoft,1 Microsoft Way,Redmond,8600,WA,United States,47.6405683,-122.129373

    Note that the entity type is specified in brackets to the right of the column name. Also notice the first line specifies the data source schema version (always 1.0 for now). Also notice how the first line has no trailing commas.


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 10:09 AM
  • Hi Christopher,

    350 is the number of columns you can have in a data source. You can have 200,000 rows/entites in a single data source in an Enterprise account. You can also o two incremental uploads of 200,000 to make your data source contain 600,000 locations.

    That said basic/trial accounts are limited to 50 rows/entities.

    If you want to send me your data source I can check the formatting for you. Looking at your screen shot you are missing the first line which defines the schema version. Also, Excel has a happen of adding quotes on the column names and trailing commas on the schema line, neither of these should be there. You can email me at ricky_brundritt at Hotmail dot com.


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 12:44 PM

All replies

  • Yes. This can be done using Bing Maps. The first task is to geocode all your data that is in the CSV file if there isn't already latitude and longitude values in it. You might want to turn the file into a Bing Spatial Data Source and batch geocode it using the Bing Maps portal: http://msdn.microsoft.com/en-us/library/hh698204.aspx

    If the data doesn't contain any sensitive data then you could also host it in the Bing Spatial Data Services and have it exposed as a spatial REST service.

    Linking a flat file to a web page isn't a great idea as that means the user will have to download all the data when they may only need a few rows. Hosting this data in a database or as a list in Office 365 might make more sense. If you want to use a flat file you will then need to parse it into JSON. You might find it makes more sense to convert the CSV to JSON ahead of time to make for faster parsing. Take a look at this simple tool: http://csvtojson.com/

    Once you have each record in JSON it is just a matter of looping through each one and running your search logic against it. The take the results and create a pushpin based on the latitude and longitude values and then add it to the map. You can all this in a simple web page. You can then add the web page in an iframe in Office 365. Here is an article on how to do this with SharePoint 2013, the steps are very similar. http://rbrundritt.wordpress.com/2013/03/26/connecting-a-sharepoint-list-to-bing-maps/


    http://rbrundritt.wordpress.com

    Thursday, March 27, 2014 4:59 PM
  • Many thanks for replying. Impressive resource however I'm not sure how I can apply webcast or WordPress articles to Office 365 public site requirement.

    CSV file is geocode see below.

    In the example below ignore the current map this is an example only and functionality doesn't work http://www.arcpartners.co.uk/offered-sites.

    Essentially a website visitor enters required data and clicks search to view results. Nothing fancy just marks on a map to show location. 

    Is there an app to enable this and or tutorial (online training) on how to achieve the below webpage example.


    Christopher Bird

    Friday, March 28, 2014 11:46 AM
  • Hi Christopher,

    There isn't any blog posts that are 100% in line with what your asking for, mainly because most people who want good performance when using thousands of locations usually don't use flat files. Also it's worth noting that the fact your are using Office 365 really makes no difference. At the end of the day you can create all of the functionality as a standalone webpage and then add it to your Office 365 site. Here is a good example of how that can be done: http://community.office365.com/en-us/forums/148/t/161594.aspx

    I strongly recommend using the Bing Spatial Data Services as it will provide you with the most flexibility and best performance. However, if you want to stick with a flat file here is how you can go about it. Note that this is going to require a lot more code than the if you used the Bing Spatial Data Services.

    1. Convert your CSV file into a JSON file. You can then load this in as a JavaScript file. You will need to store the location data as a variable so add something like the following inside the JSON file: var data = [YOUR_JSON_DATA];
    2. Once this is done create a simple web page and load in the JavaScript file and the Bing Maps control. I recommend starting off with the basic example here: http://msdn.microsoft.com/en-us/library/gg427624.aspx
    3. Next create a function that searches through your data object. This search algorithm will need to first geocode the users query and then take those coordinates and calculate the distance to each data point in your data set using the Haversine formula and storing those that are within your specified radius. You can use the Search module do the geocoding: http://msdn.microsoft.com/en-us/library/hh868060.aspx
    4. If you want to also display an infobox I recommend using this approach: http://rbrundritt.wordpress.com/2011/10/13/multiple-pushpins-and-infoboxes-in-bing-maps-v7/

    I've put together a simplified code sample below to get you started.

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>
       <head>
          <title></title>
          <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    
          <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0&mkt=en-GB"></script>
    	  <script type="text/javascript">
    	  //Hard coding data for sample
    	  var data = [
    		{
    			name: 'Point 1',
    			latitude : 51.5, 
    			longitude : 0
    		},{
    			name: 'Point 2',
    			latitude : 54,
    			longitude : -1
    		},{
    			name: 'Point 2',
    			latitude : 51.6,
    			longitude : 0.1
    		}
    	  ];
    
    	  var map, infobox, dataLayer, searchManager;
    	  
          function GetMap()
          {   
            map = new Microsoft.Maps.Map(document.getElementById("mapDiv"), 
    		{
    			credentials: "YOUR_BING_MAPS_KEY"
    		});
    				
    		dataLayer = new Microsoft.Maps.EntityCollection();
    		map.entities.push(dataLayer);
    
    		var infoboxLayer = new Microsoft.Maps.EntityCollection();
    		map.entities.push(infoboxLayer);
    
    		infobox = new Microsoft.Maps.Infobox(new Microsoft.Maps.Location(0, 0), { visible: false, offset: new Microsoft.Maps.Point(0, 20) });
    		infoboxLayer.push(infobox);
    				
    		Microsoft.Maps.loadModule('Microsoft.Maps.Search');
          }
    	  
    	  function Search(query, radius){		
    		dataLayer.clear();
    		infobox.setOptions({ visible: false });
    		
    		if(!searchManager){
    			searchManager = new Microsoft.Maps.Search.SearchManager(map);
    		}
    		
            searchManager.geocode({where:query, count:10, callback: function(r){
    			if(r && r.results && r.results.length > 0){
    				var center = r.results[0].location;
    			    var locs = []; //An array to store the locations of all results in view. Used to position map later.
    				
    				//Loop through data and find locations that are within specified distance
    				for(var i=0;i<data.length;i++){
    					var d = HaversineDistance(center.latitude, center.longitude, data[i].latitude, data[i].longitude);
    					
    					if(d <= radius){
    						//add location to map if it is within the specified radius
    						var loc = new Microsoft.Maps.Location(data[i].latitude, data[i].longitude);
    						locs.push(loc);
    						
    						var pin = new Microsoft.Maps.Pushpin(loc);
    						pin.Metadata = data[i];
    						Microsoft.Maps.Events.addHandler(pin, 'click', displayInfobox);
    						dataLayer.push(pin);
    					}
    				}
    				
    				//Zoom into the results
    				if(locs.length > 0){
    					var bounds = Microsoft.Maps.LocationRect.fromLocations(locs);
    					map.setView({
    						bounds: bounds,
    						padding : 100
    					});
    				}
    			}
    		}});
          }
    
    	  function HaversineDistance(lat1,lon1,lat2,lon2){
    			var R = 6371; // km 
    			var x1 = lat2-lat1;
    			var dLat = x1 * Math.PI / 180;  
    			var x2 = lon2-lon1;
    			var dLon = x2 * Math.PI / 180;  
    			var a = Math.sin(dLat/2) * Math.sin(dLat/2) + 
    							Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) * 
    							Math.sin(dLon/2) * Math.sin(dLon/2);  
    			var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
    			return R * c; 
    	  }
    	  
    	  function displayInfobox(e) {
    		  if (e.targetType == 'pushpin') {
    			  infobox.setLocation(e.target.getLocation());
    			  infobox.setOptions({ visible: true, title: e.target.Metadata.name});
    		  }
    	  } 
          </script>
       </head>
       <body onload="GetMap();">
          <div id='mapDiv' style="; width:800px; height:600px;"></div>
    	  <br/>
    	  <input type='button' value='Search' onclick='Search("London, UK", 20)'/>
       </body>
    </html>

    http://rbrundritt.wordpress.com

    Friday, March 28, 2014 3:27 PM
  • Many thanks for reply.

    I'm really struggling with this. Following your recommendation I've setup a Bing Spatial Data Services account. Created two keys and attempted numerous times to upload excel csv file and failed see illustration below. Clearly I don't understand. Step by step instructions would be great.

    Assuming I manage to upload the file what do I do next. I'm assuming this http://msdn.microsoft.com/en-us/library/hh305205.aspx

    Reading available resources points to app solutions rather than a web site page coding. Is there a book I can buy that would help. Ideally I would like to master this and clearly I need to begin with elementary functionality. I try the Bing Maps for Windows Store Apps Training Kit to see if this will help me. Thank you.


    Example csv data upload to https://www.bingmapsportal.com/UploadDataSet/CreateUploadView

    What would be a good idea is to provide excel template for example MS Dynamics CRM provide Contact & Account templates. Download fill in and upload more often than not this works. Clearly I'm missing something.

    Christopher Bird





    • Edited by CABird142 Monday, March 31, 2014 10:23 AM
    Sunday, March 30, 2014 12:29 PM
  • I do have a book on Bing Maps that I recently released as a free eBook. Chapter 6 talks about the Bing Spatial Data Services but doesn't go into too much detail on creating data sources but has lots of information on how to use them. You can download a copy of the book here: http://rbrundritt.wordpress.com/my-book/

    As for the uploading issue I recommend taking a look at this document: http://msdn.microsoft.com/en-us/library/hh463582.aspx


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 10:05 AM
  • Also looking at you CSV file screenshot it is not inline with the schema. It should look something like this:

    Bing Spatial Data Services, 1.0, Location
    EntityID(Edm.String,primaryKey),Name(Edm.String),AddressLine(Edm.String),Locality(Edm.String),PostalCode(Edm.String),AdminDistrict(Edm.String),CountryRegion(Edm.String),Latitude(Edm.Double),Longitude(Edm.Double)
    1,Microsoft,1 Microsoft Way,Redmond,8600,WA,United States,47.6405683,-122.129373

    Note that the entity type is specified in brackets to the right of the column name. Also notice the first line specifies the data source schema version (always 1.0 for now). Also notice how the first line has no trailing commas.


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 10:09 AM
  • Thanks for reply much appreciated. 

    If I understand correctly Bing Spatial Data Services requirements (see below) upload fails.  

    Reading http://msdn.microsoft.com/en-us/library/hh463582.aspx 350 entries is the limit given there are 5170 entries this explains upload failure.

    I'll read the book and work through the exercises but sadly it looks like Google maps for this websites requirements. Thank you for you help I appreciate you finding the time to do so.


    Christopher Bird

    Monday, March 31, 2014 10:51 AM
  • Hi Christopher,

    350 is the number of columns you can have in a data source. You can have 200,000 rows/entites in a single data source in an Enterprise account. You can also o two incremental uploads of 200,000 to make your data source contain 600,000 locations.

    That said basic/trial accounts are limited to 50 rows/entities.

    If you want to send me your data source I can check the formatting for you. Looking at your screen shot you are missing the first line which defines the schema version. Also, Excel has a happen of adding quotes on the column names and trailing commas on the schema line, neither of these should be there. You can email me at ricky_brundritt at Hotmail dot com.


    http://rbrundritt.wordpress.com

    Monday, March 31, 2014 12:44 PM