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> (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> wrote: > > Sounds like a good idea. I logged > 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> > > 2017-11-28 20:36 GMT-05:00 Julian Hyde <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>> >> >>> On Nov 28, 2017, at 1:11 PM, Michael Mior <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> >>> >>> 2017-11-28 14:18 GMT-05:00 Christian Tzolov <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>> >> 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> wrote: >>>>> >>>>>> I believe that should work. I'll let others correct me if I'm missing >>>> the >>>>>> boat here. >>>>>> >>>>>> -- >>>>>> Michael Mior >>>>>> mm...@apache.org >>>>>> >>>>>> 2017-11-28 12:31 GMT-05:00 Christian Tzolov <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> >>>> 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 >>>>>>>> >>>>>>>> 2017-11-28 4:27 GMT-05:00 Christian Tzolov <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> >>>>>>>> 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> >>>>>> 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 >>>>>>>> >>>>>>>>>>> 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/> >>>>>>>>>> | 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 >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> 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 >>>>> >>>> >>>> >>>> >>>> -- >>>> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle >> Software >>>> Engineer | Spring <https://spring.io/>.io | Pivotal <http://pivotal.io/> >> | >>>> ctzo...@pivotal.io
