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

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)?

  • 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. – John Powell Dec 10 '14 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 Dec 10 '14 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. – John Powell Dec 10 '14 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. – John Powell Dec 11 '14 at 12:34
  • Yes, I sometimes go to "urban beers", that also covers geospatial topics and "Barcelona Free Software". What about you? – doublebyte Dec 15 '14 at 8:43

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.)

| improve this answer | |
  • 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 Jan 21 '15 at 12:04
  • We are looking at that that one and will comment on it. – Randall Whitman Jan 21 '15 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 . – Randall Whitman Jan 27 '15 at 18:04
  • Based on your comment I assume you haven't tried with latest release of Hadoop (2.6.)... – doublebyte Jan 28 '15 at 12:51
  • Correct, we do not yet have Hadoop-2.6 with which to test. – Randall Whitman Jan 28 '15 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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