none
Bing as a MapPoint alternative RRS feed

  • Question

  • Pardon my ignorance here, but I have been working exclusively with MapPoint as the map data source for my VBA (Microsoft Access) applications for about seven years, and am just now considering moving to a web-based data/mapping source.

    My applications do the following things:

    1. Open a set of locations from a MySQL database.
    2. Send the locations (by address or coordinates) to MapPoint
    3. Allow the user to select a subset of those addresses.
    4. Within that subset, route and optimize
    5. Within the MapPoint GUI, manually adjust the position of any specific points.
    6. Retrieve the stop order established on the map back into my app.
    7. Update records in the database.

     #6 is the key element here. Is there a way to do this in Bing (retrieve the stop order on the map along with the identifier used when sending the data to the map so it matches the record in the host app)?

    Monday, May 12, 2014 2:41 PM

Answers

  • Hi Brian,

    Before I try to answer your questions I want to highlight that MapPoint was designed to be a finished product that could be extended through development. Bing Maps has been designed to be a development API. As such there will be a bit more development required to use Bing Maps for what you are looking to do. Also, Bing Maps will require an internet connection to work, there is no offline option. Here are some answers to your questions:

    1. Open a set of locations from a MySQL database.
      If the database is online you will need to create a web service. If the database is local and you are creating a desktop app then you can connect to it directly. Assuming you are using .NET you can follow this article on how to connect the two:
      https://blogs.oracle.com/MySqlOnWindows/entry/how_to_using_spatial_data
      Once you are using EntityFramework to connect to your database you can create a bunch of common spatial queries as outlined in this blog post: http://www.bing.com/blogs/site_blogs/b/maps/archive/2013/07/31/how-to-create-a-spatial-web-service-that-connects-a-database-to-bing-maps-using-ef5.aspx
      http://www.bing.com/blogs/site_blogs/b/maps/archive/2013/08/05/advance-spatial-queries-using-entity-framework-5.aspx
    2. Send the locations (by address or coordinates) to MapPoint
      I'm assuming you meant Bing Maps here. If you are creating a desktop app I would recommend using the Bing Maps WPF control. Assuming you have connected to your database using the previously described method you can then connect this to the map fairly easily. Take a look at this blog post: http://rbrundritt.wordpress.com/2012/06/08/entity-framework-5-bing-maps-wpf/
    3. Allow the user to select a subset of those addresses.
      See response to number 1. It should be fairly easy to create a front end UI that has the filtering functionality you want connected to the database.
    4. Within that subset, route and optimize
      Routing can be done using the Bing Maps REST services. Documentation on the REST services and using them in .NET can be found here: http://msdn.microsoft.com/en-us/library/ff701713.aspx As for optimizing the route doing a traveling salesmen type optimization a bit more work is required as this is not built into the Bing Maps platform. Take a look at this blog post as a possible solution: http://msdn.microsoft.com/library/windows/apps/hh699893(v=VS.110).aspx
    5. Within the MapPoint GUI, manually adjust the position of any specific points.
      I'm assuming you meant Bing here. If you just have pushpins then you can create draggable pushpins as described in this blog post: http://rbrundritt.wordpress.com/2013/10/03/draggable-pushpins-in-bing-maps-net/ Once the drag has been completed you could trigger a recalculation of your routes if you wanted.
    6. Retrieve the stop order established on the map back into my app.
      You would be able to get this from the calculation in point 4.
    7. Update records in the database.
      You could easily do this once you have the entity framework connected to your database.

    I've made the assumption you are looking at creating a desktop (WPF or WinForm) app, if your database is online a Windows Store (windows 8) app can also be done. If that's something you are interested in doing, let me know and I'll provide you some additional resources.


    http://rbrundritt.wordpress.com

    Wednesday, May 14, 2014 9:37 AM

All replies

  • Hi Brian,

    Before I try to answer your questions I want to highlight that MapPoint was designed to be a finished product that could be extended through development. Bing Maps has been designed to be a development API. As such there will be a bit more development required to use Bing Maps for what you are looking to do. Also, Bing Maps will require an internet connection to work, there is no offline option. Here are some answers to your questions:

    1. Open a set of locations from a MySQL database.
      If the database is online you will need to create a web service. If the database is local and you are creating a desktop app then you can connect to it directly. Assuming you are using .NET you can follow this article on how to connect the two:
      https://blogs.oracle.com/MySqlOnWindows/entry/how_to_using_spatial_data
      Once you are using EntityFramework to connect to your database you can create a bunch of common spatial queries as outlined in this blog post: http://www.bing.com/blogs/site_blogs/b/maps/archive/2013/07/31/how-to-create-a-spatial-web-service-that-connects-a-database-to-bing-maps-using-ef5.aspx
      http://www.bing.com/blogs/site_blogs/b/maps/archive/2013/08/05/advance-spatial-queries-using-entity-framework-5.aspx
    2. Send the locations (by address or coordinates) to MapPoint
      I'm assuming you meant Bing Maps here. If you are creating a desktop app I would recommend using the Bing Maps WPF control. Assuming you have connected to your database using the previously described method you can then connect this to the map fairly easily. Take a look at this blog post: http://rbrundritt.wordpress.com/2012/06/08/entity-framework-5-bing-maps-wpf/
    3. Allow the user to select a subset of those addresses.
      See response to number 1. It should be fairly easy to create a front end UI that has the filtering functionality you want connected to the database.
    4. Within that subset, route and optimize
      Routing can be done using the Bing Maps REST services. Documentation on the REST services and using them in .NET can be found here: http://msdn.microsoft.com/en-us/library/ff701713.aspx As for optimizing the route doing a traveling salesmen type optimization a bit more work is required as this is not built into the Bing Maps platform. Take a look at this blog post as a possible solution: http://msdn.microsoft.com/library/windows/apps/hh699893(v=VS.110).aspx
    5. Within the MapPoint GUI, manually adjust the position of any specific points.
      I'm assuming you meant Bing here. If you just have pushpins then you can create draggable pushpins as described in this blog post: http://rbrundritt.wordpress.com/2013/10/03/draggable-pushpins-in-bing-maps-net/ Once the drag has been completed you could trigger a recalculation of your routes if you wanted.
    6. Retrieve the stop order established on the map back into my app.
      You would be able to get this from the calculation in point 4.
    7. Update records in the database.
      You could easily do this once you have the entity framework connected to your database.

    I've made the assumption you are looking at creating a desktop (WPF or WinForm) app, if your database is online a Windows Store (windows 8) app can also be done. If that's something you are interested in doing, let me know and I'll provide you some additional resources.


    http://rbrundritt.wordpress.com

    Wednesday, May 14, 2014 9:37 AM
  • Thank you, Ricky.

    I understand that Bing is available only online and that the API will be very different from MapPoint's API. Let me clarify a few things and see if you still think it can be done:

    • As noted in my original post, I am working in VBA (Microsoft Access), not .NET.
    • My applications already use the MapPoint API heavily. User interaction is limited to those elements requiring visual adjustment on the map. All the sending and retrieving data from/to the map is done programmatically. I do understand that I will have additional development to use the classes offered in the Bing API.
    • The address database is a MySQL database that is hosted online, but my Access desktop application accesses it using the MySQL ODBC DSN.
    • #2 already sends a filtered subset of the data to the map. The subset selection in #3 cannot be done by filtering; it must be done visually on the map. The process description below should help to clarify this.

    The underlying process (i.e. as my Access application as it exists today), and all of these steps are required in order for the application to work for the businesses using it:

    1. The user sends addresses for an entire day's worth of deliveries (perhaps 800 addresses) out to the map at once. This is a subset of the entire week's worth of deliveries.
    2. The user then (visually on the map) selects a subset of those points to be used to create a route that will fit onto one delivery van. This could be 80 to 100 addresses, so it cannot be done by individual selection; the user needs the ability to select them en masse (in MapPoint, it is by drawing a circle around them).
    3. The warehouse location is then added as the start and end in relation to that subset of addresses, since this is where the driver will begin and end his day.
    4. That set of addresses (i.e. those selected on the map and the warehouse start and end points) is then automatically optimized into an efficient order (the magic of MapPoint, and I assume Bing also) so that each address is assigned a stop number.
    5. The user can move one or more points up or down in the stop order, reverse the stop order for a range of selected stops, or both at once (move and reverse a range of stops).
    6. In my application, the user clicks a button which records the stop number for each delivery and updates the record for that delivery in the back-end MySQL DB to include the stop number. It then removes the already-routed addresses from the map so the user can see what is left.
    7. The user then repeats steps 2 through 5 until no points remain on the map. He may also print the directions for each route as it is completed (i.e. between steps 4 and 5) for the drivers.
    8. The users can then print the delivery manifest from my application (data coming from the MySQL DB), which is used to load the vehicle in the order of stops (LIFO--Last In, First Off) as well as tell the driver what goods will be dropped at each stop.

    The driving force behind the consideration to move away from MapPoint is the cost per seat, its data becomes obsolete between versions, and I have discovered a couple of bugs that, while not critical, remain somewhat of a nuisance to us. I have no problem doing all the work to rewrite my application as long as I can stay within Visual Basic (VBA) and Bing can actually do all the things I need it to do. I would just hate to spend 20 hours developing my way through things and then find out that it has no way of, for example, allowing on-map grouping or removing selected points from the map via a programmatic command.


    • Edited by Brian D. Hart Wednesday, May 14, 2014 3:29 PM Clarification
    Wednesday, May 14, 2014 3:28 PM
  • You should be able to access the Bing Maps REST services from VBA, but won't be able to create an interactive map. One thing you might want to consider is using the in for Access. This will allow you to use .NET and the Bing Maps WPF map control.

    As for removing selected its from the map or grouping (assuming clustering) these are all things that can be done with a bit of work with the WPF control. In fact I created a set of tools that work with the WPF control that make things a lot easier. http://mapstoolbox.codeplex.com/


    http://rbrundritt.wordpress.com

    Wednesday, May 14, 2014 3:43 PM
  • Thank you, Ricky. It looks like I have some work ahead of me to analyze the potential migration path here. You have answered my questions. However, I think a word or two got chopped out of your response somehow:

    • "One thing you might want to consider is using the in for Access" [I think a word or two might have been dropped here]
    • "As for removing selected its from the map..." [Maybe part of a word was dropped]

    Note that the reason I am sticking to Access here is that I am primarily a systems integrator: managing networks, servers, computers, and programming only as required. So while I have had to write some fairly sophisticated applications that have, over time, become central to some of my clients' operations, I never have time to make the jump to any other development platforms such as .NET, despite the benefits. So it is not that I do not see the benefits; there are just not enough hours in the day (or enough space in my brain).

    Wednesday, May 14, 2014 5:29 PM
  • The first word was supposed to be: Visual Studio Tools for Office (VSTO)

    The second word was meant to be "removing selected items"


    http://rbrundritt.wordpress.com

    Wednesday, May 14, 2014 6:22 PM
  • Thank you, Ricky. It looks like I have some serious work ahead of me!
    Wednesday, May 14, 2014 10:45 PM