Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Master Mentor

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.

1 ACCEPTED SOLUTION

avatar
Master Mentor

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;

View solution in original post

7 REPLIES 7

avatar
Master Guru

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.

avatar
Master Mentor

@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.

avatar
Master Mentor

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;

avatar
Master Guru

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

avatar
Master Mentor

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

avatar
Master Mentor

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;

avatar

I can't wait to see this demo!