In a previous blog post, I wrote about adding additional spatial reference systems to SAP HANA here. There are over 3900 spatial reference systems defined in the EPSG data set. Click here to read more. Although you can easily add more either using the Metadata Installer (adds 3900 more) or executing a SQL Statement (adds one per statement), out of the box HANA supports 4 by default. Three of them are: cartesian (origin at 0,0 - a flat two dimensional coordinate system you may remember from grade school), spheroidal WGS 84 (4326) and planar WGS 84 (1000004326).
There are many reasons why you want to transform between spatial reference systems (SRS) in HANA. You may be streaming or loading data into HANA with different SRSs and you want to perform spatial analytics or mash up the data. Or you may simply want the spatial data to be in the same SRS. Performing the transforms in HANA is extremely fast and can process millions of rows. GIS packages typically are not meant to perform transforms in bulk. Another reason is that when publishing a feature service from Esri ArcMap, for instance, all WGS84 layers are stored as SRID 1000004326 in HANA due to previous limitations of the round-earth SRS implementation. Under some conditions tables that store spatial data in HANA with spatial reference system 4326 may not behave as expected in ArcGIS. See this page on the Esri website for more details.
Some spatial calculations like ST_AREA, ST_DISTANCE and others can return different answers depending on the size of the polygon or distance between two points and the spatial reference system used. This is because a spheroidal SRS takes the curvature of the earth into account. The SAP HANA Spatial Reference Guide has a section that describes this here. You may want to transform spatial data stored in SAP HANA as SRID of 1000004326 to a spheroidal SRS like 4326 before performing these and other similar calculations. Conversely, if you are going to consume HANA spatial data in the ArcGIS platform and that data is stored with an SRID of 4326, you may want to transform it to the planar representation (SRID of 1000004326) either in the underlying HANA table or using a view.
Fortunately, it is easy to transform between spatial reference systems in HANA. First, check to see if the spatial reference system(s) used by the data you are loading into HANA are defined. To check for SRID of 3857, run the following query in a SQL Console in HANA Studio:
If the SRID is present in the SAP HANA instance, you should see the following result:
After you've checked that all the expected SRIDs are defined in HANA, you can load your spatial data into HANA and transform between spatial reference systems as needed.
In this example, I create a table to hold the transformed values. I'm converting two floats (representing the longitude (x) and latitude (y)) to points in two different spatial reference systems. I'm using two spatial reference systems that HANA is configured with by default.
Note that once a table is created with a spatial column (the SRID defaults to 0 if one isn't specified as shown above), you can't modify the SRID of the column.
Next, I run an INSERT to obtain the values from an existing table:
Let's take a look at the spatial functions used to perform the transform. ST_SRID( <SRID>) is used to change the SRID of the data but the transform must be done with ST_TRANSFORM(<SRID>). The ST_SRID function is needed to set the SRID because the ST_POINT constructor form used above returns a value with an SRID of zero (for which there is no transform defined).
The PREMISE_NO is converted to a VARCHAR because ArcGIS requires a unique key and float is not an valid datatype for the unique identifier. Defining a query layer in ArcGIS requires that a unique key be specified.
Next, I issue a SELECT to examine the values and verify the transforms worked as expected:
The function ST_ASEWKT() returns the spatial data in a human readable form called extended well known text (EWKT). This is the result:
The SELECT does two things. First, it constructs a point using the lat and long which are floats and then transforms that point to SRID of 4326. Second, it shows the two transformed values - one for SRID 1000004326 and the other for SRID of 4326. The result shows the transforms in the INSERT statement above yielded the expected result.
I created a view for convenience because query layers in ArcGIS can't return more than one spatial column - the table I created has two. You may wish to perform transforms in the view as well. I could have also edited the SQL in the Query Layer definition (see below)
Another restriction is the column containing the spatial values can only contain spatial values of the same spatial data type ("shape type"). You can't mix points and polygons for instance. It's OK to create a table with an ST_GEOMETRY column. Just make sure the table doesn't contain different geometries ("shape type") in that column. Alternatively, you could use the method ST_GeometryType() in the WHERE clause to filter for the desired spatial data type. See here for more info. You can read more about query layers and requirements here.
You define a query layer using the following dialog. I could have simply specified one spatial column in the target list instead of creating a view.
And here is what appears in ArcMap once the query layer is defined and executed:
In summary, transforming between different spatial reference systems in SAP HANA is straightforward. Just make sure you understand when and why you need to transform between different spatial reference systems - especially between spheroidal and planar and the impact on spatial calculations like ST_AREA and ST_DISTANCE. Happy transforming!
There are many reasons why you want to transform between spatial reference systems (SRS) in HANA. You may be streaming or loading data into HANA with different SRSs and you want to perform spatial analytics or mash up the data. Or you may simply want the spatial data to be in the same SRS. Performing the transforms in HANA is extremely fast and can process millions of rows. GIS packages typically are not meant to perform transforms in bulk. Another reason is that when publishing a feature service from Esri ArcMap, for instance, all WGS84 layers are stored as SRID 1000004326 in HANA due to previous limitations of the round-earth SRS implementation. Under some conditions tables that store spatial data in HANA with spatial reference system 4326 may not behave as expected in ArcGIS. See this page on the Esri website for more details.
Some spatial calculations like ST_AREA, ST_DISTANCE and others can return different answers depending on the size of the polygon or distance between two points and the spatial reference system used. This is because a spheroidal SRS takes the curvature of the earth into account. The SAP HANA Spatial Reference Guide has a section that describes this here. You may want to transform spatial data stored in SAP HANA as SRID of 1000004326 to a spheroidal SRS like 4326 before performing these and other similar calculations. Conversely, if you are going to consume HANA spatial data in the ArcGIS platform and that data is stored with an SRID of 4326, you may want to transform it to the planar representation (SRID of 1000004326) either in the underlying HANA table or using a view.
Fortunately, it is easy to transform between spatial reference systems in HANA. First, check to see if the spatial reference system(s) used by the data you are loading into HANA are defined. To check for SRID of 3857, run the following query in a SQL Console in HANA Studio:
select * from ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 3857;If the SRID is present in the SAP HANA instance, you should see the following result:
After you've checked that all the expected SRIDs are defined in HANA, you can load your spatial data into HANA and transform between spatial reference systems as needed.
In this example, I create a table to hold the transformed values. I'm converting two floats (representing the longitude (x) and latitude (y)) to points in two different spatial reference systems. I'm using two spatial reference systems that HANA is configured with by default.
CREATE COLUMN TABLE "WORKORDER"."PREMISE_COORDINATES_TMP" (
"PREMISE_NO" VARCHAR( 20) CS_STRING,
"GPS_LATITUDE" DECIMAL(15,8) CS_FIXED,
"GPS_LONGITUDE" DECIMAL(15,8) CS_FIXED,
"PREMISE_GEO_1000004326" ST_POINT(1000004326) CS_POINT,
"PREMISE_GEO_4326" ST_POINT(4326) CS_POINTNote that once a table is created with a spatial column (the SRID defaults to 0 if one isn't specified as shown above), you can't modify the SRID of the column.
Next, I run an INSERT to obtain the values from an existing table:
INSERT INTO "WORKORDER"."PREMISE_COORDINATES_TMP2" ("PREMISE_NO", "GPS_LATITUDE", "GPS_LONGITUDE",
"PREMISE_GEO_1000004326", "PREMISE_GEO_4326"
)
SELECT TO_VARCHAR( PREMISE_NO), GPS_LATITUDE, GPS_LONGITUDE, NEW ST_POINT( GPS_LONGITUDE,
GPS_LATITUDE).ST_SRID( 4326).ST_TRANSFORM( 1000004326), NEW ST_POINT( GPS_LONGITUDE,
GPS_LATITUDE).ST_SRID( 4326).ST_TRANSFORM( 4326)
FROM "WORKORDER"."PREMISE_ADDRESS";Let's take a look at the spatial functions used to perform the transform. ST_SRID( <SRID>) is used to change the SRID of the data but the transform must be done with ST_TRANSFORM(<SRID>). The ST_SRID function is needed to set the SRID because the ST_POINT constructor form used above returns a value with an SRID of zero (for which there is no transform defined).
The PREMISE_NO is converted to a VARCHAR because ArcGIS requires a unique key and float is not an valid datatype for the unique identifier. Defining a query layer in ArcGIS requires that a unique key be specified.
Next, I issue a SELECT to examine the values and verify the transforms worked as expected:
SELECT PREMISE_NO, NEW ST_POINT( GPS_LONGITUDE, GPS_LATITUDE).ST_SRID( 4326).ST_TRANSFORM(
4326).ST_ASEWKT() AS GEO_ORIG, PREMISE_GEO_1000004326.ST_ASEWKT() AS PREMISE_EWKT_1000004326,
PREMISE_GEO_4326.ST_ASEWKT() AS PREMISE_EWKT_4326
FROM "WORKORDER"."PREMISE_COORDINATES_TMP2";The function ST_ASEWKT() returns the spatial data in a human readable form called extended well known text (EWKT). This is the result:
The SELECT does two things. First, it constructs a point using the lat and long which are floats and then transforms that point to SRID of 4326. Second, it shows the two transformed values - one for SRID 1000004326 and the other for SRID of 4326. The result shows the transforms in the INSERT statement above yielded the expected result.
I created a view for convenience because query layers in ArcGIS can't return more than one spatial column - the table I created has two. You may wish to perform transforms in the view as well. I could have also edited the SQL in the Query Layer definition (see below)
CREATE VIEW PREMISE_COORDINATES_ESRI (PREMISE_NO, PREMISE_GEO_4326)
AS SELECT PREMISE_NO, PREMISE_GEO_1000004326
FROM "WORKORDER"."PREMISE_COORDINATES_TMP2";Another restriction is the column containing the spatial values can only contain spatial values of the same spatial data type ("shape type"). You can't mix points and polygons for instance. It's OK to create a table with an ST_GEOMETRY column. Just make sure the table doesn't contain different geometries ("shape type") in that column. Alternatively, you could use the method ST_GeometryType() in the WHERE clause to filter for the desired spatial data type. See here for more info. You can read more about query layers and requirements here.
You define a query layer using the following dialog. I could have simply specified one spatial column in the target list instead of creating a view.
And here is what appears in ArcMap once the query layer is defined and executed:
In summary, transforming between different spatial reference systems in SAP HANA is straightforward. Just make sure you understand when and why you need to transform between different spatial reference systems - especially between spheroidal and planar and the impact on spatial calculations like ST_AREA and ST_DISTANCE. Happy transforming!
- SAP Managed Tags:
2 Comments
