Re: About the Spatial functionality

[Julian Hyde]

Thanks for the review. I just committed the fix as 
http://git-wip-us.apache.org/repos/asf/calcite/commit/c1749ade 
. 

> On Dec 9, 2017, at 1:51 PM, Vamshi Krishna  wrote:
> 
> Hi Julian,
> 
> I have tested the fix posted for CALCITE-2072 and i was able to use the
> geometry functions.
> 
> Thanks for the fix.
> 
> Thanks,
> Vamshi.
> 
> On Fri, Dec 8, 2017 at 4:38 PM, Julian Hyde  > wrote:
> 
>> I don’t know. I don’t have time to look at the code right now.
>> 
>>> On Dec 8, 2017, at 12:53 PM, Vamshi Krishna 
>> wrote:
>>> 
>>> I think you meant the following:
>>> 
>>> 1. Create a user defined operator table for spatial functions (Say
>>> SpatialOperatorTable).
>>> 2. Honor the newly defined operator table (SpatialOperatorTable) when
>>> fun=spatial at CalciteConnectionConfiImpl.java#L102
>>> 
>>> Regarding Step  1: I don't see a way to convert or load the functions in
>>> GeoFunctions class into SqlFunction format other than manually adding
>> them
>>> similar to OracleSqlOperatorTable. Is my understanding right ?
>>> 
>>> 
>>> 
>>> On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde >>  > jh...@apache.org >> wrote:
>>> 
 You’re basically running into https://issues.apache.org/ 
  <
>> https://issues.apache.org/ >
 jira/browse/CALCITE-2072  <
>> https://issues.apache.org/ >
 jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
 couple of lines around https://github.com/apache/ 
  <
>> https://github.com/apache/ >
 calcite/blob/master/core/src/main/java/org/apache/calcite/config/
 CalciteConnectionConfigImpl.java#L102  <
>> https://github.com/apache/ >
 calcite/blob/master/core/src/main/java/org/apache/calcite/config/
 CalciteConnectionConfigImpl.java#L102> - so can you make that fix and
>> see
 whether it fixes the problem.
 
> On Dec 8, 2017, at 6:52 AM, Vamshi Krishna  
>>> 
 wrote:
> 
> Hi Christian,
> 
> Yes,  I have changed the conformance level to LENIENT.
> I was able test the create table syntax using geometry data type.
> 
> 
> Here's what i have in the code:
> //create root schema
> rootSchema = Frameworks.createRootSchema(true);
> 
> //add geo functions
> ModelHandler.addFunctions(rootSchema, null,
 ImmutableList.of(),
>   GeoFunctions.class.getName(), "*", true);
> 
> 
>  // Initialize default planner
>  FrameworkConfig calciteFrameworkConfig =
> Frameworks.newConfigBuilder()
>  .operatorTable(ChainedSqlOperatorTable.of(
> OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
> 
> .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
>  // Lexical configuration defines how identifiers
> are quoted, whether they are converted to upper or lower
>  // case when they are read, and whether
 identifiers
> are matched case-sensitively.
>  .setParserFactory(SqlParserImpl.FACTORY)
>  .setLex(Lex.ORACLE)
>  .build())
>  // Sets the schema to use by the planner
>  .defaultSchema(rootSchema.add("CATALOG",schema))
>  .traitDefs(traitDefs)
>  // Context provides a way to store data within the
>> planner
> session that can be accessed in planner rules.
>  .context(Contexts.EMPTY_CONTEXT)
>  // Rule sets to use in transformation phases. Each
> transformation phase can use a different set of rules.
>  .ruleSets(RuleSets.ofList())
>  // Custom cost factory to use during optimization
>  .costFactory(null)
>  .typeSystem(RelDataTypeSystem.DEFAULT)
>  .build();
> 
>  this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
> 
>  planner.parse();
> 
>  planner.validate(); <<-- reporting error with no match found.
> 
> Currently i am only looking for syntax and data type validation support
 and
> not the runtime implementation of the geo functions.
> 
> 
> 
> I am not sure if the GeoFunctions extension can be used for this
>> purpose
> similar to functions in OracleSqlOperator.
> 
> Thanks,
> Vamshi.
> 
> 

Re: About the Spatial functionality

[Vamshi Krishna]

Hi Julian,

I have tested the fix posted for CALCITE-2072 and i was able to use the
geometry functions.

Thanks for the fix.

Thanks,
Vamshi.

On Fri, Dec 8, 2017 at 4:38 PM, Julian Hyde  wrote:

> I don’t know. I don’t have time to look at the code right now.
>
> > On Dec 8, 2017, at 12:53 PM, Vamshi Krishna 
> wrote:
> >
> > I think you meant the following:
> >
> > 1. Create a user defined operator table for spatial functions (Say
> > SpatialOperatorTable).
> > 2. Honor the newly defined operator table (SpatialOperatorTable) when
> > fun=spatial at CalciteConnectionConfiImpl.java#L102
> >
> > Regarding Step  1: I don't see a way to convert or load the functions in
> > GeoFunctions class into SqlFunction format other than manually adding
> them
> > similar to OracleSqlOperatorTable. Is my understanding right ?
> >
> >
> >
> > On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde > wrote:
> >
> >> You’re basically running into https://issues.apache.org/ <
> https://issues.apache.org/>
> >> jira/browse/CALCITE-2072  https://issues.apache.org/>
> >> jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
> >> couple of lines around https://github.com/apache/ <
> https://github.com/apache/>
> >> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> >> CalciteConnectionConfigImpl.java#L102  https://github.com/apache/>
> >> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> >> CalciteConnectionConfigImpl.java#L102> - so can you make that fix and
> see
> >> whether it fixes the problem.
> >>
> >>> On Dec 8, 2017, at 6:52 AM, Vamshi Krishna  >
> >> wrote:
> >>>
> >>> Hi Christian,
> >>>
> >>> Yes,  I have changed the conformance level to LENIENT.
> >>> I was able test the create table syntax using geometry data type.
> >>>
> >>>
> >>> Here's what i have in the code:
> >>> //create root schema
> >>> rootSchema = Frameworks.createRootSchema(true);
> >>>
> >>> //add geo functions
> >>>  ModelHandler.addFunctions(rootSchema, null,
> >> ImmutableList.of(),
> >>>GeoFunctions.class.getName(), "*", true);
> >>>
> >>>
> >>>   // Initialize default planner
> >>>   FrameworkConfig calciteFrameworkConfig =
> >>> Frameworks.newConfigBuilder()
> >>>   .operatorTable(ChainedSqlOperatorTable.of(
> >>> OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
> >>>
> >>> .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
> >>>   // Lexical configuration defines how identifiers
> >>> are quoted, whether they are converted to upper or lower
> >>>   // case when they are read, and whether
> >> identifiers
> >>> are matched case-sensitively.
> >>>   .setParserFactory(SqlParserImpl.FACTORY)
> >>>   .setLex(Lex.ORACLE)
> >>>   .build())
> >>>   // Sets the schema to use by the planner
> >>>   .defaultSchema(rootSchema.add("CATALOG",schema))
> >>>   .traitDefs(traitDefs)
> >>>   // Context provides a way to store data within the
> planner
> >>> session that can be accessed in planner rules.
> >>>   .context(Contexts.EMPTY_CONTEXT)
> >>>   // Rule sets to use in transformation phases. Each
> >>> transformation phase can use a different set of rules.
> >>>   .ruleSets(RuleSets.ofList())
> >>>   // Custom cost factory to use during optimization
> >>>   .costFactory(null)
> >>>   .typeSystem(RelDataTypeSystem.DEFAULT)
> >>>   .build();
> >>>
> >>>   this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
> >>>
> >>>   planner.parse();
> >>>
> >>>   planner.validate(); <<-- reporting error with no match found.
> >>>
> >>> Currently i am only looking for syntax and data type validation support
> >> and
> >>> not the runtime implementation of the geo functions.
> >>>
> >>>
> >>>
> >>> I am not sure if the GeoFunctions extension can be used for this
> purpose
> >>> similar to functions in OracleSqlOperator.
> >>>
> >>> Thanks,
> >>> Vamshi.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
> >>> wrote:
> >>>
>  Hi Vamshi,
> 
>  Have you set the conformance to such that supports Geometry? i've been
>  using lenient like this: jdbc:calcite:conformance=LENIENT;
>  ​model=...my model​
>  ​
>  ​
>  ​
> 
>  On 7 December 2017 at 13:53, Vamshi Krishna <
> vamshi.v.kris...@gmail.com
> >>>
>  wrote:
> 
> > Hello Team,
> >
> > I have tried to use these functions by adding it to my schema as
> given
> > below:
> >
> > ModelHandler.addFunctions(rootSchema, null,
> >> ImmutableList.of(),
> > 

Re: About the Spatial functionality

[Julian Hyde]

I don’t know. I don’t have time to look at the code right now.

> On Dec 8, 2017, at 12:53 PM, Vamshi Krishna  
> wrote:
> 
> I think you meant the following:
> 
> 1. Create a user defined operator table for spatial functions (Say
> SpatialOperatorTable).
> 2. Honor the newly defined operator table (SpatialOperatorTable) when
> fun=spatial at CalciteConnectionConfiImpl.java#L102
> 
> Regarding Step  1: I don't see a way to convert or load the functions in
> GeoFunctions class into SqlFunction format other than manually adding them
> similar to OracleSqlOperatorTable. Is my understanding right ?
> 
> 
> 
> On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde  > wrote:
> 
>> You’re basically running into https://issues.apache.org/ 
>> 
>> jira/browse/CALCITE-2072 > 
>> jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
>> couple of lines around https://github.com/apache/ 
>> 
>> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
>> CalciteConnectionConfigImpl.java#L102 > 
>> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
>> CalciteConnectionConfigImpl.java#L102> - so can you make that fix and see
>> whether it fixes the problem.
>> 
>>> On Dec 8, 2017, at 6:52 AM, Vamshi Krishna 
>> wrote:
>>> 
>>> Hi Christian,
>>> 
>>> Yes,  I have changed the conformance level to LENIENT.
>>> I was able test the create table syntax using geometry data type.
>>> 
>>> 
>>> Here's what i have in the code:
>>> //create root schema
>>> rootSchema = Frameworks.createRootSchema(true);
>>> 
>>> //add geo functions
>>>  ModelHandler.addFunctions(rootSchema, null,
>> ImmutableList.of(),
>>>GeoFunctions.class.getName(), "*", true);
>>> 
>>> 
>>>   // Initialize default planner
>>>   FrameworkConfig calciteFrameworkConfig =
>>> Frameworks.newConfigBuilder()
>>>   .operatorTable(ChainedSqlOperatorTable.of(
>>> OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
>>> 
>>> .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
>>>   // Lexical configuration defines how identifiers
>>> are quoted, whether they are converted to upper or lower
>>>   // case when they are read, and whether
>> identifiers
>>> are matched case-sensitively.
>>>   .setParserFactory(SqlParserImpl.FACTORY)
>>>   .setLex(Lex.ORACLE)
>>>   .build())
>>>   // Sets the schema to use by the planner
>>>   .defaultSchema(rootSchema.add("CATALOG",schema))
>>>   .traitDefs(traitDefs)
>>>   // Context provides a way to store data within the planner
>>> session that can be accessed in planner rules.
>>>   .context(Contexts.EMPTY_CONTEXT)
>>>   // Rule sets to use in transformation phases. Each
>>> transformation phase can use a different set of rules.
>>>   .ruleSets(RuleSets.ofList())
>>>   // Custom cost factory to use during optimization
>>>   .costFactory(null)
>>>   .typeSystem(RelDataTypeSystem.DEFAULT)
>>>   .build();
>>> 
>>>   this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
>>> 
>>>   planner.parse();
>>> 
>>>   planner.validate(); <<-- reporting error with no match found.
>>> 
>>> Currently i am only looking for syntax and data type validation support
>> and
>>> not the runtime implementation of the geo functions.
>>> 
>>> 
>>> 
>>> I am not sure if the GeoFunctions extension can be used for this purpose
>>> similar to functions in OracleSqlOperator.
>>> 
>>> Thanks,
>>> Vamshi.
>>> 
>>> 
>>> 
>>> 
>>> 
>>> On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
>>> wrote:
>>> 
 Hi Vamshi,
 
 Have you set the conformance to such that supports Geometry? i've been
 using lenient like this: jdbc:calcite:conformance=LENIENT;
 ​model=...my model​
 ​
 ​
 ​
 
 On 7 December 2017 at 13:53, Vamshi Krishna >> 
 wrote:
 
> Hello Team,
> 
> I have tried to use these functions by adding it to my schema as given
> below:
> 
> ModelHandler.addFunctions(rootSchema, null,
>> ImmutableList.of(),
> GeoFunctions.class.getName(), "*", true);
> 
> but i run into an validation issue when calling the planner's validate
> routine with the below error:
> No match found for function signature ST_MAKEPOINT(,
>> ,
> )
> 
> Do we have to register these functions manually similar to
> OracleSqlOperatorTable or is there another way out for this ?
> 
> 
> Thanks,
> Vamshi.
> 
> 
> 
> 

Re: About the Spatial functionality

[Vamshi Krishna]

I think you meant the following:

1. Create a user defined operator table for spatial functions (Say
SpatialOperatorTable).
2. Honor the newly defined operator table (SpatialOperatorTable) when
fun=spatial at CalciteConnectionConfiImpl.java#L102

Regarding Step  1: I don't see a way to convert or load the functions in
GeoFunctions class into SqlFunction format other than manually adding them
similar to OracleSqlOperatorTable. Is my understanding right ?



On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde  wrote:

> You’re basically running into https://issues.apache.org/
> jira/browse/CALCITE-2072  jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
> couple of lines around https://github.com/apache/
> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> CalciteConnectionConfigImpl.java#L102  calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> CalciteConnectionConfigImpl.java#L102> - so can you make that fix and see
> whether it fixes the problem.
>
> > On Dec 8, 2017, at 6:52 AM, Vamshi Krishna 
> wrote:
> >
> > Hi Christian,
> >
> > Yes,  I have changed the conformance level to LENIENT.
> > I was able test the create table syntax using geometry data type.
> >
> >
> > Here's what i have in the code:
> >  //create root schema
> >  rootSchema = Frameworks.createRootSchema(true);
> >
> >  //add geo functions
> >   ModelHandler.addFunctions(rootSchema, null,
> ImmutableList.of(),
> > GeoFunctions.class.getName(), "*", true);
> >
> >
> >// Initialize default planner
> >FrameworkConfig calciteFrameworkConfig =
> > Frameworks.newConfigBuilder()
> >.operatorTable(ChainedSqlOperatorTable.of(
> > OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
> >
> > .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
> >// Lexical configuration defines how identifiers
> > are quoted, whether they are converted to upper or lower
> >// case when they are read, and whether
> identifiers
> > are matched case-sensitively.
> >.setParserFactory(SqlParserImpl.FACTORY)
> >.setLex(Lex.ORACLE)
> >.build())
> >// Sets the schema to use by the planner
> >.defaultSchema(rootSchema.add("CATALOG",schema))
> >.traitDefs(traitDefs)
> >// Context provides a way to store data within the planner
> > session that can be accessed in planner rules.
> >.context(Contexts.EMPTY_CONTEXT)
> >// Rule sets to use in transformation phases. Each
> > transformation phase can use a different set of rules.
> >.ruleSets(RuleSets.ofList())
> >// Custom cost factory to use during optimization
> >.costFactory(null)
> >.typeSystem(RelDataTypeSystem.DEFAULT)
> >.build();
> >
> >this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
> >
> >planner.parse();
> >
> >planner.validate(); <<-- reporting error with no match found.
> >
> > Currently i am only looking for syntax and data type validation support
> and
> > not the runtime implementation of the geo functions.
> >
> >
> >
> > I am not sure if the GeoFunctions extension can be used for this purpose
> > similar to functions in OracleSqlOperator.
> >
> > Thanks,
> > Vamshi.
> >
> >
> >
> >
> >
> > On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
> > wrote:
> >
> >> Hi Vamshi,
> >>
> >> Have you set the conformance to such that supports Geometry? i've been
> >> using lenient like this: jdbc:calcite:conformance=LENIENT;
> >> ​model=...my model​
> >> ​
> >> ​
> >> ​
> >>
> >> On 7 December 2017 at 13:53, Vamshi Krishna  >
> >> wrote:
> >>
> >>> Hello Team,
> >>>
> >>> I have tried to use these functions by adding it to my schema as given
> >>> below:
> >>>
> >>> ModelHandler.addFunctions(rootSchema, null,
> ImmutableList.of(),
> >>> GeoFunctions.class.getName(), "*", true);
> >>>
> >>> but i run into an validation issue when calling the planner's validate
> >>> routine with the below error:
> >>> No match found for function signature ST_MAKEPOINT(,
> ,
> >>> )
> >>>
> >>> Do we have to register these functions manually similar to
> >>> OracleSqlOperatorTable or is there another way out for this ?
> >>>
> >>>
> >>> Thanks,
> >>> Vamshi.
> >>>
> >>>
> >>>
> >>>
> >>> On Sat, Dec 2, 2017 at 4:33 AM, Christian Tzolov 
> >>> wrote:
> >>>
>  @Michael, sure go ahead and use the query if you find it fit. I am
> >>> looking
>  forward to read this paper! If you need an "external" opinion about
> how
>  Calcite fits in the broader  data management ecosystem or "patterns"
> >> for
>  building 

Re: About the Spatial functionality

[Vamshi Krishna]

Hi Christian,

Yes,  I have changed the conformance level to LENIENT.
I was able test the create table syntax using geometry data type.


Here's what i have in the code:
  //create root schema
  rootSchema = Frameworks.createRootSchema(true);

  //add geo functions
   ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
 GeoFunctions.class.getName(), "*", true);


// Initialize default planner
FrameworkConfig calciteFrameworkConfig =
Frameworks.newConfigBuilder()
.operatorTable(ChainedSqlOperatorTable.of(
OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))

.parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
// Lexical configuration defines how identifiers
are quoted, whether they are converted to upper or lower
// case when they are read, and whether identifiers
are matched case-sensitively.
.setParserFactory(SqlParserImpl.FACTORY)
.setLex(Lex.ORACLE)
.build())
// Sets the schema to use by the planner
.defaultSchema(rootSchema.add("CATALOG",schema))
.traitDefs(traitDefs)
// Context provides a way to store data within the planner
session that can be accessed in planner rules.
.context(Contexts.EMPTY_CONTEXT)
// Rule sets to use in transformation phases. Each
transformation phase can use a different set of rules.
.ruleSets(RuleSets.ofList())
// Custom cost factory to use during optimization
.costFactory(null)
.typeSystem(RelDataTypeSystem.DEFAULT)
.build();

this.planner = new CustomPlannerImpl(calciteFrameworkConfig);

planner.parse();

planner.validate(); <<-- reporting error with no match found.

Currently i am only looking for syntax and data type validation support and
not the runtime implementation of the geo functions.



I am not sure if the GeoFunctions extension can be used for this purpose
similar to functions in OracleSqlOperator.

Thanks,
Vamshi.





On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
wrote:

> Hi Vamshi,
>
> Have you set the conformance to such that supports Geometry? i've been
> using lenient like this: jdbc:calcite:conformance=LENIENT;
> ​model=...my model​
> ​
> ​
> ​
>
> On 7 December 2017 at 13:53, Vamshi Krishna 
> wrote:
>
> > Hello Team,
> >
> > I have tried to use these functions by adding it to my schema as given
> > below:
> >
> > ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
> > GeoFunctions.class.getName(), "*", true);
> >
> > but i run into an validation issue when calling the planner's validate
> > routine with the below error:
> > No match found for function signature ST_MAKEPOINT(, ,
> > )
> >
> > Do we have to register these functions manually similar to
> > OracleSqlOperatorTable or is there another way out for this ?
> >
> >
> > Thanks,
> > Vamshi.
> >
> >
> >
> >
> > On Sat, Dec 2, 2017 at 4:33 AM, Christian Tzolov 
> > wrote:
> >
> > > @Michael, sure go ahead and use the query if you find it fit. I am
> > looking
> > > forward to read this paper! If you need an "external" opinion about how
> > > Calcite fits in the broader  data management ecosystem or "patterns"
> for
> > > building Calcite adapters i can share few ideas ;)
> > >
> > > @Julian, i'm aware and agree with the approach for providing spatial
> > > support. Currently I'm only trying to make it work (somehow) in the
> > context
> > > of the Geode adapter! My goal is to mention it during my talk at Apache
> > > Geode Summit [1] on Monday.
> > >
> > > While on the topic of my talk [1], i've been looking for tempting
> reasons
> > > to engage/involve the Geode community with the project or at least
> start
> > > the argument. Here are my slides [2] (for internal use only until
> > Monday).
> > > In the pros and cons section i've shared some ideas:
> > >
> > > 1. In the context of data exploration and cleansing, an obvious
> advantage
> > > is the easiness for integration with 3rd party DMS tools and
> > >
> > > 2. In the same context the easy to correlate Geode data with data from
> > > multiple Sql and NoSql data stores (e.g. Data Federation).
> > >
> > > 3. But given that Geode is primarily used for OLTP-ish (e.g.
> > > transactional)  workloads, even the OQL is considered a second-class
> > > citizen. Therefore i find the concept of "SQL Stream" quite relevant
> and
> > i
> > > expect that the Geode community will find it interesting too. Geode
> > already
> > > provides limited CQ (Continues Querying) functionality and IMO the "Sql
> > > Stream" will be like advanced CQ++. Unfortunately i haven't had time to
> > > build and prototype in the context of Geode. Can you point me to some
> > code
> > > 

Re: About the Spatial functionality

[Christian Tzolov]

Hi Vamshi,

Have you set the conformance to such that supports Geometry? i've been
using lenient like this: jdbc:calcite:conformance=LENIENT;
​model=...my model​
​
​
​

On 7 December 2017 at 13:53, Vamshi Krishna 
wrote:

> Hello Team,
>
> I have tried to use these functions by adding it to my schema as given
> below:
>
> ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
> GeoFunctions.class.getName(), "*", true);
>
> but i run into an validation issue when calling the planner's validate
> routine with the below error:
> No match found for function signature ST_MAKEPOINT(, ,
> )
>
> Do we have to register these functions manually similar to
> OracleSqlOperatorTable or is there another way out for this ?
>
>
> Thanks,
> Vamshi.
>
>
>
>
> On Sat, Dec 2, 2017 at 4:33 AM, Christian Tzolov 
> wrote:
>
> > @Michael, sure go ahead and use the query if you find it fit. I am
> looking
> > forward to read this paper! If you need an "external" opinion about how
> > Calcite fits in the broader  data management ecosystem or "patterns" for
> > building Calcite adapters i can share few ideas ;)
> >
> > @Julian, i'm aware and agree with the approach for providing spatial
> > support. Currently I'm only trying to make it work (somehow) in the
> context
> > of the Geode adapter! My goal is to mention it during my talk at Apache
> > Geode Summit [1] on Monday.
> >
> > While on the topic of my talk [1], i've been looking for tempting reasons
> > to engage/involve the Geode community with the project or at least start
> > the argument. Here are my slides [2] (for internal use only until
> Monday).
> > In the pros and cons section i've shared some ideas:
> >
> > 1. In the context of data exploration and cleansing, an obvious advantage
> > is the easiness for integration with 3rd party DMS tools and
> >
> > 2. In the same context the easy to correlate Geode data with data from
> > multiple Sql and NoSql data stores (e.g. Data Federation).
> >
> > 3. But given that Geode is primarily used for OLTP-ish (e.g.
> > transactional)  workloads, even the OQL is considered a second-class
> > citizen. Therefore i find the concept of "SQL Stream" quite relevant and
> i
> > expect that the Geode community will find it interesting too. Geode
> already
> > provides limited CQ (Continues Querying) functionality and IMO the "Sql
> > Stream" will be like advanced CQ++. Unfortunately i haven't had time to
> > build and prototype in the context of Geode. Can you point me to some
> code
> > examples? I know about the Tests but are there and actual adapters or
> other
> > applications that use the "SQL Stream"?
> >
> > 4. @Julian you have mentioned the idea about dynamic materializations.
> But
> > i'm not sure i completely understand the approach and that confident to
> > bring it for discussion. If you have some written references that can
> help
> > me i will appreciate it.
> >
> > Cheers,
> > Christian
> >
> >
> > [1] Enable SQL/JDBC Access to Apache Geode/GemFire Using Apache Calcite:
> > https://springoneplatform.io/sessions/enable-sql-jdbc-
> > access-to-apache-geode-gemfire-using-apache-calcite
> > [2] Slides:
> > https://docs.google.com/presentation/d/1zo473pcupWEjRXOA_
> > W5rgaKSmS2Vmyl2U2ATKmrS26M/edit?usp=sharing
> >
> >
> > On 1 December 2017 at 21:05, Julian Hyde  wrote:
> >
> > > The Natural earth dataset (which https://github.com/zzolo/geo_
> > > simple_countries_wkt_csv  > > simple_countries_wkt_csv> is based upon) is Public Domain, which makes
> it
> > > suitable for our purposes.
> > >
> > > > Although inefficient (all spatial computations happen on calcite
> side)
> > > it is still very cool! :)
> > >
> > > That’s exactly what I was going for. First make it work (by adding all
> > > OpenGIS functions as UDFs), then make it fast (by adding rewrite rules
> > that
> > > recognize functions and particular patterns of materialized views).
> > >
> > > Your query is a spatial join of polygons (cities) to polygons
> > (countries).
> > > I have in mind a materialized view where polygons are sliced into
> > bounding
> > > “tiles” and I think it should speed up this kind of query.
> > >
> > > Julian
> > >
> > >
> > > > On Dec 1, 2017, at 9:09 AM, Christian Tzolov 
> > wrote:
> > > >
> > > > The OpenGIS Spec datasets sounds like right, "canonical" spatial
> > dataset
> > > to have.
> > > >
> > > > In the meantime for the purposes of my tests i found a dataset (
> > > https://github.com/zzolo/geo_simple_countries_wkt_csv <
> > > https://github.com/zzolo/geo_simple_countries_wkt_csv>) that contains
> > > world country boundaries as WKT polygons along with their names, ISO
> > > abbreviations  and other metadata. I've also converted the csv into
> json
> > > (attached) to make it easy for loading in Geode.
> > > >
> > > > This allows me to run crazy queries like this :)
> > > >
> > > > SELECT "NAME", 

Re: About the Spatial functionality

[Christian Tzolov]

@Michael, sure go ahead and use the query if you find it fit. I am looking
forward to read this paper! If you need an "external" opinion about how
Calcite fits in the broader  data management ecosystem or "patterns" for
building Calcite adapters i can share few ideas ;)

@Julian, i'm aware and agree with the approach for providing spatial
support. Currently I'm only trying to make it work (somehow) in the context
of the Geode adapter! My goal is to mention it during my talk at Apache
Geode Summit [1] on Monday.

While on the topic of my talk [1], i've been looking for tempting reasons
to engage/involve the Geode community with the project or at least start
the argument. Here are my slides [2] (for internal use only until Monday).
In the pros and cons section i've shared some ideas:

1. In the context of data exploration and cleansing, an obvious advantage
is the easiness for integration with 3rd party DMS tools and

2. In the same context the easy to correlate Geode data with data from
multiple Sql and NoSql data stores (e.g. Data Federation).

3. But given that Geode is primarily used for OLTP-ish (e.g.
transactional)  workloads, even the OQL is considered a second-class
citizen. Therefore i find the concept of "SQL Stream" quite relevant and i
expect that the Geode community will find it interesting too. Geode already
provides limited CQ (Continues Querying) functionality and IMO the "Sql
Stream" will be like advanced CQ++. Unfortunately i haven't had time to
build and prototype in the context of Geode. Can you point me to some code
examples? I know about the Tests but are there and actual adapters or other
applications that use the "SQL Stream"?

4. @Julian you have mentioned the idea about dynamic materializations.  But
i'm not sure i completely understand the approach and that confident to
bring it for discussion. If you have some written references that can help
me i will appreciate it.

Cheers,
Christian


[1] Enable SQL/JDBC Access to Apache Geode/GemFire Using Apache Calcite:
https://springoneplatform.io/sessions/enable-sql-jdbc-access-to-apache-geode-gemfire-using-apache-calcite
[2] Slides:
https://docs.google.com/presentation/d/1zo473pcupWEjRXOA_W5rgaKSmS2Vmyl2U2ATKmrS26M/edit?usp=sharing


On 1 December 2017 at 21:05, Julian Hyde  wrote:

> The Natural earth dataset (which https://github.com/zzolo/geo_
> simple_countries_wkt_csv  simple_countries_wkt_csv> is based upon) is Public Domain, which makes it
> suitable for our purposes.
>
> > Although inefficient (all spatial computations happen on calcite side)
> it is still very cool! :)
>
> That’s exactly what I was going for. First make it work (by adding all
> OpenGIS functions as UDFs), then make it fast (by adding rewrite rules that
> recognize functions and particular patterns of materialized views).
>
> Your query is a spatial join of polygons (cities) to polygons (countries).
> I have in mind a materialized view where polygons are sliced into bounding
> “tiles” and I think it should speed up this kind of query.
>
> Julian
>
>
> > On Dec 1, 2017, at 9:09 AM, Christian Tzolov  wrote:
> >
> > The OpenGIS Spec datasets sounds like right, "canonical" spatial dataset
> to have.
> >
> > In the meantime for the purposes of my tests i found a dataset (
> https://github.com/zzolo/geo_simple_countries_wkt_csv <
> https://github.com/zzolo/geo_simple_countries_wkt_csv>) that contains
> world country boundaries as WKT polygons along with their names, ISO
> abbreviations  and other metadata. I've also converted the csv into json
> (attached) to make it easy for loading in Geode.
> >
> > This allows me to run crazy queries like this :)
> >
> > SELECT "NAME", ST_Distance("Country", ST_GeomFromText('POINT(23.288269
> 42.731883)')) as "distanceToBG"
> > FROM (
> >   SELECT
> >"NAME",
> > ST_GeomFromText('POLYGON((4.822998 52.427652, 4.971313 52.427652,
> 4.971313 52.333661, 4.822998 52.333661, 4.822998 52.427652))') AS
> "Amsterdam",
> > ST_GeomFromText("WKT") AS "Country"
> >   FROM "geode"."Country"
> > )
> > WHERE ST_Contains("Country", "Amsterdam");
> >
> > E.g. retrieves the countries that contain the Amsterdam, NL area and for
> the result computes the distances to Sofia, BG. The result is actually
> correct :)
> >
> > | Netherlands | 18.93796871505074 |
> >
> > Although inefficient (all spatial computations happen on calcite side)
> it is still very cool! :)
> >
> > Btw the dataset license seems permissive and if you are interested i can
> add the json version to the test-calcite project. If not mistaken some of
> the other adapters load data from json datasets too?
> >
> > Cheers,
> > Christian
> >
> >
> >
> > On 30 November 2017 at 19:39, Julian Hyde > wrote:
> > Yes, a small heterogeneous data set. The OpenGIS spec has that — small
> enough, in fact, create the tables and populate them in a .iq script.
> >
> > If/when we do spatial 

Re: About the Spatial functionality

[Julian Hyde]

The Natural earth dataset (which 
https://github.com/zzolo/geo_simple_countries_wkt_csv 
 is based upon) is 
Public Domain, which makes it suitable for our purposes.

> Although inefficient (all spatial computations happen on calcite side) it is 
> still very cool! :)

That’s exactly what I was going for. First make it work (by adding all OpenGIS 
functions as UDFs), then make it fast (by adding rewrite rules that recognize 
functions and particular patterns of materialized views).

Your query is a spatial join of polygons (cities) to polygons (countries). I 
have in mind a materialized view where polygons are sliced into bounding 
“tiles” and I think it should speed up this kind of query.

Julian


> On Dec 1, 2017, at 9:09 AM, Christian Tzolov  wrote:
> 
> The OpenGIS Spec datasets sounds like right, "canonical" spatial dataset to 
> have.
> 
> In the meantime for the purposes of my tests i found a dataset 
> (https://github.com/zzolo/geo_simple_countries_wkt_csv 
> ) that contains world 
> country boundaries as WKT polygons along with their names, ISO abbreviations  
> and other metadata. I've also converted the csv into json (attached) to make 
> it easy for loading in Geode. 
> 
> This allows me to run crazy queries like this :)
> 
> SELECT "NAME", ST_Distance("Country", ST_GeomFromText('POINT(23.288269 
> 42.731883)')) as "distanceToBG"
> FROM (
>   SELECT
>"NAME",
> ST_GeomFromText('POLYGON((4.822998 52.427652, 4.971313 52.427652, 
> 4.971313 52.333661, 4.822998 52.333661, 4.822998 52.427652))') AS "Amsterdam",
> ST_GeomFromText("WKT") AS "Country"
>   FROM "geode"."Country"
> )
> WHERE ST_Contains("Country", "Amsterdam");
> 
> E.g. retrieves the countries that contain the Amsterdam, NL area and for the 
> result computes the distances to Sofia, BG. The result is actually correct :)
> 
> | Netherlands | 18.93796871505074 |
> 
> Although inefficient (all spatial computations happen on calcite side) it is 
> still very cool! :)
> 
> Btw the dataset license seems permissive and if you are interested i can add 
> the json version to the test-calcite project. If not mistaken some of the 
> other adapters load data from json datasets too?
> 
> Cheers,
> Christian
> 
> 
> 
> On 30 November 2017 at 19:39, Julian Hyde  > wrote:
> Yes, a small heterogeneous data set. The OpenGIS spec has that — small 
> enough, in fact, create the tables and populate them in a .iq script.
> 
> If/when we do spatial joins (points to polygons or polygons to polygons) a 
> larger data set would be useful, e.g. the 50 US states and their polygon 
> boundaries (about 5 MB compressed), major US cities, and US national parks. 
> In the past I have packaged up such data sets as hsqldb DBs embedded in JARs 
> - so people can get them from maven central.
> 
> This is pretty fun: 
> https://github.com/johan/world.geo.json/tree/master/countries/USA 
>   > 
> (especially as Github can render GeoJSON as a map in your browser).
> 
> > On Nov 30, 2017, at 9:47 AM, Michael Mior  > > wrote:
> >
> > Sounds like a good idea. I logged
> > https://issues.apache.org/jira/browse/CALCITE-2072 
> >  
> >  > >. I'd be up for 
> > tackling
> > this myself. I'm just not sure how the ScalarFunctions in GeoFunctions can
> > be converted to SqlFunctions for use in the operator table.
> >
> > As for test data, I assume for testing the best would be relatively small
> > datasets (although we can subset ourselves if necessary) that contain a
> > diverse set of data types.
> >
> > --
> > Michael Mior
> > mm...@apache.org   > >
> >
> > 2017-11-28 20:36 GMT-05:00 Julian Hyde  >   > >>:
> >
> >> There are no test data sets, I’m afraid. I would love to add a data set
> >> that includes various kinds of geometries (points, lines, polygons). One
> >> candidate is the one in the OpenGIS Simple Feature Access spec[1] section
> >> C.3.1.2 onwards.
> >>
> >> There ought to be (but isn’t, right now) an easier way to import the list
> >> of GIS functions than calling ModelHandler.addFunctions. You can currently
> >> add ‘fun=oracle’ to the JDBC URL to load the operators in
> >> OracleSqlOperatorTable; we ought to allow ‘fun=spatial’ or
> >> ‘fun=oracle,spatial’.
> >>
> >> Julian
> >>
> >> [1] 

Re: About the Spatial functionality

[Julian Hyde]

Yes, a small heterogeneous data set. The OpenGIS spec has that — small enough, 
in fact, create the tables and populate them in a .iq script.

If/when we do spatial joins (points to polygons or polygons to polygons) a 
larger data set would be useful, e.g. the 50 US states and their polygon 
boundaries (about 5 MB compressed), major US cities, and US national parks. In 
the past I have packaged up such data sets as hsqldb DBs embedded in JARs - so 
people can get them from maven central.

This is pretty fun: 
https://github.com/johan/world.geo.json/tree/master/countries/USA 
 (especially 
as Github can render GeoJSON as a map in your browser).

> On Nov 30, 2017, at 9:47 AM, Michael Mior  wrote:
> 
> Sounds like a good idea. I logged
> https://issues.apache.org/jira/browse/CALCITE-2072 
> . I'd be up for tackling
> this myself. I'm just not sure how the ScalarFunctions in GeoFunctions can
> be converted to SqlFunctions for use in the operator table.
> 
> As for test data, I assume for testing the best would be relatively small
> datasets (although we can subset ourselves if necessary) that contain a
> diverse set of data types.
> 
> --
> Michael Mior
> mm...@apache.org 
> 
> 2017-11-28 20:36 GMT-05:00 Julian Hyde  >:
> 
>> There are no test data sets, I’m afraid. I would love to add a data set
>> that includes various kinds of geometries (points, lines, polygons). One
>> candidate is the one in the OpenGIS Simple Feature Access spec[1] section
>> C.3.1.2 onwards.
>> 
>> There ought to be (but isn’t, right now) an easier way to import the list
>> of GIS functions than calling ModelHandler.addFunctions. You can currently
>> add ‘fun=oracle’ to the JDBC URL to load the operators in
>> OracleSqlOperatorTable; we ought to allow ‘fun=spatial’ or
>> ‘fun=oracle,spatial’.
>> 
>> Julian
>> 
>> [1] http://portal.opengeospatial.org/files/?artifact_id=25354 <
>> http://portal.opengeospatial.org/files/?artifact_id=25354 
>> >
>> 
>>> On Nov 28, 2017, at 1:11 PM, Michael Mior >> > wrote:
>>> 
>>> Yes, you should not use quotes if upcase is true since all functions are
>>> registered with uppercase names and all unquoted literals are also
>>> automatically upcased. Glad this helped!
>>> 
>>> --
>>> Michael Mior
>>> mm...@apache.org 
>>> 
>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov >> >:
>>> 
 ​Ok, ​
 I think i
 ​ solved the riddle​
 .
 ​H
 ad to remove
 ​the ​
 quotes from
 ​the ​
 function name (e.g. use ST_Point instead of "ST_Point"). This
 ​ is due to the ​
 upCase=TURE parameter
 ​in
 ​
 addFunctions
 ​ ​
 .
 
 I don't see the error anymore. Now i'm facing another issue i believe is
 related with my adapter implementation.
 
 Thanks for the support!
 
 On 28 November 2017 at 18:43, Christian Tzolov >
>> wrote:
 
> Unfortunately it didn't help still get " No match found for function
> signature ST_Point(, )"
> ​.
> 
> ​Could it be that i need to ad some schema or other prefix? e.g.
> "geode"."ST_Point"(
> 
> Also can i check interactively what are the registered functions? ​
> 
> On 28 November 2017 at 18:33, Michael Mior  wrote:
> 
>> I believe that should work. I'll let others correct me if I'm missing
 the
>> boat here.
>> 
>> --
>> Michael Mior
>> mm...@apache.org
>> 
>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov :
>> 
>>> Thanks @Michael!  Can i assume that
>>> ​ ​
>>> in
>>> ​ ​
>>> the SchemaFactory
>>> ​#​
>>> create(SchemaPlus parentSchema, String name,
>>> ​ ...​
>>> )
>>> ​ method ​the root schema is constructed?  And can i use the
>>> parentSchema
>>> ​ like this:
>>> 
>>> ModelHandler.addFunctions(parentSchema, null,
>> ImmutableList.of(),
>>> ​ ​
>>> GeoFunctions.class.getName(), "*", true);
>>> 
>>> On 28 November 2017 at 16:58, Michael Mior 
 wrote:
>>> 
 I believe the geospatial functions are not currently registered by
>>> default.
 You can see an example of how to do this in CalciteAssert.java. Once
>> you
 have constructed the root schema, the following should be
 sufficient:
 
 ModelHandler.addFunctions(rootSchema, null,
>> ImmutableList.of(),
 GeoFunctions.class.getName(), "*", true);
 
 --
 Michael Mior
 mm...@apache.org
 

Re: About the Spatial functionality

[Michael Mior]

Sounds like a good idea. I logged
https://issues.apache.org/jira/browse/CALCITE-2072. I'd be up for tackling
this myself. I'm just not sure how the ScalarFunctions in GeoFunctions can
be converted to SqlFunctions for use in the operator table.

As for test data, I assume for testing the best would be relatively small
datasets (although we can subset ourselves if necessary) that contain a
diverse set of data types.

--
Michael Mior
mm...@apache.org

2017-11-28 20:36 GMT-05:00 Julian Hyde :

> There are no test data sets, I’m afraid. I would love to add a data set
> that includes various kinds of geometries (points, lines, polygons). One
> candidate is the one in the OpenGIS Simple Feature Access spec[1] section
> C.3.1.2 onwards.
>
> There ought to be (but isn’t, right now) an easier way to import the list
> of GIS functions than calling ModelHandler.addFunctions. You can currently
> add ‘fun=oracle’ to the JDBC URL to load the operators in
> OracleSqlOperatorTable; we ought to allow ‘fun=spatial’ or
> ‘fun=oracle,spatial’.
>
> Julian
>
> [1] http://portal.opengeospatial.org/files/?artifact_id=25354 <
> http://portal.opengeospatial.org/files/?artifact_id=25354>
>
> > On Nov 28, 2017, at 1:11 PM, Michael Mior  wrote:
> >
> > Yes, you should not use quotes if upcase is true since all functions are
> > registered with uppercase names and all unquoted literals are also
> > automatically upcased. Glad this helped!
> >
> > --
> > Michael Mior
> > mm...@apache.org
> >
> > 2017-11-28 14:18 GMT-05:00 Christian Tzolov :
> >
> >> ​Ok, ​
> >> I think i
> >> ​ solved the riddle​
> >> .
> >> ​H
> >> ad to remove
> >> ​the ​
> >> quotes from
> >> ​the ​
> >> function name (e.g. use ST_Point instead of "ST_Point"). This
> >> ​ is due to the ​
> >> upCase=TURE parameter
> >> ​in
> >> ​
> >> addFunctions
> >> ​ ​
> >> .
> >>
> >> I don't see the error anymore. Now i'm facing another issue i believe is
> >> related with my adapter implementation.
> >>
> >> Thanks for the support!
> >>
> >> On 28 November 2017 at 18:43, Christian Tzolov 
> wrote:
> >>
> >>> Unfortunately it didn't help still get " No match found for function
> >>> signature ST_Point(, )"
> >>> ​.
> >>>
> >>> ​Could it be that i need to ad some schema or other prefix? e.g.
> >>> "geode"."ST_Point"(
> >>>
> >>> Also can i check interactively what are the registered functions? ​
> >>>
> >>> On 28 November 2017 at 18:33, Michael Mior  wrote:
> >>>
>  I believe that should work. I'll let others correct me if I'm missing
> >> the
>  boat here.
> 
>  --
>  Michael Mior
>  mm...@apache.org
> 
>  2017-11-28 12:31 GMT-05:00 Christian Tzolov :
> 
> > Thanks @Michael!  Can i assume that
> > ​ ​
> > in
> > ​ ​
> > the SchemaFactory
> > ​#​
> > create(SchemaPlus parentSchema, String name,
> > ​ ...​
> > )
> > ​ method ​the root schema is constructed?  And can i use the
> > parentSchema
> > ​ like this:
> >
> > ModelHandler.addFunctions(parentSchema, null,
>  ImmutableList.of(),
> > ​ ​
> > GeoFunctions.class.getName(), "*", true);
> >
> > On 28 November 2017 at 16:58, Michael Mior 
> >> wrote:
> >
> >> I believe the geospatial functions are not currently registered by
> > default.
> >> You can see an example of how to do this in CalciteAssert.java. Once
>  you
> >> have constructed the root schema, the following should be
> >> sufficient:
> >>
> >> ModelHandler.addFunctions(rootSchema, null,
>  ImmutableList.of(),
> >> GeoFunctions.class.getName(), "*", true);
> >>
> >> --
> >> Michael Mior
> >> mm...@apache.org
> >>
> >> 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
> >>
> >>> I've tried to cast the Zip's loc column into double like this:
> >>>
> >>> SELECT
> >>> ​ ​
> >>> "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
>  DOUBLE)
> >> AS
> >>> "lat"
> >>> ​ ​
> >>> FROM "geode"."Zips"
> >>> ​ ​
> >>> LIMIT  10;
> >>>
> >>> ​This seems to work fine. ​But when i try to use the ST_Point
>  function
> > i
> >>> get: "No match found for function signature ST_Point(,
> >> )"
> >>> (full stack is below)
> >>>
> >>> It seems like i've not registered a jar dependency or haven't
>  enabled
> >>> something else?
> >>>
> >>>
> >>> jdbc:calcite:conformance=LENIENT> SELECT "city",
>  "ST_Point"(cast("loc"
> >> [0]
> >>> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
> >> 10;
> >>> 2017-11-28 10:19:15,199 [main] ERROR -
> >>> org.apache.calcite.sql.validate.SqlValidatorException: No match
>  found
> >> for
> >>> function signature ST_Point(, )
> >>> 2017-11-28 10:19:15,199 [main] ERROR 

Re: About the Spatial functionality

[Edmon Begoli]

What data sets (types) would we want and need for testing?

My colleagues run a very large GIS research center*, and I am sure I can
get hold of all kinds of test data.

* - https://www.ornl.gov/division/csed/gist

On Tue, Nov 28, 2017 at 8:36 PM, Julian Hyde  wrote:

> There are no test data sets, I’m afraid. I would love to add a data set
> that includes various kinds of geometries (points, lines, polygons). One
> candidate is the one in the OpenGIS Simple Feature Access spec[1] section
> C.3.1.2 onwards.
>
> There ought to be (but isn’t, right now) an easier way to import the list
> of GIS functions than calling ModelHandler.addFunctions. You can currently
> add ‘fun=oracle’ to the JDBC URL to load the operators in
> OracleSqlOperatorTable; we ought to allow ‘fun=spatial’ or
> ‘fun=oracle,spatial’.
>
> Julian
>
> [1] http://portal.opengeospatial.org/files/?artifact_id=25354 <
> http://portal.opengeospatial.org/files/?artifact_id=25354>
>
> > On Nov 28, 2017, at 1:11 PM, Michael Mior  wrote:
> >
> > Yes, you should not use quotes if upcase is true since all functions are
> > registered with uppercase names and all unquoted literals are also
> > automatically upcased. Glad this helped!
> >
> > --
> > Michael Mior
> > mm...@apache.org
> >
> > 2017-11-28 14:18 GMT-05:00 Christian Tzolov :
> >
> >> ​Ok, ​
> >> I think i
> >> ​ solved the riddle​
> >> .
> >> ​H
> >> ad to remove
> >> ​the ​
> >> quotes from
> >> ​the ​
> >> function name (e.g. use ST_Point instead of "ST_Point"). This
> >> ​ is due to the ​
> >> upCase=TURE parameter
> >> ​in
> >> ​
> >> addFunctions
> >> ​ ​
> >> .
> >>
> >> I don't see the error anymore. Now i'm facing another issue i believe is
> >> related with my adapter implementation.
> >>
> >> Thanks for the support!
> >>
> >> On 28 November 2017 at 18:43, Christian Tzolov 
> wrote:
> >>
> >>> Unfortunately it didn't help still get " No match found for function
> >>> signature ST_Point(, )"
> >>> ​.
> >>>
> >>> ​Could it be that i need to ad some schema or other prefix? e.g.
> >>> "geode"."ST_Point"(
> >>>
> >>> Also can i check interactively what are the registered functions? ​
> >>>
> >>> On 28 November 2017 at 18:33, Michael Mior  wrote:
> >>>
>  I believe that should work. I'll let others correct me if I'm missing
> >> the
>  boat here.
> 
>  --
>  Michael Mior
>  mm...@apache.org
> 
>  2017-11-28 12:31 GMT-05:00 Christian Tzolov :
> 
> > Thanks @Michael!  Can i assume that
> > ​ ​
> > in
> > ​ ​
> > the SchemaFactory
> > ​#​
> > create(SchemaPlus parentSchema, String name,
> > ​ ...​
> > )
> > ​ method ​the root schema is constructed?  And can i use the
> > parentSchema
> > ​ like this:
> >
> > ModelHandler.addFunctions(parentSchema, null,
>  ImmutableList.of(),
> > ​ ​
> > GeoFunctions.class.getName(), "*", true);
> >
> > On 28 November 2017 at 16:58, Michael Mior 
> >> wrote:
> >
> >> I believe the geospatial functions are not currently registered by
> > default.
> >> You can see an example of how to do this in CalciteAssert.java. Once
>  you
> >> have constructed the root schema, the following should be
> >> sufficient:
> >>
> >> ModelHandler.addFunctions(rootSchema, null,
>  ImmutableList.of(),
> >> GeoFunctions.class.getName(), "*", true);
> >>
> >> --
> >> Michael Mior
> >> mm...@apache.org
> >>
> >> 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
> >>
> >>> I've tried to cast the Zip's loc column into double like this:
> >>>
> >>> SELECT
> >>> ​ ​
> >>> "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
>  DOUBLE)
> >> AS
> >>> "lat"
> >>> ​ ​
> >>> FROM "geode"."Zips"
> >>> ​ ​
> >>> LIMIT  10;
> >>>
> >>> ​This seems to work fine. ​But when i try to use the ST_Point
>  function
> > i
> >>> get: "No match found for function signature ST_Point(,
> >> )"
> >>> (full stack is below)
> >>>
> >>> It seems like i've not registered a jar dependency or haven't
>  enabled
> >>> something else?
> >>>
> >>>
> >>> jdbc:calcite:conformance=LENIENT> SELECT "city",
>  "ST_Point"(cast("loc"
> >> [0]
> >>> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
> >> 10;
> >>> 2017-11-28 10:19:15,199 [main] ERROR -
> >>> org.apache.calcite.sql.validate.SqlValidatorException: No match
>  found
> >> for
> >>> function signature ST_Point(, )
> >>> 2017-11-28 10:19:15,199 [main] ERROR -
> >>> org.apache.calcite.runtime.CalciteContextException: From line 1,
> > column
> >> 16
> >>> to line 1, column 79: No match found for function signature
> >>> ST_Point(, )
> >>> Error: Error while 

Re: About the Spatial functionality

[Julian Hyde]

There are no test data sets, I’m afraid. I would love to add a data set that 
includes various kinds of geometries (points, lines, polygons). One candidate 
is the one in the OpenGIS Simple Feature Access spec[1] section C.3.1.2 onwards.

There ought to be (but isn’t, right now) an easier way to import the list of 
GIS functions than calling ModelHandler.addFunctions. You can currently add 
‘fun=oracle’ to the JDBC URL to load the operators in OracleSqlOperatorTable; 
we ought to allow ‘fun=spatial’ or ‘fun=oracle,spatial’.

Julian

[1] http://portal.opengeospatial.org/files/?artifact_id=25354 
 

> On Nov 28, 2017, at 1:11 PM, Michael Mior  wrote:
> 
> Yes, you should not use quotes if upcase is true since all functions are
> registered with uppercase names and all unquoted literals are also
> automatically upcased. Glad this helped!
> 
> --
> Michael Mior
> mm...@apache.org
> 
> 2017-11-28 14:18 GMT-05:00 Christian Tzolov :
> 
>> ​Ok, ​
>> I think i
>> ​ solved the riddle​
>> .
>> ​H
>> ad to remove
>> ​the ​
>> quotes from
>> ​the ​
>> function name (e.g. use ST_Point instead of "ST_Point"). This
>> ​ is due to the ​
>> upCase=TURE parameter
>> ​in
>> ​
>> addFunctions
>> ​ ​
>> .
>> 
>> I don't see the error anymore. Now i'm facing another issue i believe is
>> related with my adapter implementation.
>> 
>> Thanks for the support!
>> 
>> On 28 November 2017 at 18:43, Christian Tzolov  wrote:
>> 
>>> Unfortunately it didn't help still get " No match found for function
>>> signature ST_Point(, )"
>>> ​.
>>> 
>>> ​Could it be that i need to ad some schema or other prefix? e.g.
>>> "geode"."ST_Point"(
>>> 
>>> Also can i check interactively what are the registered functions? ​
>>> 
>>> On 28 November 2017 at 18:33, Michael Mior  wrote:
>>> 
 I believe that should work. I'll let others correct me if I'm missing
>> the
 boat here.
 
 --
 Michael Mior
 mm...@apache.org
 
 2017-11-28 12:31 GMT-05:00 Christian Tzolov :
 
> Thanks @Michael!  Can i assume that
> ​ ​
> in
> ​ ​
> the SchemaFactory
> ​#​
> create(SchemaPlus parentSchema, String name,
> ​ ...​
> )
> ​ method ​the root schema is constructed?  And can i use the
> parentSchema
> ​ like this:
> 
> ModelHandler.addFunctions(parentSchema, null,
 ImmutableList.of(),
> ​ ​
> GeoFunctions.class.getName(), "*", true);
> 
> On 28 November 2017 at 16:58, Michael Mior 
>> wrote:
> 
>> I believe the geospatial functions are not currently registered by
> default.
>> You can see an example of how to do this in CalciteAssert.java. Once
 you
>> have constructed the root schema, the following should be
>> sufficient:
>> 
>> ModelHandler.addFunctions(rootSchema, null,
 ImmutableList.of(),
>> GeoFunctions.class.getName(), "*", true);
>> 
>> --
>> Michael Mior
>> mm...@apache.org
>> 
>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
>> 
>>> I've tried to cast the Zip's loc column into double like this:
>>> 
>>> SELECT
>>> ​ ​
>>> "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
 DOUBLE)
>> AS
>>> "lat"
>>> ​ ​
>>> FROM "geode"."Zips"
>>> ​ ​
>>> LIMIT  10;
>>> 
>>> ​This seems to work fine. ​But when i try to use the ST_Point
 function
> i
>>> get: "No match found for function signature ST_Point(,
>> )"
>>> (full stack is below)
>>> 
>>> It seems like i've not registered a jar dependency or haven't
 enabled
>>> something else?
>>> 
>>> 
>>> jdbc:calcite:conformance=LENIENT> SELECT "city",
 "ST_Point"(cast("loc"
>> [0]
>>> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
>> 10;
>>> 2017-11-28 10:19:15,199 [main] ERROR -
>>> org.apache.calcite.sql.validate.SqlValidatorException: No match
 found
>> for
>>> function signature ST_Point(, )
>>> 2017-11-28 10:19:15,199 [main] ERROR -
>>> org.apache.calcite.runtime.CalciteContextException: From line 1,
> column
>> 16
>>> to line 1, column 79: No match found for function signature
>>> ST_Point(, )
>>> Error: Error while executing SQL "SELECT "city",
 "ST_Point"(cast("loc"
>> [0]
>>> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
>> 10":
> From
>>> line 1, column 16 to line 1, column 79: No match found for
>> function
>>> signature ST_Point(, ) (state=,code=0)
>>> 
>>> On 28 November 2017 at 09:32, Christian Tzolov <
>> ctzo...@pivotal.io>
>> wrote:
>>> 
 @Julian are there some tests, json datasets? Perhaps in
 calcite-test-dataset?
 Also I will try to cast the "loc" from Zips into DOUBLE 

Re: About the Spatial functionality

[Michael Mior]

Yes, you should not use quotes if upcase is true since all functions are
registered with uppercase names and all unquoted literals are also
automatically upcased. Glad this helped!

--
Michael Mior
mm...@apache.org

2017-11-28 14:18 GMT-05:00 Christian Tzolov :

> ​Ok, ​
> I think i
> ​ solved the riddle​
> .
> ​H
> ad to remove
> ​the ​
> quotes from
> ​the ​
> function name (e.g. use ST_Point instead of "ST_Point"). This
> ​ is due to the ​
> upCase=TURE parameter
> ​in
>  ​
> addFunctions
> ​ ​
> .
>
> I don't see the error anymore. Now i'm facing another issue i believe is
> related with my adapter implementation.
>
> Thanks for the support!
>
> On 28 November 2017 at 18:43, Christian Tzolov  wrote:
>
> > Unfortunately it didn't help still get " No match found for function
> > signature ST_Point(, )"
> > ​.
> >
> > ​Could it be that i need to ad some schema or other prefix? e.g.
> > "geode"."ST_Point"(
> >
> > Also can i check interactively what are the registered functions? ​
> >
> > On 28 November 2017 at 18:33, Michael Mior  wrote:
> >
> >> I believe that should work. I'll let others correct me if I'm missing
> the
> >> boat here.
> >>
> >> --
> >> Michael Mior
> >> mm...@apache.org
> >>
> >> 2017-11-28 12:31 GMT-05:00 Christian Tzolov :
> >>
> >> > Thanks @Michael!  Can i assume that
> >> > ​ ​
> >> > in
> >> > ​ ​
> >> > the SchemaFactory
> >> > ​#​
> >> > create(SchemaPlus parentSchema, String name,
> >> > ​ ...​
> >> > )
> >> > ​ method ​the root schema is constructed?  And can i use the
> >> > parentSchema
> >> > ​ like this:
> >> >
> >> > ModelHandler.addFunctions(parentSchema, null,
> >> ImmutableList.of(),
> >> > ​ ​
> >> > GeoFunctions.class.getName(), "*", true);
> >> >
> >> > On 28 November 2017 at 16:58, Michael Mior 
> wrote:
> >> >
> >> > > I believe the geospatial functions are not currently registered by
> >> > default.
> >> > > You can see an example of how to do this in CalciteAssert.java. Once
> >> you
> >> > > have constructed the root schema, the following should be
> sufficient:
> >> > >
> >> > > ModelHandler.addFunctions(rootSchema, null,
> >> ImmutableList.of(),
> >> > > GeoFunctions.class.getName(), "*", true);
> >> > >
> >> > > --
> >> > > Michael Mior
> >> > > mm...@apache.org
> >> > >
> >> > > 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
> >> > >
> >> > > > I've tried to cast the Zip's loc column into double like this:
> >> > > >
> >> > > > SELECT
> >> > > > ​ ​
> >> > > > "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
> >> DOUBLE)
> >> > > AS
> >> > > > "lat"
> >> > > > ​ ​
> >> > > > FROM "geode"."Zips"
> >> > > > ​ ​
> >> > > > LIMIT  10;
> >> > > >
> >> > > > ​This seems to work fine. ​But when i try to use the ST_Point
> >> function
> >> > i
> >> > > > get: "No match found for function signature ST_Point(,
> >> > > )"
> >> > > > (full stack is below)
> >> > > >
> >> > > > It seems like i've not registered a jar dependency or haven't
> >> enabled
> >> > > > something else?
> >> > > >
> >> > > >
> >> > > > jdbc:calcite:conformance=LENIENT> SELECT "city",
> >> "ST_Point"(cast("loc"
> >> > > [0]
> >> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
> 10;
> >> > > > 2017-11-28 10:19:15,199 [main] ERROR -
> >> > > > org.apache.calcite.sql.validate.SqlValidatorException: No match
> >> found
> >> > > for
> >> > > > function signature ST_Point(, )
> >> > > > 2017-11-28 10:19:15,199 [main] ERROR -
> >> > > > org.apache.calcite.runtime.CalciteContextException: From line 1,
> >> > column
> >> > > 16
> >> > > > to line 1, column 79: No match found for function signature
> >> > > > ST_Point(, )
> >> > > > Error: Error while executing SQL "SELECT "city",
> >> "ST_Point"(cast("loc"
> >> > > [0]
> >> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT
> 10":
> >> > From
> >> > > > line 1, column 16 to line 1, column 79: No match found for
> function
> >> > > > signature ST_Point(, ) (state=,code=0)
> >> > > >
> >> > > > On 28 November 2017 at 09:32, Christian Tzolov <
> ctzo...@pivotal.io>
> >> > > wrote:
> >> > > >
> >> > > > > @Julian are there some tests, json datasets? Perhaps in
> >> > > > > calcite-test-dataset?
> >> > > > > Also I will try to cast the "loc" from Zips into DOUBLE columns
> to
> >> > test
> >> > > > > the ST_Point
> >> > > > >
> >> > > > > On 28 November 2017 at 02:24, Julian Hyde 
> >> wrote:
> >> > > > >
> >> > > > >> It’s true that you can’t define a GEOMETRY column in a foreign
> >> > table.
> >> > > > But
> >> > > > >> you can define a VARCHAR column and apply the ST_GeomFromText
> to
> >> it,
> >> > > or
> >> > > > if
> >> > > > >> you want a point you can define a pair of DOUBLE columns and
> >> apply
> >> > the
> >> > > > >> ST_Point function.
> >> > > > >>
> >> > > > >> In essence, our implementation of GEOMETRY is only an in-memory
> >> > format
> >> > > > >> 

Re: About the Spatial functionality

[Christian Tzolov]

​Ok, ​
I think i
​ solved the riddle​
.
​H
ad to remove
​the ​
quotes from
​the ​
function name (e.g. use ST_Point instead of "ST_Point"). This
​ is due to the ​
upCase=TURE parameter
​in
 ​
addFunctions
​ ​
.

I don't see the error anymore. Now i'm facing another issue i believe is
related with my adapter implementation.

Thanks for the support!

On 28 November 2017 at 18:43, Christian Tzolov  wrote:

> Unfortunately it didn't help still get " No match found for function
> signature ST_Point(, )"
> ​.
>
> ​Could it be that i need to ad some schema or other prefix? e.g.
> "geode"."ST_Point"(
>
> Also can i check interactively what are the registered functions? ​
>
> On 28 November 2017 at 18:33, Michael Mior  wrote:
>
>> I believe that should work. I'll let others correct me if I'm missing the
>> boat here.
>>
>> --
>> Michael Mior
>> mm...@apache.org
>>
>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov :
>>
>> > Thanks @Michael!  Can i assume that
>> > ​ ​
>> > in
>> > ​ ​
>> > the SchemaFactory
>> > ​#​
>> > create(SchemaPlus parentSchema, String name,
>> > ​ ...​
>> > )
>> > ​ method ​the root schema is constructed?  And can i use the
>> > parentSchema
>> > ​ like this:
>> >
>> > ModelHandler.addFunctions(parentSchema, null,
>> ImmutableList.of(),
>> > ​ ​
>> > GeoFunctions.class.getName(), "*", true);
>> >
>> > On 28 November 2017 at 16:58, Michael Mior  wrote:
>> >
>> > > I believe the geospatial functions are not currently registered by
>> > default.
>> > > You can see an example of how to do this in CalciteAssert.java. Once
>> you
>> > > have constructed the root schema, the following should be sufficient:
>> > >
>> > > ModelHandler.addFunctions(rootSchema, null,
>> ImmutableList.of(),
>> > > GeoFunctions.class.getName(), "*", true);
>> > >
>> > > --
>> > > Michael Mior
>> > > mm...@apache.org
>> > >
>> > > 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
>> > >
>> > > > I've tried to cast the Zip's loc column into double like this:
>> > > >
>> > > > SELECT
>> > > > ​ ​
>> > > > "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
>> DOUBLE)
>> > > AS
>> > > > "lat"
>> > > > ​ ​
>> > > > FROM "geode"."Zips"
>> > > > ​ ​
>> > > > LIMIT  10;
>> > > >
>> > > > ​This seems to work fine. ​But when i try to use the ST_Point
>> function
>> > i
>> > > > get: "No match found for function signature ST_Point(,
>> > > )"
>> > > > (full stack is below)
>> > > >
>> > > > It seems like i've not registered a jar dependency or haven't
>> enabled
>> > > > something else?
>> > > >
>> > > >
>> > > > jdbc:calcite:conformance=LENIENT> SELECT "city",
>> "ST_Point"(cast("loc"
>> > > [0]
>> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
>> > > > 2017-11-28 10:19:15,199 [main] ERROR -
>> > > > org.apache.calcite.sql.validate.SqlValidatorException: No match
>> found
>> > > for
>> > > > function signature ST_Point(, )
>> > > > 2017-11-28 10:19:15,199 [main] ERROR -
>> > > > org.apache.calcite.runtime.CalciteContextException: From line 1,
>> > column
>> > > 16
>> > > > to line 1, column 79: No match found for function signature
>> > > > ST_Point(, )
>> > > > Error: Error while executing SQL "SELECT "city",
>> "ST_Point"(cast("loc"
>> > > [0]
>> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10":
>> > From
>> > > > line 1, column 16 to line 1, column 79: No match found for function
>> > > > signature ST_Point(, ) (state=,code=0)
>> > > >
>> > > > On 28 November 2017 at 09:32, Christian Tzolov 
>> > > wrote:
>> > > >
>> > > > > @Julian are there some tests, json datasets? Perhaps in
>> > > > > calcite-test-dataset?
>> > > > > Also I will try to cast the "loc" from Zips into DOUBLE columns to
>> > test
>> > > > > the ST_Point
>> > > > >
>> > > > > On 28 November 2017 at 02:24, Julian Hyde 
>> wrote:
>> > > > >
>> > > > >> It’s true that you can’t define a GEOMETRY column in a foreign
>> > table.
>> > > > But
>> > > > >> you can define a VARCHAR column and apply the ST_GeomFromText to
>> it,
>> > > or
>> > > > if
>> > > > >> you want a point you can define a pair of DOUBLE columns and
>> apply
>> > the
>> > > > >> ST_Point function.
>> > > > >>
>> > > > >> In essence, our implementation of GEOMETRY is only an in-memory
>> > format
>> > > > >> right now, not an on-disk format. It’s a little less efficient
>> than
>> > a
>> > > > >> native GEOMETRY data type but hopefully over time we will write
>> > > > optimizer
>> > > > >> rules that push down filters etc. so we don’t literally
>> construct an
>> > > > >> in-memory geometry object for every row, only the rows we are
>> > > > interested in.
>> > > > >>
>> > > > >> Julian
>> > > > >>
>> > > > >> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov <
>> ctzo...@pivotal.io
>> > >
>> > > > >> wrote:
>> > > > >> >
>> > > > >> > Hey there,
>> > > > >> >
>> > > > >> > I'm exploring the new Spatial 

Re: About the Spatial functionality

[Christian Tzolov]

Unfortunately it didn't help still get " No match found for function
signature ST_Point(, )"
​.

​Could it be that i need to ad some schema or other prefix? e.g.
"geode"."ST_Point"(

Also can i check interactively what are the registered functions? ​

On 28 November 2017 at 18:33, Michael Mior  wrote:

> I believe that should work. I'll let others correct me if I'm missing the
> boat here.
>
> --
> Michael Mior
> mm...@apache.org
>
> 2017-11-28 12:31 GMT-05:00 Christian Tzolov :
>
> > Thanks @Michael!  Can i assume that
> > ​ ​
> > in
> > ​ ​
> > the SchemaFactory
> > ​#​
> > create(SchemaPlus parentSchema, String name,
> > ​ ...​
> > )
> > ​ method ​the root schema is constructed?  And can i use the
> > parentSchema
> > ​ like this:
> >
> > ModelHandler.addFunctions(parentSchema, null,
> ImmutableList.of(),
> > ​ ​
> > GeoFunctions.class.getName(), "*", true);
> >
> > On 28 November 2017 at 16:58, Michael Mior  wrote:
> >
> > > I believe the geospatial functions are not currently registered by
> > default.
> > > You can see an example of how to do this in CalciteAssert.java. Once
> you
> > > have constructed the root schema, the following should be sufficient:
> > >
> > > ModelHandler.addFunctions(rootSchema, null,
> ImmutableList.of(),
> > > GeoFunctions.class.getName(), "*", true);
> > >
> > > --
> > > Michael Mior
> > > mm...@apache.org
> > >
> > > 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
> > >
> > > > I've tried to cast the Zip's loc column into double like this:
> > > >
> > > > SELECT
> > > > ​ ​
> > > > "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS
> DOUBLE)
> > > AS
> > > > "lat"
> > > > ​ ​
> > > > FROM "geode"."Zips"
> > > > ​ ​
> > > > LIMIT  10;
> > > >
> > > > ​This seems to work fine. ​But when i try to use the ST_Point
> function
> > i
> > > > get: "No match found for function signature ST_Point(,
> > > )"
> > > > (full stack is below)
> > > >
> > > > It seems like i've not registered a jar dependency or haven't enabled
> > > > something else?
> > > >
> > > >
> > > > jdbc:calcite:conformance=LENIENT> SELECT "city",
> "ST_Point"(cast("loc"
> > > [0]
> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
> > > > 2017-11-28 10:19:15,199 [main] ERROR -
> > > > org.apache.calcite.sql.validate.SqlValidatorException: No match
> found
> > > for
> > > > function signature ST_Point(, )
> > > > 2017-11-28 10:19:15,199 [main] ERROR -
> > > > org.apache.calcite.runtime.CalciteContextException: From line 1,
> > column
> > > 16
> > > > to line 1, column 79: No match found for function signature
> > > > ST_Point(, )
> > > > Error: Error while executing SQL "SELECT "city",
> "ST_Point"(cast("loc"
> > > [0]
> > > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10":
> > From
> > > > line 1, column 16 to line 1, column 79: No match found for function
> > > > signature ST_Point(, ) (state=,code=0)
> > > >
> > > > On 28 November 2017 at 09:32, Christian Tzolov 
> > > wrote:
> > > >
> > > > > @Julian are there some tests, json datasets? Perhaps in
> > > > > calcite-test-dataset?
> > > > > Also I will try to cast the "loc" from Zips into DOUBLE columns to
> > test
> > > > > the ST_Point
> > > > >
> > > > > On 28 November 2017 at 02:24, Julian Hyde 
> wrote:
> > > > >
> > > > >> It’s true that you can’t define a GEOMETRY column in a foreign
> > table.
> > > > But
> > > > >> you can define a VARCHAR column and apply the ST_GeomFromText to
> it,
> > > or
> > > > if
> > > > >> you want a point you can define a pair of DOUBLE columns and apply
> > the
> > > > >> ST_Point function.
> > > > >>
> > > > >> In essence, our implementation of GEOMETRY is only an in-memory
> > format
> > > > >> right now, not an on-disk format. It’s a little less efficient
> than
> > a
> > > > >> native GEOMETRY data type but hopefully over time we will write
> > > > optimizer
> > > > >> rules that push down filters etc. so we don’t literally construct
> an
> > > > >> in-memory geometry object for every row, only the rows we are
> > > > interested in.
> > > > >>
> > > > >> Julian
> > > > >>
> > > > >> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov <
> ctzo...@pivotal.io
> > >
> > > > >> wrote:
> > > > >> >
> > > > >> > Hey there,
> > > > >> >
> > > > >> > I'm exploring the new Spatial (https://calcite.apache.org/do
> > > > >> cs/spatial.html)
> > > > >> > functionality and i've been trying to figure out what are the
> > > minimal
> > > > >> > requirements for using it with my custom adapter.
> > > > >> >
> > > > >> > Following the guidelines i've set LENIENT  conformance in my
> jdbc
> > > URL
> > > > (
> > > > >> > jdbc:calcite:conformance=LENIENT;
> > > > >> > ​model=...my model​
> > > > >> > ​
> > > > >> > ​
> > > > >> > ​)
> > > > >> >
> > > > >> > But I am not sure how define the GEOMETRY column types?​
> > > > >> >
> > > > >> > Currently my custom Schema/Table 

Re: About the Spatial functionality

[Michael Mior]

I believe that should work. I'll let others correct me if I'm missing the
boat here.

--
Michael Mior
mm...@apache.org

2017-11-28 12:31 GMT-05:00 Christian Tzolov :

> Thanks @Michael!  Can i assume that
> ​ ​
> in
> ​ ​
> the SchemaFactory
> ​#​
> create(SchemaPlus parentSchema, String name,
> ​ ...​
> )
> ​ method ​the root schema is constructed?  And can i use the
> parentSchema
> ​ like this:
>
> ModelHandler.addFunctions(parentSchema, null, ImmutableList.of(),
> ​ ​
> GeoFunctions.class.getName(), "*", true);
>
> On 28 November 2017 at 16:58, Michael Mior  wrote:
>
> > I believe the geospatial functions are not currently registered by
> default.
> > You can see an example of how to do this in CalciteAssert.java. Once you
> > have constructed the root schema, the following should be sufficient:
> >
> > ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
> > GeoFunctions.class.getName(), "*", true);
> >
> > --
> > Michael Mior
> > mm...@apache.org
> >
> > 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
> >
> > > I've tried to cast the Zip's loc column into double like this:
> > >
> > > SELECT
> > > ​ ​
> > > "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS DOUBLE)
> > AS
> > > "lat"
> > > ​ ​
> > > FROM "geode"."Zips"
> > > ​ ​
> > > LIMIT  10;
> > >
> > > ​This seems to work fine. ​But when i try to use the ST_Point function
> i
> > > get: "No match found for function signature ST_Point(,
> > )"
> > > (full stack is below)
> > >
> > > It seems like i've not registered a jar dependency or haven't enabled
> > > something else?
> > >
> > >
> > > jdbc:calcite:conformance=LENIENT> SELECT "city", "ST_Point"(cast("loc"
> > [0]
> > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
> > > 2017-11-28 10:19:15,199 [main] ERROR -
> > > org.apache.calcite.sql.validate.SqlValidatorException: No match found
> > for
> > > function signature ST_Point(, )
> > > 2017-11-28 10:19:15,199 [main] ERROR -
> > > org.apache.calcite.runtime.CalciteContextException: From line 1,
> column
> > 16
> > > to line 1, column 79: No match found for function signature
> > > ST_Point(, )
> > > Error: Error while executing SQL "SELECT "city", "ST_Point"(cast("loc"
> > [0]
> > > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10":
> From
> > > line 1, column 16 to line 1, column 79: No match found for function
> > > signature ST_Point(, ) (state=,code=0)
> > >
> > > On 28 November 2017 at 09:32, Christian Tzolov 
> > wrote:
> > >
> > > > @Julian are there some tests, json datasets? Perhaps in
> > > > calcite-test-dataset?
> > > > Also I will try to cast the "loc" from Zips into DOUBLE columns to
> test
> > > > the ST_Point
> > > >
> > > > On 28 November 2017 at 02:24, Julian Hyde  wrote:
> > > >
> > > >> It’s true that you can’t define a GEOMETRY column in a foreign
> table.
> > > But
> > > >> you can define a VARCHAR column and apply the ST_GeomFromText to it,
> > or
> > > if
> > > >> you want a point you can define a pair of DOUBLE columns and apply
> the
> > > >> ST_Point function.
> > > >>
> > > >> In essence, our implementation of GEOMETRY is only an in-memory
> format
> > > >> right now, not an on-disk format. It’s a little less efficient than
> a
> > > >> native GEOMETRY data type but hopefully over time we will write
> > > optimizer
> > > >> rules that push down filters etc. so we don’t literally construct an
> > > >> in-memory geometry object for every row, only the rows we are
> > > interested in.
> > > >>
> > > >> Julian
> > > >>
> > > >> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov  >
> > > >> wrote:
> > > >> >
> > > >> > Hey there,
> > > >> >
> > > >> > I'm exploring the new Spatial (https://calcite.apache.org/do
> > > >> cs/spatial.html)
> > > >> > functionality and i've been trying to figure out what are the
> > minimal
> > > >> > requirements for using it with my custom adapter.
> > > >> >
> > > >> > Following the guidelines i've set LENIENT  conformance in my jdbc
> > URL
> > > (
> > > >> > jdbc:calcite:conformance=LENIENT;
> > > >> > ​model=...my model​
> > > >> > ​
> > > >> > ​
> > > >> > ​)
> > > >> >
> > > >> > But I am not sure how define the GEOMETRY column types?​
> > > >> >
> > > >> > Currently my custom Schema/Table factory implementation infers the
> > > >> column
> > > >> > types from the underlaying system's field types.
> > > >> >
> > > >> > So it seems that i need to change my implementation and somehow to
> > > hint
> > > >> > which fields needs to be mapped to GEOMETRY types?  Or perhaps i
> can
> > > >> try to
> > > >> > do some expensive casting in SQL?
> > > >> >
> > > >> > Are there any guidelines, examples ​for using Spatial
> functionality
> > on
> > > >> 3rd
> > > >> > party (e.g. custom) adapters?
> > > >> >
> > > >> > Thanks,
> > > >> > Christian
> > > >>
> > > >>
> > > >
> > > >
> > > > --
> > > > Christian Tzolov 

Re: About the Spatial functionality

[Christian Tzolov]

Thanks @Michael!  Can i assume that
​ ​
in
​ ​
the SchemaFactory
​#​
create(SchemaPlus parentSchema, String name,
​ ...​
)
​ method ​the root schema is constructed?  And can i use the
parentSchema
​ like this:

ModelHandler.addFunctions(parentSchema, null, ImmutableList.of(),
​ ​
GeoFunctions.class.getName(), "*", true);

On 28 November 2017 at 16:58, Michael Mior  wrote:

> I believe the geospatial functions are not currently registered by default.
> You can see an example of how to do this in CalciteAssert.java. Once you
> have constructed the root schema, the following should be sufficient:
>
> ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
> GeoFunctions.class.getName(), "*", true);
>
> --
> Michael Mior
> mm...@apache.org
>
> 2017-11-28 4:27 GMT-05:00 Christian Tzolov :
>
> > I've tried to cast the Zip's loc column into double like this:
> >
> > SELECT
> > ​ ​
> > "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS DOUBLE)
> AS
> > "lat"
> > ​ ​
> > FROM "geode"."Zips"
> > ​ ​
> > LIMIT  10;
> >
> > ​This seems to work fine. ​But when i try to use the ST_Point function i
> > get: "No match found for function signature ST_Point(,
> )"
> > (full stack is below)
> >
> > It seems like i've not registered a jar dependency or haven't enabled
> > something else?
> >
> >
> > jdbc:calcite:conformance=LENIENT> SELECT "city", "ST_Point"(cast("loc"
> [0]
> > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
> > 2017-11-28 10:19:15,199 [main] ERROR -
> > org.apache.calcite.sql.validate.SqlValidatorException: No match found
> for
> > function signature ST_Point(, )
> > 2017-11-28 10:19:15,199 [main] ERROR -
> > org.apache.calcite.runtime.CalciteContextException: From line 1, column
> 16
> > to line 1, column 79: No match found for function signature
> > ST_Point(, )
> > Error: Error while executing SQL "SELECT "city", "ST_Point"(cast("loc"
> [0]
> > AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10": From
> > line 1, column 16 to line 1, column 79: No match found for function
> > signature ST_Point(, ) (state=,code=0)
> >
> > On 28 November 2017 at 09:32, Christian Tzolov 
> wrote:
> >
> > > @Julian are there some tests, json datasets? Perhaps in
> > > calcite-test-dataset?
> > > Also I will try to cast the "loc" from Zips into DOUBLE columns to test
> > > the ST_Point
> > >
> > > On 28 November 2017 at 02:24, Julian Hyde  wrote:
> > >
> > >> It’s true that you can’t define a GEOMETRY column in a foreign table.
> > But
> > >> you can define a VARCHAR column and apply the ST_GeomFromText to it,
> or
> > if
> > >> you want a point you can define a pair of DOUBLE columns and apply the
> > >> ST_Point function.
> > >>
> > >> In essence, our implementation of GEOMETRY is only an in-memory format
> > >> right now, not an on-disk format. It’s a little less efficient than a
> > >> native GEOMETRY data type but hopefully over time we will write
> > optimizer
> > >> rules that push down filters etc. so we don’t literally construct an
> > >> in-memory geometry object for every row, only the rows we are
> > interested in.
> > >>
> > >> Julian
> > >>
> > >> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov 
> > >> wrote:
> > >> >
> > >> > Hey there,
> > >> >
> > >> > I'm exploring the new Spatial (https://calcite.apache.org/do
> > >> cs/spatial.html)
> > >> > functionality and i've been trying to figure out what are the
> minimal
> > >> > requirements for using it with my custom adapter.
> > >> >
> > >> > Following the guidelines i've set LENIENT  conformance in my jdbc
> URL
> > (
> > >> > jdbc:calcite:conformance=LENIENT;
> > >> > ​model=...my model​
> > >> > ​
> > >> > ​
> > >> > ​)
> > >> >
> > >> > But I am not sure how define the GEOMETRY column types?​
> > >> >
> > >> > Currently my custom Schema/Table factory implementation infers the
> > >> column
> > >> > types from the underlaying system's field types.
> > >> >
> > >> > So it seems that i need to change my implementation and somehow to
> > hint
> > >> > which fields needs to be mapped to GEOMETRY types?  Or perhaps i can
> > >> try to
> > >> > do some expensive casting in SQL?
> > >> >
> > >> > Are there any guidelines, examples ​for using Spatial functionality
> on
> > >> 3rd
> > >> > party (e.g. custom) adapters?
> > >> >
> > >> > Thanks,
> > >> > Christian
> > >>
> > >>
> > >
> > >
> > > --
> > > Christian Tzolov  | Principle
> > Software
> > > Engineer | Spring .io | Pivotal <
> http://pivotal.io/>
> > > | ctzo...@pivotal.io
> > >
> >
> >
> >
> > --
> > Christian Tzolov  | Principle
> Software
> > Engineer | Spring .io | Pivotal 
> |
> > ctzo...@pivotal.io
> >
>



-- 
Christian Tzolov  | Principle Software
Engineer | Spring 

Re: About the Spatial functionality

[Michael Mior]

I believe the geospatial functions are not currently registered by default.
You can see an example of how to do this in CalciteAssert.java. Once you
have constructed the root schema, the following should be sufficient:

ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
GeoFunctions.class.getName(), "*", true);

--
Michael Mior
mm...@apache.org

2017-11-28 4:27 GMT-05:00 Christian Tzolov :

> I've tried to cast the Zip's loc column into double like this:
>
> SELECT
> ​ ​
> "city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS DOUBLE) AS
> "lat"
> ​ ​
> FROM "geode"."Zips"
> ​ ​
> LIMIT  10;
>
> ​This seems to work fine. ​But when i try to use the ST_Point function i
> get: "No match found for function signature ST_Point(, )"
> (full stack is below)
>
> It seems like i've not registered a jar dependency or haven't enabled
> something else?
>
>
> jdbc:calcite:conformance=LENIENT> SELECT "city", "ST_Point"(cast("loc" [0]
> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
> 2017-11-28 10:19:15,199 [main] ERROR -
> org.apache.calcite.sql.validate.SqlValidatorException: No match found for
> function signature ST_Point(, )
> 2017-11-28 10:19:15,199 [main] ERROR -
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 16
> to line 1, column 79: No match found for function signature
> ST_Point(, )
> Error: Error while executing SQL "SELECT "city", "ST_Point"(cast("loc" [0]
> AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10": From
> line 1, column 16 to line 1, column 79: No match found for function
> signature ST_Point(, ) (state=,code=0)
>
> On 28 November 2017 at 09:32, Christian Tzolov  wrote:
>
> > @Julian are there some tests, json datasets? Perhaps in
> > calcite-test-dataset?
> > Also I will try to cast the "loc" from Zips into DOUBLE columns to test
> > the ST_Point
> >
> > On 28 November 2017 at 02:24, Julian Hyde  wrote:
> >
> >> It’s true that you can’t define a GEOMETRY column in a foreign table.
> But
> >> you can define a VARCHAR column and apply the ST_GeomFromText to it, or
> if
> >> you want a point you can define a pair of DOUBLE columns and apply the
> >> ST_Point function.
> >>
> >> In essence, our implementation of GEOMETRY is only an in-memory format
> >> right now, not an on-disk format. It’s a little less efficient than a
> >> native GEOMETRY data type but hopefully over time we will write
> optimizer
> >> rules that push down filters etc. so we don’t literally construct an
> >> in-memory geometry object for every row, only the rows we are
> interested in.
> >>
> >> Julian
> >>
> >> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov 
> >> wrote:
> >> >
> >> > Hey there,
> >> >
> >> > I'm exploring the new Spatial (https://calcite.apache.org/do
> >> cs/spatial.html)
> >> > functionality and i've been trying to figure out what are the minimal
> >> > requirements for using it with my custom adapter.
> >> >
> >> > Following the guidelines i've set LENIENT  conformance in my jdbc URL
> (
> >> > jdbc:calcite:conformance=LENIENT;
> >> > ​model=...my model​
> >> > ​
> >> > ​
> >> > ​)
> >> >
> >> > But I am not sure how define the GEOMETRY column types?​
> >> >
> >> > Currently my custom Schema/Table factory implementation infers the
> >> column
> >> > types from the underlaying system's field types.
> >> >
> >> > So it seems that i need to change my implementation and somehow to
> hint
> >> > which fields needs to be mapped to GEOMETRY types?  Or perhaps i can
> >> try to
> >> > do some expensive casting in SQL?
> >> >
> >> > Are there any guidelines, examples ​for using Spatial functionality on
> >> 3rd
> >> > party (e.g. custom) adapters?
> >> >
> >> > Thanks,
> >> > Christian
> >>
> >>
> >
> >
> > --
> > Christian Tzolov  | Principle
> Software
> > Engineer | Spring .io | Pivotal 
> > | ctzo...@pivotal.io
> >
>
>
>
> --
> Christian Tzolov  | Principle Software
> Engineer | Spring .io | Pivotal  |
> ctzo...@pivotal.io
>

Re: About the Spatial functionality

[Christian Tzolov]

I've tried to cast the Zip's loc column into double like this:

SELECT
​ ​
"city",  cast("loc" [0] AS DOUBLE) AS "lon",  cast("loc" [1] AS DOUBLE) AS
"lat"
​ ​
FROM "geode"."Zips"
​ ​
LIMIT  10;

​This seems to work fine. ​But when i try to use the ST_Point function i
get: "No match found for function signature ST_Point(, )"
(full stack is below)

It seems like i've not registered a jar dependency or haven't enabled
something else?


jdbc:calcite:conformance=LENIENT> SELECT "city", "ST_Point"(cast("loc" [0]
AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10;
2017-11-28 10:19:15,199 [main] ERROR -
org.apache.calcite.sql.validate.SqlValidatorException: No match found for
function signature ST_Point(, )
2017-11-28 10:19:15,199 [main] ERROR -
org.apache.calcite.runtime.CalciteContextException: From line 1, column 16
to line 1, column 79: No match found for function signature
ST_Point(, )
Error: Error while executing SQL "SELECT "city", "ST_Point"(cast("loc" [0]
AS DOUBLE), cast("loc" [1] AS DOUBLE)) FROM "geode"."Zips"LIMIT 10": From
line 1, column 16 to line 1, column 79: No match found for function
signature ST_Point(, ) (state=,code=0)

On 28 November 2017 at 09:32, Christian Tzolov  wrote:

> @Julian are there some tests, json datasets? Perhaps in
> calcite-test-dataset?
> Also I will try to cast the "loc" from Zips into DOUBLE columns to test
> the ST_Point
>
> On 28 November 2017 at 02:24, Julian Hyde  wrote:
>
>> It’s true that you can’t define a GEOMETRY column in a foreign table. But
>> you can define a VARCHAR column and apply the ST_GeomFromText to it, or if
>> you want a point you can define a pair of DOUBLE columns and apply the
>> ST_Point function.
>>
>> In essence, our implementation of GEOMETRY is only an in-memory format
>> right now, not an on-disk format. It’s a little less efficient than a
>> native GEOMETRY data type but hopefully over time we will write optimizer
>> rules that push down filters etc. so we don’t literally construct an
>> in-memory geometry object for every row, only the rows we are interested in.
>>
>> Julian
>>
>> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov 
>> wrote:
>> >
>> > Hey there,
>> >
>> > I'm exploring the new Spatial (https://calcite.apache.org/do
>> cs/spatial.html)
>> > functionality and i've been trying to figure out what are the minimal
>> > requirements for using it with my custom adapter.
>> >
>> > Following the guidelines i've set LENIENT  conformance in my jdbc URL (
>> > jdbc:calcite:conformance=LENIENT;
>> > ​model=...my model​
>> > ​
>> > ​
>> > ​)
>> >
>> > But I am not sure how define the GEOMETRY column types?​
>> >
>> > Currently my custom Schema/Table factory implementation infers the
>> column
>> > types from the underlaying system's field types.
>> >
>> > So it seems that i need to change my implementation and somehow to hint
>> > which fields needs to be mapped to GEOMETRY types?  Or perhaps i can
>> try to
>> > do some expensive casting in SQL?
>> >
>> > Are there any guidelines, examples ​for using Spatial functionality on
>> 3rd
>> > party (e.g. custom) adapters?
>> >
>> > Thanks,
>> > Christian
>>
>>
>
>
> --
> Christian Tzolov  | Principle Software
> Engineer | Spring .io | Pivotal 
> | ctzo...@pivotal.io
>



-- 
Christian Tzolov  | Principle Software
Engineer | Spring .io | Pivotal  |
ctzo...@pivotal.io

Re: About the Spatial functionality

[Christian Tzolov]

@Julian are there some tests, json datasets? Perhaps in
calcite-test-dataset?
Also I will try to cast the "loc" from Zips into DOUBLE columns to test the
ST_Point

On 28 November 2017 at 02:24, Julian Hyde  wrote:

> It’s true that you can’t define a GEOMETRY column in a foreign table. But
> you can define a VARCHAR column and apply the ST_GeomFromText to it, or if
> you want a point you can define a pair of DOUBLE columns and apply the
> ST_Point function.
>
> In essence, our implementation of GEOMETRY is only an in-memory format
> right now, not an on-disk format. It’s a little less efficient than a
> native GEOMETRY data type but hopefully over time we will write optimizer
> rules that push down filters etc. so we don’t literally construct an
> in-memory geometry object for every row, only the rows we are interested in.
>
> Julian
>
> > On Nov 27, 2017, at 2:59 AM, Christian Tzolov 
> wrote:
> >
> > Hey there,
> >
> > I'm exploring the new Spatial (https://calcite.apache.org/
> docs/spatial.html)
> > functionality and i've been trying to figure out what are the minimal
> > requirements for using it with my custom adapter.
> >
> > Following the guidelines i've set LENIENT  conformance in my jdbc URL (
> > jdbc:calcite:conformance=LENIENT;
> > ​model=...my model​
> > ​
> > ​
> > ​)
> >
> > But I am not sure how define the GEOMETRY column types?​
> >
> > Currently my custom Schema/Table factory implementation infers the column
> > types from the underlaying system's field types.
> >
> > So it seems that i need to change my implementation and somehow to hint
> > which fields needs to be mapped to GEOMETRY types?  Or perhaps i can try
> to
> > do some expensive casting in SQL?
> >
> > Are there any guidelines, examples ​for using Spatial functionality on
> 3rd
> > party (e.g. custom) adapters?
> >
> > Thanks,
> > Christian
>
>


-- 
Christian Tzolov  | Principle Software
Engineer | Spring .io | Pivotal  |
ctzo...@pivotal.io

Re: About the Spatial functionality

[Julian Hyde]

It’s true that you can’t define a GEOMETRY column in a foreign table. But you 
can define a VARCHAR column and apply the ST_GeomFromText to it, or if you want 
a point you can define a pair of DOUBLE columns and apply the ST_Point function.

In essence, our implementation of GEOMETRY is only an in-memory format right 
now, not an on-disk format. It’s a little less efficient than a native GEOMETRY 
data type but hopefully over time we will write optimizer rules that push down 
filters etc. so we don’t literally construct an in-memory geometry object for 
every row, only the rows we are interested in.

Julian

> On Nov 27, 2017, at 2:59 AM, Christian Tzolov  wrote:
> 
> Hey there,
> 
> I'm exploring the new Spatial (https://calcite.apache.org/docs/spatial.html)
> functionality and i've been trying to figure out what are the minimal
> requirements for using it with my custom adapter.
> 
> Following the guidelines i've set LENIENT  conformance in my jdbc URL (
> jdbc:calcite:conformance=LENIENT;
> ​model=...my model​
> ​
> ​
> ​)
> 
> But I am not sure how define the GEOMETRY column types?​
> 
> Currently my custom Schema/Table factory implementation infers the column
> types from the underlaying system's field types.
> 
> So it seems that i need to change my implementation and somehow to hint
> which fields needs to be mapped to GEOMETRY types?  Or perhaps i can try to
> do some expensive casting in SQL?
> 
> Are there any guidelines, examples ​for using Spatial functionality on 3rd
> party (e.g. custom) adapters?
> 
> Thanks,
> Christian