Geo-Spatial Functions

[Scott Young <sy...@gmail.com>]

Dear Devs,

I'm trying to implement the following functions from PostGIS in Calcite
using JTS. Two of the functions convert binary data from the column and
would usually be in the list of projections. The others are topological
boolean functions that would usually be in the list of selections. What is
the most straightforward and easy way to do this?




*Projections:| <ST_ASBINARY>,| <ST_ASTEXT>*

*Selections:*











*| <ST_DISTANCE>,| <ST_EQUALS>,| <ST_DISJOINT>,| <ST_INTERSECTS>,|
<ST_TOUCHES>,| <ST_CROSSES>,| <ST_OVERLAPS>,| <ST_CONTAINS>,| <ST_LENGTH>,|
<ST_AREA>,| <ST_CENTROID>*

*example query:*
*--DISTRICT table contains:*
*-- an ID primary key*
*-- a POLY **varbinary(4*1024*1024) field*
*--VOTER_HOME table contains:*
*-- an ID primary key*
*-- a VID foreign key for joining to a VOTER table*
*-- a POINT binary(21) field*
*SELECT D.ID <http://D.ID>, V.VID, ST_ASTEXT(D.POLY), ST_ASTEXT(V.POINT)*
*FROM DISTRICT D*
*INNER JOIN VOTER_HOME V ON ST_CONTAINS(D.POLY,V.POINT);--*

Re: Geo-Spatial Functions

[Julian Hyde <jh...@apache.org>]

First of all, can you create a JIRA case? It would help organize your work with any GIS-related work other people might be doing, now or in future.

Is it fair to say that some of the PostGIS functions appear to operate on rows but are actually relational operators (e.g. utilizing indexes)? (I’m not very familiar with GIS, but that is certainly the case for SQL full-text search, which is similar in some regards. For example, if I write “SELECT * FROM Employee WHERE resume CONTAINS ‘Java’”, it is not literally evaluating the CONTAINS function for each row.)

If so, you should be modeling such functions as relational rewrite rules. Other functions you might be able to model more simply; see UdfTest for examples of writing user-defined functions.

You probably already know this, but all functions and operators will need to be in SqlOperatorTable (either the built-in one, SqlStdOperatorTable, or an extension table) and depending on the syntax of the GIS functions you may or may not need to change the parser.

Julian





> On Apr 23, 2017, at 12:46 PM, Scott Young <sy...@gmail.com> wrote:
> 
> Dear Devs,
> 
> I'm trying to implement the following functions from PostGIS in Calcite
> using JTS. Two of the functions convert binary data from the column and
> would usually be in the list of projections. The others are topological
> boolean functions that would usually be in the list of selections. What is
> the most straightforward and easy way to do this?
> 
> 
> 
> 
> *Projections:| <ST_ASBINARY>,| <ST_ASTEXT>*
> 
> *Selections:*
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> *| <ST_DISTANCE>,| <ST_EQUALS>,| <ST_DISJOINT>,| <ST_INTERSECTS>,|
> <ST_TOUCHES>,| <ST_CROSSES>,| <ST_OVERLAPS>,| <ST_CONTAINS>,| <ST_LENGTH>,|
> <ST_AREA>,| <ST_CENTROID>*
> 
> *example query:*
> *--DISTRICT table contains:*
> *-- an ID primary key*
> *-- a POLY **varbinary(4*1024*1024) field*
> *--VOTER_HOME table contains:*
> *-- an ID primary key*
> *-- a VID foreign key for joining to a VOTER table*
> *-- a POINT binary(21) field*
> *SELECT D.ID <http://D.ID>, V.VID, ST_ASTEXT(D.POLY), ST_ASTEXT(V.POINT)*
> *FROM DISTRICT D*
> *INNER JOIN VOTER_HOME V ON ST_CONTAINS(D.POLY,V.POINT);--*