Sounds like a good idea. I logged 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 [email protected] 2017-11-28 20:36 GMT-05:00 Julian Hyde <[email protected]>: > 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> > > > On Nov 28, 2017, at 1:11 PM, Michael Mior <[email protected]> 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 > > [email protected] > > > > 2017-11-28 14:18 GMT-05:00 Christian Tzolov <[email protected]>: > > > >> 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 <[email protected]> > 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 <[email protected]> wrote: > >>> > >>>> I believe that should work. I'll let others correct me if I'm missing > >> the > >>>> boat here. > >>>> > >>>> -- > >>>> Michael Mior > >>>> [email protected] > >>>> > >>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <[email protected]>: > >>>> > >>>>> 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 <[email protected]> > >> 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 > >>>>>> [email protected] > >>>>>> > >>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <[email protected]>: > >>>>>> > >>>>>>> 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 < > >> [email protected]> > >>>>>> 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 <[email protected]> > >>>> 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 < > >>>> [email protected] > >>>>>> > >>>>>>>>> wrote: > >>>>>>>>>> > >>>>>>>>>> Hey there, > >>>>>>>>>> > >>>>>>>>>> I'm exploring the new Spatial (https://calcite.apache.org/do > >>>>>>>>> cs/spatial.html) > >>>>>>>>>> functionality and i've been trying to figure out what are the > >>>>>> minimal > >>>>>>>>>> requirements for using it with my custom adapter. > >>>>>>>>>> > >>>>>>>>>> Following the guidelines i've set LENIENT conformance in my > >>>> jdbc > >>>>>> URL > >>>>>>> ( > >>>>>>>>>> jdbc:calcite:conformance=LENIENT; > >>>>>>>>>> model=...my model > >>>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> ) > >>>>>>>>>> > >>>>>>>>>> But I am not sure how define the GEOMETRY column types? > >>>>>>>>>> > >>>>>>>>>> Currently my custom Schema/Table factory implementation > >> infers > >>>> the > >>>>>>>>> column > >>>>>>>>>> types from the underlaying system's field types. > >>>>>>>>>> > >>>>>>>>>> So it seems that i need to change my implementation and > >>>> somehow to > >>>>>>> hint > >>>>>>>>>> which fields needs to be mapped to GEOMETRY types? Or > >> perhaps > >>>> i > >>>>> can > >>>>>>>>> try to > >>>>>>>>>> do some expensive casting in SQL? > >>>>>>>>>> > >>>>>>>>>> Are there any guidelines, examples for using Spatial > >>>>> functionality > >>>>>> on > >>>>>>>>> 3rd > >>>>>>>>>> party (e.g. custom) adapters? > >>>>>>>>>> > >>>>>>>>>> Thanks, > >>>>>>>>>> Christian > >>>>>>>>> > >>>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> -- > >>>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | > >> Principle > >>>>>>> Software > >>>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal < > >>>>>> http://pivotal.io/> > >>>>>>>> | [email protected] > >>>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> -- > >>>>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle > >>>>>> Software > >>>>>>> Engineer | Spring <https://spring.io/>.io | Pivotal < > >>>>> http://pivotal.io/> > >>>>>> | > >>>>>>> [email protected] > >>>>>>> > >>>>>> > >>>>> > >>>>> > >>>>> > >>>>> -- > >>>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle > >>>> Software > >>>>> Engineer | Spring <https://spring.io/>.io | Pivotal < > >> http://pivotal.io/> > >>>> | > >>>>> [email protected] > >>>>> > >>>> > >>> > >>> > >>> > >>> -- > >>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle > >> Software > >>> Engineer | Spring <https://spring.io/>.io | Pivotal < > http://pivotal.io/> > >>> | [email protected] > >>> > >> > >> > >> > >> -- > >> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle > Software > >> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> > | > >> [email protected] > >> > >
