Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Geo Spatial Queries with JPA
Geo Spatial Queries with JPA [message #557616] Wed, 08 September 2010 10:27 Go to next message
No real name is currently offline No real nameFriend
Messages: 1
Registered: September 2010
Junior Member
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
Re: Geo Spatial Queries with JPA [message #558616 is a reply to message #557616] Mon, 13 September 2010 17:12 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 Go to previous messageGo to next message
Arne Menting is currently offline Arne MentingFriend
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

(no subject) [message #726764 is a reply to message #558616] Mon, 19 September 2011 12:59 Go to previous message
Arne Menting is currently offline Arne MentingFriend
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 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();
Previous Topic:(no subject)
Next Topic:When an Entity has two different relationships to another Entity which is identified by Compound PKs
Goto Forum:
  


Current Time: Sun Dec 21 12:09:28 GMT 2014

Powered by FUDForum. Page generated in 0.10361 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software