CODEWITHSUNDEEP
hadoophivegishiveql
GihanDB
GihanDB

Reputation: 631

How to join 2 hive tables by point and corresponding polygon?

I have 2 Hive tables. Table 1 have longitudes and latitudes in 2 columns and 2nd table have polygon information in WKT format like bellow:

POLYGON ((6.93614 79.842882, 6.950198 79.856958, 6.943638 79.877815, 6.931795 79.877129, 6.92566 79.861507, 6.919184 79.861507, 6.917906 79.847603, 6.93614 79.842882))

I want to find out which points in the table 1 belong to which polygons.I'm trying to use ST_Geometric library for hadoop (https://github.com/Esri/spatial-framework-for-hadoop/wiki/UDF-Documentation) to do this. Since data in 2 tables i have to join them. I try to use 

ST_Contains(polygon, point)

function to join 2 tables. But I couldn't do it. Do some one know a way to do this?

Upvotes: 0

Views: 714

Answers (1)

Alex Libov
Alex Libov

Reputation: 1491

ST_Contains runs on a single polygon and a single point, not full tables.

You need to do a full cartesian product join (an inner join with no conditions) and then apply the ST_Contains in the where clause:

 select * from polygonTable a,pointsTable b where ST_Contains(a.polygon,b.point);

Note that doing a cartesian product is inefficient and may take a lot of time on big tables.

Upvotes: 2

Related Questions