# Spatial Reference Systems – Background Knowledge and the use in SAP HANA

##### Tags: spatial_reference_systemspatialspatial_processing

In the context of geodata, spatial reference systems (SRS) links a coordinate system to the Earth, so that geographic entities can be located unambiguously.

When working with spatial data in SAP HANA or geographic information systems (GIS) like ArcGis, it is important to know the concept of spatial reference systems and which one you should use. Background knowledge about SRS and therefore the use of the right SRS will help you avoid mistakes like calculation errors or misinterpretation of coordinates.

Basic structure of spatial reference systems

The earth isn’t flat and it isn’t even a perfect sphere either. Because of that, we unfortunately need several models to locate an object on the surface of the earth correctly. The conjunction of these models makes up a spatial reference system. The basic structure of an SRS consists of three parts: a reference ellipsoid, a geocentric datum and a coordinate system. Using a datum and an ellipsoid, the SRS links a coordinate system to the Earth. In the following I will explain these models.

Reference ellipsoids of the earth and geodetic datums

There are two main questions which are clarified by the ellipsoid and the geodetic datum: What is the shape of the earth and where is its center. The real shape of the physical earth is very irregular with big excursions (including mountain ranges, for example the Himalayas) and depressions (for example the Dead Sea). This surface is approximated and thereby smoothed by the geoid which represents the shape that the surface of the ocean would take, if only influenced by the gravitation and rotation of the Earth. This surface is extended through the continents, so that every point on the Geoid has the same gravity potential energy (equipotential). According to general assumptions, the geoid has variations of +106 to – 85 meters. This mathematical figure of the Earth results in the fact that mass within and on the Earth is distributed. This is illustrated in Figure 1 and in Figure 2, respectively. Figure 1 shows the mass distribution around the Earth, while Figure 2 shows the mass distribution around the equator compared to a circle.

Figure 1; Soure: Wikipedia

Figure 2; Source: Wikipedia

Since the Geoid is too complicated and too complex to work with as a reference model of the earth, most of the predefined SRS use a reference ellipsoid as approximation of the geoid. Most commonly, Earth is thought of as a sphere, but due to its rotation, its approximated shape is more an oblate spheroid, also called an ellipsoid. This means that the planet is flattened and the equator axis is 21 kilometers longer than the polar axis. In the past, the approximation of the geoid could only be done locally. This led to the development and the existence of many ellipsoids, so that nearly every country had developed their own ellipsoid. Each ellipsoid was best-fit for only a limited area and all with different size and shape. Today, many of these reference ellipsoids are out dated and no longer needed. By the help of modern technologies, it was possible to develop global fitting ellipsoids, like the ellipsoid of the world geodetic system (WGS84) that is used by the global positioning system (GPS). However, there are still a few of the “old” ellipsoids in use. For instance, the Airy ellipsoid (from 1830) or the Bessel ellipsoid (from 1841).

Figure 3; Source: IOGP

An ellipsoid is defined by the semimajor axis a and either the semiminor axis b or the flattening f. The flattening of an ellipsoid is the difference between the two axes and it is described as:

f = (a – b) / a

In the case of the earth, the flattening is approximately 0.003353, a very small value Because of this it is usually given as 1/f.  There are two often used Ellipsoids which are the WGS84 and the GRS80. The parameters of both are listed in the following table:

 Semimajor axis a (m) Semiminor axis (m) Inverse Flattening (1/f) WGS84 6,378,137 6,356,752.31424 298.257223563 GRS80 6,378,137 6,356,752.3141 298.257222101

Because of the fact that changing an ellipsoid of an SRS leads to the consequence of changing all coordinate values, many organizations stick to older ellipsoids instead of using newer and more accurate ones.

The geodetic datum defines the position of the ellipsoid’s center, the orientation of the ellipsoid and the prime meridian which is used as zero longitude. The center of the earth is defined through the use of a set of points on the ground which are related to points on the ellipsoid. There are two types of datums – geocentric and local datums. The geocentric datums are earth-centered and try to deliver a description for the whole earth, using the earth’s center of mass as origin. Because of the fact that changing an ellipsoid of an SRS leads to the consequence of changing all coordinate values, many organizations stick to older ellipsoids instead of using newer and more accurate ones.

A local datum tries to fit the ellipsoid in the best way to the earth’s surface in only a particular area. In a local datum there is exactly one point which matches a point of the ellipsoid to a point of the earth’s surface. This point is the point of origin of the datum and all other points in the SRS is calculated from it. WGS1984 and GRS1980 are examples for SRS with geocentric datums, while ED1950, which was designed for Europe and NAD1927, which was designed for North-America, are examples for local datums. In Figure 4, you can see the difference between a local datum and a geocentric datum compared to the surface of the earth.

Figure 4; Source: ESRI

Because of continental drift, plate tectonics and other processes included in the mantle convection, the earth’s center of mass is changing. Therefore datums have to be recalculated from time to time. Because of the movement of continents and the thereof resulting shift of the point of origin, this also applies to local datums. For instance, Australia is moving 7 centimeters per year. Over time, this would lead to relatively big miscalculations. This is the reason why there is always a date included in the datum’s name. While the WGS84 Ellipsoid stayed the same until today, the datum of the SRS WGS84 was adjusted several times.

Coordinate Systems

A coordinate system is simply a system which uses one or more numbers (called coordinates) to uniquely determine the position of a point relative to a center. In an SRS, this is usually done by one of the following three systems.

Probably the most well-known coordinate system in the context of spatial data is the geographic coordinate system or spherical coordinate system. It uses angles relative to the prime meridian (which is most of the time Greenwich) and the equator. Those angles are given as Longitude and Latitude. Longitude represents the position of a point east or west of the prime meridian, while north and south of the equator are covered by Latitude. The bounds of this coordinate system are -180° W to 180° E and -90° S to 90° N. Points are described by degrees, minutes and seconds.  Heights (if given) are usually relative to the mean sea level or the datum. For Instance, the geographic coordinates in WGS84 of the Eiffel Tower are:

48˚ 51’ 29”N, 002˚ 17’ 40”E

The second coordinate system which is commonly used is the Cartesian coordinate system. Most of us used that kind of coordinate system in an easy way in school’s math class. It’s a “flat” coordinate system which is placed on the surface of the earth. Cartesian coordinate systems are very easy to work with and are fairly accurate over small distances. A well-known example for a Cartesian coordinate system is the Universial Transverse Mercator (UTM) system which was established by the US-Army and is today, among others, used by the NATO.

The third is the geocentric coordinate system. It is technically also a Cartesian coordinate system but because it is used very often, it can be seen as an independent system. Like the Cartesian coordinate system, it is also based on “normal” X, Y and Z coordinates but with its origin at the center of the earth.

Figure 5; Source: Shargis

The coordinate system defines the unit of measure of the SRS (for instance meter or degree) and the bounds of the SRS and therefore the maximum and minimum coordinates. Figure 5 shows a good summary of the concepts (reference ellipsoid, datum and coordinate system) which are at least needed to define an SRS.

Figure 6;  Source: ICSM

Types of spatial reference systems

There are several different types of SRS, like geographic, projected, engineering or vertical. For the basic use of spatial data in SAP HANA and GIS, only the first two play an important role. A geographic coordinate reference system consists of the three models described above. A projected coordinate reference system is always based on a geographic coordinate system and adds a map projection.

Map Projections

Because the earth isn’t flat, it is impossible to put the earth correctly on a flat paper or a computer screen. There will always be some sort of distortion. So it was and is always the task or desire of mapmakers to preserve some features of the earth in order to express the intention or purpose of the map. Some of them preserve area (for instance the expanse of countries) while others preserve angles. A pretty good example for the always existing distortion is the Mercator projection. This projection is nearly 500 years old but still plays a role in all of our lives. The Mercator projection forms the basis for nearly all web maps and is the standard for web mapping.  Google maps, Bing, HERE and all others use the Web Mercator projection which is a modification of the original Mercator projection. This projection displays areas close to the equator correctly, but further away from the equator, the distortion of areas increases by a factor of up to 10. That is the reason why Greenland is displayed in Web Maps nearly as large as the continent Africa (see Figure 6), while, in fact, Africa is 15 times larger than Greenland. The same holds for Antarctica, which looks in Web Maps as large as all other land masses combined, while its size is only barely the half of Africa. In Figure 7 and in this article, you can get a good feeling for the real expanse of Africa.

Figure 7: Map with Web Mercator projection

Figure 8: Size of Africa compared to several countries; Source: Economist

As described above, the first aspect with which projections can be classified is the preservation of metric properties:

• Conformal projections: preserve angles and shapes locally. No map projection can preserve shapes of larger regions
• Equal-area projections: preserve the area of displayed features, but distort all other properties (like shape, angles and scale)
• Equidistant projections: preserve the distances between certain points
• Compromise projections: try to create a balance or compromise between the different distortions in order to make the displayed features “look right”

The surface of the projection is the second classification aspect. Each of them projects the earth or a part of it on a specified surface. There are basically three different types:

• Cylindrical
• Conical
• Azimuthal

Figure 9; Source: ITC

All of them can either be tangent surfaces or secant. The first means that they touch the earth only in one point or a closed line. The second means that they touch the earth in one or two closed lines. On the right you can see the three different tangent surfaces. These three are the basic map projections. However, there are a lot more. But common to them is that they always transform the underlying coordinate system to a Cartesian coordinate system, which means that adding a projection to an SRS will always involve the change from a round-earth SRS (with geographic coordinate system) to a flat-earth SRS (with Cartesian coordinate system).  For a detailed description of all map projections, refer to here. If you have trouble selecting the right projection, you can take a look at the “projection wizard” tool, in which you can select the requested area and the distortion property.

Parameters and Well-known Text of spatial reference system

Now that we have the background knowledge of the basic structure of SRS, we know the models of the parameters needed to define an SRS and can understand the Well-known Text language / format that is usually associated with SRS.

According to the International Association of Oil & Gas Producers (IOGP), the minimum parameters needed to define a geographic coordinate reference system are:

• Geographical coordinate reference system name
• Geographical coordinate reference system axes units
• Geodetic datum name, if this is different from the geographic coordinate reference system name
• Ellipsoid name
• Ellipsoid defining parameters: semi-major axis value, semi-major axis units name and  inverse flattening value.

If the prime meridian is not Greenwich, the following must also be given:

• Either prime meridian name (from EPSG database – EPSG is explained below) or Greenwich longitude of prime meridian.

If the geographic coordinate reference system is three dimensional, the following must also be given:

•     Units for ellipsoid height

The minimum parameters needed to define a projected coordinate reference system are:

• Projected coordinate reference system name
• Geographic coordinate reference system as described above, except that the geographic coordinate reference system axes units are not required
• Map projection (coordinate conversion) name
• Name of map projection (coordinate operation) method
• Map projection (coordinate operation) parameters, values and units
• Projected coordinate reference system axes units name, and if not meters the unit conversion to be applied to the units to convert to meters

The defining parameters of an SRS are usually given as Well-known Text (WKT). The WKT of WGS 84, the most common geographic coordinate reference system is:

GEOGCS["WGS 84",

DATUM["World Geodetic System 1984",

ELLIPSOID["WGS84",6378137,298.257223563,LENGTHUNIT["metre",1.0]]],

CS[ellipsoidal,2],

AXIS["latitude",north,ORDER[1]],

AXIS["longitude",east,ORDER[2]],

ANGLEUNIT["degree",0.01745329252]

An example for a projected coordinate reference system is the WGS84 /Pseudo Mercator, also known as Web Mercator. I mentioned it already above. Its WKT is:

PROJCRS["WGS 84 / Pseudo-Mercator",

BASEGEOGCRS["WGS 84",

DATUM["World Geodetic System 1984",

ELLIPSOID["WGS 84",6378137,298.257223563,LENGTHUNIT["metre",1.0]]]],

CONVERSION["Popular Visualisation Pseudo-Mercator",

METHOD["Popular Visualisation Pseudo Mercator",

PARAMETER["Latitude of natural   origin",0,ANGLEUNIT["degree",0.01745329252]],

PARAMETER["Longitude of natural origin",0,ANGLEUNIT["degree",0.01745329252]],

PARAMETER["False easting",0,LENGTHUNIT["metre",1.0]],

PARAMETER["False northing",0,LENGTHUNIT["metre",1.0]]],

CS[cartesian,2],

AXIS["easting (X)",east,ORDER[1]],

AXIS["northing (Y)",north,ORDER[2]],

LENGTHUNIT["metre",1.0]

Importance of the right SRS: Examples for miscalculations and wrong localization

When working with spatial data, there can easily occur errors because of the wrong use of SRS. I want to show you two examples why it is important to know the SRS you are working with and its parameters. The first example is about the value of coordinates. It is important to note that Latitude and Longitude, and therefore the coordinates of geographic coordinate system, are not unique. Most people think that the GPS coordinates are universal but that’s not the case. Above, I mentioned the coordinates of the Eiffel Tower in WGS84 (48˚ 51’ 29”N, 002˚ 17’ 40”E). These coordinates are similar to GPS coordinates, because the underlying SRS of the global positioning system is WGS84. If we use the same coordinates in another geographic coordinate reference system (round-earth) that uses different parameters (like the ellipsoid or Datum), we end up being somewhere completely different. To illustrate how sensible this error is, I have done that with the position of my office. In the satellite picture below, you can see that my office is in the SAP Headquarter in Walldorf (Germany) and I am sitting in Building 3. This location is represented by the light blue point and its WGS84 coordinates are 49° 17´ 34.35´´ N, 8° 38´35.58´´ E. The orange point has the same Coordinates, but its SRS is the local-used ED50. Because of the different parameters, I end up sitting on a parking lot which is nearly 160 meters away from my desk. Even worse is the location of the pink point. Again, it has the same coordinates, but its SRS is AGD84, which is an SRS best-fitting for Australia. With this SRS, I end up sitting approximately 205 meters away, in Building 2.

Figure 10

Even worse errors and miscalculations can occur when working with projected coordinate reference systems (flat-earth). Because of the distortion, which accompanies with the transforming of the earth to a flat surface, huge miscalculations can easily happen. The common Web Mercator is again a good example. In contrast to most projected coordinate reference system, the Web Mercator isn’t designed to minimize any type of distortion. Instead, it was designed to enable a best possible performance in Web applications. To do this, the entire earth was fit into a square area that could be covered by 256 x 256 pixel tiles. Like in the original Mercator projection, this causes huge distortions, if further away from the equator (see Section “projections”). This distortion affects the measurements or calculations. While calculations close to the equator are fairly accurate, calculations done further north or south are very inaccurate. There is a Demo of ESRI that shows this effect pretty well. The Demo is located in the US state Oregon and compares measurements in three different SRS. The first one is the Web Mercator and the other two are local SRS best-fitting for North America. As you see in the Picture, the measurements exhibit a huge difference. The area of the drawn polygon in Web Mercator is approximately 17.500.000 m² greater and the extent of the polygon is about 7150 meter greater compared to the local-centered SRS. You can find the “compare measurements” Demo here.

Figure 11; Source: ESRI

Spatial Reference System Identifiers, EPSG and setting up your own SRS in HANA

A Spatial Reference System Identifier (SRID) is a unique value which unambiguously identifies the associated SRS. When SAP HANA performs operations or calculations, it uses the SRID to look up the spatial reference system definition so that the calculations are performed properly for the SRS. Nearly every major provider of spatial data processing has their own SRID implementation. That counts also for SAP HANA. You can look up the SRS and their SRID, included in HANA, by the following SQL Command:

select * from public.st_spatial_reference_systems;

If you’re looking for a certain SRS, you can look it up by (WGS84 as example):

select * from public.st_spatial_reference_systems where srs_id = 4326;

The SRIDs used in HANA are unique and not universal. Although most SRID in HANA, just like in many other GIS or Databases, refer to the Authority of the European Petroleum Survey Group (EPSG) which is today maintained by the IOGP. The EPSG registry contains a vast set of predefined SRS that are still in use or required. You can search in this registry for Name, Type, area of use or EPSG ID. There are also other registries (like EPSG.io or spatialreference.org) which contain not only the SRS listed by the EPSG but also SRS that are for instance used/contained in ArcGis. In EPSG.io you can do the same search as in the original EPSG registry, but you can additionally pick a position on a map to get a fitting SRS or transform coordinates from one SRS to another. If you want to create your own SRS in SAP HANA, these independent registries can offer you information of existing SRS that are not included in the EPSG registry. Here you can find a guide for creating an own SRS in SAP Hana.

Spatial Data and their spatial reference system in SAP HANA - Import and Modelling

When importing spatial data in SAP HANA, you need to identify the SRS of the data using its SRID. You can do this either with the SQL Command for importing or by the use of the menu functions. Both possibilities are shown below by the Import of Shapefiles with SRID 4326 (WGS84).

IMPORT MY_SCHEMA.MY_TABLE AS SHAPEFILE FROM '/tmp/shapefile' WITH SRID 4326;

If the SRS is not specified, SAP HANA uses the Default SRS with SRID 0. This is a spatial reference system with Cartesian coordinate system and it works with the data on a flat, two dimensional plane (flat-earth SRS). Every coordinate is located using a single pair of x and y coordinates, where x and y are between -1.000.000 to 1.000.000. Most of the time, it is advisable to specify the SRS. When working with spatial data, you will find the Number 4326 a lot. As already mentioned, this is the EPSG Code of WGS84. In SAP HANA, it has the same SRID as in the EPSG registry. This round-earth SRS is used commonly, because it covers the whole earth and still offers an accuracy of ± 1 meter. Additionally, it is the SRS used by GPS. When the SRS of your data is WGS84, you can also use the projected version of it. WGS84 (planar) adds an equirectangular projection to the original SRS and its SRID is 1000004326. While the Mercator projection (which we already dealt with above) stretches areas close to the poles, the equirectangular projection squashes them. For example, at the equator in both WGS 84 and WGS 84 (planar), 1 degree longitude is approximately 111 km. At 80 degrees north, 1 degree of longitude is approximately 19 km in WGS84, but WGS84 (planar) treats 1 degree of longitude as approximately 111 km at all latitudes. This results in heavy distortion. That is the reason why this SRS should not be used for distance and area calculations. However, it still offers advantages compared to the round-earth WGS84. Import, modelling and calculations can be done with higher performance which means that you save time. You can use WGS84 (planar) in a quick way for relationship predicates like ST_Contains, ST_Touches, ST_Covers and so on. For all other calculations, you should use WGS84 because of its higher accuracy. Generally it can be said that geographic coordinate reference systems (round-earth SRS) offer a higher accuracy, while projected coordinate reference systems (flat-earth SRS) offer a higher performance.

If you want to import spatial data and don’t know the SRID of the SRS of your data, you can use this tool to find the EPSG Code by the SRS definition (Well-Known Text). Copy your Well-Known Text in there or just upload a .prj file and you will get the fitting EPSG Code. The SRS definition (WKT definition) of Shapefiles are saved in the .prj datatype. You can open this datatype with Notepad to see the definition of the SRS.

Figure 12; Source: Prj2EPSG.org

Transforming spatial reference systems with SAP HANA and ArcGis

With SAP HANA, you can transform the SRS of geometries. With this, you change the underlying SRS to another different one. You can use this for individual geometries or for a whole column by the SQL command:

<geometry-expression>.ST_Transform(<srid>)

Example:

SELECT NEW ST_Point('POINT(-86 36)',4326).ST_Transform(1000004326) from dummy;

If you want to transform/change the SRS of a whole dataset (for instance of a Shapefile), you can use a tool in ArcGis. In the tool “project” you can select an Input feature, an Output feature and the new SRS. You find this tool under ArcToolbox\Data Management Tools\Projections and Transformations\Project or by typing “Project” in the Search Window. The open-source GIS QGIS offers a similar tool. You can find the “define current projection” tool in the data management tools.