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.
