none
Importing Excel list of addresses into Bing Maps???? RRS feed

  • Question

  • I am wondering if this is even avaiIable, and if so, if someone could help me out. I have spent months on this to no avail. I have an excel file with 665 customer addresses that i would like to import into Bing Maps. Once the addresses are installed, I will visually be able to see 3 different sales peoples customers base. I would like to use this information to create 3 different territories within a Metropolitan area. From here, I would like to take this information and import it into a Garmin GPS. Is this possible? If so, can you please point me in the right direction. Thank you for any help or guidance that I can get on this!
    Sunday, November 27, 2016 4:12 AM

Answers

  • When you say import into Bing Maps, do you want to display it on top a Bing Maps map within Excel, or within a custom application. Or are you looking to display it on the Bing Maps website (not an option).

    If you simply want to display the custom locations on Bing Maps in side of Excel, there are many ways to do this. Here is a blog post with details: https://blogs.msdn.microsoft.com/bingdevcenter/2014/07/15/4-easy-ways-to-visualize-excel-data-on-bing-maps/

    If you want to display the data inside a custom Bing Maps application, then you will need to convert your data into a format Bing Maps can understand and geocode it. I recommend converting your file to a CSV and uploading to the Bing Spatial Data Services. The first half of this blog post shows how to get your data into the Bing Spatial Data Services: https://rbrundritt.wordpress.com/2014/10/10/how-to-add-custom-auto-complete-functionality-to-your-map-app/ Once uploaded you can easily pull it into Bing Maps. Here is a code sample: https://msdn.microsoft.com/en-us/library/mt750554.aspx

    The above will allow you to display your addresses on a map. Once displayed you can change the icons based on another column of data such as the sales person. This is fairly easy to do.

    Now, if you want to then create polygon territories base don this data, then things get a lot more complicated. There are a few ways to do this, but the most accurate way is to first create Voronoi diagram using all the data points. Then loop through each polygon in the Voronoi diagram, determine which data point intersects it so that you can then determine which sales person this is for, then separate the polygons into separate arrays for each sales person. From there, union all the polygons in each array to generate your sales regions. All of this can be accomplished using the spatial math module built into Bing Maps V8: https://msdn.microsoft.com/en-US/library/mt762861.aspx

    Once this is done you can export the generated polygons in a number of different ways. The two main ways is as GeoJSON or Well Known Text (used by spatial databases). GeoJSON is one of the most common file formats used for spatial data these days. Your GPS device may be able to import them in. If not, there are a bunch of online tools that can be used to convert this file to another format. Your GPS device likely supports GPX, however GPX doesn't support polygons. You can convert your shapes though to lines which GPX will support, but this will only display the sales region as an outline on your GPS device.


    [Blog] [twitter] [LinkedIn]

    Monday, November 28, 2016 9:05 PM

All replies

  • Hi,

    It is an interesting idea.
    I'm not sure it's possible or not, but I'd like to ask you to provide some sample addresses.
    The word "customer addresses" is not sufficient.

    Ragards,
    Ashidacchi
    Sunday, November 27, 2016 5:44 AM
  • When you say import into Bing Maps, do you want to display it on top a Bing Maps map within Excel, or within a custom application. Or are you looking to display it on the Bing Maps website (not an option).

    If you simply want to display the custom locations on Bing Maps in side of Excel, there are many ways to do this. Here is a blog post with details: https://blogs.msdn.microsoft.com/bingdevcenter/2014/07/15/4-easy-ways-to-visualize-excel-data-on-bing-maps/

    If you want to display the data inside a custom Bing Maps application, then you will need to convert your data into a format Bing Maps can understand and geocode it. I recommend converting your file to a CSV and uploading to the Bing Spatial Data Services. The first half of this blog post shows how to get your data into the Bing Spatial Data Services: https://rbrundritt.wordpress.com/2014/10/10/how-to-add-custom-auto-complete-functionality-to-your-map-app/ Once uploaded you can easily pull it into Bing Maps. Here is a code sample: https://msdn.microsoft.com/en-us/library/mt750554.aspx

    The above will allow you to display your addresses on a map. Once displayed you can change the icons based on another column of data such as the sales person. This is fairly easy to do.

    Now, if you want to then create polygon territories base don this data, then things get a lot more complicated. There are a few ways to do this, but the most accurate way is to first create Voronoi diagram using all the data points. Then loop through each polygon in the Voronoi diagram, determine which data point intersects it so that you can then determine which sales person this is for, then separate the polygons into separate arrays for each sales person. From there, union all the polygons in each array to generate your sales regions. All of this can be accomplished using the spatial math module built into Bing Maps V8: https://msdn.microsoft.com/en-US/library/mt762861.aspx

    Once this is done you can export the generated polygons in a number of different ways. The two main ways is as GeoJSON or Well Known Text (used by spatial databases). GeoJSON is one of the most common file formats used for spatial data these days. Your GPS device may be able to import them in. If not, there are a bunch of online tools that can be used to convert this file to another format. Your GPS device likely supports GPX, however GPX doesn't support polygons. You can convert your shapes though to lines which GPX will support, but this will only display the sales region as an outline on your GPS device.


    [Blog] [twitter] [LinkedIn]

    Monday, November 28, 2016 9:05 PM
  • This method doesn't work for my particular issue.  Let me explain my issue, my current method and what I'd like to see.

    Problem: Delivery company has boxes on the truck with 20 different addresses.  We'd like to be efficient in delivery, so we want to see a map with 20 pins of some kind so we can visualize a good order for the truck.

    Current method: I bring up Bing maps in one window and the list in a text document in another window.  I highlight the first address, copy and paste into Bing maps and hit enter (first pin).  Repeat for the next address. Etc.  When I'm done I have I have a nice map of pins.  (I'd like to show you an example, but I can't attach an image until my account has been verified.  I'll come back after that happens to post a picture. Or try it yourself by typing 3 or 4 addresses into Bing maps)

    The problem is that I have to cut and paste each address separately.

    What I'd like to see:  I'd like a way that I could highlight the entire list of addresses from the text document, paste it as a unit into Bing, and have Bing put up all ten addresses.

    Is there a way to do that?

    P.S.  This would solve the other problems above without having to geocode it or use an addon... Simply print the spreadsheet to a document putting the address together like a mail merge.  Then cut and paste as a unit.

    Sunday, June 16, 2019 3:14 PM
  • Hi Campanellajc,

    You can certainly use powermap (https://support.office.com/en-us/article/Get-started-with-Power-Map) for part of this effort but Excel wasn't really designed to be a router/scheduler. I recommend you reach out to our Bing Maps partner, OnTerra, and find out about their RouteSavvy product. It is on the top of the list at this recommendation page (https://www.microsoft.com/en-us/maps/mappoint-alternatives).

    Sincerely,

    IoTGirl

    Monday, June 17, 2019 5:07 PM
    Owner