esri hive st\ U contains无法正常工作

cdmah0mi  于 2021-05-29  发布在  Hadoop
关注(0)|答案(2)|浏览(504)

用我能找到的jar试试这个(不确定它们是最好的选择,我需要使用esri并在hive中进行):

ADD JAR /home/user/lib/esri-geometry-api-1.2.1.jar;
ADD JAR /home/user/lib/spatial-sdk-hive-1.1.1-SNAPSHOT.jar;
ADD JAR /home/user/lib/esri-geometry-api.jar;
ADD JAR /home/user/lib/spatial-sdk-hadoop.jar;

CREATE TEMPORARY FUNCTION ST_Polygon AS 'com.esri.hadoop.hive.ST_Polygon';
CREATE TEMPORARY FUNCTION ST_Point AS 'com.esri.hadoop.hive.ST_Point';
CREATE TEMPORARY FUNCTION ST_Contains AS 'com.esri.hadoop.hive.ST_Contains';
CREATE TEMPORARY FUNCTION ST_Geometry AS 'com.esri.hadoop.hive.ST_Geometry';

运行以下查询:

SELECT 
    IF(1=1, 40.7484445, 0) AS latitude, 
    IF(1=1,-73.9878531, 0) AS longitude 
FROM any_table 
WHERE 
    NOT ST_Contains(
        ST_POLYGON('POLYGON((170.0 20.0, -170.0 73.0, -50.0 20.0, -50.0 73.0))'), 
        ST_Point(CAST(longitude AS DOUBLE), CAST(latitude AS DOUBLE))) 
LIMIT 1;

其中多边形 'POLYGON((170.0 20.0, -170.0 73.0, -50.0 20.0, -50.0 73.0))' 在给定的坐标下,是一个大致相同的盒子 40.7484445,-73.9878531 属于纽约。结果应该是空的where not,但它仍然返回这些坐标。它并没有像它应该的那样过滤。
我做错了什么?

amrnrhlw

amrnrhlw1#

只应加载一个版本的几何体api。同样地,只有spatialsdkhadoop或spatialsdkjson和spatialsdkhive对中的一个。
wkt多边形使用重复起始顶点的结束顶点闭合。
多边形需要按顶点围绕周长的顺序指定,而不是按之字形顺序指定。
geometry api是平面的,不支持环绕国际日期线。
可能是-170而不是+170纬度。
wget公司https://github.com/esri/spatial-framework-for-hadoop/releases/download/v1.1/spatial-sdk-hive-1.1.jar \
https://github.com/esri/spatial-framework-for-hadoop/releases/download/v1.1/spatial-sdk-json-1.1.jar \
https://github.com/esri/geometry-api-java/releases/download/v1.2.1/esri-geometry-api-1.2.1.jar
Hive-s
添加jar/pathto/esri-geometry-api-1.2.1.jar
/pathto/spatial-sdk-json-1.1.jar
/pathto/spatial-sdk-hive-1.1.jar;
创建临时函数st_asbinary为'com.esri.hadoop.hive.st_asbinary';
-- ...
选择stïcontains(stï多边形(1,1,1,4,4,4,1),stï点(2,3));
是的
选择st_contains(st_polygon('polygon((1,1 4,4 4,4 1,1))')、st_point(2,3));
是的
选择st_contains(st_polygon((-170.0 20.0,-170.0 73.0,-50.0 20.0,-50.0 73.0,-170.0 20.0))),st_point(-73.9878531,40.7484445));
是的
选择not st_contains(st_polygon((-170.0 20.0,-170.0 73.0,-50.0 20.0,-50.0 73.0,-170.0 20.0))),st_point(-73.9878531,40.7484445));

xfb7svmp

xfb7svmp2#

add jar /home/..../esri-geometry-api-1.2.1.jar;
add jar /home/..../spatial-sdk-json-1.2.0.jar;
add jar /home/..../spatial-sdk-hive-1.2.0.jar;
add jar /home/..../spatial-sdk-hadoop.jar;

create temporary function ST_AsBinary as 'com.esri.hadoop.hive.ST_AsBinary';

CREATE TEMPORARY FUNCTION ST_Polygon AS 'com.esri.hadoop.hive.ST_Polygon';

CREATE TEMPORARY FUNCTION ST_Point AS 'com.esri.hadoop.hive.ST_Point';

CREATE TEMPORARY FUNCTION ST_Contains AS 'com.esri.hadoop.hive.ST_Contains';

CREATE TEMPORARY FUNCTION ST_Geometry AS 'com.esri.hadoop.hive.ST_Geometry';

A) load table from geojson data to hive:

CREATE TABLE default.lim_xxx_pais
(
NOM_PLAN string,
 NMO_PLAN  string,
 APROXIMADO string,
 ID1 string,
 BoundaryShape binary
)
ROW FORMAT SERDE 'com.esri.hadoop.hive.serde.GeoJsonSerDe'              
STORED AS INPUTFORMAT 'com.esri.json.hadoop.EnclosedGeoJsonInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

B)

LOAD DATA INPATH '/user/.../lim_xxx_pais.geojson' OVERWRITE INTO TABLE lim_xxx_pais

;

C)

select NOM_PLAN, NMO_PLAN,APROXIMADO,ID1 from default.lim_centrales_pais aa
where ST_Contains(aa.boundaryshape, ST_POINT(-72.08726603,-36.62627804) )
;

相关问题