Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here. Want to know more about what has changed? Check out the Community News blog.

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

SOLVED Go to solution
Highlighted

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

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

Accepted Solutions

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

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;
7 REPLIES 7

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

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.

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

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.

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

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;

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

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

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

Mentor

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

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

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;

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

I can't wait to see this demo!