Hibernate Spatial and legacy db

105 views Asked by At

Background:

I have legacy db and I cannot change its schema. Mapping of my entity looks like:

class SomeObject {
    @Column(name = "LONGITUDE", nullable = false)
    public BigDecimal longitude;

    @Column(name = "LATITUDE", nullable = false)
    public BigDecimal latitude;

    ...
}

Problem:

I need to create hibernate query which will select all SomeObjects in a circle. I through hibernate spatial should be right tool to solve this problem. But, all examples which I can find declare column of type Point instead of using separate columns for coordinates as I have. Since I cannot change db schema such approach will not work for me.

So, can I use hibernate spatial to solve this problem? If yes how query will look like?

Solution:

Karthik Prasad clearly and correctly described what is spatial and what options I have in general. Since, I am using Oracle DB I had one more option: use sdo_geom package. So, at the end instead of hibernate query/criteria I used SQL query:

select * 
from SOMETHING
where 
-- add box to decrease number of records
-- where box is radius converted to degree by multiply on (1. / 69.) * 1.1 
:lon - :box < longitude and longitude < :lon + :box and 
:lat - :box < latitude and latitude < :lat + :box and 
sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, null, sdo_elem_info_array(1, 1, 1), sdo_ordinate_array(longitude, latitude)),
  sdo_geometry(2001, 4326, null, sdo_elem_info_array(1, 1, 1), sdo_ordinate_array(:lon, :lat)),
  1, 'unit=mile') < :radius
1

There are 1 answers

1
Karthik Prasad On BEST ANSWER

Hibernate Spatial is specifically operates on Database having type Geometry with/without Spatial Indexes. Only options I think off is create clone table with another column added with geometry type point(or as per your usecase) your database enabled with Spatial extension/module.