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 <vamshi.v.kris...@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 <jh...@apache.org > <mailto: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 <vamshi.v.kris...@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 <jh...@apache.org >>> <mailto:jh...@apache.org> <mailto: >> jh...@apache.org <mailto:jh...@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.kris...@gmail.com >>>>> <mailto:vamshi.v.kris...@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 <ctzo...@pivotal.io >>>>> <mailto:ctzo...@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.kris...@gmail.com <mailto:vamshi.v.kris...@gmail.com> >>>>> >>>>>> wrote: >>>>>> >>>>>>> Hello Team, >>>>>>> >>>>>>> I have tried to use these functions by adding it to my schema as >> given >>>>>>> below: >>>>>>> >>>>>>> ModelHandler.addFunctions(rootSchema, null, >>>> ImmutableList.<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 <ctzo...@pivotal.io >>>>>>> <mailto:ctzo...@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 <jh...@apache.org >>>>>>>> <mailto:jh...@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 <ctzo...@pivotal.io >>>>>>>>>> <mailto:ctzo...@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 <jh...@apache.org >>>>>>>>>> <mailto:jh...@apache.org> >>>>>> <mailto: >>>>>>>>> jh...@apache.org <mailto: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/ >>>>>>>>>> <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 <mm...@uwaterloo.ca >>>>>>>>>>> <mailto:mm...@uwaterloo.ca> >>>>>>>> <mailto: >>>>>>>>> mm...@uwaterloo.ca <mailto: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> < >>>>>>>>> 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 >>>>>>>>>>> mm...@apache.org <mailto:mm...@apache.org> <mailto:mm...@apache.org >>>>>>>>>>> <mailto:mm...@apache.org>> <mailto: >>>>>>> mm...@apache.org <mailto:mm...@apache.org> >>>>>>>>> <mailto:mm...@apache.org <mailto:mm...@apache.org>>> >>>>>>>>>>> >>>>>>>>>>> 2017-11-28 20:36 GMT-05:00 Julian Hyde <jh...@apache.org >>>>>>>>>>> <mailto:jh...@apache.org> >>>>>> <mailto: >>>>>>>>> jh...@apache.org <mailto:jh...@apache.org>> <mailto:jh...@apache.org >>>>>>>>> <mailto:jh...@apache.org> <mailto: >> jh...@apache.org <mailto:jh...@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 <mm...@uwaterloo.ca >>>>>>>>>>>>> <mailto:mm...@uwaterloo.ca> >>>>>>>>> <mailto:mm...@uwaterloo.ca <mailto:mm...@uwaterloo.ca>> >>>>>>>>> <mailto:mm...@uwaterloo.ca <mailto:mm...@uwaterloo.ca> <mailto: >>>>>>>>> mm...@uwaterloo.ca <mailto:mm...@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 >>>>>>>>>>>>> mm...@apache.org <mailto:mm...@apache.org> >>>>>>>>>>>>> <mailto:mm...@apache.org <mailto:mm...@apache.org>> <mailto: >>>>>>>> mm...@apache.org <mailto:mm...@apache.org> >>>>>>>>> <mailto:mm...@apache.org <mailto:mm...@apache.org>>> >>>>>>>>>>>>> >>>>>>>>>>>>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov < >>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@pivotal.io>> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> <mailto: >>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@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 < >>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@pivotal.io>> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> <mailto: >>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@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 <mailto:mm...@uwaterloo.ca> >>>>>>>>> <mailto:mm...@uwaterloo.ca <mailto:mm...@uwaterloo.ca>>> wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> I believe that should work. I'll let others correct me if >>>>>> I'm >>>>>>>>> missing >>>>>>>>>>>>>> the >>>>>>>>>>>>>>>> boat here. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> -- >>>>>>>>>>>>>>>> Michael Mior >>>>>>>>>>>>>>>> mm...@apache.org <mailto:mm...@apache.org> >>>>>>>>>>>>>>>> <mailto:mm...@apache.org <mailto:mm...@apache.org>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov < >>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@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 <mailto:mm...@uwaterloo.ca> >>>>>>>>> <mailto:mm...@uwaterloo.ca <mailto: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 >>>>>>>>>>>>>>>>>> mm...@apache.org <mailto:mm...@apache.org> >>>>>>>>>>>>>>>>>> <mailto:mm...@apache.org <mailto:mm...@apache.org>> >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov < >>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@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 < >>>>>>>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@pivotal.io>>> >>>>>>>>>>>>>>>>>> wrote: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>> @Julian are there some tests, json datasets? Perhaps in >>>>>>>>>>>>>>>>>>>> calcite-test-dataset? >>>>>>>>>>>>>>>>>>>> Also I will try to cast the "loc" from Zips into DOUBLE >>>>>>>>> columns >>>>>>>>>>>>>> to >>>>>>>>>>>>>>>>> test >>>>>>>>>>>>>>>>>>>> the ST_Point >>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>> On 28 November 2017 at 02:24, Julian Hyde < >>>>>>> jh...@apache.org <mailto:jh...@apache.org> >>>>>>>>> <mailto:jh...@apache.org <mailto: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 < >>>>>>>>>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>>>>>>>>> <mailto:ctzo...@pivotal.io <mailto:ctzo...@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/>> >>>>>>>>>>>>>>>>>>>> | ctzo...@pivotal.io <mailto:ctzo...@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/>> >>>>>>>>>>>>>>>>>> | >>>>>>>>>>>>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@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/>> >>>>>>>>>>>>>>>> | >>>>>>>>>>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@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/>> >>>>>>>>>>>>>>> | ctzo...@pivotal.io <mailto:ctzo...@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/>> >>>>>>>>>>>> | >>>>>>>>>>>>>> ctzo...@pivotal.io <mailto:ctzo...@pivotal.io> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle >>>>>>>>> Software Engineer | Spring <https://spring.io/>.io | Pivotal < >>>>>>>>> http://pivotal.io/> | ctzo...@pivotal.io <mailto: >> ctzo...@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/> >>>>>>> | >>>>>>>> ctzo...@pivotal.io >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle >>>> Software >>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal < >> http://pivotal.io/> >>>> | >>>>>> ctzo...@pivotal.io
