Hibernate-Spatial Query for Points within an area

Question

Dear stackoverflow readers,

I'm currently working on an application that has the requirement to load specific items based on its coordinates. Example: I want all shops from coordinates x or within 15 km of it.

I have been building this web application in Java EE 6 and used Hibernate 3.3 for data persistence to my Postgres database. After some reseach I found Hibernate-Spatial as an possibility to achieve my goal. I installed Postgis and have Hibernate-Spatial running.

I'm using Hibernate 3.3, Hibernate-Spatial 1.0, PostgreSQL 9.2 and postgis-jdbc 1.3.3.

I created an entity which has a location field:

@Column(columnDefinition = "Geometry", nullable = true) 
@Type(type = "org.hibernatespatial.GeometryUserType")
private Point location;

Initially I thought I could create a Circle and basically query on every point which is located in the circle. But this seems harder than I thought. So hard that I'm starting to think I made the wrong choice of using Hibernate-Spatial.

My question is if it's possible by using Hibernate-Spatial to query on every Point within a specific boundry (coordinates x and y km's from there).

I'm also interested in other possibilities to solve my requirement. I've been thinking about the following possibilities:

• Just use a Native query on PostgreSQL, map those results to entities and use those in the other parts of the application (Basically remove Hibernate-Spatial and use native queries).
• Switch to Hibernate-Search and use Lucene to fulfill my task.
• Something completely else

Can anyone please provide an example of how I can achieve my wish in Hibernate-Spatial or how I can achieve my requirement in another way.

Othaothe

Answer 1

For the PostgreSQL/PostGIS dialect, Hibernate Spatial supports the dwithin function.

boolean dwithin(Geometry, Geometry, double) - Returns true if the geometries are within the specified distance of one another

Paraphrasing the Hibernate Spatial tutorial, the query would look something like this:

String wktCenterPoint = "POINT(10 15)"
Geometry centerPoint = wktToGeometry(wktCenterPoint);    
...
Query query = em.createQuery("select e from Event e where dwithin(e.location, :centerPoint, 15) = true", Event.class);
...

This would return all events within 15 units from the center point.

However the distance given in dwithin will have the same units as the underlying dataset, not necessarily kilometers. If the dataset units are meters, the distance will be treated as meters.

If the dataset is in lat-lng, the distance will be degrees. If this is the case, and your search distance is small (15 Km is small), and you are at low latitudes, you can approximate a Km as 0.009. This will give you a perfect search circle at the equator, but as you move north or south this circle will turn into an ellipse with the long axis pointed north and south. At +/- 60 degrees latitude, the ellipse will be twice as tall as it is wide. Obviously not ideal, but it may work in your situation.

The other alternative is to reproject and store your dataset in PostgreSQL as a projected dataset with units of meters. This would distort your data if displayed on a map at large scale, but your search would then work as expected.

Fuqua

dwithin Does not work in MYSQL is there an alternative? – Plain

This is one reason why I usually steer people away from MySQL for spatial use. Your best bet is to calculate the distances to features yourself, using something like this: gis.stackexchange.com/questions/31628/… – Fuqua

Answer 2

by below mention code you can get all location within 50 to 110 meters as i use 0.001, you can use distance according to your requirements.

GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);
        factory.createPoint(new Coordinate(longitude,latitude));
        Point comparisonPoint = factory.createPoint(new Coordinate(longitude,latitude));
        predicates.add(SpatialPredicates.distanceWithin(cb, geom from db), comparisonPoint, 0.001));

distanceWithin() function take distance in degree which you have to cast to geograph but hibernate Spatial doesn't support so there is another way by which you can achieve this by providing distance in decimal according to your requirement.link is mention below for decimal points.

for decimal convertion LINK : https://en.wikipedia.org/wiki/Decimal_degrees

for creating geom and add extension in database LINK : https://www.compose.com/articles/geofile-everything-in-the-radius-with-postgis/

Chaise