Specification/Predicate for Hibernate/spatial to filter an Entity whether it is in radius or not

Question

I would like to rewrite the following query in a Predicate/Specification so that i can link them.

this query filters all my defined OptEvent Entitys within a specific area

@Query(value = "SELECT * FROM opt_event WHERE ST_DWithin(cast(opt_event.locationpoint as geography),ST_SetSRID(ST_Point(?2, ?1),4326), 100000);", nativeQuery = true)
    public Set<OptEvent> findAllEventsInRange(double longitude, double latitude);

normally I write a specification like this, and can string them together later. Depending on whether a filter is applied or not.

public static Specification<OptEvent> filterArea(Double longitude, Double latitude) {
        return new Specification<OptEvent>() {
            @Override
            public Predicate toPredicate(Root<OptEvent> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

            SpatialRelateExpression geomExpression = SpatialRestrictions.within("locationpoint ", area);
            //this is the only thing i could find, but i have no idea if its the right path or how i can transform it to the Specification
            // what is area ?

                //Example when i filter for the destination in the OptEvent
                //return (root, query, builder) -> builder.like(builder.upper(root.get("destination")),
                //"%" + destination.toUpperCase() + "%");

            }
        };
    }

further information regarding my environment. My pom below. I use Postgres and Hibernate 5.4.10.Final.

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            //Version 5.4.10 FINAL from Hibernate-Core
        </dependency>
        <dependency>
            <groupId>com.vividsolutions</groupId>
            <artifactId>jts</artifactId>
            <version>1.13</version>
        </dependency>
    </dependencies>

for my location point i use org.locationtech.jts.geom.Point;

@Entity
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
public class OptEvent {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String destination;
    private String description;
    private int year;
    private EventSeries eventSeries;
    private LocalDate eventDate;


    @JsonSerialize(using = GeometrySerializer.class)
    @JsonDeserialize(contentUsing = GeometryDeserializer.class)
    private Point locationpoint;

    @OneToMany(mappedBy = "event", cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonIgnoreProperties("event")
    private Set<OptResult> results = new HashSet<>();

    public OptEvent() {

    }

Coagulant

Answer 1

I have now found out the answer myself.

public static Specification<Event> filterWithinRadius(double longitute, double latitude, double radius) {
    return new Specification<Event>() {
        @Override
        public Predicate toPredicate(Root<Event> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            GeometryFactory factory = new GeometryFactory();
            Point comparisonPoint = factory.createPoint(new Coordinate(latitude, longitute));
            return SpatialPredicates.distanceWithin(builder, root.get("location"), comparisonPoint, radius);
        }
    };
}

Important SpatialPredicates was first missing in version 5.4.10 FINAL, so I had to bring the dependency to 5.4.14 FINAL.

<dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-spatial</artifactId>
        <version>5.4.14.Final</version>
    </dependency>

To rebuild the function 1 to 1 as in the initial question the following structure is the correct one. Otherwise the conversion from geometry to geography is missing

public static Specification<Event> filterWithinRadius(double longitute, double latitude, double radius) {
    return new Specification<Event>() {
        @Override
        public Predicate toPredicate(Root<Event> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

            Expression<Geometry> geography = builder.function("geography", Geometry.class, root.get("location"));
            Expression<Point> point = builder.function("ST_Point", Point.class, builder.literal(longitute),
                    builder.literal(latitude));
            Expression<Point> comparisonPoint = builder.function("ST_SetSRID", Point.class, point,
                    builder.literal(4326));
            Expression<Boolean> expression = builder.function(SpatialFunction.dwithin.toString(), boolean.class,
                    geography, comparisonPoint, builder.literal(radius));
            return builder.equal(expression, true);
        }
    };
}

Coagulant

Is this final solution? or you have any other updated code? – Kinakinabalu

It was my final solution. Since then I have not worked much on the project and therefore have no update. – Coagulant

I got an error in the first expression- ORA-00904: "GEOGRAPHY": invalid identifier Could you help? – Kinakinabalu

I didn't know about the SpatialPredicates, that's so cool! I was using the CriteriaBuilder and building the query myself, but this is much better!! Thank you! – Pape

what is "4326"? "radius" in what units? – Adna

what to do if getting error in mysql: Caused by: java.sql.SQLSyntaxErrorException: FUNCTION db.dwithin does not exist – Adna