Блог

Всем привет Я отчаянно пытаюсь выполнить пользовательскую функцию в построителе критериев, чтобы получить ближайший город из точки, используя функцию Postgis ST_Distance, но, похоже, она не читает функцию, так как она возвращает мне эту ошибку:

 java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode   -[METHOD_CALL] MethodNode: 'function (ST_Distance)'   -[METHOD_NAME] IdentNode: 'ST_Distance' {originalText=ST_Distance}  -[EXPR_LIST] SqlNode: 'exprList'   -[DOT] DotNode: 'commune1_.geometrie' {propertyName=geometrie,dereferenceType=PRIMITIVE,getPropertyPath=geometrie,path=generatedAlias1.geometrie,tableAlias=commune1_,className=com.gu.domain.model.Commune,classAlias=generatedAlias1}  |  -[ALIAS_REF] IdentNode: 'commune1_.idcommune' {alias=generatedAlias1, className=com.gu.domain.model.Commune, tableAlias=commune1_}  | -[IDENT] IdentNode: 'geometrie' {originalText=geometrie}   -[METHOD_CALL] MethodNode: 'function (GeomFromText)'  |  -[METHOD_NAME] IdentNode: 'GeomFromText' {originalText=GeomFromText}  | -[EXPR_LIST] SqlNode: 'exprList'  |  -[NAMED_PARAM] ParameterNode: '?' {name=param1, expectedType=null}  | -[NUM_INT] LiteralNode: '4326'  -[NAMED_PARAM] ParameterNode: '?' {name=param2, expectedType=null}  [select generatedAlias0 from com.gu.domain.model.Commune as generatedAlias0 where ( ST_Distance(:param0, generatedAlias0.geometrie)=(select function('ST_Distance', generatedAlias1.geometrie, function('GeomFromText', :param1, 4326), :param2) from com.gu.domain.model.Commune as generatedAlias1) ) and ( generatedAlias0.dateModifgt;=:param3 )]  

Я думаю, что, возможно, Postgis или что-то еще не позволяет понять команду ST_Distance, но как я могу сделать это лучше ?

Моя пользовательская функция PostGISQueryBuilder для вычисления расстояния, которая возвращает двойное значение.

  public class PostGISQueryBuilder implements GisQueryBuilder {  private static final Logger logger = LoggerFactory.getLogger(PostGISQueryBuilder.class);   public static final int WGS84_SRID = 4326;  private static final String BBOX_FUNCTION = "BOX3D";  private static final String DISTANCE_SPHERE_FUNCTION = "distance_sphere";  private static final String DISTANCE_FUNCTION = "distance_sphere";  private static final String GEOM_FROM_TEXT_FUNCTION = "GeomFromText";  private static final String OVERLAPS_FUNCTION = "amp;amp;";  private static final String OVERLAPS_FUNCTION_OGC = "overlaps"; // for JPA..  private static final String POINT = "POINT";  private static final double RADIUS_OF_EARTH_IN_METERS = 6371008;   public Expressionlt;Doublegt; distanceSpheroidExpression(CriteriaBuilder criteriaBuilder,  Expressionlt;Geometrygt; locationExpression,  Point centroid) {  return criteriaBuilder.function(  "ST_Distance",  Double.class,  locationExpression,  criteriaBuilder.function(  GEOM_FROM_TEXT_FUNCTION,  Geometry.class,  criteriaBuilder.literal(new WKTWriter(2).write(centroid)),  criteriaBuilder.literal(WGS84_SRID)),  criteriaBuilder.literal(true));   } }  

GisQueryBuilder, который расширяет PostGisQueryBuilder

 public interface GisQueryBuilder {  Expressionlt;Doublegt; distanceSpheroidExpression(CriteriaBuilder criteriaBuilder,  Expressionlt;Geometrygt; locationExpression,  Point centroid);   Predicate withinPredicate(CriteriaBuilder criteriaBuilder,  Expressionlt;Geometrygt; locationExpression,  Point centroid,  Double numericDistanceInMeters);  }    

The criteria Builder

 @Override  public Commune getClosestCommune(Geometry geometrie) {   CriteriaBuilder cb = getCriteriaBuilder();  CriteriaQuerylt;Communegt; cq = cb.createQuery(Commune.class);  Rootlt;Communegt; commune = cq.from(Commune.class);    Subquerylt;Doublegt; minDistance = cq.subquery(Double.class);  Rootlt;Communegt; rootCommuneSubquery = minDistance.from(Commune.class);   Point pointGeometry = geometrie.getCentroid();     DistancePredicate distance = new DistancePredicate((CriteriaBuilderImpl) cb, geometrie ,commune.get(Commune_.geometrie), null);     PostGISQueryBuilder postgis = new PostGISQueryBuilder();  minDistance.select(postgis.distanceSpheroidExpression((CriteriaBuilderImpl) cb, rootCommuneSubquery.get(Commune_.geometrie), pointGeometry));    cq.select(commune).where(cb.and(cb.equal(distance, minDistance)),  cb.greaterThanOrEqualTo(commune.get(Commune_.dateModif), dateReference));    return getSingleResult(cq);