我有一张table如下：
id_center|latitude_of_center|longitude_of_center|id_point|latitude_of_point|longitude_of_point这张table有数百万行
我试图得到一个输出，它将显示每个id\u中心，哪个id\u点在半径为5英里的范围内，以及距离有多远，按降序排序。每一行都是完全填充的，所以每个id\u中心都在单独的行中有所有可能的id\u点。以下是我迄今为止尝试的结果，我只得到了空结果：
hive> add jar /home/me/gis-tools-for-hadoop/samples/lib/esri-geometry-api.jar;
Added [/home/me/gis-tools-for-hadoop/samples/lib/esri-geometry-api.jar] to class path
Added resources: [/home/me/gis-tools-for-hadoop/samples/lib/esri-geometry-api.jar]
hive> add jar /home/me/gis-tools-for-hadoop/samples/lib/spatial-sdk-hadoop.jar;
Added [/home/me/gis-tools-for-hadoop/samples/lib/spatial-sdk-hadoop.jar] to class path
Added resources: [/home/me/gis-tools-for-hadoop/samples/lib/spatial-sdk-hadoop.jar]
hive> create temporary function ST_GeodesicLengthWGS84 AS 'com.esri.hadoop.hive.ST_GeodesicLengthWGS84';
OK
Time taken: 0.014 seconds
hive> create temporary function ST_SetSRID AS 'com.esri.hadoop.hive.ST_SetSRID';
OK
Time taken: 0.008 seconds
hive> create temporary function ST_LineString AS 'com.esri.hadoop.hive.ST_LineString';
SELECT * FROM mytable WHERE ST_GeodesicLengthWGS84(ST_SetSRID(ST_LineString(latitude_of_center, longitude_of_center, latitude_of_point, longitude_of_point), 4326)) <= 8046.72
2条答案
按热度按时间2mbi3lxu1#
我曾经
ST_Point内部ST_LineString在做类似的工作时。在文档中选中此选项。就你而言：rmbxnbpk2#
对于st_linestring，首先需要经度，然后是纬度-（x，y）顺序(如gis se所述https://gis.stackexchange.com/questions/178950/hive-gis-st-geodesiclengthwgs84-not-returning-expected-distance)