[DISCUSSION] Support JOIN query with spatial index

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[DISCUSSION] Support JOIN query with spatial index

Indhumathi
Hi community,

Currently, carbon supports IN_POLYGON and IN_POLYGON_LIST udf's,
where user has to manually provide the polygon points(series of latitude
and longitude pair), to query carbon table based on spatial index.

This feature will support JOIN tables based on IN_POLYGON udf
filter, where polygon data exists in a table.

Please find below link of design doc. Please check and give
your inputs/suggestions.

https://docs.google.com/document/d/11PnotaAiEJQK_QvKsHznDy1I9tO4idflW32LstwcLhc/edit#heading=h.yh6qp815dh3p


Thanks & Regards,
Indhumathi M



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: [DISCUSSION] Support JOIN query with spatial index

kunalkapoor
+1

On Mon, Mar 22, 2021 at 4:07 PM Indhumathi <[hidden email]> wrote:

> Hi community,
>
> Currently, carbon supports IN_POLYGON and IN_POLYGON_LIST udf's,
> where user has to manually provide the polygon points(series of latitude
> and longitude pair), to query carbon table based on spatial index.
>
> This feature will support JOIN tables based on IN_POLYGON udf
> filter, where polygon data exists in a table.
>
> Please find below link of design doc. Please check and give
> your inputs/suggestions.
>
>
> https://docs.google.com/document/d/11PnotaAiEJQK_QvKsHznDy1I9tO4idflW32LstwcLhc/edit#heading=h.yh6qp815dh3p
>
>
> Thanks & Regards,
> Indhumathi M
>
>
>
> --
> Sent from:
> http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: [DISCUSSION] Support JOIN query with spatial index

Ajantha Bhat
Hi, I have some doubts and suggestions for the same.

Currently, we support these UDFs --> IN_POLYGON, IN_POLYGON_LIST,
IN_POLYLINE_LIST, IN_POLYGON_RANGE_LIST
but the user needs to give polygon input manually and as polygon can have
many points, it is hard to give manually.
So, your requirement is to give new UDF , IN_POLYGON_JOIN where polygon
inputs are present in another table and you want to join with the main
table.

*please find my doubts below*
a. why to do join ? when you can form a subquery to query the polygons from
table2 and give it as input for IN_POLYGON UDF and other existing UDFs
b. no need to support the same for  IN_POLYLINE_LIST
and IN_POLYGON_RANGE_LIST UDF also ?

*Suggestions:*
a. Table names and queries are not matching, please update
b. The query doesn't look like the union query as explained in the diagram,
please update and explain
c. Please consider some sample data with examples for t1 and t2. Also,
provide the expected query result also
d. Also mention, how to select data from single polygon and multi polygon
from the tables.

Thanks,
Ajantha

On Tue, Mar 30, 2021 at 11:25 AM Kunal Kapoor <[hidden email]>
wrote:

> +1
>
> On Mon, Mar 22, 2021 at 4:07 PM Indhumathi <[hidden email]>
> wrote:
>
> > Hi community,
> >
> > Currently, carbon supports IN_POLYGON and IN_POLYGON_LIST udf's,
> > where user has to manually provide the polygon points(series of latitude
> > and longitude pair), to query carbon table based on spatial index.
> >
> > This feature will support JOIN tables based on IN_POLYGON udf
> > filter, where polygon data exists in a table.
> >
> > Please find below link of design doc. Please check and give
> > your inputs/suggestions.
> >
> >
> >
> https://docs.google.com/document/d/11PnotaAiEJQK_QvKsHznDy1I9tO4idflW32LstwcLhc/edit#heading=h.yh6qp815dh3p
> >
> >
> > Thanks & Regards,
> > Indhumathi M
> >
> >
> >
> > --
> > Sent from:
> > http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: [DISCUSSION] Support JOIN query with spatial index

Indhumathi
This post was updated on .
Hello all,

Current design is based on Union of polygons identified from
polygon table.

Here, (Latitude and Longitude) pair is present in spatial table
and polygons define a dimension table.

Based on discussion with customer, need to change the design to
support IN_POLYGON_JOIN in below way.

Apply IN_POLYGON udf on each polygon identified from Polygon table
and apply aggregation/group by each polygon result.

For example:
Select sum(t1.col1),t2.polygonId
from table1 t1
inner join
(select polygon,polygonId from table2 where type='x') t2
on in_polygon_join(t1, t2.polygon)
group by t2.polygonId

table1:
------------+-------------------
Col1       + mygeohash |
------------+------------------
1             |01                 |
2             |02                 |
3             |03                 |
4             |04                 |
5             |05                 |
6             |06                 |
------------+------------------

table2:
-----------------+------------------+--------------
polygon        + type             | polygonId |
-----------------+------------------+--------------
1_Polygon() |x                   |1                 |
2_Polygon() |y                   |2                 |
3_Polygon() |x                   |3                 |
4_Polygon() |r                    |4                 |
------------+---------------------+----------------

If 1_polygon lies in range (0 & 1), 3_polygon in (5 & 6),
result could be like,
-----------------+--------------------
sum(t1.col1  + polygonId      |
-----------------+--------------------
3                  + 1                    |
11                +3                     |
-----------------+-------------------

To achieve this, a solution could be to, run query with IN_POLYGON
udf of each polygon and  finally make a union of query result.

Will update design on further analysis.
Any opinion or suggestions are welcomed.

Thanks,
Indhumathi M



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: [DISCUSSION] Support JOIN query with spatial index

Indhumathi
Hello all,

Please find the design document link attached in JIRA,  CARBONDATA-4166
<https://issues.apache.org/jira/browse/CARBONDATA-4166>  
Any inputs/suggestions from the community is most welcomed.

Regards,
Indhumathi



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/