SSRS and maps of route from data?

Question

I have data that has the longitude and latitude values for a point of origin, stops and the return point of origin. I'd like to be able to create a matrix that uses this information from an Excel spreadsheet and then have a map displayed with a line from the beginning to the end. I can figure out how to create the matrix and group so that each route would be separated but how do I create the map with the route line?

Answer 1

The key to the answer for one route is this: create a Scatter diagram, with Connected points. This requires one column with X values and another column with Y values. Since you have coordinates for the starting point, each point along the way, and the starting point again, this will draw the entire route.

Two questions remain: a) how to convert the (latitude, longitude) Pairs into (X, Y) Pairs , and b) how to separate the different routes from each other.

a) converting (Lat, Long) into (X, Y). How you do this depends on how localized your route is: if your route is smaller than a few thousand miles, you can draw it as a flat map (for example, in Mercator projection). On the other hand, if it covers a major portion of the globe, you’ll want a conversion that draws on the surface of a transparent globe. Read the google article on Map Projections to evaluate what kind of projection meets the needs of your route map. From that, one can write formulas into the Excel cells that calculate the (X, Y) from the (Lat, Long).

b) In Excel chart-making, you can draw all the routes at once, by concatenating the X values into one X column, and the Y values into one Y column. To be more specific, put the X values of one route into column A, and the corresponding Y values into the same rows of column B. Then, add one row with no value at all for X and Y. Then, add rows below for the next route. Add a row for each point on a route, then one empty row to separate that route from the next. Once you have all routes concatenation, then you can make one chart with all routes drawn on it by selecting the two columns, and creating a Scatter diagram with Connected points. Excel will conveniently skip drawing to and from a row that has no value for X or Y; this way, there is no extraneous line drawn from one route to the next.

Does this help? If the google discussion of Map Projections is not helpful, please add a comment, and we can work together to decide what kind of projection to use, and I can answer how to make the formulas to implement them.