Spatial Queries for MS SQL in Eclipselink JPA Expressions

The Problem: How to query Microsoft SQL Server 2008 Geography Data Type with Eclipselink Expressions?

Eclipselink provides an easy, extensible way of mapping relational data to java objects. But: it does not yet implement direct mapping of the geography data type that is available on Microsoft SQL Server 2008 R2 (byte array mapping is possible though).

Now this is  easy with a native query like this simple query, that selects all entries, that are within a buffer of 500m within a given route - the route being provided in the "Well Known Text" format:

SELECT * FROM [Address]  WHERE (GEOGRAPHY::Parse(
'LINESTRING (27.45 51.34, 25.45 50.214)'
).STBuffer( 500 ).STIntersects([LocationData])) = 1

This is ok, if you just want to query that specific case. I'd like to show, how we could make use of the object relational features, that the object oriented query would provide such as automatic mapping between different entities (joining the mapped tables), querying by example and dynamic building of queries with the ExpressionBuilder object.

The Approach: Extend JPA in a way that makes spatial queries possible

I chose to extend eclipselink in a way, so that the expression builder can be used to build queries with integrated searching for geospatial data. The next step would be to find a way to integrate the used functions into commonly known functions that can be used inside JPQL (Java Persistence Query Language) queries. The main point, why this is not possible at the moment is the "." in the notation (SELECT * FROM [Address]  WHERE (GEOGRAPHY::Parse(?).STBuffer(?).STIntersects([LocationData])) = 1) that uses .NET class objects that provide the spatial datatypes in the SQL Server at the moment. Eclipslink just throws a parse error when trying to use functions like ".STBuffer", which is pretty understandable in most cases. Please send me an email, if you can provide further info, on how to use functions like that.

The solution: Create an own Extension to org.eclipse.persistence.platform.database.SQLServerPlatform and use it

The solution I came up with is: extend the database platform and integrate a new function that takes three arguments as a vector. This advanced technique seems to circumvent the extensive syntax checking when using the getFunction method of the eclipselink expressions. This pattern can be easily extended to provide those basic functions that are missing in your current environment. If you switch the platform, you just have to implement those patters for the new database you are switching to. To use this new DatabasePlatform definition, you just have to specify it inside the persistence unit properties in your persistence.xml file:

..
<property name="javax.persistence.jdbc.url" value="jdbc:jtds:sqlserver://someserver:someport/somedatabas;instance=someintstance" />
<property name="eclipselink.target-database" value="de.itbalance.persistence.platform.database.SQLServerSpatialPlatform"/>
..

This is the code for the new DatabasePlatform extension:

public class SQLServerSpatialPlatform extends SQLServerPlatform {
    
    /**
     * ID for a function GEOGRAPHY::Parse( ? ).STBuffer( ? ).STIntersects( ? )
     *
     * where arguments are
     *
     * ?1: "LINESTRING (27.45 51.34, 25.45 50.214)" (OGS WKT - Well Know Text - format)
     * ?2: 500
     * ?3: <GEOGRAPHY-FIELD-NAME> in database table (MS SQL Geography Data Type)
     *
     * result is 1, if ?3 is inside the range of a buffer of ?2 meters around the geometric figure described in ?1
     *
     */
    public static final int FUNC_ISNEARGEO = 14000;
    
    
    /**
     *
     */
    private static final long serialVersionUID = -4972647627903866454L;
    
    public SQLServerSpatialPlatform() {
        super();
    }

    protected void initializePlatformOperators() {
        super.initializePlatformOperators();

        // create user-defined functions
        
        Vector<String> opStrings = new Vector<String>();
        opStrings.add("GEOGRAPHY::Parse(");
        opStrings.add(").STBuffer(");
        opStrings.add(").STIntersects(");
        opStrings.add(")");
        ExpressionOperator op = new ExpressionOperator();
        op.setSelector(FUNC_ISNEARGEO); //ExpressionOperator.*
        op.printsAs(opStrings);
        //op.bePrefix();
        op.setNodeClass(FunctionExpression.class);
    
        // make it available to this platform (only!)
        addOperator(op);
    }

}

The corresponding junit test function, that demonstrates the useage is:
  
    @SuppressWarnings("unchecked")
    @Test
    public void testExpressionsWithDatabasePlatform(){
        
        //pre-selections
        String lineString="LINESTRING (27.45 51.34, 25.45 50.214)";
        int buffer = 500;
        
        
        //get session and execute
        EntityManagerFactory factorySpatial;
        EntityManager emSpatial;
        factorySpatial = Persistence.createEntityManagerFactory("spatialBlogDemo");
        emSpatial = factorySpatial.createEntityManager();
        JpaEntityManager emImpl = (JpaEntityManager)emSpatial.getDelegate();

        //generate query with an expression builder
        ReadAllQuery query = new ReadAllQuery(Address.class);
        ExpressionBuilder builder = query.getExpressionBuilder();
        
        //prepare arguments for FUNC_ISNEARGEO
        Vector<Object> args = new Vector<Object>();
        args.add(lineString);
        args.add(Integer.toString(buffer));
        args.add(builder.getField("Location"));
    
        //set selection criteria
        query.setSelectionCriteria(builder.getFunction(SQLServerSpatialPlatform.FUNC_ISNEARGEO, args).equal(1));
        
        //create query
        TypedQuery<Address> jpaQuery = (TypedQuery<Address>) emImpl.createQuery(query);
        List<Address> list = jpaQuery.getResultList();
        
        //assert test result
        assertEquals(list.size()>0,true);
        emSpatial.close();
    }

So, this was pretty in-depht. Contact me, if you have any questions.