| Geo Spatial Queries with JPA [message #557616] |
Wed, 08 September 2010 06:27  |
Eclipse User |
|
|
|
Using the Criteria api, how can I go about using SQL Server 2008 GeoSpatial functions in my queries?
CriteriaBuilder.function() looks promising:
function
<T> Expression<T> function(java.lang.String name,
java.lang.Class<T> type,
Expression<?>... args)
Create an expression for the execution of a database function.
Parameters:
name - function name
type - expected result type
args - function arguments
Returns:
expression
It looks like I can use CriteriaBuilder.function() for static geometry functions, but how would I go about calling a geometry instance function of SQL Server?
Example: shape.STIntersects('POLYGON((1 2,3 4,5 6,1 2))')
Thanks
|
|
|
|
|
| (no subject) [message #726764 is a reply to message #558616] |
Mon, 19 September 2011 08:59  |
| Eclipse User |
|
|
|
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 foll blog post.
persistenc.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();
|
|
|
Powered by
FUDForum. Page generated in 0.03055 seconds
Home