Hibernate spatial API query for Oracle

Question

I tried to use spatial API in Spring boot.

I am able to save the SDO_GEOMETRY data types into oracle database.
I am able to retrieve it using SQL query,
Only problem is If I use with Hibernate APIs it throws an error (don't want to use SQL)

I tested following SQL query it works fine, so the problem not in database

SELECT
    s.ID,
    s.LOCATION
FROM PORTS s
where SDO_WITHIN_DISTANCE(
              s.LOCATION,
              SDO_GEOMETRY(2001, 8307,
                           SDO_POINT_TYPE( 24.817768,46.599417, NULL),NULL, NULL
                  ),
              'distance=10 unit=KM'
          ) = 'TRUE';

My Entity:

@Entity
@Table(name = "ports")
@Getter @Setter
@NoArgsConstructor
public class JpaPort {
    @Id
    @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
 @Column(name = "location",columnDefinition="SDO_GEOMETRY")
    private Geometry location;
    private String name;
}

My repository query:

 portRepository.findAll(filterWithinRadius(portDTO.getLat(), portDTO.getLon(), portDTO.getRangeInMeters()));

Specification for this:

public static Specification<JpaPort> filterWithinRadius(double latitude, double longitude, double radius) {
        return new Specification<JpaPort>() {
            @Override
            public Predicate toPredicate(Root<JpaPort> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
                GeometryFactory factory = new GeometryFactory();

                Geometry comparisonPoint = factory.createPoint(new Coordinate(latitude,longitude));

                comparisonPoint.setSRID(8307);

               Expression<Geometry> dbPoint = root.get("location").as(Geometry.class);
                Expression<Boolean> expression = builder.function("SDO_WITHIN_DISTANCE", boolean.class,
                        dbPoint, builder.literal(comparisonPoint),builder.literal("DISTANCE=1 UNIT=MILE"));
                return builder.equal(expression, true);
            }
        };
    }

If I'm using Hibernate I got following error

 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

 oracle.jdbc.OracleDatabaseException: ORA-01722: invalid number

I don't know my this approach is correct/wrong, because I'm new to spring development, if you have any other solution feel free to answer or comment.

Is there any other way to make the Spatial API query in Hibernate?

My source code - https://drive.google.com/file/d/1loSLFg3Cok9iwtv3apXP3f59QiJdI2eS/view?usp=sharing

return builder.equal(expression, true) - there is no boolean datatype in oracle, moreover, your initial SQL deals with varchar 'TRUE' — Andrey B. Panfilov
– Andrey B. Panfilov, Commented Aug 10, 2022 at 20:19
@Olivier ok got it. I'm new to spring development. I added the JPA tag — Ranjithkumar
– Ranjithkumar, Commented Aug 13, 2022 at 7:57
Now you should follow Andrey's advice and fix the return type of SDO_WITHIN_DISTANCE(). — Olivier
– Olivier, Commented Aug 13, 2022 at 8:06

Christian Beikov · Accepted Answer · 2022-08-15 11:49:20Z

2

+100

You have to compare the result against the varchar 'TRUE', just like in your SQL example. Use this:

Expression<String> expression = builder.function("SDO_WITHIN_DISTANCE", String.class,
    dbPoint, builder.literal(comparisonPoint),builder.literal("DISTANCE=1 UNIT=MILE"));
return builder.equal(expression, "TRUE");

answered Aug 15, 2022 at 11:49

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate spatial API query for Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related