More examples of how to use the JSON object inside of Hive?

[kylesorlietitlow]

Could you provide some more sample queries inside of Hive that use the JSON object you describe/create on the "Hive JSON SerDe" page? Perhaps showing how it interacts with the UDF's. Thank you!

[randallwhitman]

Have you seen some or all of -

• https://github.com/Esri/gis-tools-for-hadoop/tree/master/samples/point-in-polygon-aggregation-hive
• https://github.com/Esri/gis-tools-for-hadoop/wiki/Getting-a-Feature-Class-into-HDFS
• https://github.com/Esri/gis-tools-for-hadoop/wiki/Getting-the-results-of-a-Hive-query-into-ArcGIS
• https://github.com/Esri/gis-tools-for-hadoop/wiki/Aggregating-CSV-Data-(Spatial-Binning)
• https://github.com/Esri/gis-tools-for-hadoop/wiki/Vehicle-Trip-Discovery-with-GIS-Tools-for-Hadoop

[kylesorlietitlow]

I was able to replicate the sample query from the demo:

SELECT counties.name, count(*) cnt FROM counties JOIN earthquakes WHERE ST_Contains(counties.boundaryshape, ST_Point(earthquakes.longitude, earthquakes.latitude)) GROUP BY counties.name ORDER BY cnt desc;

However, I am having trouble creating any other type of query. For instance, I want to do a query akin to getting all of the earthquakes inside of the polygon (reversing the SELET and JOIN tables from the query above) and it fails.

SELECT * FROM earthquakes JOIN counties WHERE ST_Contains(counties.boundaryshape, ST_Point(earthquakes.longitude, earthquakes.latitude));

It would be helpful to have more examples of spatial queries and joins that use the geometry attribute of the "counties" dataset.

[randallwhitman]

For me both those queries work OK on Spark-SQL-2.4 What error message? Which versions of Spatial-Framework-for-Hadoop, Hive, and Hadoop?