CODEWITHSUNDEEP
mysqlhibernatespatialspatial-queryhibernate-spatial
user3529850
user3529850

Reputation: 976

Find points within a circle using hibernate Spatial criteria - distanceWithin

I have a domain object car. One of the car properties is location stored in MySQL as spatial type Point (columnDefinition can't be of Geometry, throws an exception). 

@Type(type="org.hibernate.spatial.GeometryType")
@Column(name = "location", columnDefinition="Point")
private Point location;

Using hibernate spatial criteria I would like to get those that are in a certain radius. In native sql I could use ST_Distance_Sphere, but I would like to use criteria instead. The problem is, that this gives me an error org.hibernate.HibernateException: dwithin function not supported by this dialect:

final Point circleCenterPoint = 
new GeometryFactory().createPoint(new Coordinate(latitude,longitude));

(...).add(SpatialRestrictions.distanceWithin(Car.LOCATION, circleCenterPoint, radiusInKm * 0.009));

I am using: jts-1.13, hibernate-core-4.3.4, hibernate-spatial-4.3 (according to that, they should match) as well as org.hibernate.spatial.dialect.mysql.MySQLSpatial56Dialect

Upvotes: 0

Views: 1659

Answers (2)

Mehmet Aydoğdu
Mehmet Aydoğdu

Reputation: 96

As a workaround to "dwithin function not supported by this dialect", I added dwithin function as below.

CREATE FUNCTION `dwithin`(`pt1` POINT, `pt2` POINT, `radius` FLOAT) RETURNS tinyint(1)
 DETERMINISTIC
BEGIN
 DECLARE dist FLOAT;
 SELECT ST_Distance_Sphere(pt1, pt2) INTO dist;
 RETURN dist<radius;
END

Upvotes: 0

Karel Maesen
Karel Maesen

Reputation: 891

The problem is simply that the function ST_Distance_Sphere is not supported in Hibernate Spatial version 4.x (or version 5.x for that matter).

I created a ticket (https://hibernate.atlassian.net/browse/HHH-12229) to update the MySQL Spatial dialects and add support for these newer functions. These changes will not be backported to version 4.x, however.

Upvotes: 2

Related Questions