You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Christian Tzolov <ct...@pivotal.io> on 2017/12/01 17:09:36 UTC

Re: About the Spatial functionality

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 <jh...@apache.org> 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 <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 <mm...@uwaterloo.ca> wrote:
> >
> > Sounds like a good idea. I logged
> > https://issues.apache.org/jira/browse/CALCITE-2072 <
> 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
> > mmior@apache.org <ma...@apache.org>
> >
> > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org>>:
> >
> >> 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 <
> http://portal.opengeospatial.org/files/?artifact_id=25354>>
> >>
> >>> On Nov 28, 2017, at 1:11 PM, Michael Mior <mmior@uwaterloo.ca <mailto:
> mmior@uwaterloo.ca>> 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
> >>> mmior@apache.org <ma...@apache.org>
> >>>
> >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> <ma...@pivotal.io>>:
> >>>
> >>>> 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 <ctzolov@pivotal.io
> <ma...@pivotal.io>>
> >> wrote:
> >>>>
> >>>>> Unfortunately it didn't help still get " No match found for function
> >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> >>>>> .
> >>>>>
> >>>>> 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 <mm...@uwaterloo.ca>
> wrote:
> >>>>>
> >>>>>> I believe that should work. I'll let others correct me if I'm
> missing
> >>>> the
> >>>>>> boat here.
> >>>>>>
> >>>>>> --
> >>>>>> Michael Mior
> >>>>>> mmior@apache.org
> >>>>>>
> >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <ct...@pivotal.io>:
> >>>>>>
> >>>>>>> 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.<String>of(),
> >>>>>>>  
> >>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>
> >>>>>>> On 28 November 2017 at 16:58, Michael Mior <mm...@uwaterloo.ca>
> >>>> 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.<String>of(),
> >>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> Michael Mior
> >>>>>>>> mmior@apache.org
> >>>>>>>>
> >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <ct...@pivotal.io>:
> >>>>>>>>
> >>>>>>>>> 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(<NUMERIC>,
> >>>>>>>> <NUMERIC>)"
> >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> >>>>>>>>>
> >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> >>>> ctzolov@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 <jh...@apache.org>
> >>>>>> 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 <
> >>>>>> ctzolov@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 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 <http://www.linkedin.com/in/tzolov> |
> >>>> Principle
> >>>>>>>>> Software
> >>>>>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>>>>>> http://pivotal.io/>
> >>>>>>>>>> | ctzolov@pivotal.io
> >>>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> --
> >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>>>>>> Software
> >>>>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>>>>> http://pivotal.io/>
> >>>>>>>> |
> >>>>>>>>> ctzolov@pivotal.io
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> --
> >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>>>> Software
> >>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>> http://pivotal.io/>
> >>>>>> |
> >>>>>>> ctzolov@pivotal.io
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>> Software
> >>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >> http://pivotal.io/>
> >>>>> | ctzolov@pivotal.io
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >> Software
> >>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> http://pivotal.io/>
> >> |
> >>>> ctzolov@pivotal.io
>
>


-- 
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
ctzolov@pivotal.io

Re: About the Spatial functionality

Posted by Julian Hyde <jh...@apache.org>.

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

> On Dec 9, 2017, at 1:51 PM, Vamshi Krishna <va...@gmail.com> 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 <jhyde@apache.org <ma...@apache.org>> 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 <va...@gmail.com>
>> 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 <jhyde@apache.org <ma...@apache.org> <mailto:
>> jhyde@apache.org <ma...@apache.org>>> wrote:
>>> 
>>>> You’re basically running into https://issues.apache.org/ <https://issues.apache.org/> <
>> https://issues.apache.org/ <https://issues.apache.org/>>
>>>> jira/browse/CALCITE-2072 <https://issues.apache.org/ <https://issues.apache.org/> <
>> https://issues.apache.org/ <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/> <
>> 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/ <https://github.com/apache/> <
>> https://github.com/apache/ <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 <vamshi.v.krishna@gmail.com <ma...@gmail.com>
>>> 
>>>> 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.<String>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 <ctzolov@pivotal.io <ma...@pivotal.io>>
>>>>> 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.krishna@gmail.com <ma...@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.<String>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(<NUMERIC>,
>>>> <NUMERIC>,
>>>>>>> <NUMERIC>)
>>>>>>> 
>>>>>>> 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 <ctzolov@pivotal.io <ma...@pivotal.io>
>>> 
>>>>>>> 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- <https://springoneplatform.io/sessions/enable-sql-jdbc->
>>>>>>>> access-to-apache-geode-gemfire-using-apache-calcite
>>>>>>>> [2] Slides:
>>>>>>>> https://docs.google.com/presentation/d/1zo473pcupWEjRXOA_ <https://docs.google.com/presentation/d/1zo473pcupWEjRXOA_>
>>>>>>>> W5rgaKSmS2Vmyl2U2ATKmrS26M/edit?usp=sharing
>>>>>>>> 
>>>>>>>> 
>>>>>>>> On 1 December 2017 at 21:05, Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>>>>>>>> 
>>>>>>>>> The Natural earth dataset (which https://github.com/zzolo/geo_ <https://github.com/zzolo/geo_>
>>>>>>>>> simple_countries_wkt_csv <https://github.com/zzolo/geo_ <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 <ctzolov@pivotal.io <ma...@pivotal.io>>
>>>>>>>> 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> <
>>>>>>>>> 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 <jhyde@apache.org <ma...@apache.org>
>>>>>> <mailto:
>>>>>>>>> jhyde@apache.org <ma...@apache.org>>> 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/ <https://github.com/johan/>
>>>>>>>> world.geo.json/tree/master/
>>>>>>>>> countries/USA <https://github.com/johan/ <https://github.com/johan/>
>> world.geo.json/tree/master/
>>>>>>>>> countries/USA><https://github.com/johan/world.geo.json/tree/ <https://github.com/johan/world.geo.json/tree/>
>>>>>>>>> master/countries/USA <https://github.com/johan/ <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 <mmior@uwaterloo.ca <ma...@uwaterloo.ca>
>>>>>>>> <mailto:
>>>>>>>>> mmior@uwaterloo.ca <ma...@uwaterloo.ca>>> wrote:
>>>>>>>>>>> 
>>>>>>>>>>> Sounds like a good idea. I logged
>>>>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <https://issues.apache.org/jira/browse/CALCITE-2072> <
>>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <https://issues.apache.org/jira/browse/CALCITE-2072>> <
>>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <https://issues.apache.org/jira/browse/CALCITE-2072> <
>>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <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
>>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>> <mailto:
>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>> <mailto:mmior@apache.org <ma...@apache.org>>>
>>>>>>>>>>> 
>>>>>>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <ma...@apache.org>
>>>>>> <mailto:
>>>>>>>>> jhyde@apache.org <ma...@apache.org>> <mailto:jhyde@apache.org <ma...@apache.org> <mailto:
>> jhyde@apache.org <ma...@apache.org>
>>>>>>>>>> :
>>>>>>>>>>> 
>>>>>>>>>>>> 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> <
>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <http://portal.opengeospatial.org/files/?artifact_id=25354>> <
>>>>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <http://portal.opengeospatial.org/files/?artifact_id=25354> <
>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <http://portal.opengeospatial.org/files/?artifact_id=25354>> <
>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <http://portal.opengeospatial.org/files/?artifact_id=25354> <
>>>>>>>>> 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 <mmior@uwaterloo.ca <ma...@uwaterloo.ca>
>>>>>>>>> <mailto:mmior@uwaterloo.ca <ma...@uwaterloo.ca>> <mailto:mmior@uwaterloo.ca <ma...@uwaterloo.ca> <mailto:
>>>>>>>>> mmior@uwaterloo.ca <ma...@uwaterloo.ca>>>> 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
>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>> <mailto:
>>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>> <mailto:mmior@apache.org <ma...@apache.org>>>
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io> <mailto:
>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>>>>:
>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 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 <
>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io> <mailto:
>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>>>>
>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> Unfortunately it didn't help still get " No match found for
>>>>>>>>> function
>>>>>>>>>>>>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
>>>>>>>>>>>>>>> .
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 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 <
>>>>>> mmior@uwaterloo.ca <ma...@uwaterloo.ca>
>>>>>>>>> <mailto:mmior@uwaterloo.ca <ma...@uwaterloo.ca>>> wrote:
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> I believe that should work. I'll let others correct me if
>>>>>> I'm
>>>>>>>>> missing
>>>>>>>>>>>>>> the
>>>>>>>>>>>>>>>> boat here.
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>>
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>>:
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>>>>>  
>>>>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> On 28 November 2017 at 16:58, Michael Mior <
>>>>>>> mmior@uwaterloo.ca <ma...@uwaterloo.ca>
>>>>>>>>> <mailto:mmior@uwaterloo.ca <ma...@uwaterloo.ca>>>
>>>>>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>>
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>>:
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> 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(<NUMERIC>,
>>>>>>>>>>>>>>>>>> <NUMERIC>)"
>>>>>>>>>>>>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <ma...@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 <
>>>>>>> jhyde@apache.org <ma...@apache.org>
>>>>>>>>> <mailto:jhyde@apache.org <ma...@apache.org>>>
>>>>>>>>>>>>>>>> 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 <
>>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> Hey there,
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> I'm exploring the new Spatial (
>>>>>>>>> https://calcite.apache.org/do <https://calcite.apache.org/do> <https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
>>>>>>>>> http://www.linkedin.com/in/tzolov>> |
>>>>>>>>>>>>>> Principle
>>>>>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>>>>>> https://spring.io/
>>>>>>>>>> .io
>>>>>>>>> | Pivotal <
>>>>>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>>>>>> https://spring.io/
>>>>>>>>>> .io
>>>>>>>>> | Pivotal <
>>>>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>>>> |
>>>>>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>>>>> .io
>>>>>>>>> | Pivotal <
>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>> |
>>>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>>>> .io
>>>>>>> |
>>>>>>>>> Pivotal <
>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>> Software
>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>>>> .io
>>>>>>> |
>>>>>>>>> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>> |
>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> --
>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>>>>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
>>>>>>>>> http://pivotal.io/> | ctzolov@pivotal.io <mailto:
>> ctzolov@pivotal.io
>>>>>>> <
>>>>>>>>> wkt-countries.json.zip>
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> --
>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>>>>> Software
>>>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
>>>>>> http://pivotal.io/>
>>>>>>> |
>>>>>>>> ctzolov@pivotal.io
>>>>>>>> 
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> --
>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>> Software
>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
>> http://pivotal.io/>
>>>> |
>>>>>> ctzolov@pivotal.io

Re: About the Spatial functionality

Posted by Vamshi Krishna <va...@gmail.com>.

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 <jh...@apache.org> 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 <va...@gmail.com>
> 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 <jhyde@apache.org <mailto:
> jhyde@apache.org>> wrote:
> >
> >> You’re basically running into https://issues.apache.org/ <
> https://issues.apache.org/>
> >> jira/browse/CALCITE-2072 <https://issues.apache.org/ <
> 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/ <
> 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 <vamshi.v.krishna@gmail.com
> >
> >> 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.<String>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 <ct...@pivotal.io>
> >>> 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.krishna@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.<String>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(<NUMERIC>,
> >> <NUMERIC>,
> >>>>> <NUMERIC>)
> >>>>>
> >>>>> 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 <ctzolov@pivotal.io
> >
> >>>>> 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 <jh...@apache.org> wrote:
> >>>>>>
> >>>>>>> The Natural earth dataset (which https://github.com/zzolo/geo_
> >>>>>>> simple_countries_wkt_csv <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 <ct...@pivotal.io>
> >>>>>> 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 <jhyde@apache.org
> >>>> <mailto:
> >>>>>>> jhyde@apache.org>> 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 <https://github.com/johan/
> world.geo.json/tree/master/
> >>>>>>> countries/USA><https://github.com/johan/world.geo.json/tree/
> >>>>>>> master/countries/USA <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 <mmior@uwaterloo.ca
> >>>>>> <mailto:
> >>>>>>> mmior@uwaterloo.ca>> wrote:
> >>>>>>>>>
> >>>>>>>>> Sounds like a good idea. I logged
> >>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
> >>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072> <
> >>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
> >>>>>>> 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
> >>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
> >>>>> mmior@apache.org
> >>>>>>> <ma...@apache.org>>
> >>>>>>>>>
> >>>>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org
> >>>> <mailto:
> >>>>>>> jhyde@apache.org> <mailto:jhyde@apache.org <mailto:
> jhyde@apache.org
> >>>>>>>> :
> >>>>>>>>>
> >>>>>>>>>> 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> <
> >>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> >>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354> <
> >>>>>>> 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 <mmior@uwaterloo.ca
> >>>>>>> <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> >>>>>>> mmior@uwaterloo.ca>>> 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
> >>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
> >>>>>> mmior@apache.org
> >>>>>>> <ma...@apache.org>>
> >>>>>>>>>>>
> >>>>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
> >>>> ctzolov@pivotal.io
> >>>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> >>>>>>> ctzolov@pivotal.io>>>:
> >>>>>>>>>>>
> >>>>>>>>>>>> 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 <
> >>>>>> ctzolov@pivotal.io
> >>>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> >>>>>>> ctzolov@pivotal.io>>>
> >>>>>>>>>> wrote:
> >>>>>>>>>>>>
> >>>>>>>>>>>>> Unfortunately it didn't help still get " No match found for
> >>>>>>> function
> >>>>>>>>>>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> >>>>>>>>>>>>> .
> >>>>>>>>>>>>>
> >>>>>>>>>>>>> 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 <
> >>>> mmior@uwaterloo.ca
> >>>>>>> <ma...@uwaterloo.ca>> wrote:
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>> I believe that should work. I'll let others correct me if
> >>>> I'm
> >>>>>>> missing
> >>>>>>>>>>>> the
> >>>>>>>>>>>>>> boat here.
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>> --
> >>>>>>>>>>>>>> Michael Mior
> >>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
> >>>>> ctzolov@pivotal.io
> >>>>>>> <ma...@pivotal.io>>:
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> 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.<String>of(),
> >>>>>>>>>>>>>>>  
> >>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> On 28 November 2017 at 16:58, Michael Mior <
> >>>>> mmior@uwaterloo.ca
> >>>>>>> <ma...@uwaterloo.ca>>
> >>>>>>>>>>>> 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.<String>of(),
> >>>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>> --
> >>>>>>>>>>>>>>>> Michael Mior
> >>>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
> >>>>>> ctzolov@pivotal.io
> >>>>>>> <ma...@pivotal.io>>:
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>> 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(<NUMERIC>,
> >>>>>>>>>>>>>>>> <NUMERIC>)"
> >>>>>>>>>>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> >>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> >>>>>>>>>>>> ctzolov@pivotal.io <ma...@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 <
> >>>>> jhyde@apache.org
> >>>>>>> <ma...@apache.org>>
> >>>>>>>>>>>>>> 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 <
> >>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>>> wrote:
> >>>>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>>>> Hey there,
> >>>>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>>>> I'm exploring the new Spatial (
> >>>>>>> https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> >>>>>>> http://www.linkedin.com/in/tzolov>> |
> >>>>>>>>>>>> Principle
> >>>>>>>>>>>>>>>>> Software
> >>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
> >>>> https://spring.io/
> >>>>>>>> .io
> >>>>>>> | Pivotal <
> >>>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>> --
> >>>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>>>>>>>> Software
> >>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
> >>>> https://spring.io/
> >>>>>>>> .io
> >>>>>>> | Pivotal <
> >>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>>>>> |
> >>>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> --
> >>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>>>>>> Software
> >>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>>>>> .io
> >>>>>>> | Pivotal <
> >>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>>> |
> >>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>> --
> >>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>>>> Software
> >>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>>>> .io
> >>>>> |
> >>>>>>> Pivotal <
> >>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>> --
> >>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>> Software
> >>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>>>> .io
> >>>>> |
> >>>>>>> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>> |
> >>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>>>>> http://pivotal.io/> | ctzolov@pivotal.io <mailto:
> ctzolov@pivotal.io
> >>>>> <
> >>>>>>> wkt-countries.json.zip>
> >>>>>>>
> >>>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>>> Software
> >>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>> http://pivotal.io/>
> >>>>> |
> >>>>>> ctzolov@pivotal.io
> >>>>>>
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >> Software
> >>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> http://pivotal.io/>
> >> |
> >>>> ctzolov@pivotal.io
>
>

Re: About the Spatial functionality

Posted by Julian Hyde <jh...@apache.org>.

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 <va...@gmail.com> 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 <jhyde@apache.org <ma...@apache.org>> wrote:
> 
>> You’re basically running into https://issues.apache.org/ <https://issues.apache.org/>
>> jira/browse/CALCITE-2072 <https://issues.apache.org/ <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/ <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 <va...@gmail.com>
>> 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.<String>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 <ct...@pivotal.io>
>>> 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.krishna@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.<String>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(<NUMERIC>,
>> <NUMERIC>,
>>>>> <NUMERIC>)
>>>>> 
>>>>> 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 <ct...@pivotal.io>
>>>>> 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 <jh...@apache.org> wrote:
>>>>>> 
>>>>>>> The Natural earth dataset (which https://github.com/zzolo/geo_
>>>>>>> simple_countries_wkt_csv <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 <ct...@pivotal.io>
>>>>>> 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 <jhyde@apache.org
>>>> <mailto:
>>>>>>> jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
>>>>>>> countries/USA><https://github.com/johan/world.geo.json/tree/
>>>>>>> master/countries/USA <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 <mmior@uwaterloo.ca
>>>>>> <mailto:
>>>>>>> mmior@uwaterloo.ca>> wrote:
>>>>>>>>> 
>>>>>>>>> Sounds like a good idea. I logged
>>>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072> <
>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
>>>>>>> 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
>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
>>>>> mmior@apache.org
>>>>>>> <ma...@apache.org>>
>>>>>>>>> 
>>>>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org
>>>> <mailto:
>>>>>>> jhyde@apache.org> <mailto:jhyde@apache.org <mailto:jhyde@apache.org
>>>>>>>> :
>>>>>>>>> 
>>>>>>>>>> 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> <
>>>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <
>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354> <
>>>>>>> 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 <mmior@uwaterloo.ca
>>>>>>> <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
>>>>>>> mmior@uwaterloo.ca>>> 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
>>>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
>>>>>> mmior@apache.org
>>>>>>> <ma...@apache.org>>
>>>>>>>>>>> 
>>>>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
>>>> ctzolov@pivotal.io
>>>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
>>>>>>> ctzolov@pivotal.io>>>:
>>>>>>>>>>> 
>>>>>>>>>>>> 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 <
>>>>>> ctzolov@pivotal.io
>>>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
>>>>>>> ctzolov@pivotal.io>>>
>>>>>>>>>> wrote:
>>>>>>>>>>>> 
>>>>>>>>>>>>> Unfortunately it didn't help still get " No match found for
>>>>>>> function
>>>>>>>>>>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
>>>>>>>>>>>>> .
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 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 <
>>>> mmior@uwaterloo.ca
>>>>>>> <ma...@uwaterloo.ca>> wrote:
>>>>>>>>>>>>> 
>>>>>>>>>>>>>> I believe that should work. I'll let others correct me if
>>>> I'm
>>>>>>> missing
>>>>>>>>>>>> the
>>>>>>>>>>>>>> boat here.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
>>>>> ctzolov@pivotal.io
>>>>>>> <ma...@pivotal.io>>:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>>>  
>>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> On 28 November 2017 at 16:58, Michael Mior <
>>>>> mmior@uwaterloo.ca
>>>>>>> <ma...@uwaterloo.ca>>
>>>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
>>>>>> ctzolov@pivotal.io
>>>>>>> <ma...@pivotal.io>>:
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 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(<NUMERIC>,
>>>>>>>>>>>>>>>> <NUMERIC>)"
>>>>>>>>>>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
>>>>>>>>>>>> ctzolov@pivotal.io <ma...@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 <
>>>>> jhyde@apache.org
>>>>>>> <ma...@apache.org>>
>>>>>>>>>>>>>> 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 <
>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>> Hey there,
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>> I'm exploring the new Spatial (
>>>>>>> https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
>>>>>>> http://www.linkedin.com/in/tzolov>> |
>>>>>>>>>>>> Principle
>>>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>>>> https://spring.io/
>>>>>>>> .io
>>>>>>> | Pivotal <
>>>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>>>> https://spring.io/
>>>>>>>> .io
>>>>>>> | Pivotal <
>>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>> |
>>>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>>> .io
>>>>>>> | Pivotal <
>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>> |
>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>> Software
>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>> .io
>>>>> |
>>>>>>> Pivotal <
>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> --
>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>> Software
>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>>> .io
>>>>> |
>>>>>>> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>> |
>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> --
>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
>>>>>>> http://pivotal.io/> | ctzolov@pivotal.io <mailto:ctzolov@pivotal.io
>>>>> <
>>>>>>> wkt-countries.json.zip>
>>>>>>> 
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> --
>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>>> Software
>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
>>>> http://pivotal.io/>
>>>>> |
>>>>>> ctzolov@pivotal.io
>>>>>> 
>>>>> 
>>>> 
>>>> 
>>>> 
>>>> --
>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>> Software
>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/>
>> |
>>>> ctzolov@pivotal.io

Re: About the Spatial functionality

Posted by Vamshi Krishna <va...@gmail.com>.

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/
> 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/
> 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 <va...@gmail.com>
> 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.<String>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 <ct...@pivotal.io>
> > 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.krishna@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.<String>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(<NUMERIC>,
> <NUMERIC>,
> >>> <NUMERIC>)
> >>>
> >>> 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 <ct...@pivotal.io>
> >>> 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 <jh...@apache.org> wrote:
> >>>>
> >>>>> The Natural earth dataset (which https://github.com/zzolo/geo_
> >>>>> simple_countries_wkt_csv <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 <ct...@pivotal.io>
> >>>> 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 <jhyde@apache.org
> >> <mailto:
> >>>>> jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
> >>>>> countries/USA><https://github.com/johan/world.geo.json/tree/
> >>>>> master/countries/USA <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 <mmior@uwaterloo.ca
> >>>> <mailto:
> >>>>> mmior@uwaterloo.ca>> wrote:
> >>>>>>>
> >>>>>>> Sounds like a good idea. I logged
> >>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
> >>>>> https://issues.apache.org/jira/browse/CALCITE-2072> <
> >>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
> >>>>> 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
> >>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
> >>> mmior@apache.org
> >>>>> <ma...@apache.org>>
> >>>>>>>
> >>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org
> >> <mailto:
> >>>>> jhyde@apache.org> <mailto:jhyde@apache.org <mailto:jhyde@apache.org
> >>>>>> :
> >>>>>>>
> >>>>>>>> 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> <
> >>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> >>>>> http://portal.opengeospatial.org/files/?artifact_id=25354> <
> >>>>> 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 <mmior@uwaterloo.ca
> >>>>> <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> >>>>> mmior@uwaterloo.ca>>> 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
> >>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
> >>>> mmior@apache.org
> >>>>> <ma...@apache.org>>
> >>>>>>>>>
> >>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
> >> ctzolov@pivotal.io
> >>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> >>>>> ctzolov@pivotal.io>>>:
> >>>>>>>>>
> >>>>>>>>>> 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 <
> >>>> ctzolov@pivotal.io
> >>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> >>>>> ctzolov@pivotal.io>>>
> >>>>>>>> wrote:
> >>>>>>>>>>
> >>>>>>>>>>> Unfortunately it didn't help still get " No match found for
> >>>>> function
> >>>>>>>>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> >>>>>>>>>>> .
> >>>>>>>>>>>
> >>>>>>>>>>> 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 <
> >> mmior@uwaterloo.ca
> >>>>> <ma...@uwaterloo.ca>> wrote:
> >>>>>>>>>>>
> >>>>>>>>>>>> I believe that should work. I'll let others correct me if
> >> I'm
> >>>>> missing
> >>>>>>>>>> the
> >>>>>>>>>>>> boat here.
> >>>>>>>>>>>>
> >>>>>>>>>>>> --
> >>>>>>>>>>>> Michael Mior
> >>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>>>>>>>
> >>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
> >>> ctzolov@pivotal.io
> >>>>> <ma...@pivotal.io>>:
> >>>>>>>>>>>>
> >>>>>>>>>>>>> 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.<String>of(),
> >>>>>>>>>>>>>  
> >>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>>>>>>
> >>>>>>>>>>>>> On 28 November 2017 at 16:58, Michael Mior <
> >>> mmior@uwaterloo.ca
> >>>>> <ma...@uwaterloo.ca>>
> >>>>>>>>>> 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.<String>of(),
> >>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>> --
> >>>>>>>>>>>>>> Michael Mior
> >>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
> >>>> ctzolov@pivotal.io
> >>>>> <ma...@pivotal.io>>:
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> 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(<NUMERIC>,
> >>>>>>>>>>>>>> <NUMERIC>)"
> >>>>>>>>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> >>>>>>>>>> ctzolov@pivotal.io <ma...@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 <
> >>> jhyde@apache.org
> >>>>> <ma...@apache.org>>
> >>>>>>>>>>>> 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 <
> >>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>> wrote:
> >>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>> Hey there,
> >>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>> I'm exploring the new Spatial (
> >>>>> https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> >>>>> http://www.linkedin.com/in/tzolov>> |
> >>>>>>>>>> Principle
> >>>>>>>>>>>>>>> Software
> >>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
> >> https://spring.io/
> >>>>>> .io
> >>>>> | Pivotal <
> >>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>> --
> >>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>>>>>> Software
> >>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
> >> https://spring.io/
> >>>>>> .io
> >>>>> | Pivotal <
> >>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>>>> |
> >>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>> --
> >>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>>>> Software
> >>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>>> .io
> >>>>> | Pivotal <
> >>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>>> |
> >>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>>
> >>>>>>>>>>> --
> >>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>>>> Software
> >>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>> .io
> >>> |
> >>>>> Pivotal <
> >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> --
> >>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> >>>>> http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>> Software
> >>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>>> .io
> >>> |
> >>>>> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>> |
> >>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> >>>>> http://pivotal.io/> | ctzolov@pivotal.io <mailto:ctzolov@pivotal.io
> >>> <
> >>>>> wkt-countries.json.zip>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> >>> Software
> >>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
> >> http://pivotal.io/>
> >>> |
> >>>> ctzolov@pivotal.io
> >>>>
> >>>
> >>
> >>
> >>
> >> --
> >> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> Software
> >> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/>
> |
> >> ctzolov@pivotal.io
> >>
>
>

Re: About the Spatial functionality

Posted by Julian Hyde <jh...@apache.org>.

You’re basically running into 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/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 <va...@gmail.com> 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.<String>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 <ct...@pivotal.io>
> 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 <va...@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.<String>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(<NUMERIC>, <NUMERIC>,
>>> <NUMERIC>)
>>> 
>>> 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 <ct...@pivotal.io>
>>> 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 <jh...@apache.org> wrote:
>>>> 
>>>>> The Natural earth dataset (which https://github.com/zzolo/geo_
>>>>> simple_countries_wkt_csv <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 <ct...@pivotal.io>
>>>> 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 <jhyde@apache.org
>> <mailto:
>>>>> jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
>>>>> countries/USA><https://github.com/johan/world.geo.json/tree/
>>>>> master/countries/USA <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 <mmior@uwaterloo.ca
>>>> <mailto:
>>>>> mmior@uwaterloo.ca>> wrote:
>>>>>>> 
>>>>>>> Sounds like a good idea. I logged
>>>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
>>>>> https://issues.apache.org/jira/browse/CALCITE-2072> <
>>>>> https://issues.apache.org/jira/browse/CALCITE-2072 <
>>>>> 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
>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
>>> mmior@apache.org
>>>>> <ma...@apache.org>>
>>>>>>> 
>>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org
>> <mailto:
>>>>> jhyde@apache.org> <mailto:jhyde@apache.org <mailto:jhyde@apache.org
>>>>>> :
>>>>>>> 
>>>>>>>> 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> <
>>>>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354 <
>>>>> http://portal.opengeospatial.org/files/?artifact_id=25354> <
>>>>> 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 <mmior@uwaterloo.ca
>>>>> <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
>>>>> mmior@uwaterloo.ca>>> 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
>>>>>>>>> mmior@apache.org <ma...@apache.org> <mailto:
>>>> mmior@apache.org
>>>>> <ma...@apache.org>>
>>>>>>>>> 
>>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
>> ctzolov@pivotal.io
>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
>>>>> ctzolov@pivotal.io>>>:
>>>>>>>>> 
>>>>>>>>>> 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 <
>>>> ctzolov@pivotal.io
>>>>> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
>>>>> ctzolov@pivotal.io>>>
>>>>>>>> wrote:
>>>>>>>>>> 
>>>>>>>>>>> Unfortunately it didn't help still get " No match found for
>>>>> function
>>>>>>>>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
>>>>>>>>>>> .
>>>>>>>>>>> 
>>>>>>>>>>> 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 <
>> mmior@uwaterloo.ca
>>>>> <ma...@uwaterloo.ca>> wrote:
>>>>>>>>>>> 
>>>>>>>>>>>> I believe that should work. I'll let others correct me if
>> I'm
>>>>> missing
>>>>>>>>>> the
>>>>>>>>>>>> boat here.
>>>>>>>>>>>> 
>>>>>>>>>>>> --
>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>>>>> 
>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
>>> ctzolov@pivotal.io
>>>>> <ma...@pivotal.io>>:
>>>>>>>>>>>> 
>>>>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>  
>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>> 
>>>>>>>>>>>>> On 28 November 2017 at 16:58, Michael Mior <
>>> mmior@uwaterloo.ca
>>>>> <ma...@uwaterloo.ca>>
>>>>>>>>>> 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.<String>of(),
>>>>>>>>>>>>>> GeoFunctions.class.getName(), "*", true);
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>>>> mmior@apache.org <ma...@apache.org>
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
>>>> ctzolov@pivotal.io
>>>>> <ma...@pivotal.io>>:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 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(<NUMERIC>,
>>>>>>>>>>>>>> <NUMERIC>)"
>>>>>>>>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
>>>>>>>>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
>>>>>>>>>> ctzolov@pivotal.io <ma...@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 <
>>> jhyde@apache.org
>>>>> <ma...@apache.org>>
>>>>>>>>>>>> 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 <
>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> Hey there,
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> I'm exploring the new Spatial (
>>>>> https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
>>>>> http://www.linkedin.com/in/tzolov>> |
>>>>>>>>>> Principle
>>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>> https://spring.io/
>>>>>> .io
>>>>> | Pivotal <
>>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>>>> Software
>>>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <
>> https://spring.io/
>>>>>> .io
>>>>> | Pivotal <
>>>>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>>>> |
>>>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>>>> Software
>>>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>>> .io
>>>>> | Pivotal <
>>>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>>> |
>>>>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> --
>>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>>>> Software
>>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>> .io
>>> |
>>>>> Pivotal <
>>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> --
>>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
>>>>> http://www.linkedin.com/in/tzolov>> | Principle
>>>>>>>> Software
>>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
>>>> .io
>>> |
>>>>> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
>>>>>>>> |
>>>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> --
>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
>>>>> http://pivotal.io/> | ctzolov@pivotal.io <mailto:ctzolov@pivotal.io
>>> <
>>>>> wkt-countries.json.zip>
>>>>> 
>>>>> 
>>>> 
>>>> 
>>>> --
>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
>>> Software
>>>> Engineer | Spring <https://spring.io/>.io | Pivotal <
>> http://pivotal.io/>
>>> |
>>>> ctzolov@pivotal.io
>>>> 
>>> 
>> 
>> 
>> 
>> --
>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
>> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
>> ctzolov@pivotal.io
>>

Re: About the Spatial functionality

Posted by Vamshi Krishna <va...@gmail.com>.

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.<String>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 <ct...@pivotal.io>
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 <va...@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.<String>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(<NUMERIC>, <NUMERIC>,
> > <NUMERIC>)
> >
> > 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 <ct...@pivotal.io>
> > 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 <jh...@apache.org> wrote:
> > >
> > > > The Natural earth dataset (which https://github.com/zzolo/geo_
> > > > simple_countries_wkt_csv <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 <ct...@pivotal.io>
> > > 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 <jhyde@apache.org
> <mailto:
> > > > jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
> > > > countries/USA><https://github.com/johan/world.geo.json/tree/
> > > > master/countries/USA <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 <mmior@uwaterloo.ca
> > > <mailto:
> > > > mmior@uwaterloo.ca>> wrote:
> > > > > >
> > > > > > Sounds like a good idea. I logged
> > > > > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > > > https://issues.apache.org/jira/browse/CALCITE-2072> <
> > > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > > > 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
> > > > > > mmior@apache.org <ma...@apache.org> <mailto:
> > mmior@apache.org
> > > > <ma...@apache.org>>
> > > > > >
> > > > > > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org
> <mailto:
> > > > jhyde@apache.org> <mailto:jhyde@apache.org <mailto:jhyde@apache.org
> > >>>:
> > > > > >
> > > > > >> 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> <
> > > > > >> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> > > > http://portal.opengeospatial.org/files/?artifact_id=25354> <
> > > > 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 <mmior@uwaterloo.ca
> > > > <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> > > > mmior@uwaterloo.ca>>> 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
> > > > > >>> mmior@apache.org <ma...@apache.org> <mailto:
> > > mmior@apache.org
> > > > <ma...@apache.org>>
> > > > > >>>
> > > > > >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <
> ctzolov@pivotal.io
> > > > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > > > ctzolov@pivotal.io>>>:
> > > > > >>>
> > > > > >>>> 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 <
> > > ctzolov@pivotal.io
> > > > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > > > ctzolov@pivotal.io>>>
> > > > > >> wrote:
> > > > > >>>>
> > > > > >>>>> Unfortunately it didn't help still get " No match found for
> > > > function
> > > > > >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> > > > > >>>>> .
> > > > > >>>>>
> > > > > >>>>> 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 <
> mmior@uwaterloo.ca
> > > > <ma...@uwaterloo.ca>> wrote:
> > > > > >>>>>
> > > > > >>>>>> I believe that should work. I'll let others correct me if
> I'm
> > > > missing
> > > > > >>>> the
> > > > > >>>>>> boat here.
> > > > > >>>>>>
> > > > > >>>>>> --
> > > > > >>>>>> Michael Mior
> > > > > >>>>>> mmior@apache.org <ma...@apache.org>
> > > > > >>>>>>
> > > > > >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
> > ctzolov@pivotal.io
> > > > <ma...@pivotal.io>>:
> > > > > >>>>>>
> > > > > >>>>>>> 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.<String>of(),
> > > > > >>>>>>>  
> > > > > >>>>>>> GeoFunctions.class.getName(), "*", true);
> > > > > >>>>>>>
> > > > > >>>>>>> On 28 November 2017 at 16:58, Michael Mior <
> > mmior@uwaterloo.ca
> > > > <ma...@uwaterloo.ca>>
> > > > > >>>> 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.<String>of(),
> > > > > >>>>>>>> GeoFunctions.class.getName(), "*", true);
> > > > > >>>>>>>>
> > > > > >>>>>>>> --
> > > > > >>>>>>>> Michael Mior
> > > > > >>>>>>>> mmior@apache.org <ma...@apache.org>
> > > > > >>>>>>>>
> > > > > >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
> > > ctzolov@pivotal.io
> > > > <ma...@pivotal.io>>:
> > > > > >>>>>>>>
> > > > > >>>>>>>>> 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(<NUMERIC>,
> > > > > >>>>>>>> <NUMERIC>)"
> > > > > >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> > > > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> > > > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> > > > > >>>>>>>>>
> > > > > >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> > > > > >>>> ctzolov@pivotal.io <ma...@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 <
> > jhyde@apache.org
> > > > <ma...@apache.org>>
> > > > > >>>>>> 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 <
> > > > > >>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > > >>>>>>>>
> > > > > >>>>>>>>>>> wrote:
> > > > > >>>>>>>>>>>>
> > > > > >>>>>>>>>>>> Hey there,
> > > > > >>>>>>>>>>>>
> > > > > >>>>>>>>>>>> I'm exploring the new Spatial (
> > > > https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> > > > http://www.linkedin.com/in/tzolov>> |
> > > > > >>>> Principle
> > > > > >>>>>>>>> Software
> > > > > >>>>>>>>>> Engineer | Spring <https://spring.io/ <
> https://spring.io/
> > > >>.io
> > > > | Pivotal <
> > > > > >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > > >>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > > > >>>>>>>>>>
> > > > > >>>>>>>>>
> > > > > >>>>>>>>>
> > > > > >>>>>>>>>
> > > > > >>>>>>>>> --
> > > > > >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > > >>>>>>>> Software
> > > > > >>>>>>>>> Engineer | Spring <https://spring.io/ <
> https://spring.io/
> > > >>.io
> > > > | Pivotal <
> > > > > >>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > > >>>>>>>> |
> > > > > >>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > > >>>>>>>>>
> > > > > >>>>>>>>
> > > > > >>>>>>>
> > > > > >>>>>>>
> > > > > >>>>>>>
> > > > > >>>>>>> --
> > > > > >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > > >>>>>> Software
> > > > > >>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> > >>.io
> > > > | Pivotal <
> > > > > >>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > > >>>>>> |
> > > > > >>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > > >>>>>>>
> > > > > >>>>>>
> > > > > >>>>>
> > > > > >>>>>
> > > > > >>>>>
> > > > > >>>>> --
> > > > > >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > > >>>> Software
> > > > > >>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>.io
> > |
> > > > Pivotal <
> > > > > >> http://pivotal.io/ <http://pivotal.io/>>
> > > > > >>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > > > >>>>>
> > > > > >>>>
> > > > > >>>>
> > > > > >>>>
> > > > > >>>> --
> > > > > >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > > >> Software
> > > > > >>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>.io
> > |
> > > > Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> > > > > >> |
> > > > > >>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> > > > Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> > > > http://pivotal.io/> | ctzolov@pivotal.io <mailto:ctzolov@pivotal.io
> ><
> > > > wkt-countries.json.zip>
> > > >
> > > >
> > >
> > >
> > > --
> > > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> > Software
> > > Engineer | Spring <https://spring.io/>.io | Pivotal <
> http://pivotal.io/>
> > |
> > > ctzolov@pivotal.io
> > >
> >
>
>
>
> --
> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
> ctzolov@pivotal.io
>

Re: About the Spatial functionality

Posted by Christian Tzolov <ct...@pivotal.io>.

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 <va...@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.<String>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(<NUMERIC>, <NUMERIC>,
> <NUMERIC>)
>
> 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 <ct...@pivotal.io>
> 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 <jh...@apache.org> wrote:
> >
> > > The Natural earth dataset (which https://github.com/zzolo/geo_
> > > simple_countries_wkt_csv <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 <ct...@pivotal.io>
> > 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 <jhyde@apache.org <mailto:
> > > jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
> > > countries/USA><https://github.com/johan/world.geo.json/tree/
> > > master/countries/USA <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 <mmior@uwaterloo.ca
> > <mailto:
> > > mmior@uwaterloo.ca>> wrote:
> > > > >
> > > > > Sounds like a good idea. I logged
> > > > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > > https://issues.apache.org/jira/browse/CALCITE-2072> <
> > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > > 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
> > > > > mmior@apache.org <ma...@apache.org> <mailto:
> mmior@apache.org
> > > <ma...@apache.org>>
> > > > >
> > > > > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <mailto:
> > > jhyde@apache.org> <mailto:jhyde@apache.org <mailto:jhyde@apache.org
> >>>:
> > > > >
> > > > >> 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> <
> > > > >> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> > > http://portal.opengeospatial.org/files/?artifact_id=25354> <
> > > 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 <mmior@uwaterloo.ca
> > > <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> > > mmior@uwaterloo.ca>>> 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
> > > > >>> mmior@apache.org <ma...@apache.org> <mailto:
> > mmior@apache.org
> > > <ma...@apache.org>>
> > > > >>>
> > > > >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> > > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > > ctzolov@pivotal.io>>>:
> > > > >>>
> > > > >>>> 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 <
> > ctzolov@pivotal.io
> > > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > > ctzolov@pivotal.io>>>
> > > > >> wrote:
> > > > >>>>
> > > > >>>>> Unfortunately it didn't help still get " No match found for
> > > function
> > > > >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> > > > >>>>> .
> > > > >>>>>
> > > > >>>>> 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 <mmior@uwaterloo.ca
> > > <ma...@uwaterloo.ca>> wrote:
> > > > >>>>>
> > > > >>>>>> I believe that should work. I'll let others correct me if I'm
> > > missing
> > > > >>>> the
> > > > >>>>>> boat here.
> > > > >>>>>>
> > > > >>>>>> --
> > > > >>>>>> Michael Mior
> > > > >>>>>> mmior@apache.org <ma...@apache.org>
> > > > >>>>>>
> > > > >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <
> ctzolov@pivotal.io
> > > <ma...@pivotal.io>>:
> > > > >>>>>>
> > > > >>>>>>> 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.<String>of(),
> > > > >>>>>>>  
> > > > >>>>>>> GeoFunctions.class.getName(), "*", true);
> > > > >>>>>>>
> > > > >>>>>>> On 28 November 2017 at 16:58, Michael Mior <
> mmior@uwaterloo.ca
> > > <ma...@uwaterloo.ca>>
> > > > >>>> 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.<String>of(),
> > > > >>>>>>>> GeoFunctions.class.getName(), "*", true);
> > > > >>>>>>>>
> > > > >>>>>>>> --
> > > > >>>>>>>> Michael Mior
> > > > >>>>>>>> mmior@apache.org <ma...@apache.org>
> > > > >>>>>>>>
> > > > >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
> > ctzolov@pivotal.io
> > > <ma...@pivotal.io>>:
> > > > >>>>>>>>
> > > > >>>>>>>>> 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(<NUMERIC>,
> > > > >>>>>>>> <NUMERIC>)"
> > > > >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> > > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> > > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> > > > >>>>>>>>>
> > > > >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> > > > >>>> ctzolov@pivotal.io <ma...@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 <
> jhyde@apache.org
> > > <ma...@apache.org>>
> > > > >>>>>> 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 <
> > > > >>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >>>>>>>>
> > > > >>>>>>>>>>> wrote:
> > > > >>>>>>>>>>>>
> > > > >>>>>>>>>>>> Hey there,
> > > > >>>>>>>>>>>>
> > > > >>>>>>>>>>>> I'm exploring the new Spatial (
> > > https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> > > http://www.linkedin.com/in/tzolov>> |
> > > > >>>> Principle
> > > > >>>>>>>>> Software
> > > > >>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> > >>.io
> > > | Pivotal <
> > > > >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > >>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >>>>>>>>>>
> > > > >>>>>>>>>
> > > > >>>>>>>>>
> > > > >>>>>>>>>
> > > > >>>>>>>>> --
> > > > >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > >>>>>>>> Software
> > > > >>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> > >>.io
> > > | Pivotal <
> > > > >>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > >>>>>>>> |
> > > > >>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >>>>>>>>>
> > > > >>>>>>>>
> > > > >>>>>>>
> > > > >>>>>>>
> > > > >>>>>>>
> > > > >>>>>>> --
> > > > >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > >>>>>> Software
> > > > >>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>.io
> > > | Pivotal <
> > > > >>>> http://pivotal.io/ <http://pivotal.io/>>
> > > > >>>>>> |
> > > > >>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >>>>>>>
> > > > >>>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>>
> > > > >>>>> --
> > > > >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > >>>> Software
> > > > >>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> |
> > > Pivotal <
> > > > >> http://pivotal.io/ <http://pivotal.io/>>
> > > > >>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > > >>>>>
> > > > >>>>
> > > > >>>>
> > > > >>>>
> > > > >>>> --
> > > > >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > > http://www.linkedin.com/in/tzolov>> | Principle
> > > > >> Software
> > > > >>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> |
> > > Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> > > > >> |
> > > > >>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> > > Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> > > http://pivotal.io/> | ctzolov@pivotal.io <ma...@pivotal.io><
> > > wkt-countries.json.zip>
> > >
> > >
> >
> >
> > --
> > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> Software
> > Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/>
> |
> > ctzolov@pivotal.io
> >
>



-- 
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
ctzolov@pivotal.io

Re: About the Spatial functionality

Posted by Vamshi Krishna <va...@gmail.com>.

Hello Team,

I have tried to use these functions by adding it to my schema as given
below:

ModelHandler.addFunctions(rootSchema, null, ImmutableList.<String>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(<NUMERIC>, <NUMERIC>,
<NUMERIC>)

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 <ct...@pivotal.io> 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 <jh...@apache.org> wrote:
>
> > The Natural earth dataset (which https://github.com/zzolo/geo_
> > simple_countries_wkt_csv <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 <ct...@pivotal.io>
> 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 <jhyde@apache.org <mailto:
> > jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
> > countries/USA><https://github.com/johan/world.geo.json/tree/
> > master/countries/USA <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 <mmior@uwaterloo.ca
> <mailto:
> > mmior@uwaterloo.ca>> wrote:
> > > >
> > > > Sounds like a good idea. I logged
> > > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > https://issues.apache.org/jira/browse/CALCITE-2072> <
> > https://issues.apache.org/jira/browse/CALCITE-2072 <
> > 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
> > > > mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org
> > <ma...@apache.org>>
> > > >
> > > > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <mailto:
> > jhyde@apache.org> <mailto:jhyde@apache.org <ma...@apache.org>>>:
> > > >
> > > >> 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> <
> > > >> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> > http://portal.opengeospatial.org/files/?artifact_id=25354> <
> > 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 <mmior@uwaterloo.ca
> > <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> > mmior@uwaterloo.ca>>> 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
> > > >>> mmior@apache.org <ma...@apache.org> <mailto:
> mmior@apache.org
> > <ma...@apache.org>>
> > > >>>
> > > >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > ctzolov@pivotal.io>>>:
> > > >>>
> > > >>>> 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 <
> ctzolov@pivotal.io
> > <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> > ctzolov@pivotal.io>>>
> > > >> wrote:
> > > >>>>
> > > >>>>> Unfortunately it didn't help still get " No match found for
> > function
> > > >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> > > >>>>> .
> > > >>>>>
> > > >>>>> 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 <mmior@uwaterloo.ca
> > <ma...@uwaterloo.ca>> wrote:
> > > >>>>>
> > > >>>>>> I believe that should work. I'll let others correct me if I'm
> > missing
> > > >>>> the
> > > >>>>>> boat here.
> > > >>>>>>
> > > >>>>>> --
> > > >>>>>> Michael Mior
> > > >>>>>> mmior@apache.org <ma...@apache.org>
> > > >>>>>>
> > > >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> > <ma...@pivotal.io>>:
> > > >>>>>>
> > > >>>>>>> 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.<String>of(),
> > > >>>>>>>  
> > > >>>>>>> GeoFunctions.class.getName(), "*", true);
> > > >>>>>>>
> > > >>>>>>> On 28 November 2017 at 16:58, Michael Mior <mmior@uwaterloo.ca
> > <ma...@uwaterloo.ca>>
> > > >>>> 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.<String>of(),
> > > >>>>>>>> GeoFunctions.class.getName(), "*", true);
> > > >>>>>>>>
> > > >>>>>>>> --
> > > >>>>>>>> Michael Mior
> > > >>>>>>>> mmior@apache.org <ma...@apache.org>
> > > >>>>>>>>
> > > >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <
> ctzolov@pivotal.io
> > <ma...@pivotal.io>>:
> > > >>>>>>>>
> > > >>>>>>>>> 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(<NUMERIC>,
> > > >>>>>>>> <NUMERIC>)"
> > > >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> > > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> > > >>>>>>>>>
> > > >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> > > >>>> ctzolov@pivotal.io <ma...@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 <jhyde@apache.org
> > <ma...@apache.org>>
> > > >>>>>> 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 <
> > > >>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > >>>>>>>>
> > > >>>>>>>>>>> wrote:
> > > >>>>>>>>>>>>
> > > >>>>>>>>>>>> Hey there,
> > > >>>>>>>>>>>>
> > > >>>>>>>>>>>> I'm exploring the new Spatial (
> > https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> > http://www.linkedin.com/in/tzolov>> |
> > > >>>> Principle
> > > >>>>>>>>> Software
> > > >>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>.io
> > | Pivotal <
> > > >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > >>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > >>>>>>>>>>
> > > >>>>>>>>>
> > > >>>>>>>>>
> > > >>>>>>>>>
> > > >>>>>>>>> --
> > > >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > http://www.linkedin.com/in/tzolov>> | Principle
> > > >>>>>>>> Software
> > > >>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/
> >>.io
> > | Pivotal <
> > > >>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > > >>>>>>>> |
> > > >>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > >>>>>>>>>
> > > >>>>>>>>
> > > >>>>>>>
> > > >>>>>>>
> > > >>>>>>>
> > > >>>>>>> --
> > > >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > http://www.linkedin.com/in/tzolov>> | Principle
> > > >>>>>> Software
> > > >>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> > | Pivotal <
> > > >>>> http://pivotal.io/ <http://pivotal.io/>>
> > > >>>>>> |
> > > >>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > > >>>>>>>
> > > >>>>>>
> > > >>>>>
> > > >>>>>
> > > >>>>>
> > > >>>>> --
> > > >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > http://www.linkedin.com/in/tzolov>> | Principle
> > > >>>> Software
> > > >>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io |
> > Pivotal <
> > > >> http://pivotal.io/ <http://pivotal.io/>>
> > > >>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > > >>>>>
> > > >>>>
> > > >>>>
> > > >>>>
> > > >>>> --
> > > >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> > http://www.linkedin.com/in/tzolov>> | Principle
> > > >> Software
> > > >>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io |
> > Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> > > >> |
> > > >>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > >
> > >
> > >
> > >
> > > --
> > > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> > Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> > http://pivotal.io/> | ctzolov@pivotal.io <ma...@pivotal.io><
> > wkt-countries.json.zip>
> >
> >
>
>
> --
> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
> ctzolov@pivotal.io
>

Re: About the Spatial functionality

Posted by Christian Tzolov <ct...@pivotal.io>.

@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 <jh...@apache.org> wrote:

> The Natural earth dataset (which https://github.com/zzolo/geo_
> simple_countries_wkt_csv <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 <ct...@pivotal.io> 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 <jhyde@apache.org <mailto:
> jhyde@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/
> countries/USA><https://github.com/johan/world.geo.json/tree/
> master/countries/USA <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 <mmior@uwaterloo.ca <mailto:
> mmior@uwaterloo.ca>> wrote:
> > >
> > > Sounds like a good idea. I logged
> > > https://issues.apache.org/jira/browse/CALCITE-2072 <
> https://issues.apache.org/jira/browse/CALCITE-2072> <
> https://issues.apache.org/jira/browse/CALCITE-2072 <
> 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
> > > mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org
> <ma...@apache.org>>
> > >
> > > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org> <mailto:jhyde@apache.org <ma...@apache.org>>>:
> > >
> > >> 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> <
> > >> http://portal.opengeospatial.org/files/?artifact_id=25354 <
> http://portal.opengeospatial.org/files/?artifact_id=25354> <
> 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 <mmior@uwaterloo.ca
> <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <mailto:
> mmior@uwaterloo.ca>>> 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
> > >>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org
> <ma...@apache.org>>
> > >>>
> > >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> ctzolov@pivotal.io>>>:
> > >>>
> > >>>> 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 <ctzolov@pivotal.io
> <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <mailto:
> ctzolov@pivotal.io>>>
> > >> wrote:
> > >>>>
> > >>>>> Unfortunately it didn't help still get " No match found for
> function
> > >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> > >>>>> .
> > >>>>>
> > >>>>> 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 <mmior@uwaterloo.ca
> <ma...@uwaterloo.ca>> wrote:
> > >>>>>
> > >>>>>> I believe that should work. I'll let others correct me if I'm
> missing
> > >>>> the
> > >>>>>> boat here.
> > >>>>>>
> > >>>>>> --
> > >>>>>> Michael Mior
> > >>>>>> mmior@apache.org <ma...@apache.org>
> > >>>>>>
> > >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> <ma...@pivotal.io>>:
> > >>>>>>
> > >>>>>>> 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.<String>of(),
> > >>>>>>>  
> > >>>>>>> GeoFunctions.class.getName(), "*", true);
> > >>>>>>>
> > >>>>>>> On 28 November 2017 at 16:58, Michael Mior <mmior@uwaterloo.ca
> <ma...@uwaterloo.ca>>
> > >>>> 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.<String>of(),
> > >>>>>>>> GeoFunctions.class.getName(), "*", true);
> > >>>>>>>>
> > >>>>>>>> --
> > >>>>>>>> Michael Mior
> > >>>>>>>> mmior@apache.org <ma...@apache.org>
> > >>>>>>>>
> > >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io
> <ma...@pivotal.io>>:
> > >>>>>>>>
> > >>>>>>>>> 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(<NUMERIC>,
> > >>>>>>>> <NUMERIC>)"
> > >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> > >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> > >>>>>>>>>
> > >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> > >>>> ctzolov@pivotal.io <ma...@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 <jhyde@apache.org
> <ma...@apache.org>>
> > >>>>>> 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 <
> > >>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > >>>>>>>>
> > >>>>>>>>>>> wrote:
> > >>>>>>>>>>>>
> > >>>>>>>>>>>> Hey there,
> > >>>>>>>>>>>>
> > >>>>>>>>>>>> I'm exploring the new Spatial (
> https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> |
> > >>>> Principle
> > >>>>>>>>> Software
> > >>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> | Pivotal <
> > >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > >>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > >>>>>>>>>>
> > >>>>>>>>>
> > >>>>>>>>>
> > >>>>>>>>>
> > >>>>>>>>> --
> > >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> | Principle
> > >>>>>>>> Software
> > >>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> | Pivotal <
> > >>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> > >>>>>>>> |
> > >>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > >>>>>>>>>
> > >>>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>> --
> > >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> | Principle
> > >>>>>> Software
> > >>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io
> | Pivotal <
> > >>>> http://pivotal.io/ <http://pivotal.io/>>
> > >>>>>> |
> > >>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> > >>>>>>>
> > >>>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>> --
> > >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> | Principle
> > >>>> Software
> > >>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io |
> Pivotal <
> > >> http://pivotal.io/ <http://pivotal.io/>>
> > >>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> > >>>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> --
> > >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> | Principle
> > >> Software
> > >>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io |
> Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> > >> |
> > >>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >
> >
> >
> >
> > --
> > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle
> Software Engineer | Spring <https://spring.io/>.io | Pivotal <
> http://pivotal.io/> | ctzolov@pivotal.io <ma...@pivotal.io><
> wkt-countries.json.zip>
>
>


-- 
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> |
ctzolov@pivotal.io

Re: About the Spatial functionality

Posted by Julian Hyde <jh...@apache.org>.

The Natural earth dataset (which https://github.com/zzolo/geo_simple_countries_wkt_csv <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 <ct...@pivotal.io> 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 <jhyde@apache.org <ma...@apache.org>> 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 <https://github.com/johan/world.geo.json/tree/master/countries/USA><https://github.com/johan/world.geo.json/tree/master/countries/USA <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 <mmior@uwaterloo.ca <ma...@uwaterloo.ca>> wrote:
> >
> > Sounds like a good idea. I logged
> > https://issues.apache.org/jira/browse/CALCITE-2072 <https://issues.apache.org/jira/browse/CALCITE-2072> <https://issues.apache.org/jira/browse/CALCITE-2072 <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
> > mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>>
> >
> > 2017-11-28 20:36 GMT-05:00 Julian Hyde <jhyde@apache.org <ma...@apache.org> <mailto:jhyde@apache.org <ma...@apache.org>>>:
> >
> >> 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> <
> >> http://portal.opengeospatial.org/files/?artifact_id=25354 <http://portal.opengeospatial.org/files/?artifact_id=25354> <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 <mmior@uwaterloo.ca <ma...@uwaterloo.ca> <mailto:mmior@uwaterloo.ca <ma...@uwaterloo.ca>>> 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
> >>> mmior@apache.org <ma...@apache.org> <mailto:mmior@apache.org <ma...@apache.org>>
> >>>
> >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>>:
> >>>
> >>>> 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 <ctzolov@pivotal.io <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>>
> >> wrote:
> >>>>
> >>>>> Unfortunately it didn't help still get " No match found for function
> >>>>> signature ST_Point(<NUMERIC>, <NUMERIC>)"
> >>>>> .
> >>>>>
> >>>>> 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 <mmior@uwaterloo.ca <ma...@uwaterloo.ca>> wrote:
> >>>>>
> >>>>>> I believe that should work. I'll let others correct me if I'm missing
> >>>> the
> >>>>>> boat here.
> >>>>>>
> >>>>>> --
> >>>>>> Michael Mior
> >>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>
> >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>>:
> >>>>>>
> >>>>>>> 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.<String>of(),
> >>>>>>>  
> >>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>
> >>>>>>> On 28 November 2017 at 16:58, Michael Mior <mmior@uwaterloo.ca <ma...@uwaterloo.ca>>
> >>>> 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.<String>of(),
> >>>>>>>> GeoFunctions.class.getName(), "*", true);
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> Michael Mior
> >>>>>>>> mmior@apache.org <ma...@apache.org>
> >>>>>>>>
> >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>>:
> >>>>>>>>
> >>>>>>>>> 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(<NUMERIC>,
> >>>>>>>> <NUMERIC>)"
> >>>>>>>>> (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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>> 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(<NUMERIC>, <NUMERIC>)
> >>>>>>>>> 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(<NUMERIC>, <NUMERIC>) (state=,code=0)
> >>>>>>>>>
> >>>>>>>>> On 28 November 2017 at 09:32, Christian Tzolov <
> >>>> ctzolov@pivotal.io <ma...@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 <jhyde@apache.org <ma...@apache.org>>
> >>>>>> 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 <
> >>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>
> >>>>>>>>>>> wrote:
> >>>>>>>>>>>>
> >>>>>>>>>>>> Hey there,
> >>>>>>>>>>>>
> >>>>>>>>>>>> I'm exploring the new Spatial (https://calcite.apache.org/do <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 <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> |
> >>>> Principle
> >>>>>>>>> Software
> >>>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io | Pivotal <
> >>>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> --
> >>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>>>> Software
> >>>>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io | Pivotal <
> >>>>>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>>>> |
> >>>>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> --
> >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Principle
> >>>>>> Software
> >>>>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io | Pivotal <
> >>>> http://pivotal.io/ <http://pivotal.io/>>
> >>>>>> |
> >>>>>>> ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Principle
> >>>> Software
> >>>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io | Pivotal <
> >> http://pivotal.io/ <http://pivotal.io/>>
> >>>>> | ctzolov@pivotal.io <ma...@pivotal.io>
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Principle
> >> Software
> >>>> Engineer | Spring <https://spring.io/ <https://spring.io/>>.io | Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> >> |
> >>>> ctzolov@pivotal.io <ma...@pivotal.io>
> 
> 
> 
> 
> -- 
> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> | ctzolov@pivotal.io <ma...@pivotal.io><wkt-countries.json.zip>