Trying this with the JARs I could find (not sure they are the best choice for this, I needed to use ESRI and do it in 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';
Running the following query:
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;
Where polygon 'POLYGON((170.0 20.0, -170.0 73.0, -50.0 20.0, -50.0 73.0))' is a roughly USA box, given coordinates 40.7484445,-73.9878531 belong to New York. The result is supposed to be empty with WHERE NOT, but it still returns these coordinates. It does not filter as it supposed to.
What I am doing wrong?
Only one version of the geometry API should be loaded. Likewise only one of either spatial-sdk-hadoop or the pair of spatial-sdk-json and spatial-sdk-hive .
WKT polygons are closed with an end vertex that repeats the start vertex.
The polygon needs to be specified by vertices in order around the perimeter, not a zigzag order.
The Geometry API is planar and will not support wrapping around the antimeridian.
Probably -170 rather than +170 latitude is intended.
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
add jar /pathto/esri-geometry-api-1.2.1.jar
/pathto/spatial-sdk-json-1.1.jar
/pathto/spatial-sdk-hive-1.1.jar ;
create temporary function ST_AsBinary as 'com.esri.hadoop.hive.ST_AsBinary';
-- ...
select ST_Contains(ST_Polygon(1, 1, 1, 4, 4, 4, 4, 1), ST_Point(2, 3));
true
select ST_Contains(ST_Polygon('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))'), ST_Point(2, 3));
true
select ST_Contains(ST_POLYGON('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));
true
select not ST_Contains(ST_POLYGON('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));
false