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 <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>> 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.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> > >>> wrote: > >>> > >>>> Hi Vamshi, > >>>> > >>>> Have you set the conformance to such that supports Geometry? i've been > >>>> using lenient like this: jdbc:calcite:conformance=LENIENT; > >>>> model=...my model > >>>> > >>>> > >>>> > >>>> > >>>> On 7 December 2017 at 13:53, Vamshi Krishna < > vamshi.v.kris...@gmail.com > >>> > >>>> wrote: > >>>> > >>>>> Hello Team, > >>>>> > >>>>> I have tried to use these functions by adding it to my schema as > given > >>>>> below: > >>>>> > >>>>> ModelHandler.addFunctions(rootSchema, null, > >> ImmutableList.<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 > > > >>>>> 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 <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>) 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>> 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 <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>>. 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>> > >>>>>>>>> > >>>>>>>>> 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 > >>>>>>>> : > >>>>>>>>> > >>>>>>>>>> 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 <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>> > >>>>>>>>>>> > >>>>>>>>>>> 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>>>: > >>>>>>>>>>> > >>>>>>>>>>>> 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>>> > >>>>>>>>>> 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>> 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> > >>>>>>>>>>>>>> > >>>>>>>>>>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov < > >>>>> 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>> > >>>>>>>>>>>> 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> > >>>>>>>>>>>>>>>> > >>>>>>>>>>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov < > >>>>>> 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>> > >>>>>>>>>>>>>>>> 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>> > >>>>>>>>>>>>>> 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> > >>>>>>>>>>>>>>>> > >>>>>>>>>>>>>>>>>>> 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/>> > >>>>>>>>>>>>>>>>>> | 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 > >
