|
Re: Geo Spatial Queries with JPA [message #558616 is a reply to message #557616] |
Mon, 13 September 2010 17:12 |
|
Criteria.function() will not allow generating SQL like this, but you should be able to use EclipseLink ExpressionOperators and Expressions for this. Criteria just wrap Expressions in EclipseLink, so you could probably even mix the two.
Otherwise use native SQL.
James : Wiki : Book : Blog : Twitter
|
|
|
Re: Geo Spatial Queries with JPA [message #726757 is a reply to message #558616] |
Mon, 19 September 2011 12:59 |
Arne Menting Messages: 2 Registered: September 2011 Location: Paderborn |
Junior Member |
|
|
Many thanks for the nudge in the right direction, here is one possibility to do what you wanted to do: just visit techdriveactive.blogspot.com to get the full blog post.
persistence.xml:
<property name="eclipselink.target-database" value="de.itbalance.persistence.platform.database.SQLServerSpatialPlatform"/>
DatabasePlatform extension that adds a spatial function for MS SQL Server:
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);
}
}
useage:
//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();
[Updated on: Mon, 19 September 2011 13:01] Report message to a moderator
|
|
|
|
Powered by
FUDForum. Page generated in 0.02541 seconds