question Re: I have a binary column in Hive, what is the equivalent ESRI UDF to convert binary or base64 to ESRI ST_GeometryType? in Archives of Support Questions (Read Only)

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

aervits — Sat, 18 Jun 2016 02:16:05 GMT

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.

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

pminovic — Sat, 18 Jun 2016 07:50:42 GMT

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?

aervits — Sat, 18 Jun 2016 08:17:54 GMT

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

aervits — Sat, 18 Jun 2016 09:02:16 GMT

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?

pminovic — Sat, 18 Jun 2016 09:25:00 GMT

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?

aervits — Sat, 18 Jun 2016 09:52:51 GMT

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?

tmccuch — Sun, 19 Jun 2016 01:11:37 GMT

I can't wait to see this demo!

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

aervits — Sun, 19 Jun 2016 21:42:13 GMT

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;