Hibernate-Spatial Query for Points within an area

Wouter picture Wouter · May 2, 2013 · Viewed 7.2k times · Source

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.

Answer

lreeder picture lreeder · May 18, 2013

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.