I have a binary column in Hive, what is the equivalent ESRI UDF to convert binary or base64 to ESRI ST_GeometryType?

[#1]

I can't seem to find an applicable UDF to convert base64 string or binary type to an ESRI equivalent geometry type. Can someone recommend a solution? I'm aware of Brandon Wilson's awesome article on HCC.

[#2]

Hi @Artem Ervits, you are always on the cutting edge of new technologies! Regarding your question how about one of ST_*FromWKB functions, like for example ST_GeomFromWKB. There are more details about Well-known binary here.

[#3]

@Predrag Minovic I had the same train of thought though unfortunately I was not able to treat it as well known binary. The only way I was able to process it so far was either by base64 encoding it or using the ESRI JSON serialization, at which point I was still stuck. I may have to ingest the column in different format perhaps.

[#4 · Accepted solution]

solved with

add jar hdfs://namenode:8020/tmp/esri/esri-geometry-api.jar;
add jar hdfs://namenode:8020/tmp/esri/spatial-sdk-hive-1.1.1-SNAPSHOT.jar;
add jar hdfs://namenode:8020/tmp/esri/spatial-sdk-json-1.1.1-SNAPSHOT.jar;
use default;
create temporary function st_asgeojson as 'com.esri.hadoop.hive.ST_AsGeoJson';
select st_asgeojson(shape) from table;

[#5]

Glad to hear it works! By the way, what type is "shape"? According to docs: ST_AsGeoJson(geometry) return GeoJson representation of geometry.

[#6]

It is geometry in SQL Server and in Hive it is binary.

[#7]

a little update, function st_asgeojson only returns a JSON representation of geometry, to really make it useful, you still need to convert it to ST_Geometry type, with that object you can take full advantages of ESRI framework

create temporary function st_geomfromgeojson as 'com.esri.hadoop.hive.ST_GeomFromGeoJson';
select st_geomfromgeojson(st_asgeojson(shape)) from table;

[#8]

I can't wait to see this demo!