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 >
