[teiid-issues] [JBoss JIRA] (TEIID-3038) Add spatial query support to the Teiid MongoDB translator

[Michael Farwell (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12987092#comment-12987092 ] 

Michael Farwell commented on TEIID-3038:
----------------------------------------

We've taken a stab at implementing spatial support in the MongoDB translator, but we'd like to see it supported in a released version.  We've implemented a basic geoIntersects and geoWithin that works with a bounding box.

We added the following code to org.teiid.translator.mongodb.MongoDBSelectVisitor:

@Override
public void visit(Comparison obj) {
       
    // this for $cond in the select statement, and formatting of command for $cond vs $match is different
    if (this.processingDerivedColumn) {
       visitDerivedExpression(obj);
       return;
    }
    
    // NEW CODE   
    if (obj.getLeftExpression() instanceof Function) {
        Function function = (Function)obj.getLeftExpression();
        String fname = function.getName();
        if (fname.equals("geoIntersects") || fname.equals("geoWithin")) {
            handleGeospatial(function);
            return;
        }
    }
            
    // this for the normal where clause
    ColumnAlias exprAlias = getExpressionAlias(obj.getLeftExpression());
...

And the following method:

private void handleGeospatial(Function function) {
   List<Expression> args = function.getParameters();
   String fname = function.getName();
   if (args.size() == 5 && args.get(0) instanceof ColumnReference) {
       ColumnReference cr = (ColumnReference) args.get(0);
       String loc   = cr.getName();
       double north = Double.parseDouble(args.get(1).toString());
       double east  = Double.parseDouble(args.get(2).toString());
       double west  = Double.parseDouble(args.get(3).toString());
       double south = Double.parseDouble(args.get(4).toString());

       BasicDBList points = new BasicDBList();
       points.add(new double[]{west, north});
       points.add(new double[]{east, north});
       points.add(new double[]{east, south});
       points.add(new double[]{west, south});
       points.add(new double[]{west, north});
       BasicDBList parentList = new BasicDBList();
       parentList.add(points);

       DBObject search = BasicDBObjectBuilder.start()
          .push(loc)
              .push("$" + fname)
              .push("$geometry")
                   .add("type", "Polygon")
                   .add("coordinates", parentList)
          .get();

        this.onGoingExpression.push(search);
        this.onGoingPullCriteria.push(search);
    }
}

We declared the following foreign functions in our Dynamic VDB:  

CREATE FOREIGN FUNCTION geoIntersects (ref object, north double, east double, west double, south double) RETURNS boolean;
CREATE FOREIGN FUNCTION geoWithin (ref object, north double, east double, west double, south double) RETURNS boolean;

This allows us to make the following queries:

SELECT * FROM features WHERE geoIntersects(loc, 43.0, 11.0, 10.0, 42.0)


> Add spatial query support to the Teiid MongoDB translator
> ---------------------------------------------------------
>
>                 Key: TEIID-3038
>                 URL: https://issues.jboss.org/browse/TEIID-3038
>             Project: Teiid
>          Issue Type: Feature Request
>      Security Level: Public(Everyone can see) 
>          Components: Misc. Connectors
>    Affects Versions: 8.8
>            Reporter: Michael Farwell
>            Assignee: Steven Hawkins
>             Fix For: 8.8
>
>




--
This message was sent by Atlassian JIRA
(v6.2.6#6264)