More

How can I limit a spatial binning query to a specific bounding box?


I am using Esri's GIS tools for Hadoop but I think the query is pretty standard stuff. The SQL/HQL below is from the Esri tutorial found here https://github.com/Esri/gis-tools-for-hadoop/wiki/Aggregating-CSV-Data-%28Spatial-Binning%29

So I have a datastore of millions of points but I only want to produce a map of a specific region (say Europe).

The query I currently run generates a Spatial bin of the entire world

FROM (SELECT ST_Bin(0.001, ST_Point(dropoff_longitude,dropoff_latitude)) bin_id, *FROM taxi_demo) bins INSERT OVERWRITE TABLE taxi_agg SELECT ST_BinEnvelope(0.001, bin_id) shape, COUNT(*) count GROUP BY bin_id;

How would I limit this to a specific bounding box? Any Ideas?

EDIT: including solution for reference (UK envelope):

SELECT ST_Bin(0.001, ST_Point(coordinates.coordinates[0],coordinates.coordinates[1])) bin_id, *FROM data_final WHERE ST_Contains(ST_Envelope(ST_Polygon(-11.115,49.511, 2.537,49.511, 2.537,59.41, -11.115,59.41, -11.115,49.511)), ST_Point(coordinates.coordinates[0],coordinates.coordinates[1]))

You can limit the extent of your result by adding a filter to the select fromtaxi_demo.

Change…

SELECT ST_Bin(0.001, ST_Point(dropoff_longitude,dropoff_latitude)) bin_id, *FROM taxi_demo

to something like…

SELECT ST_Bin(0.001, ST_Point(dropoff_longitude,dropoff_latitude)) bin_id, *FROM taxi_demo WHERE ST_Contains(ST_Envelope([xmin],[ymin],[xmax],[ymax]), ST_Point(dropoff_longitude,dropoff_latitude))


Watch the video: Siegreicher Salzburger Tisch aus Bruck (October 2021).