Spring Boot空间查询报错：ST_MakePoint函数不存在

使用Spring Data JPA原生查询调用PostGIS空间函数时抛出异常，但直接在数据库执行相同语句可正常运行。

查询代码 #

@Query(nativeQuery = true, value = "SELECT * FROM locations WHERE ST_Contains(polygon, ST_Transform(ST_SetSRID(ST_MakePoint(:x, :y), 4326), 3785))")
List<Location> test(@Param("x") double x,@Param("y") double y);

异常信息 #

ERROR: function st_makepoint(double precision, double precision) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

已尝试配置 #

• 添加hibernate-spatial依赖
• 配置Hibernate方言：properties.put("hibernate.dialect", "org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect");

这个问题的核心是：数据库能自动隐式转换参数类型匹配ST_MakePoint函数，但Hibernate绑定原生查询的double参数时，会严格按传入类型传递，导致找不到对应函数重载。

推荐两种解决办法：

1. 给参数添加显式类型转换 #

在SQL语句中把:x和:y强制转换成PostGIS兼容的float8类型：

@Query(nativeQuery = true, value = "SELECT * FROM locations WHERE ST_Contains(polygon, ST_Transform(ST_SetSRID(ST_MakePoint(CAST(:x AS float8), CAST(:y AS float8)), 4326), 3785))")
List<Location> test(@Param("x") double x,@Param("y") double y);

2. 改用Hibernate Spatial的JPQL查询 #

既然已经引入hibernate-spatial依赖，直接用它封装的空间查询能力，让Hibernate自动处理参数类型映射：

@Query("SELECT l FROM Location l WHERE ST_Contains(l.polygon, ST_Transform(ST_SetSRID(ST_MakePoint(:x, :y), 4326), 3785))")
List<Location> test(@Param("x") double x,@Param("y") double y);

只需去掉nativeQuery=true，Hibernate会自动将double参数转换成PostGIS可识别的类型，避免原生查询的类型匹配问题。

如果偏好手动构建空间对象，也可以用原生API实现：

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.PrecisionModel;
import org.hibernate.spatial.transform.GeometryTransformer;
import javax.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;

// 在Repository接口中添加默认方法
default List<Location> test(double x, double y) {
    GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);
    Point point = factory.createPoint(new Coordinate(x, y));
    // 转换坐标到3785坐标系
    Point transformedPoint = (Point) new GeometryTransformer().transform(point, 3785);
    
    return entityManager.createQuery("SELECT l FROM Location l WHERE ST_Contains(l.polygon, :point)", Location.class)
            .setParameter("point", transformedPoint)
            .getResultList();
}

内容的提问来源于stack exchange，提问作者noob123