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>
