@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
