6

I am using the ESRI spatial framework for Hadoop, that extends Hive to use spatial types and operations.

My objective is to translate a set of simple queries on PostGIS into Hadoop, in order to reach horizontal scalability.

I have a grid with a count for each cell.

enter image description here

The objective of my query is to select all cells that have a count higher than a certain threshold, and group(merge) all cells that are together. For instance in this case, I would end up with something like this: 4 polygons.

enter image description here

To do this in PostGIS, I use a combination of ST_Dump and ST_SnapToGrid

CREATE TABLE exploded AS
SELECT
(ST_Dump(st_union)).geom
FROM  (SELECT ST_Union(ST_SnapToGrid(geom,0.0001)) 
 FROM grid where ptcnt > 'threshold) as q;

Unfortunately, none of these functions is available on ESRI's spatial framework.

I can perform the threshold filter, but I have no way of aggregating the nearby geometries based on the proximity (a trick perform by the grid):

create table exploded as select u as geom from (select geom as u from grid_cnt where ptcnt > 11467) as q; 

enter image description here

Does anybody can think of a workaround (perhaps using Union)?

7
  • 1
    Interesting question and thanks for the pointer. I work with pretty large datasets in Postgres, and have been wondering how to parallelize some of my queries. I have played with Hadoop a bit, but not Hive, and didn't know about this ESRI Hadoop stuff at all. As to your question, it is a shame there is no ST_Expand, but you might be able to use Buffer in conjuntion with Union as a proxy? Obviously, this is less precise than Snap_To_Grid, but if there is nothing in the neighbouring cell, nothing will get unioned. Commented Dec 10, 2014 at 13:39
  • Hi John, thanks a lot for the comment. I already though about replacing the ST_SnapToGrid with St_Buffer, but I would still need the ST_Dump to collect the results of the grouping...
    – doublebyte
    Commented Dec 10, 2014 at 14:29
  • 1
    You can't use ST_Union in conjunction with WHERE ST_Intersects, ie, only union those that intersect, to avoid having to dump again. To be honest, I am not sure from this if Hive would even support this kind of query. Maybe I should shut up and leave it to the experts. Commented Dec 10, 2014 at 14:34
  • Do you go to any meetup groups in Barcelona? I am always happy to meet people interested in spatial and big data. Commented Dec 11, 2014 at 12:34
  • Yes, I sometimes go to "urban beers", that also covers geospatial topics and "Barcelona Free Software". What about you?
    – doublebyte
    Commented Dec 15, 2014 at 8:43

2 Answers 2

1

The ST_Bin and ST_BinEnvelope functions (added in 2014) may help in place of ST_SnapToGrid. There is an example in step 4 of this tutorial.

The ST_Aggr_Union function may also be useful. There is an example in the blog post announcing the aggregate functions.

(Disclosure: I am a collaborator on the GIS Tools for Hadoop at Esri.)

7
  • Thanks for your answer: it gave me indeed some good pointers. I think the St_Aggr_Union could be a good solution for my problem, but unfortunately I cannot get it to work. I wrote a post here gis.stackexchange.com/questions/131141/… . Can you help?
    – doublebyte
    Commented Jan 21, 2015 at 12:04
  • We are looking at that that one and will comment on it. Commented Jan 21, 2015 at 16:50
  • As also mentioned under the other question, the ST_Aggr_Union example from the blog post, works for us on Hive 0.14, 0.13, and 0.12 with Hadoop 2.2 . Commented Jan 27, 2015 at 18:04
  • Based on your comment I assume you haven't tried with latest release of Hadoop (2.6.)...
    – doublebyte
    Commented Jan 28, 2015 at 12:51
  • Correct, we do not yet have Hadoop-2.6 with which to test. Commented Jan 28, 2015 at 17:24
0

Input: a grid named ("polygon_grid"), with the names of the "id" and "count" fields shown in the figure below.

enter image description here

Output: merged cells by a certain value.

Execute the query:

WITH 
tbla AS (SELECT id, (ST_Dump(geom)).geom FROM polygon_grid WHERE count<=185 OR count>=963 GROUP BY id)
SELECT (ST_Dump(geom)).geom FROM (SELECT ST_Union(geom) geom FROM tbla) foo;

See the figure below for the result.

enter image description here

I played with the data in EPSG 4326.

Original solutions...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.