Friday, March 28, 2014

HANA GeoSpatial Support

Beginning with SPS 7, Revision 70, geospatial datatypes are generally available. I spent the day today learning what I could about HANA's geospatial datatype features.

Although geospatial data types are available, there are currently quite a few limitations:
  • You can't declare a spatial datatype in an .hdbtable file. You have to use SQL.
  • You can't use spatial datatypes in attribute, analytic or computed views.
  • You can't pass a spatial datatype as an argument in a stored procedure. (you can pass a string representation, which is almost as good).
I have an existing table with LATITUDE and LONGITUDE columns and want to use them to create a new ST_POINT column. From there, I hope to take advantage of the new geospatial functions. Getting it working took most of the day and learned a few lessons.

Here's the first lesson learned: To use latitudes and longitudes, you must specify the SRID (coordinate system) of 4326 when you create the column:

alter table <TABLENAME> add( LOCATION st_point(4326) );

This SRID represents WGS84, which is a spherical model of the earth. The default SRID of zero gives you a "flat earth" Cartesian coordinate system. For most real-world applications, this is pretty useless.

The second lesson learned: You also have to specify the SRID in the update statement.  

update <TABLENAME> set LOCATION = new st_point( 'POINT(' || LONGITUDE || ' ' || LATITUDE ||')',4326)

I'm not sure why this is necessary, but omitting the SRID results in point geometries that can't be used in distance calculations.

The third lesson I learned was that performance is disappointing. The table in question contains 91,000 weather stations. The query below returns stations within 50 km of a specified lat/long and takes almost 11 seconds to run (on an AWS HANA):

SELECT ID, name, longitude, latitude, location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)'))/1000 as Dist_KM
where location.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)'))/1000 < 50

Almost all the time went into WHERE clause. Hopefully, the HANA developers will work on this.

No comments: