Preparing Latitude & Longitude Data for showing in SAP Lumira and SAP Analytics Cloud

Overview

Many mapping tools (and other applications like phones and GPS units) are able to create maps directly from columns containing latitude and longitude data. To use this data in SAP Lumira or SAP Analytics Cloud, requires you to present the data in correct format.  In this article we’re going to go through a number of ways that you can quickly manipulate the latitude and longitude data you already have, and show accurate, high performing maps with 10,000s of points.

Quick(ish) background

Latitude and longitude are used to locate points on the globe as a pair of numbers. Latitude represents how far north/south the point is and longitude represents how far east/east the point is. The numbers are often expressed in degrees because the earth is approximately a sphere. Because we can travel all around the world we use 360 degrees of longitude for a full circle and 180 degrees of latitude for the half circle of north-south. To make life more interesting, we don’t use 0-360 longitude and 0-180 degrees latitude though. We use -180 to 180 degrees longitude and -90 to 90 degrees latitude.

(Source: Wikipedia)

0 degrees longitude passes through the ‘prime meridian’ of Greenwich, in south west London, England. Points west of this are negative longitude, points east are positive. 0 degrees latitude is the equator with positive numbers representing the northern hemisphere and negative the southern hemisphere.

See https://en.wikipedia.org/wiki/Geographic_coordinate_system for more of the technical details!

Numeric format conversions

The most common step necessary to get existing longitude latitude data successfully into maps is to convert the the values into a format that the maps available for SAP Lumira can understand. Here are some of the most common conversions:

Tip 1 – Convert from degrees, minutes & seconds to decimal degrees

Probably one of the most common formats for latitude and longitude is the format of degrees, minutes, seconds (DMS). For example (38.897680 , -77.03653) becomes (38° 53′ 51.648″ N 77° 2′ 11.508″ W). Degrees, minutes and seconds breaks down angles a little bit like the hands of the clock into hours, minutes and seconds (hence the name). To show these in many mapping tools such as SAP Lumira & Visual Crossing requires us to convert from DMS to decimal degrees using the formula:

Decimal Degrees=degrees+minutes/60+seconds/3600

Here’s an example of how the values apply to New York.

If you are user of Microsoft Excel, Microsoft has created a series of macros that provide a function for performing the conversion – see https://support.microsoft.com/en-us/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle.

This could also be created as a custom calculation in SAP but I would recommend performing such conversions ahead of time to improve performance.

Tip 2 – Your data contains prefixes rather than negative and numbers

Sometimes you will encounter latitude, longitude data such as (38.897680N , 77.03653W). The N indicates ‘North of the equator’ and the W indicates ‘West of the prime meridian’. In these cases we need to remove the letter suffixes and replace them with negative values where appropriate. Negative values are for ‘West’ and ‘South’. Therefore we use (38.897680 , -77.03653) as our final result. It is typically better to perform this conversion as part of the data preparation step rather than at run time during the story rendering within SAP Lumira – this will improve performance for larger datasets.

Tip 3 -Your data contains values that aren’t in the correct range

As we mentioned above, most mapping tools support latitudes from -180 to 180 longitude and -90 to 90 degrees latitude. If your data is between 0 to 360 and 0 to 180 degrees, simply subtract 180 from the longitude and 90 from your latitude! Sometimes you may encounter data that goes beyond the range. For example, some latitude and longitude data may be expressed in radians rather than degrees or other differences related to the projection of the data. If the data is in radians rather than degrees, expect to see numbers in the range of -2*Pi to 2*Pi (approximately -6.2 to 6.2).

Another frequent data range problem is a result of the data being expressed as projected numbers, typically ‘meters’ due to projections such as Universal Transverse Mercator (UTM). This is common in many data sets where the host country has historically uses these projected values. Discussions of projections is out of the scope of this document but if you see numbers in the millions (such as 308165.30 607733.40) then this is a good hint that you may be dealing with projected values.

Tip 4 – Making sure the values have enough precision

One of the most common reported problems users have is when a map of points resembles this display:

The real map should look like this.

This problem occurs because the values don’t have enough precision – typically you want at least four or five digits after the decimal point to produce an accurate business map (some highly accurate use cases may require even greater precision!) The rounding of the numbers causes the values to snap to a grid-like appearance like the first screenshot. The fix for this common problem is to review your data to ensure that you have at least four  digits after the decimal point.

Tip 5 – points are displayed just off the Horn of Africa

Often we see maps that include points just off the Horn of Africa. Why is this? Because that is where latitude, longitude 0,0 exists. This therefore indicates points in the data set that have missing, or 0,0 values for latitude and longitude (see https://en.wikipedia.org/wiki/Null_Island).

Final thoughts

Once you have your longitude and latitude data in the right format with enough precision you will be able to construct high performing maps in your SAP Lumira and SAP Analytics Cloud deployment. In addition to presenting geographical data in a simple, easy to consume format, displaying latitude and longitude data on a map can provide for many sophisticated analysis techniques unavailable to other visualizations.